SQL GROUP BY句を使いこなして、データ分析をレベルアップ！
2024-04-02
SQL で複数の列によるグループ化
基本的な構文
SELECT 列名, 集計関数(列名)
FROM テーブル名
GROUP BY 列名, 列名;
例:
SELECT 国, COUNT(*)
FROM 顧客
GROUP BY 国;
この例では、顧客テーブルを国別にグループ化し、各国の顧客数を取得します。
複数の列によるグループ化
GROUP BY 句で複数の列を指定することで、より詳細なグループ化を行うことができます。
SELECT 国, 性別, COUNT(*)
FROM 顧客
GROUP BY 国, 性別;
集計関数の使用
GROUP BY 句と組み合わせて、さまざまな集計関数を使用することができます。
代表的な集計関数:
- COUNT(*) - グループ内のデータ件数を取得
- SUM() - グループ内の数値列の合計値を取得
SELECT 国, 性別, COUNT(*), AVG(年齢)
FROM 顧客
GROUP BY 国, 性別;
- GROUP BY 句の後に
ORDER BY句を使用すると、グループ化結果をソートすることができます。 HAVING句を使用すると、グループ化結果に対して条件を指定することができます。
SELECT 国, 性別, COUNT(*)
FROM 顧客
GROUP BY 国, 性別
HAVING COUNT(*) > 10;
この例では、顧客数が10人を超える国と性別のみを取得します。
-- 顧客テーブル
CREATE TABLE 顧客 (
顧客ID INT PRIMARY KEY,
氏名 VARCHAR(255),
国 VARCHAR(2),
性別 VARCHAR(1),
年齢 INT
);
-- データ挿入
INSERT INTO 顧客 (氏名, 国, 性別, 年齢) VALUES
('山田太郎', 'JP', 'M', 25),
('佐藤花子', 'JP', 'F', 30),
('田中一郎', 'US', 'M', 40),
('高橋麗子', 'US', 'F', 35),
('斎藤健太', 'JP', 'M', 20),
('藤田真由美', 'JP', 'F', 22),
('木村拓哉', 'JP', 'M', 45),
('伊藤沙織', 'JP', 'F', 38);
国別の顧客数と平均年齢
SELECT 国, COUNT(*), AVG(年齢)
FROM 顧客
GROUP BY 国;
-- 結果
-- 国 | 顧客数 | 平均年齢
-- --- | --- | ---
-- JP | 6 | 30.83
-- US | 2 | 37.50
国と性別別の顧客数
SELECT 国, 性別, COUNT(*)
FROM 顧客
GROUP BY 国, 性別;
-- 結果
-- 国 | 性別 | 顧客数
-- --- | --- | ---
-- JP | M | 4
-- JP | F | 2
-- US | M | 1
-- US | F | 1
20歳以上の顧客の国と性別別の顧客数
SELECT 国, 性別, COUNT(*)
FROM 顧客
WHERE 年齢 >= 20
GROUP BY 国, 性別;
-- 結果
-- 国 | 性別 | 顧客数
-- --- | --- | ---
-- JP | M | 3
-- JP | F | 2
-- US | M | 1
-- US | F | 1
各国の男女別顧客数の割合
SELECT 国, 性別, COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY 国) AS 割合
FROM 顧客
GROUP BY 国, 性別;
-- 結果
-- 国 | 性別 | 割合
-- --- | --- | ---
-- JP | M | 0.6667
-- JP | F | 0.3333
-- US | M | 0.5
-- US | F | 0.5
顧客数が10人を超える国と性別
SELECT 国, 性別
FROM 顧客
GROUP BY 国, 性別
HAVING COUNT(*) > 10;
-- 結果
-- 国 | 性別
-- --- | ---
-- JP | M
各国の顧客数の最大値と最小値
SELECT 国, MAX(顧客数), MIN(顧客数)
FROM (
SELECT 国, COUNT(*) AS 顧客数
FROM 顧客
GROUP BY 国
) AS T;
-- 結果
-- 国 | 最大値 | 最小値
-- --- | --- | ---
-- JP | 4 | 2
-- US | 2 | 2
国と性別別の顧客数の合計値と平均値
SELECT 国, 性別, SUM(顧客数), AVG(顧客数)
FROM (
SELECT 国, 性別, COUNT(*) AS 顧客数
FROM 顧客
GROUP BY 国, 性別
) AS T
GROUP BY 国;
-- 結果
-- 国 | 性別 | 合計値 | 平均値
-- --- | --- | --- | ---
-- JP | M | 4 | 2
-- JP | F | 2 | 1
-- US | M | 1 | 1
-- US | F | 1 | 1
SELECT 国, 性別, STDDEV(顧客数)
FROM (
SELECT 国, 性別, COUNT(*) AS 顧客数
SQLで複数の列によるグループ化を行うその他の方法
CASE式
SELECT
CASE WHEN 国 = 'JP' THEN '日本'
WHEN 国 = 'US' THEN 'アメリカ'
ELSE 'その他'
END AS 国,
性別,
COUNT(*)
FROM 顧客
GROUP BY 国, 性別;
-- 結果
-- 国 | 性別 | 顧客数
-- -------- | --- | ---
-- 日本 | M | 4
-- 日本 | F | 2
-- アメリカ | M | 1
-- アメリカ | F | 1
-- その他 | M | 0
-- その他 | F | 0
WITH句を使用して、中間的な結果を保存し、複数のクエリで参照することができます。
WITH T AS (
SELECT 国, 性別, COUNT(*) AS 顧客数
FROM 顧客
GROUP BY 国, 性別
)
SELECT
T.国,
T.性別,
T.顧客数,
T.顧客数 / SUM(T.顧客数) OVER (PARTITION BY T.国) AS 割合
FROM T;
-- 結果
-- 国 | 性別 | 顧客数 | 割合
-- -------- | --- | --- | ---
-- 日本 | M | 4 | 0.6667
-- 日本 | F | 2 | 0.3333
-- アメリカ | M | 1 | 0.5
-- アメリカ | F | 1 | 0.5
ROLLUPとCUBEを使用して、グループ化の階層を表現することができます。
SELECT 国, 性別, COUNT(*),
SUM(COUNT(*)) OVER (PARTITION BY 国) AS 国別合計,
SUM(COUNT(*)) OVER () AS 全合計
FROM 顧客
GROUP BY 国, 性別 WITH ROLLUP;
-- 結果
-- 国 | 性別 | 顧客数 | 国別合計 | 全合計
-- -------- | --- | --- | --- | ---
-- 日本 | M | 4 | 6 | 8
-- 日本 | F | 2 | 6 | 8
-- アメリカ | M | 1 | 2 | 8
-- アメリカ | F | 1 | 2 | 8
-- <null> | <null> | 8 | 8 | 8
sql group-by multiple-columns
