質問: SQLで各レコードの過去直近7日間のデータを集計したいです。 使用DBはSQLite3です。 具体例だと 日付 | 歩数 ----------------------- 2018-01-01 | 6000 2018-01-02 | 5500 2018-01-03 | 200 2018-01-04 | 9700 2018-01-05 | 1100 2018-01-06 | 5000 2018-01-07 | 800 2018-01-08 | 3600 2018-01-09 | 6000 2018-01-10 | 0 というデータに対して 日付 | 過去1週間の平均歩数 ----------------------- 2018-01-08 | 3187 2018-01-09 | 3700 2018-01-10 | 3771 のように集計がしたいです。(2018-01-01から2018-01-07の集計はできてもできなくてもよいです。) この集計を1つのSQLで書けないでしょうか。
回答: 相関サブクエリを使えばわかりやすいでしょうか ``` SELECT 日付, (SELECT AVG(t2.歩数) FROM t AS t2 WHERE date(t1.日付, '-7 days') <= t2.日付 AND t2.日付 < t1.日付) AS 過去1週間の平均歩数 FROM t AS t1; ``` 行数が多くパフォーマンスが問題になる場合は自己結合しつつ集計することでしょうか ``` SELECT t1.日付, AVG(t2.歩数) AS 過去1週間の平均歩数 FROM t AS t1 INNER JOIN t AS t2 ON date(t1.日付, '-7 days') <= t2.日付 AND t2.日付 < t1.日付 GROUP BY t1.日付; ```