週別集計をSQLで行う
日別、月別、年別の集計は簡単ですが、週別になった途端に「ん？」となってしまったので、今回は週別集計をSQLでやってみたいと思います。SQL Server 版、Oracle 版の両方でやってみます。
やりたいこと
週単位の集計には２パターンがあると思います。ひとつが「週の先頭を日曜日とした週別の集計」です。もうひとつが「最終日から数えて７日単位の集計 」です。
前者は、こんな感じです。
日付は降順にしていますので日曜日が最後に見えますが、日曜日は週の始めです。
後者はこんな感じです。
直近１週間（の繰り返し）といったイメージでしょうか。週別といえば前者の方が多いかもしれませんね。
テストデータ
テストデータとしては前項のサンプルと同じ値でテーブル「集計テスト」に格納しておきます。DDLは省略しますが、項目は「日付」「数量」のみです。
SELECT * FROM 集計テスト ORDER BY 日付 DESCSQL Management Studio でテストデータを取得した結果です。
Oracle SQL Developer でテストデータを取得した結果です。
週の先頭を日曜日とした週別の集計
これは、Google 先生に聞いてみるとたくさん出てきます。曜日を表す数値－１（日曜日を１とする場合）を該当する日付から減算すればすべて日曜日の日付になる、という性質を利用するものです。
SQL Server 版
SQL Server で曜日を取得するためには、DATEPART 関数を利用します。第一引数に weekday を指定することで日曜日を1とした連番で曜日が取得できます。各日付から曜日－１を減算すれば週の開始日（日曜日）になります。日付の減算は、DATEADD 関数を利用します。
WITH T AS (
SELECT DATEPART(WEEKDAY,日付) 曜日, 日付, 数量 FROM 集計テスト
)
SELECT DATEADD(DAY,-(曜日-1),日付) 週開始日,SUM(数量) 週計
FROM T
GROUP BY DATEADD(DAY,-(曜日-1),日付)
ORDER BY DATEADD(DAY,-(曜日-1),日付) DESC
Oracle 版
考え方は、SQL Server 版と同じです。曜日を取得するのは、TO_DATE 関数を利用します。また日付の加減算は Oracle の場合はそのまま加減算します。
WITH T AS (
SELECT TO_NUMBER(TO_CHAR(日付,'D')) 曜日, 日付, 数量 FROM 集計テスト
)
SELECT 日付 - (曜日-1) 週開始日,SUM(数量) 週計
FROM T
GROUP BY 日付 - (曜日-1)
ORDER BY 日付 - (曜日-1) DESC
最終日から数えて７日単位の集計
これは少し工夫が必要です。日付の降順で７日間単位で曜日番号のような相対日数を取得する必要があります。〇〇単位の数字といえば剰余です。日付の降順で割り振った連番と剰余を使えば相対日数が求められます。
SQL Server 版
日付の降順に並べて ROW_NUMBER 関数を使って０からの連番を振り、７で割った余りが曜日番号相当の相対日数になります。勿論、日付を降順にした連番から導出するので前項の曜日番号とは全く別物です。また降順であることから週の始めの日は剰余を「加算」することで求められます。
WITH T AS (
SELECT (ROW_NUMBER() OVER ( ORDER BY 日付 DESC ) - 1 ) % 7 AS 相対日数
, 日付
, 数量
FROM 集計テスト
)
SELECT DATEADD(DAY,相対日数,日付) 週開始日,SUM(数量) 週計
FROM T
GROUP BY DATEADD(DAY,相対日数,日付)
ORDER BY DATEADD(DAY,相対日数,日付) DESC
Oracle 版
こちらも考え方は同じです。ROW_NUMBER 関数で０からの連番を振り、７で割った余りを利用します。Oracle の場合は剰余は MOD 関数を利用します。
WITH T AS (
SELECT MOD((ROW_NUMBER() OVER ( ORDER BY 日付 DESC ) - 1 ) , 7) AS 相対日数
, 日付
, 数量
FROM 集計テスト
)
SELECT 日付 + 相対日数 週開始日,SUM(数量) 週計
FROM T
GROUP BY 日付 + 相対日数
ORDER BY 日付 + 相対日数 DESC