目次
SQLで「UNION」を使って複数テーブルを一発で集計するサンプル
今回はSQLの「UNION」を使ったサンプルです。
同じ構造の時系列データを当月分・前月分・前々月分・それ以前みたいに別テーブルにわけたものを、SQL一発でキー毎に集計するケースでよく使われるので、それを例としてやってみました。
動作確認はUbuntuのMariaDB10.6（Windows11のWSL2）で行いました。
サンプルで使うテーブルとお題について
サンプルに使うテーブルは以下のカラム構成とします。
ID番号（keyid）と日付（datet）がプライマリキーで、値には1から100の乱数が集計対象の数値としてはいっています。
この同じ構造のテーブルを３つ作り、同じ４つのID番号（10,20,30,40）ごとに以下の期間の日付別に値を格納しています。
- union_one： 2022年1月１日から2022年12月31日
- union_two： 2023年1月1日から2023年12月31日
- union_three： 2021年1月1日から2021年12月31日
この３つのテーブルにまたがって、ID番号ごとに「3年間の1月分（1月1日から1月31日）」の値を集計する・・というお題です。
joinではなくunionを使う
これは「join」でやろうとすると、以下みたいに横方向に総当たりで結合してしまうイメージなので、よぶんな行をはぶいて正しく集計するのはかなり面倒なケースです。
10 2022/01/01 99 10 2023/01/01 89 10 2021/01/01 79
10 2022/01/01 99 10 2023/01/01 89 10 2021/02/01 79
10 2022/01/01 99 10 2023/01/01 89 10 2021/03/01 79
・・・
10 2022/01/01 99 10 2023/02/01 89 10 2021/01/01 79
10 2022/01/01 99 10 2023/02/01 89 10 2021/02/01 79
10 2022/01/01 99 10 2023/02/01 89 10 2021/03/01 79
・・・
このお題は、ようするに。
10 2022/01/01 99 ：テーブル union_one
10 2023/01/01 89 ：テーブル union_two
10 2021/01/01 79 ：テーブル union_three
みたいに、各テーブルを縦に連結して集計しなさいということですから「union」を使えば簡単にできます。
unionはシンプルにSQLをつなぐ
unionは結果を縦につなげたいわけなので、ひとつひとつのテーブルに対する同じ形のSQLを作って、つなげるだけでOKです。
union_oneでID番号ごとに1月の値を集計するSQLだとこんな感じです。
select
T1.keyid
,sum(T1.vakue)
from union_one T1
where T1.datet >= '2022/01/01'
and T1.datet <= '2022/01/31'
group by T1.keyid;
この形で、テーブル名を「union_two」「union_three」にそれぞれ変えて、その日付条件を2023年1月や2021年1月に書き換えれば、テーブルごとの集計SQLはOKです。
なので、あとは、その結果を「union」でつないでやればいいです。
select
T1.keyid
,sum(T1.value)
from union_one T1
where T1.datet >= '2022/01/01'
and T1.datet <= '2022/01/31'
group by T1.keyid
union
select
T1.keyid
,sum(T1.value)
from union_two T1
where T1.datet >= '2023/01/01'
and T1.datet <= '2023/01/31'
group by T1.keyid
union
select
T1.keyid
,sum(T1.value)
from union_three T1
where T1.datet >= '2021/01/01'
and T1.datet <= '2021/01/31'
group by T1.keyid
これで各月ごとの集計結果が縦に連結されて出力されます。
連結された出力結果をFromで受けてやれば完了
上記のSQLで返される結果はこんな感じになります。
"keyid" "sum(T1.value)"
"10" "1583"
"20" "1691"
"30" "1429"
"40" "1424"
"10" "1267"
"20" "1957"
"30" "1437"
"40" "1333"
"10" "1454"
"20" "1284"
"30" "1477"
"40" "1546"
この結果をキーごとに再集計してやれば、お題の3年間合計を計算することができるで、この結果をテーブルデータのように「From」でうけて、集計するSQLをかいてやるといいわけです。
一見複雑に見えますが、付け加えた部分だけ太字の赤字にしているので、そこだけ見てもらえると、上記のSQLの結果を使っているイメージがわかると思います。
select T0.keyid,sum(T0.value) from
(select
T1.keyid
,sum(T1.value) AS value
from union_one T1
where T1.datet >= '2022/01/01'
and T1.datet <= '2022/01/31'
group by T1.keyid
union
select
T1.keyid
,sum(T1.value) AS value
from union_two T1
where T1.datet >= '2023/01/01'
and T1.datet <= '2023/01/31'
group by T1.keyid
union
select
T1.keyid
,sum(T1.value) AS value
from union_three T1
where T1.datet >= '2021/01/01'
and T1.datet <= '2021/01/31'
group by T1.keyid) T0
group by T0.keyid;
これで以下のような3年分の集計ができます。
"keyid" "sum(T0.value)"
"10" "4304"
"20" "4932"
"30" "4343"
"40" "4303"
まとめ
こういう風に「union」を使う連結は、シンプルで頭の中で結果のイメージがしやすいこともあり知っている人は「馬鹿みたいに簡単」に感じるみたいです。
でも、SQLにあまり慣れていない人からは、今回のようなお題を「union」で処理するという発想にいたるのに時間がかかるという声を聴くのも事実です。
現場の開発って、いろいろなやり方の「知識ストック・経験ストック」が生産性にきいてくるので、今回のような「こういうやり方もあります」って紹介するのも、それなりに意味があるかな・・と、個人的に思っています。
今回はこんなところで。
ではでは。
