MySQLで各IDから前の2件の合計を取得したい

Question

以下のようなテーブルがあります。

Before

id	sale	date
1	1200	2020-11-11
2	3500	2020-11-12
3	400	2020-11-13
4	5000	2020-11-14
5	4000	2020-11-15
6	15000	2020-11-16

ここから前2件の合計を取得したいのです。具体的には以下の通りです
例えばid = 3 の行に id=1とid=2のsaleの合計が入る感じです。

After

id	sale	date	sum
1	1200	2020-11-11	Null
2	3500	2020-11-12	Null
3	400	2020-11-13	5100
4	5000	2020-11-14	8900
5	4000	2020-11-15	9400
6	15000	2020-11-16	24000

試したクエリ

select *,
(select sum(F.id) FROM Sale AS F WHERE id IN (F.id, (F.id + 1))) AS 'sum'
FROM Sale;

サブクエリでやってみたのですが、sumが全ての合計になってしまっており、目的の値が取得できませんでした。
どのように修正したら良いでしょうか？

Comments

[takanaweb5]

mysqlのバージョンはいずれでしょうか？
またidは連番で歯抜けはないのでしょうか？

[LOTOyamaguchi]

idは連番です。バージョンは5.7です

Answer 1

外出していて、遅れました。
すみません、自己解決しました。id>=3の時にはNull
にしてテーブル名に適切な名前をつけて上げればできました。

select *,
CASE 
 WHEN id >= 3
 THEN (select SUM(sale) FROM ResultNumbers3 AS F WHERE F.id IN(ResultNumbers3.id, ResultNumbers3.id-1, ResultNumbers3.id-2))
 ELSE Null
END
FROM ResultNumbers3;

Answer 2

こんにちわ。

ここから前2件の合計を取得したいのです。具体的には以下の通りです
例えばid = 3 の行に id=1とid=2のsaleの合計が入る感じです。

という説明と、Afterで提示された希望の出力例に齟齬があるようですが、

どちらが正しいのでしょうか。

*** 以下追記

コメントを受けてクエリを考えました。以下でどうでしょう。

-- 修正版 実装に依存していた書き方を改めた
select 
    test.id,
    test.sale,
    test.dt as 'date',
    case when tmp.cnt < 3 then null else tmp.gokei end as 'sum' 
from test inner join 
(
    select 
        a.id, 
        sum(b.sale) as gokei,
        count(b.id) as cnt 
    from test as a inner join test as b 
    on (b.id >= a.id - 2 and b.id <= a.id)
    group by a.id 
) tmp 
on (test.id = tmp.id)

実行結果は以下のとおりです。

(テーブル名はtestとしています。
フィールド名dateは予約語の可能性があるのでdtに変更しています。)

id      sale    date	       sum	
1       1200    2020-11-11     NULL
2       3500    2020-11-12     NULL
3       400     2020-11-13     5100	
4       5000    2020-11-14     8900	
5       4000    2020-11-15     9400	
6       15000   2020-11-16     24000	

Comments

[LOTOyamaguchi]

id =3 の行のsum に id =1とid=2とsaleの合計が入るという意味でした。
id =1とid=2の行は前2件のデータがないのでNullが入っています
説明が拙くて申し訳ないですが、afterの状態が正しいです

[srsnsts]

返信ありがとうございます。

私が気になっているのはそこじゃないんですよ。

Afterの3行目のsumは、1行目から3行目までのsaleの合計、

すなわち、1200+3500+400=5100となっています。

しかし、これは

> id =3 の行のsum に id =1とid=2とsaleの合計が入る

という説明と矛盾しますよね、どちらが正しいのですか、

という質問なんです。

[LOTOyamaguchi]

すみません、afterのが正しいです。
id1.2.3の合計でした。訂正します

[srsnsts]

返信ありがとうございます。

> すみません、afterのが正しいです。
> id1.2.3の合計でした。訂正します

クエリを考えてみますね。

[srsnsts]

すみません、
クエリを書いたのですが実装に依存した書き方をしてしまいました。
修正したクエリを載せますね、もうしばらくお待ち下さい。

[srsnsts]

お待たせしました。
実装に依存していたSQL文を修正し、アップしましたので
ご確認下さい。