新しいことにはウェルカム

技術 | 電子工作 | ガジェット | ゲーム のメモ書き

SQL Server で月・週ごとに集計する方法

データを月・週ごとに集計するには、まず日付の月・週の頭の日付を求め、その頭の日付でGROUPします。

Google BigQueryなど、頭の日付取得にDATE_TRUNC()関数が使える場合は、下記のような感じで集計できます。

SELECT
    DATE_TRUNC(dt, MONTH) AS dt_month
    ,SUM(sales) AS sales
FROM tbl
GROUP BY dt_month

しかし、SQL ServerにはDATE_TRUNC()のような、日付を切り詰める関数がないので、自分で求める必要があります。

月で集計

やり方はいくつかあるのですが、FORMAT()関数を使うのが見た目的に分かりやすいかと思います。

FORMAT()関数を使って、手動で日付を「1日」にしてしまいます。

例えば、月で集計するには下記のようになります。

WITH

tmp AS
(
SELECT
    CAST(
        FORMAT(dt, 'yyyy-MM-01')
        AS DATE
    ) AS dt_month
    ,sales
FROM tbl
)

SELECT
    dt_month
    ,SUM(sales) AS sales
FROM tmp
GROUP BY dt_month

週で集計

週はややこしいです…。

DATEPART(WEEKDAY,<dt>)で曜日番号(日曜が1、月曜が2、火曜が3…)が求まるので、「曜日番号-1」だけ日付を移動させて日曜日の日付にすることにより、週の頭を求めます。

頭の曜日を変更するには、日曜との差分だけずらしておいた上で、前述の「曜日番号-1」だけ日付を移動させます。

例えば、火曜日頭で集計するには下記のようになります。

WITH

tmp AS
(
SELECT
    DATEADD(
        DAY
        ,-(DATEPART(
            WEEKDAY
            ,DATEADD(
                DAY
                ,-2 /* 火曜を頭にする場合 */
                ,dt 
            )
        ) - 1)
        ,dt
    ) AS dt_week
    ,sales
FROM tbl
)

SELECT
    dt_week
    ,SUM(dt_week) AS sales
FROM tmp
GROUP BY dt_week

頭にしたい曜日「日,月,火,...」に合わせて「0,-1,-2,...」と値を変えて使います。

サンプルは火曜日頭なので「-2」になっています。

DATEPART(WEEKDAY,<dt>)は、デフォルトでは、日曜日から順に「1,2,3,4,5,6,7」となりますが、これは環境・設定で変わることがあります。 サンプルはデフォルト想定のコードなので、デフォルトと異なる場合はそれに合わせて修正してください。

週番号を使う

DATEPART(WEEK,<dt>)を使い、曜日番号ではなく、週番号を使って集計するやり方もありますが、週番号は年の範囲内で発行されるので、 GROUPは年かつ週番号で行う必要があります。

また、1月1日の週と、12月31日の週の日数が7日間未満になることがあるので、注意が必要です。(よく忘れます…)

感想など

SQL Serverは、GROUPにASで付けた別名を使えないので、計算結果をGROUPに使うときはちょっと面倒ですね。

週頭を求める式を1から作ろうとすると、一瞬考えてしまうので、何も考えずコピペして使っています。

DATE_TRUNC()実装してくれればいいのになぁ。