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