昨日の記事では、月毎のデータをクロス表に変換しました。
SQLでは「日毎」のデータを「月毎」に変換する、というのもよくあるシナリオなので書いておきます。
下記のような日毎の売り上げデータ(のようなもの)を想定します。
例えば月毎の総売り上げを集計したいときは次のようにします。
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])
ポイントは年と月それぞれ別のものとみなして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
実際にはOrderMonth
はdate
型でクライアントサイドに返ります。
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 |
「いやいや、あくまで月毎だから'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_WS
はSQL 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 |