ユニークビジョン株式会社 Advent Calendar 2019の記事です。
ここ最近、データ出しの依頼や集計機能の実装など、業務で必要になることが多かったので、今回はSQLでの集計について書こうと思います。
当方はPostgreSQLを使うことが多いためこれを基準に書きますが、多くのRDBMSで同様の行為が行えるはずです。
1 単純集計
単純集計とは1つ1つの項目に標本nの内どれだけが該当したかを集計する方法です。
例えば、朝食を取っているかという質問に対する回答を単純集計表にするとこんな感じ。
表1 単純集計例
| 回答 | 回答数 | 割合 |
|---|---|---|
| 欠かさない | 6,155 | 88.3% |
| 欠食する | 807 | 11.6% |
| 全体 | 6,962 | 100% |
このようなものを単純集計と呼ぶそうです。
一応、厚生労働省 平成29年国民健康・栄養調査報告からデータを持ってきたのですが、端数の取り扱いの関係で割合の合計が合いませんね。まぁ、今回は大きな問題ではないです。
ちなみに、厚労省的には「何も食べない」「錠剤などのみ」に加え「菓子・果物などのみ」も欠食に入るらしいです。
私は文句なしの欠食ですね。
これをSQLで出すならばおそらくこんな感じ。
かなり適当ですがこのようなテーブルを用意。
CREATE TABLE public.peoples(
id BIGSERIAL NOT NULL
,breakfast_flag BOOLEAN -- 食べる(t) or 食べない(f)
,PRIMARY KEY(id)
);
一応IDも置いて主キーにしておきましょう。ここに上記の6,962人分の情報が入っているとします。
ここから朝食フラグを取り出し、集約してcountすることで各回答の回答者数が出せます。
SELECT
breakfast_flag
,COUNT(id) -- 回答数
FROM
public.peoples
GROUP BY
breakfast_flag
UNION ALL
SELECT
NULL AS breakfast_flag
,COUNT(id)
FROM
public.peoples
;
これで出る結果はこんな感じ。割合も一緒に出そうとすると面倒なので今回は申し訳ありませんが割愛しています。
全件のCOUNTをUNIONして合計数を出しています。
合計数を出すにはもっと便利な方法がありますがそれは後述。
表2 SQLによる単純集計結果
| breakfast_flag | count |
|---|---|
| TRUE | 6,155 |
| FALSE | 807 |
| [NULL] | 6,962 |
このように単純集計は変わったことをせず普通にSQLで出せます。
2 クロス集計
クロス集計とは複数条件を持った集計表を出す集計方法です。
上記の単純な回答数の結果をさらに性別や年齢で分類すると表現できます。
表3 クロス集計例
| 男性 | 女性 | |
|---|---|---|
| 欠かさない | 2,867 | 3,288 |
| 欠食 | 452 | 355 |
| 全体 | 3,319 | 3,643 |
ちなみに、一般的な表計算ソフトは読み込んだデータからクロス表を出力する機能を普通は持っているため、これを利用できるならこちらを使った方が楽だと思う。
googleスプレッドシートやExcelならピボットテーブルという機能です。
それは置いておいて、SQLで表現しましょう。
2-1 CASE式
よくやるのはCASE式を使った方法です。
このようなテーブルがあると想定します。
CREATE TABLE peoples(
id BIGSERIAL NOT NULL
,breakfast_flag BOOLEAN -- 食べる(t) or 食べない(f)
,gender TEXT -- 男性(m) or 女性(f)
,PRIMARY KEY(id)
);
先ほどのものに性別カラムが増えました。
これに対しこのようなSQLを実行します。
SELECT
breakfast_flag
,SUM(CASE gender WHEN 'm' THEN 1 ELSE 0 END) AS male
,SUM(CASE gender WHEN 'f' THEN 1 ELSE 0 END) AS female
FROM
public.peoples
GROUP BY
breakfast_flag
UNION ALL
SELECT
NULL AS breakfast_flag
,SUM(CASE gender WHEN 'm' THEN 1 ELSE 0 END) AS male
,SUM(CASE gender WHEN 'f' THEN 1 ELSE 0 END) AS female
FROM
public.peoples
;
この方法の良いところは出力結果がそのままクロス集計表の形で得られることだと思います。
表4 CASE式によるクロス集計結果
|breakfast|male|female|
|:-:|:-:|:-:|:-:|
|TRUE|2,867|3,288|
|FALSE|452|355||
|[NULL]|3,319|3,643|
SQLの実行結果を大した後処理もなく利用できます。
DBからの単純なデータ抽出の依頼ならDBツールでCSVに出力すればほとんど仕事は終わりです。
2-2 クロステーブル
他の方法として、プログラマのためのSQL 第４版ではクロステーブルを作成する方法も記載されていました。
良書です。読みましょう。
この方法は使ったことがなかったので実際に動かしてみました。
製品名、製品価格、販売個数、販売年というカラムを持つsalesテーブルを用意します。
CREATE TABLE public.sales (
product_name TEXT NOT NULL DEFAULT '' -- 製品名
,product_price NUMERIC NOT NULL DEFAULT 0 -- 価格
,order_qty BIGINT NOT NULL DEFAULT 0 -- 注文数量
,sales_year BIGINT NOT NULL DEFAULT 0 -- 売り上げ年
);
こんなデータも放り込んでおきましょう。適当です。
INSERT INTO public.sales VALUES
('a',100,25,1990),
('a',100,250,1991),
('a',100,348,1993),
('b',180,125,1990),
('c',230,400,1992),
('d',520,420,1994);
こんなテーブルになりました。
| product_name | product_price | order_qty | sales_year |
|---|---|---|---|
| a | 100 | 25 | 1990 |
| a | 100 | 250 | 1991 |
| a | 100 | 348 | 1993 |
| b | 180 | 125 | 1990 |
| c | 230 | 400 | 1992 |
| d | 520 | 420 | 1994 |
次にクロス表テーブルを作成します。
CREATE TABLE public.crosstabs (
sales_year BIGINT NOT NULL
,year1 BIGINT NOT NULL
,year2 BIGINT NOT NULL
,year3 BIGINT NOT NULL
,year4 BIGINT NOT NULL
,year5 BIGINT NOT NULL
,row_total BIGINT NOT NULL
);
INSERT INTO public.crosstabs VALUES
(1990,1,0,0,0,0,1),
(1991,0,1,0,0,0,1),
(1992,0,0,1,0,0,1),
(1993,0,0,0,1,0,1),
(1994,0,0,0,0,1,1);
| sales_year | year1 | year2 | year3 | year4 | year5 | row_total |
|---|---|---|---|---|---|---|
| 1990 | 1 | 0 | 0 | 0 | 0 | 1 |
| 1991 | 0 | 1 | 0 | 0 | 0 | 1 |
| 1992 | 0 | 0 | 1 | 0 | 0 | 1 |
| 1993 | 0 | 0 | 0 | 1 | 0 | 1 |
| 1994 | 0 | 0 | 0 | 0 | 1 | 1 |
その上で、集計対象テーブルとクロステーブルを結合して売上額にクロステーブルの値をかけます。
結合部を少し改造しています。
SELECT
s1.product_name
,SUM(s1.order_qty * s1.product_price * c1.year1) AS year1
,SUM(s1.order_qty * s1.product_price * c1.year2) AS year2
,SUM(s1.order_qty * s1.product_price * c1.year3) AS year3
,SUM(s1.order_qty * s1.product_price * c1.year4) AS year4
,SUM(s1.order_qty * s1.product_price * c1.year5) AS year5
,SUM(s1.order_qty * s1.product_price * c1.row_total) AS total
FROM
public.sales AS s1
INNER JOIN public.crosstabs AS c1 ON
s1.sales_year = c1.sales_year
GROUP BY
s1.product_name
ORDER BY
s1.product_name
;
このような結果が得られました。
| product_name | sum | sum | sum | sum | sum | sum |
|---|---|---|---|---|---|---|
| a | 2,500 | 25,000 | 0 | 34,800 | 0 | 62,300 |
| b | 22,500 | 0 | 0 | 0 | 0 | 22,500 |
| c | 0 | 0 | 92,000 | 0 | 0 | 92,000 |
| d | 0 | 0 | 0 | 0 | 218,400 | 218,400 |
集計対象テーブルとクロステーブルを販売年が一致することろで結合して、年毎の倍率をかけることで、該当する年と合計以外は0にしてしまう作りです。
それを製品名でGROUP BYすることで、製品毎に何年にいくら販売されたかの表になります。
これも合計を出すならUNIONをしろとのこと。
テーブルを1つ用意しなければならないし、使い捨て集計用SQLなら普通にCASE式を使えばいい気がしますね。
ただし、集計対象が限定されており、今後も大きく変更されることが無いことが判明している場合は、クロステーブルの行をINSERTするだけでSQLのコードを改造せずに集計結果を拡張することができるのでそういった場合は有用なのかと思います。
3 GROUPING演算子
毎回UNION ALLして合計件数を出すのが面倒な時にこのGROUPING演算子を使うと楽になるかもしれません。
GROUPING演算子には基本となるGROUPING SETS とその拡張であるROLLUP, CUBEがあります。
PostgreSQLでは９．５から使えるようになったようです。
実際にテストデータがあった方がわかりやすいので適当なテーブルを作っておきます。
peoplesテーブル
| id | gender | age | breakfast_frag |
|---|---|---|---|
| 1 | m | 10 | true |
| 2 | m | 20 | true |
| 3 | m | 30 | false |
| 4 | m | 10 | true |
| 5 | m | 10 | false |
| 6 | m | 10 | true |
| 7 | f | 10 | true |
| 8 | f | 10 | true |
| 9 | f | 10 | true |
| 10 | f | 10 | true |
| 11 | f | 10 | true |
| 12 | f | 10 | true |
GROUPING SETS
GROUPING SETSはどのカラムを集約の単位にするかを指定できます。
実際に動きを見るのが一番理解しやすいと思います。
SELECT
breakfast_flag
,gender
,age
,COUNT(*) AS total
FROM
public.peoples
GROUP BY
GROUPING SETS (breakfast_flag, gender, age)
;
| breakfast_frag | gender | age | total |
|---|---|---|---|
| false | [NULL] | [NULL] | 2 |
| true | [NULL] | [NULL] | 10 |
| [NULL] | m | [NULL] | 6 |
| [NULL] | f | [NULL] | 6 |
| [NULL] | [NULL] | 30 | 1 |
| [NULL] | [NULL] | 10 | 10 |
| [NULL] | [NULL] | 20 | 1 |
指定したそれぞれの項目だけで集約されました。
breakfast_fragで集約するときは他の要素は無視して集約されています。他も同様です。
また、集約する要素の組み合わせを指定することもできます。
SELECT
breakfast_flag
,gender
,age
,COUNT(*) AS total
FROM
public.peoples
GROUP BY
GROUPING SETS (breakfast_flag, (gender, age))
;
| breakfast_frag | gender | age | total |
|---|---|---|---|
| false | [NULL] | [NULL] | 2 |
| true | [NULL] | [NULL] | 10 |
| [NULL] | m | 30 | 1 |
| [NULL] | m | 10 | 4 |
| [NULL] | f | 10 | 6 |
| [NULL] | m | 20 | 1 |
朝食の有無で集約した後、性別と年代の組み合わせがユニークになるように集約されました。
また、集約セットを空で指定することで無条件で集約を行い、つまり合計が出ます。
これを使えば、クロス集計と合計の算出が一気にできます。
SELECT
breakfast_flag
,gender
,age
,COUNT(*) AS total
FROM
public.peoples
GROUP BY
GROUPING SETS ((breakfast_flag, gender, age),())
ORDER BY
breakfast_flag,gender,age
;
全ての要素の組み合わせが集約対象となり、最終行に合計が出力されます。
| breakfast_flag | gender | age | total |
|---|---|---|---|
| false | m | 10 | 1 |
| false | m | 30 | 1 |
| true | f | 10 | 6 |
| true | m | 10 | 3 |
| true | m | 20 | 1 |
| [NULL] | [NULL] | [NULL] | 12 |
ROLLUP
ROLLUPは小計と合計を出してくれます。
SELECT
breakfast_flag
,gender
,age
,COUNT(*) AS total
FROM
public.peoples
GROUP BY
ROLLUP(breakfast_flag, gender, age)
;
| breakfast_flag | gender | age | total |
|---|---|---|---|
| [NULL] | [NULL] | [NULL] | 12 |
| true | f | 10 | 6 |
| false | m | 30 | 1 |
| true | m | 10 | 3 |
| true | m | 20 | 1 |
| false | m | 10 | 1 |
| true | f | [NULL] | 6 |
| true | m | [NULL] | 4 |
| false | m | [NULL] | 2 |
| false | [NULL] | [NULL] | 2 |
| true | [NULL] | [NULL] | 10 |
ORDERをかけていないので合計が最上段にきていますね。
ROLLUPでは指定する順番が重要になります。
ROLLUPでは先に指定した要素に対して、後に指定した要素の全組み合わせが集計されます。
また、GROUPING SETSの拡張なので、全てをGROUPING SETSで表現することができます。
SELECT
breakfast_flag
,gender
,age
,COUNT(*) AS total
FROM
public.peoples
GROUP BY
GROUPING SETS (
(breakfast_flag, gender, age)
,(breakfast_flag, gender)
,(breakfast_flag)
,()
)
;
##CUBE
CUBEはROLLUPと違い要素の順番が重要ではありません。
これは与えたれた要素に関して全ての組み合わせで集約するからです。
SELECT
breakfast_flag
,gender
,age
,COUNT(*) AS total
FROM
public.peoples
GROUP BY
CUBE(breakfast_flag, gender, age)
;
| breakfast_flag | gender | age | total |
|---|---|---|---|
| [NULL] | [NULL] | [NULL] | 12 |
| true | f | 10 | 6 |
| false | m | 30 | 1 |
| true | m | 10 | 3 |
| true | m | 20 | 1 |
| false | m | 10 | 1 |
| true | f | [NULL] | 6 |
| true | m | [NULL] | 4 |
| false | m | [NULL] | 2 |
| false | [NULL] | [NULL] | 2 |
| true | [NULL] | [NULL] | 10 |
| [NULL] | m | 30 | 1 |
| [NULL] | m | 10 | 4 |
| [NULL] | f | 10 | 6 |
| [NULL] | m | 20 | 1 |
| [NULL] | m | [NULL] | 6 |
| [NULL] | f | [NULL] | 6 |
| true | [NULL] | 10 | 9 |
| false | [NULL] | 30 | 1 |
| false | [NULL] | 10 | 1 |
| true | [NULL] | 20 | 1 |
| [NULL] | [NULL] | 30 | 1 |
| [NULL] | [NULL] | 10 | 10 |
| [NULL] | [NULL] | 20 | 1 |
これもGROUPING SETS で表現が可能です。
SELECT
breakfast_flag
,gender
,age
,COUNT(*) AS total
FROM
public.peoples
GROUP BY
GROUPING SETS (
(breakfast_flag, gender, age)
,(breakfast_flag, gender)
,(breakfast_flag, age)
,(gender, age)
,(breakfast_flag)
,(gender)
,(age)
,()
)
;
GROUP BYとUNIONを使うよりも簡単に小計や合計が計算できるのがGROUPING演算子の良いところだと思います。
また、需要に応じてはROLLUPやCUBEを使うことでさらに簡単にクロス集計が可能になると思います。
ただし、先ほどから表に大量に出現しているのを見る通り、GROUPING演算子はNULLを対象に生産します。
DBがしっかりとNOT NULL制約の上設計されており、「そんなものはGROUPING演算子のせいだ。」と断言できるなら問題は無いのですが・・・。
ではnullに登場していただきましょう。
INSERT INTO public.peoples(
gender, age, breakfast_flag
)VALUES(
NULL, 20, TRUE
)
齢20の毎日朝食を欠かさない健康な、性別不詳さんです。
ではGROUPING演算子でもって、それぞれの性別が何人回答してくれたか集計して、ついでに回答総数としてレコード数も一気に出しましょう。
SELECT
breakfast_flag
,gender
,age
,COUNT(*) AS total
FROM
public.peoples
GROUP BY
GROUPING SETS (breakfast_flag, gender, age,())
ORDER BY
gender, age, breakfast_flag
;
| gender | total |
|---|---|
| f | 6 |
| m | 6 |
| [NULL] | 13 |
| [NULL] | 1 |
さて、注目していただきたいのは最下段の２行。
集約条件は全く同じ、性別に関して条件を問わず集約した、すなわち合計を出した行に見えます。
もちろん1つの結果が2つに分裂したわけではありません。
性別NULLというジェンダーフリー(?)なレコードが生まれた結果、性別を男性・女性・NULLで集約した3レコードが生まれ、最後のNULL集約が合計行と見分けがつかなくなったわけですね。
この場合は、合計回答が1な訳は無いので下から2つ目が合計でしょう。きっと。
##SELECT GROUPING
この問題への１番の対処法はNULLを生じないDB設計を徹底することです。
回答者には必ず性別を聞きましょう。
SQLのNULLはこれ以外にも様々な問題を引き起こします。忘れた頃に、ひっそりと。
せっかくなのでこちらのリンクを置いておきますね。
NULL撲滅委員会
しかし、そうはいってもNULLを使わなければならない状況もあるわけです。
最近は性別も殊更デリケートな話題です。
そんな時にはGROUPINGを使いましょう。
こいつは集約の結果生まれたNULLと本当にDBに登録されているNULLを見分けてくれます。
厳密には集約の結果生まれたNULLとそれ以外を見分けてくれます。
SELECT
GROUPING(gender)
-- こうしておくと集約で生成されたNULLなら１、そうでなければ0が返ってきます。
では、先ほどの集約SQLに組み込んでみましょう。
SELECT
(CASE GROUPING(gender) WHEN 1 THEN 'total' ELSE gender::TEXT END)
,COUNT(*) AS total
FROM
public.peoples
GROUP BY
GROUPING SETS (gender, ())
ORDER BY
gender
;
性別が集約NULLなら"total"と、
そうでないなら内容をTEXTにキャストしてそのまま報告させます。
|gender|total|
|:-:|:-:|:-:|:-:|
|total|13|
|[NULL]|1|
|m|6|
|f|6|
うーんこの並び順。
ともあれ、合計行と性別不詳行を見分けることには成功しました。
ちなみに個人的にはこっちの方が好みですね。
SELECT
(CASE WHEN GROUPING(gender) = 0 AND gender IS NULL THEN 'Unknown' ELSE gender::TEXT END)
,COUNT(*) AS total
FROM
public.peoples
GROUP BY
GROUPING SETS (gender, ())
ORDER BY
gender
;
実際のレコードのNULLだったら"Unknown"
それ以外はそのままTEXTキャストして表示させます。
| gender | total |
|---|---|
| Unknown | 1 |
| f | 6 |
| m | 6 |
| [NULL] | 13 |
#あとがき
という名の与太話
この記事は以前社内勉強会に向けて作成し発表したものの、不慮の事故により半壊させた記事を書き直したものです。
書き直さなきゃと思いつつも面倒が勝って手が動かなかったのですが、先日集計用の使い捨てSQLにGROUPING SETSを使い他人にコードをみてもらったところ、
「なにこれ？」
と言われたのでついに重い腰が上がりました。
やはり資料は残さねば。
書き直してみると自分でも忘れていることの多いこと多いこと。
結局普通に１記事書くカロリーを使った気がします。