SQLで総計を集計するときの備忘録
初めに
SQLで集計処理を行う際、特定の属性(e.g. カテゴリごとの集計)に加えて、全体集計を含める場合があります。例えば、category
別の集計結果に加えて、全体の合計であるall_category
も含める場合です。
このような全体集計を追加するために UNION ALL
を利用して集計することができます。
しかし、条件の数が増加すると UNION ALL
の組み合わせが増えるため、SQL の可読性が低下し、定義漏れが発生しやすくなります。
この記事では、デカルト積から集計対象の組み合わせを定義することで、集計漏れを防ぐ書き方 を備忘録として紹介します。
デカルト積(CROSS JOIN)とは
デカルト積(CROSS JOIN)とは、2 つのテーブル間のすべての組み合わせを生成する結合手法です。 例えば、以下のようなデータがあるとします。
A |
---|
a1 |
a2 |
B |
---|
b1 |
b2 |
b3 |
この 2 つのテーブルをデカルト積で結合すると、以下のような結果となります。
A | B |
---|---|
a1 | b1 |
a1 | b2 |
a1 | b3 |
a2 | b1 |
a2 | b2 |
a2 | b3 |
このように、総合計も含めてすべての組み合わせを強制的に生成することで、データが欠落するリスクを防ぐことができます。
各集計の例
今回は以下のデータを例にカテゴリ×注文月ごとの総計を含む集計をしてみることにします。
集計処理では、「category × order_month のすべての組み合わせ」を CROSS JOIN によって動的に作成し、そこにデータを結合することで、漏れなく集計が可能になります。
target_data
)
集計対象データ (id | category | order_month | order_count |
---|---|---|---|
1 | new | 2024-01-01 | 3 |
1 | repeat | 2024-02-01 | 4 |
2 | new | 2024-01-01 | 6 |
3 | repeat | 2024-03-01 | 5 |
期待している集計結果
category | order_month | unique_id_count | sum_order_count |
---|---|---|---|
all_category | 2024-01-01 | 2 | 9 |
all_category | 2024-02-01 | 1 | 4 |
all_category | 2024-03-01 | 1 | 5 |
all_category | 9999-12-31 | 3 | 18 |
new | 2024-01-01 | 2 | 9 |
new | 9999-12-31 | 2 | 9 |
repeat | 2024-02-01 | 1 | 4 |
repeat | 2024-03-01 | 1 | 5 |
repeat | 9999-12-31 | 2 | 9 |
※ここで 9999-12-31
は、すべての order_month を合算した「総合計」を示すダミー値として扱っています。
UNION ALL
を用いた集計
category
や order_month
などの集計条件が増えると UNION ALL
の組み合わせが増えるので管理が煩雑になり、手作業で追加するため定義漏れのリスクが起こる可能性があります。
WITH combined AS (
SELECT
id
, category
, order_month
, order_count
FROM
target_data
UNION ALL
SELECT
id
, 'all_category' AS category
, order_month
, order_count
FROM
target_data
UNION ALL
SELECT
id
, category
, '9999-12-31' AS order_month
, order_count
FROM
target_data
UNION ALL
SELECT
id
, 'all_category' AS category
, '9999-12-31' AS order_month
, order_count
FROM
target_data
)
SELECT
category
, order_month
, COUNT(DISTINCT id) AS unique_id_count
, SUM(order_count) AS sum_order_count
FROM
combined
GROUP BY
category, order_month
ORDER BY
category, order_month
;
デカルト積(CROSS JOIN)を活用した集計
1つのカラムのみの集計であれば UNION ALL
でも十分シンプルです。
しかし、複数の集計条件を追加すると管理が煩雑になるため、デカルト積を利用して mapping を定義することで、より簡潔な SQL にできます
CROSS JOIN
を用いることで、集計すべきすべての組み合わせを網羅的に定義できます。
そのため、新しいカテゴリや注文月が追加されても、SQL を変更せずに正しく集計できます。
mapping CTE では、target_data に含まれるすべての category と order_month の組み合わせに加えて、all_category
や 9999-12-31
という全体集計用のダミー値も含めることで、抜け漏れのない集計を実現しています。
-- すべての category と order_month の組み合わせを生成(全体集計用の値も含む)
WITH mapping AS (
SELECT DISTINCT
c.category
, o.order_month
FROM
(SELECT category FROM target_data UNION ALL SELECT 'all_category') AS c
CROSS JOIN (SELECT order_month FROM target_data UNION ALL SELECT '9999-12-31') AS o
)
-- 事前に作成した組み合わせ(mapping)と元データ(target_data)を結合し、集計を実施
SELECT
m.category
, m.order_month
, COUNT(DISTINCT td.id) AS unique_id_count
, SUM(td.order_count) AS sum_order_count
FROM
mapping AS m
LEFT JOIN target_data AS td
ON (m.category = td.category OR m.category = 'all_category')
AND (m.order_month = td.order_month OR m.order_month = '9999-12-31')
GROUP BY
m.category, m.order_month
ORDER BY
m.category, m.order_month
;
デカルト積を活用することで、条件の増加に対する拡張性を確保しつつ、集計漏れを防ぐことができます。 また、SQL のメンテナンスコストを削減し、可読性を向上させるため、運用時の負担を軽減できます。
Discussion