[SQL Server]データを期間別に集計する

SQL Serverで日・週・月・年別の集計表を出力する方法

SQL Serverでは、DATETIME型で管理している日付データを CONVERT関数やDATEPART関数を活用することで、 日次・週次・月次・年次の集計結果を簡単に出力できます。 本記事では、それぞれの集計方法をサンプルクエリとともに解説します。

SQL Server　Transact-SQLプログラミング　実践開発ガイド [ 五十嵐 貴之 ]

created by Rinker

• Kindle
• Amazon
• 楽天市場
• Yahooショッピング

集計方法一覧

集計には主に2つのアプローチがあります。

• CONVERT関数：日付を文字列に変換してグループ化する方法
• DATEPART関数：年・月・日などの数値を個別に取得してグループ化する方法

① 日次集計

1日単位で件数を集計します。

-- 日次集計（CONVERT を使用）
-- 書式コード 111 は「yyyy/mm/dd」形式で出力
SELECT
    CONVERT(VARCHAR, 日付, 111) AS 日次
  , COUNT(*)                   AS 件数
FROM テーブル名
GROUP BY CONVERT(VARCHAR, 日付, 111)
ORDER BY 日次;

-- 日次集計（DATEPART を使用）
-- 年・月・日を個別の列として取得
SELECT
    DATEPART(YEAR,  日付) AS 年
  , DATEPART(MONTH, 日付) AS 月
  , DATEPART(DAY,   日付) AS 日
  , COUNT(*)              AS 件数
FROM テーブル名
GROUP BY
    DATEPART(YEAR,  日付)
  , DATEPART(MONTH, 日付)
  , DATEPART(DAY,   日付)
ORDER BY 年, 月, 日;

② 週次集計

1週間単位で件数を集計します。
DATEPART(WEEK, ...)は年をまたぐ場合に週番号がリセットされるため、 年をまたぐ集計ではDATEPART(YEAR, ...)を組み合わせることを推奨します。

-- 週次集計（DATEPART を使用）※日曜日始まり
-- 年をまたぐ場合に備えて YEAR も併せて取得
SELECT
    DATEPART(YEAR, 日付) AS 年
  , DATEPART(WEEK, 日付) AS 週番号
  , COUNT(*)             AS 件数
FROM テーブル名
GROUP BY
    DATEPART(YEAR, 日付)
  , DATEPART(WEEK, 日付)
ORDER BY 年, 週番号;

SET DATEFIRST 1; -- 1:月曜, 7:日曜（デフォルト）

③ 月次集計

1か月単位で件数を集計します。

-- 月次集計（CONVERT を使用）
-- SUBSTRING で「yyyy/mm」の7文字を切り出してグループ化
SELECT
    SUBSTRING(CONVERT(VARCHAR, 日付, 111), 1, 7) AS 年月
  , COUNT(*)                                      AS 件数
FROM テーブル名
GROUP BY SUBSTRING(CONVERT(VARCHAR, 日付, 111), 1, 7)
ORDER BY 年月;

-- 月次集計（DATEPART を使用）
SELECT
    DATEPART(YEAR,  日付) AS 年
  , DATEPART(MONTH, 日付) AS 月
  , COUNT(*)              AS 件数
FROM テーブル名
GROUP BY
    DATEPART(YEAR,  日付)
  , DATEPART(MONTH, 日付)
ORDER BY 年, 月;

④ 年次集計

1年単位で件数を集計します。

-- 年次集計（CONVERT を使用）
-- SUBSTRING で「yyyy」の4文字を切り出してグループ化
SELECT
    SUBSTRING(CONVERT(VARCHAR, 日付, 111), 1, 4) AS 年
  , COUNT(*)                                      AS 件数
FROM テーブル名
GROUP BY SUBSTRING(CONVERT(VARCHAR, 日付, 111), 1, 4)
ORDER BY 年;

-- 年次集計（DATEPART を使用）
SELECT
    DATEPART(YEAR, 日付) AS 年
  , COUNT(*)             AS 件数
FROM テーブル名
GROUP BY DATEPART(YEAR, 日付)
ORDER BY 年;

まとめ

シンプルに出力したい場合はCONVERT、 集計結果を数値として後続処理で扱いたい場合はDATEPARTを選ぶと良いでしょう。