SQL Server(T-SQL)にて、日毎のデータを月毎で集計する

昨日の記事では、月毎のデータをクロス表に変換しました。

mrgchr.hatenablog.com

SQLでは「日毎」のデータを「月毎」に変換する、というのもよくあるシナリオなので書いておきます。

下記のような日毎の売り上げデータ(のようなもの)を想定します。

f:id:mrgchr:20190919235606p:plain

例えば月毎の総売り上げを集計したいときは次のようにします。

SELECT
    DATEPART(YEAR, [OrderDate]) as 'OrderYear' 
  , DATEPART(MONTH, [OrderDate]) as 'OrderMonth'
  , Sum([LineTotal]) as 'MonthlySales'
FROM [dbo].[Orders]
GROUP BY DATEPART(YEAR, [OrderDate]), DATEPART(MONTH, [OrderDate])
ORDER BY DATEPART(YEAR, [OrderDate]), DATEPART(MONTH, [OrderDate])

f:id:mrgchr:20190920000004p:plain

ポイントは年と月それぞれ別のものとみなしてGROUP BYを行っていることです。
SQLに慣れない人はこういう挙動に少し戸惑うようですね。

バリエーション

レポートとして出力するだけでなく、アプリケーションで処理する場合はdate型で返したほうが良いかもしれません。

SELECT
    DATEFROMPARTS(DATEPART(YEAR, [OrderDate]), DATEPART(MONTH, [OrderDate]), 1) as 'OrderMonth'
  , Sum([LineTotal]) as 'MonthlySales'
FROM [Sample].[dbo].[Orders]
GROUP BY DATEPART(YEAR, [OrderDate]), DATEPART(MONTH, [OrderDate])
ORDER BY OrderMonth

実際にはOrderMonthdate型でクライアントサイドに返ります。

OrderMonth MonthlySales
2019-01-01 752969
2019-02-01 664594
2019-03-01 668248
2019-04-01 718640
2019-05-01 721797
2019-06-01 720968
2019-07-01 743649
2019-08-01 590816

docs.microsoft.com

「いやいや、あくまで月毎だから'2019-01'表記にこだわりたい」という人は次のようにすると良いです。

SELECT
  CONCAT_WS('-', DATEPART(YEAR, [OrderDate]), FORMAT(DATEPART(MONTH, [OrderDate]), '00')) as 'OrderMonth'
  , Sum([LineTotal]) as 'MonthlySales'
FROM [Sample].[dbo].[Orders]
GROUP BY DATEPART(YEAR, [OrderDate]), DATEPART(MONTH, [OrderDate])
ORDER BY DATEPART(YEAR, [OrderDate]), DATEPART(MONTH, [OrderDate])

CONCAT_WSSQL Server 2017から追加された割と新しめの関数で、セパレーター付き(With Separator)のCONCAT関数です。 FORMAT関数で月の先頭を0埋めしています。

OrderMonth MonthlySales
2019-01 752969
2019-02 664594
2019-03 668248
2019-04 718640
2019-05 721797
2019-06 720968
2019-07 743649
2019-08 590816

docs.microsoft.com