SQLで総計を集計するときの備忘録

BigQueryのGROUP BYは便利
今回の記事を書いた後、BigQueryでは標準でデカルト積を使わずに組み合わせ別の総計を算出する方法があるので追記します。
GROUP BY CUBEを使うことで不要な行膨れが起こらずに総当りの組み合わせの集計が可能になります。

WITH ret AS (
    SELECT
    category
    , order_month
    , COUNT(DISTINCT td.id) AS unique_id_count
    , SUM(td.order_count) AS sum_order_count
    FROM
        target_data AS td
    GROUP BY
        CUBE (category, order_month)
    ORDER BY
        category, order_month
)

SELECT
    IFNULL(category, 'all_category') AS category
    , IFNULL(order_month, '9999-12-01') AS order_month
    , * EXCEPT(category, order_month)
FROM
    ret
;

初めに

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 のメンテナンスコストを削減し、可読性を向上させるため、運用時の負担を軽減できます。