SQLで小計と合計を出力する（GROUP BY ROLLUP)

Synapse Analytics 専用SQLプールはSQL Serverと高い互換性を持っている一方で、一部使えないSQL構文もあります。
ただ、GROUP BYオプションでROLLUPはサポートされているようです。

すこし、案件でこのGROUP BY ROLL UPを使う機会がありまして、便利だなと思ったのでこちらで紹介します。

用途

対象のデータの小計や総計（合計）を取得する。
例えば、ORDERSと言うテーブルにはO_ORDERDATEと言うdate型のカラムがあります。
年毎、月毎にどれくらいの件数があって、全体で何件ぐらいあるのかと言うのを調べるときに、このGROUP BY ROLL UPを使うといとも簡単に出力することが出来ます。

ORDERS全体件数

select
	count(*) cnt
from
	dbo.ORDERS
;

--結果
cnt
150000000

ORDERSのO_ORDERDATEの年ごとの件数

select
	 YEAR(O_ORDERDATE) YEAR
	,count(*) cnt
from
	dbo.ORDERS
group by
	YEAR(O_ORDERDATE)
order by
	YEAR
;

--結果
YEAR	cnt
1992	22810359
1993	22751520
1994	22760819
1995	22754973
1996	22819651
1997	22760815
1998	13341863

ORDERSのO_ORDERDATEの月ごとの件数

select
	 YEAR(O_ORDERDATE) YEAR
	,MONTH(O_ORDERDATE)  MONTH
	,count(*) cnt
from
	dbo.ORDERS
group by
	 YEAR(O_ORDERDATE)
	,MONTH(O_ORDERDATE)
order by
	 YEAR
	,MONTH
;

--結果
YEAR	MONTH	cnt
1992	1	1932789
1992	2	1806307
1992	3	1930727
1992	4	1869426
1992	5	1931316
1992	6	1870607
1992	7	1932411
1992	8	1933741
1992	9	1868410
1992	10	1931810
1992	11	1870784
1992	12	1932031
1993	1	1934070
--省略

使い方

SELECT文のGROUP BY句にROLLUPを追加
実際に先ほど出てきたORDERSテーブルを使って、O_ORDERDATEを全体の総計、年毎、月毎にROLLUPを使って集計してみます。

ROLLUPを使って、ORDERSのO_ORDERDATEの合計、年毎、月毎の小計を出力

select
	 YEAR(O_ORDERDATE) YEAR
	,MONTH(O_ORDERDATE)  MONTH
	,count(*) cnt
from
	ORDERS
group by rollup(
	 YEAR(O_ORDERDATE)
	,MONTH(O_ORDERDATE)
	)
order by YEAR,MONTH;

--結果
YEAR	MONTH	cnt
NULL	NULL	150000000
1992	NULL	22810359
1992	1	1932789
1992	2	1806307
1992	3	1930727
1992	4	1869426
1992	5	1931316
1992	6	1870607
1992	7	1932411
1992	8	1933741
1992	9	1868410
1992	10	1931810
1992	11	1870784
1992	12	1932031
1993	NULL	22751520
1993	1	1934070
1993	2	1746810
1993	3	1933438
--省略

このように、小計や総計も取得して調べたい場合、ROLLUPを知っているととても便利である事がわかります。

備考

ちなみに、上記の例では年毎、月毎の小計と総計をそれぞれ出力していますが、年毎だけでROLLUPするとこんな感じになります。

ROLLUPを使って、ORDERSのO_ORDERDATEの合計、年毎の小計を出力

select
	 YEAR(O_ORDERDATE) YEAR
	,count(*)  cnt
from
	ORDERS
group by rollup(
	 YEAR(O_ORDERDATE)
	)
order by YEAR;

--結果
YEAR	cnt
NULL	150000000
1992	22810359
1993	22751520
1994	22760819
1995	22754973
1996	22819651
1997	22760815
1998	13341863

便利です。ちょっと便利になる小技です。