SQLの窓関数：ROW_NUMBER、RANK、PERCENTILE_CONT

SQLで範囲ごとにグループ化する方法

CASE式を使う方法は、最もシンプルで分かりやすい方法です。CASE式を使って、各列の値をグループ化する範囲に分類します。例えば、年齢を10歳刻みでグループ化したい場合は、次のようなクエリになります。

SELECT
  CASE age
    WHEN age BETWEEN 0 AND 10 THEN '0-10歳'
    WHEN age BETWEEN 11 AND 20 THEN '11-20歳'
    WHEN age BETWEEN 21 AND 30 THEN '21-30歳'
    ELSE '31歳以上'
  END AS 年齢層,
  COUNT(*) AS 人数
FROM 顧客
GROUP BY 年齢層;

このクエリは、顧客テーブルの年齢列を10歳刻みでグループ化し、各グループの人数を表示します。

GROUP BYとBETWEENを使う方法は、CASE式よりも簡潔に記述できます。GROUP BY句でグループ化する列を指定し、HAVING句でBETWEENを使って範囲を指定します。例えば、上記の例と同じように年齢を10歳刻みでグループ化したい場合は、次のようなクエリになります。

SELECT
  age,
  COUNT(*) AS 人数
FROM 顧客
GROUP BY age
HAVING age BETWEEN 0 AND 10
OR age BETWEEN 11 AND 20
OR age BETWEEN 21 AND 30
OR age > 30;

このクエリは、CASE式を使ったクエリと同じ結果を出力します。

窓関数を使う方法は、より複雑なグループ化処理を行う場合に有効です。例えば、年齢の範囲ごとに平均収入を計算したい場合は、次のようなクエリになります。

SELECT
  age,
  AVG(収入) AS 平均収入
FROM (
  SELECT
    age,
    収入,
    SUM(CASE WHEN age BETWEEN 0 AND 10 THEN 1 ELSE 0 END) OVER (ORDER BY age) AS 年齢層
  FROM 顧客
) AS t
GROUP BY 年齢層;

このクエリは、まずサブクエリで年齢の範囲ごとに1をカウントし、それを年齢層という列として追加します。その後、メインクエリで年齢層ごとに平均収入を計算します。

SQLで範囲ごとにグループ化するには、CASE式、GROUP BYとBETWEEN、窓関数など、いくつかの方法があります。それぞれの方法の特徴を理解して、目的に合った方法を選択してください。

CASE式を使う

SELECT
  CASE age
    WHEN age BETWEEN 0 AND 10 THEN '0-10歳'
    WHEN age BETWEEN 11 AND 20 THEN '11-20歳'
    WHEN age BETWEEN 21 AND 30 THEN '21-30歳'
    ELSE '31歳以上'
  END AS 年齢層,
  COUNT(*) AS 人数
FROM 顧客
GROUP BY 年齢層;

GROUP BYとBETWEENを使う

SELECT
  age,
  COUNT(*) AS 人数
FROM 顧客
GROUP BY age
HAVING age BETWEEN 0 AND 10
OR age BETWEEN 11 AND 20
OR age BETWEEN 21 AND 30
OR age > 30;

窓関数を使う

SELECT
  age,
  AVG(収入) AS 平均収入
FROM (
  SELECT
    age,
    収入,
    SUM(CASE WHEN age BETWEEN 0 AND 10 THEN 1 ELSE 0 END) OVER (ORDER BY age) AS 年齢層
  FROM 顧客
) AS t
GROUP BY 年齢層;

CREATE TABLE 顧客 (
  id INT,
  名前 VARCHAR(255),
  年齢 INT,
  収入 INT
);

INSERT INTO 顧客 (id, 名前, 年齢, 収入) VALUES (1, '田中', 25, 300000);
INSERT INTO 顧客 (id, 名前, 年齢, 収入) VALUES (2, '佐藤', 35, 400000);
INSERT INTO 顧客 (id, 名前, 年齢, 収入) VALUES (3, '斎藤', 15, 100000);
INSERT INTO 顧客 (id, 名前, 年齢, 収入) VALUES (4, '高橋', 45, 500000);
INSERT INTO 顧客 (id, 名前, 年齢, 収入) VALUES (5, '伊藤', 55, 600000);

実行結果

年齢層 | 人数
------- | --------
0-10歳 | 1
11-20歳 | 1
21-30歳 | 1
31歳以上 | 2

age | 平均収入
------- | --------
15 | 100000
25 | 300000
35 | 400000
45 | 500000
55 | 600000

解説

注意

上記のサンプルコードはあくまでも例であり、実際の使用環境に合わせて変更する必要があります。

SQLで範囲ごとにグループ化する方法 - その他の方法

ヒストグラムは、データの分布を視覚的に表現する方法です。SQL Serverには、ヒストグラムを作成するための関数HISTOGRAMが用意されています。HISTOGRAM関数は、データの範囲を指定して、その範囲内のデータの個数をカウントします。

SELECT
  age_range,
  COUNT(*) AS 人数
FROM (
  SELECT
    age,
    FLOOR(age / 10) * 10 AS age_range
  FROM 顧客
) AS t
GROUP BY age_range;

CTE (Common Table Expressions) は、複雑なクエリを複数のパートに分割して記述する方法です。CTEを使うと、複雑なグループ化処理をより分かりやすく記述できます。

WITH t AS (
  SELECT
    age,
    FLOOR(age / 10) * 10 AS age_range
  FROM 顧客
)
SELECT
  age_range,
  COUNT(*) AS 人数
FROM t
GROUP BY age_range;

このクエリは、上記のクエリと同じ結果を出力しますが、CTEを使って記述することで、より分かりやすくなっています。

サブクエリを使うと、複雑なグループ化処理を複数のクエリに分割して記述できます。

SELECT
  age_range,
  COUNT(*) AS 人数
FROM (
  SELECT
    age
  FROM 顧客
  WHERE age BETWEEN 0 AND 10
) AS t1
UNION ALL
SELECT
  age_range,
  COUNT(*) AS 人数
FROM (
  SELECT
    age
  FROM 顧客
  WHERE age BETWEEN 11 AND 20
) AS t2
UNION ALL
...;