データ分析やレポート作成において、数値項目を範囲ごとに集計することはよくあります。
例えば、売上金額やユーザー年齢を特定の範囲でグループ化して、各グループの件数や合計値を確認したい場合です。
しかし、範囲に該当するデータが存在しない場合、そのグループが表示されないことがあり、集計結果に欠落が発生することがあります。
数値項目を範囲ごとに集計し、該当するデータが存在しない場合でも0件の行を出力するSQLの書き方を紹介します。
基本的な数値項目の範囲ごとの集計
まず、基本的な数値項目の範囲ごとの集計方法を見てみましょう。
ここでは、売上データを例にし、売上金額を100円単位でグループ化し、件数を集計するSQLクエリを示します。
SELECT
FLOOR(amount / 100) * 100 AS amount_range,
COUNT(*) AS count
FROM
sales
GROUP BY
FLOOR(amount / 100) * 100
ORDER BY
amount_range;
FLOOR(amount / 100) * 100
金額を 100 円単位で切り捨てて、各グループを定義しています。COUNT(*)
各金額範囲に該当するレコードの件数をカウントします。GROUP BY
金額範囲ごとにデータを集計します。
このクエリは、例えば0～99円、100～199円といった範囲で売上の件数を集計しますが、該当するデータがない範囲は結果に表示されません。
COUNTが0の範囲も出力するためのテクニック
次に、データが存在しない範囲も0件として表示したい場合の対処方法を見ていきます。
これを実現するためには、あらかじめすべての範囲を定義したテーブル（またはサブクエリ）を作成し、元のテーブルと LEFT JOIN で結合します。
ジェネレータを使って数値範囲を自動生成する
Snowflakeなどの一部のデータベースでは、GENERATOR 関数を使って任意の範囲を生成できます。
以下のSQLクエリでは、売上金額を100円単位で範囲に分け、0件の範囲も出力します。
WITH ranges AS (
SELECT
(seq4() * 100) AS amount_range_start,
(seq4() * 100 + 99) AS amount_range_end
FROM
TABLE(GENERATOR(ROWCOUNT => 50)) -- 0円から5000円までの範囲を生成
)
SELECT
r.amount_range_start,
r.amount_range_end,
COUNT(s.amount) AS count
FROM
ranges r
LEFT JOIN
sales s
ON s.amount BETWEEN r.amount_range_start AND r.amount_range_end
GROUP BY
r.amount_range_start, r.amount_range_end
ORDER BY
r.amount_range_start;
TABLE(GENERATOR(ROWCOUNT => 50))
0円から5000円までの範囲を 100 円単位で生成しています（必要に応じて範囲を調整）。LEFT JOIN
生成した範囲と実際の売上データを結合。売上データがない範囲も0件として表示されます。COUNT(s.amount)
売上金額が該当する範囲の件数をカウント。
このクエリにより、0～99円、100～199円などの範囲に売上データがない場合でも、件数が0で表示されます。
手動で範囲を定義したテーブルを使う
ジェネレータ関数が使用できない場合、あらかじめ範囲を手動で定義したテーブルを作成しておく方法もあります。この方法では、別途「金額範囲テーブル」を用意し、それを LEFT JOIN します。
WITH ranges AS (
SELECT 0 AS amount_range_start, 99 AS amount_range_end
UNION ALL
SELECT 100, 199
UNION ALL
SELECT 200, 299
-- 必要に応じてさらに範囲を追加
)
SELECT
r.amount_range_start,
r.amount_range_end,
COUNT(s.amount) AS count
FROM
ranges r
LEFT JOIN
sales s
ON s.amount BETWEEN r.amount_range_start AND r.amount_range_end
GROUP BY
r.amount_range_start, r.amount_range_end
ORDER BY
r.amount_range_start;
一般的なSQLクエリの例
ここまで紹介したテクニックを一般化し、他の数値項目にも適用できます。
例えば、ユーザーの年齢を範囲ごとに集計する場合、以下のように書くことができます。
WITH ranges AS (
SELECT
(seq4() * 5) AS age_range_start,
(seq4() * 5 + 4) AS age_range_end
FROM
TABLE(GENERATOR(ROWCOUNT => 20)) -- 0歳から100歳までの範囲を生成
)
SELECT
r.age_range_start,
r.age_range_end,
COUNT(u.age) AS count
FROM
ranges r
LEFT JOIN
users u
ON u.age BETWEEN r.age_range_start AND r.age_range_end
GROUP BY
r.age_range_start, r.age_range_end
ORDER BY
r.age_range_start;
このクエリでは、5歳ごとの年齢範囲（0-4歳、5-9歳…）ごとにユーザー数を集計し、該当する年齢のユーザーがいない範囲も 0 件として表示します。
特定の数値範囲を定義したテーブルと結合する方法
ジェネレータや WITH 句を使って範囲を作成する方法以外にも、事前に定義された範囲テーブルを作成しておくことも有効です。
例えば、給与のランクを決める「給与ランクテーブル」などを作成し、それを利用して範囲ごとの集計を行います。
SELECT
r.salary_range_start,
r.salary_range_end,
COUNT(e.salary) AS count
FROM
salary_ranges r
LEFT JOIN
employees e
ON e.salary BETWEEN r.salary_range_start AND r.salary_range_end
GROUP BY
r.salary_range_start, r.salary_range_end
ORDER BY
r.salary_range_start;
このように、特定の範囲が決まっているデータセットに対しても、LEFT JOIN を活用することで、0件のグループを含めた正確な集計が可能になります。
まとめ
数値項目を範囲ごとに集計し、データが存在しない場合でも0件の範囲を出力するテクニックを紹介しました。FLOOR 関数や LEFT JOIN を利用することで、あらゆる数値項目に対して簡単にグループ化と集計が可能です。データベースの機能を活用し、包括的な集計結果を得るための手法として、ぜひ活用してください。
