SQLiteでGROUP_CONCATとORDER BYを使ってデータを自由自在に並べ替える
SQLiteにおけるGROUP_CONCATとORDER BY
GROUP_CONCATの基本的な使い方
SELECT
column1,
GROUP_CONCAT(column2) AS concat_column
FROM table
GROUP BY column1;
上記の例では、column1をグループ化し、column2の値をカンマ区切りで結合してconcat_columnとして出力します。
ORDER BY句による順序制御
GROUP_CONCAT関数とORDER BY句を組み合わせることで、結合されたデータの順序を制御することができます。
例1：昇順
SELECT
column1,
GROUP_CONCAT(column2 ORDER BY column2 ASC) AS concat_column
FROM table
GROUP BY column1;
上記の例では、column2の値を昇順に並べ替えて結合します。
例2：降順
SELECT
column1,
GROUP_CONCAT(column2 ORDER BY column2 DESC) AS concat_column
FROM table
GROUP BY column1;
NULL値の扱い
GROUP_CONCAT関数は、NULL値を無視して結合処理を行います。
例：NULL値を除外
SELECT
column1,
GROUP_CONCAT(column2) AS concat_column
FROM table
WHERE column2 IS NOT NULL
GROUP BY column1;
上記の例では、NULL値ではないcolumn2の値のみを結合します。
ORDER BY句とGROUP BY句は、基本的にどちらを先に記述しても構いません。ただし、GROUP BY句で使用する列をORDER BY句でも使用する場合、GROUP BY句を先に記述する必要があります。
例：GROUP BY句を先に記述
SELECT
column1,
GROUP_CONCAT(column2 ORDER BY column2 ASC) AS concat_column
FROM table
GROUP BY column1
ORDER BY column1;
上記の例では、column1をグループ化し、column2の値を昇順に並べ替えて結合します。その後、column1の値で結果を昇順に並べ替えます。
GROUP_CONCAT関数は、COUNT()関数やSUM()関数などの集計関数と組み合わせて使用することができます。
例：GROUP_CONCATとCOUNT()関数
SELECT
column1,
GROUP_CONCAT(column2) AS concat_column,
COUNT(*) AS count
FROM table
GROUP BY column1;
SQLiteのGROUP_CONCAT関数は、ORDER BY句と組み合わせて使用することで、結合されたデータの順序を制御することができます。GROUP BY句で使用する列をORDER BY句でも使用する場合、GROUP BY句を先に記述する必要があります。
-- テーブル作成
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT NOT NULL,
salary INTEGER NOT NULL
);
-- データ挿入
INSERT INTO employees (name, department, salary) VALUES ("山田太郎", "営業部", 300000);
INSERT INTO employees (name, department, salary) VALUES ("佐藤花子", "人事部", 250000);
INSERT INTO employees (name, department, salary) VALUES ("田中一郎", "開発部", 400000);
INSERT INTO employees (name, department, salary) VALUES ("高橋由美", "営業部", 350000);
INSERT INTO employees (name, department, salary) NULL; -- NULL値を含む行
-- 確認
SELECT * FROM employees;
例2：部署ごとに社員名を昇順で結合
SELECT
department,
GROUP_CONCAT(name ORDER BY name ASC) AS employees
FROM employees
GROUP BY department;
出力例
| department | employees |
|-----------|---------------------------------------|
| 営業部 | 山田太郎, 高橋由美 |
| 人事部 | 佐藤花子 |
| 開発部 | 田中一郎 |
SELECT
department,
GROUP_CONCAT(salary ORDER BY salary DESC) AS salaries
FROM employees
GROUP BY department;
| department | salaries |
|-----------|---------------------------------------|
| 営業部 | 350000, 300000 |
| 人事部 | 250000 |
| 開発部 | 400000 |
SELECT
department,
GROUP_CONCAT(name ORDER BY name ASC) AS employees
FROM employees
WHERE name IS NOT NULL
GROUP BY department;
| department | employees |
|-----------|---------------------------------------|
| 営業部 | 山田太郎, 高橋由美 |
| 人事部 | 佐藤花子 |
| 開発部 | 田中一郎 |
SELECT
department,
GROUP_CONCAT(name ORDER BY name ASC) AS employees,
COUNT(*) AS count
FROM employees
GROUP BY department;
| department | employees | count |
|-----------|---------------------------------------|------|
| 営業部 | 山田太郎, 高橋由美 | 2 |
| 人事部 | 佐藤花子 | 1 |
| 開発部 | 田中一郎 | 1 |
SELECT
department,
GROUP_CONCAT(name ORDER BY name ASC) AS employees,
SUM(salary) AS total_salary
FROM employees
GROUP BY department;
| department | employees | total_salary |
|-----------|---------------------------------------|-------------|
| 営業部 | 山田太郎, 高橋由美 | 650000 |
| 人事部 | 佐藤花子 | 250000 |
| 開発部 | 田中一郎 | 400000 |
SQLiteにおけるGROUP_CONCATとORDER BYの代替方法
サブクエリを使用して、ORDER BY句で指定した順序でデータを結合することができます。
SELECT
department,
GROUP_CONCAT(name) AS employees
FROM (
SELECT
name
FROM employees
WHERE department = '営業部'
ORDER BY name ASC
) AS t
GROUP BY department;
| department | employees |
|-----------|---------------------------------------|
| 営業部 | 山田太郎, 高橋由美 |
WINDOW関数を用いて、結合処理の中でデータを並べ替えて結合することができます。
SELECT
department,
GROUP_CONCAT(name ORDER BY name ASC) AS employees
FROM (
SELECT
name,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY name ASC) AS rn
FROM employees
) AS t
GROUP BY department;
| department | employees |
|-----------|---------------------------------------|
| 営業部 | 山田太郎, 高橋由美 |
ユーザー定義関数を作成して、結合処理と並べ替え処理をまとめて実行することができます。
CREATE FUNCTION group_concat_order_by(
column1 TEXT,
column2 TEXT
) RETURNS TEXT
AS
BEGIN
DECLARE @result TEXT;
SELECT
@result = GROUP_CONCAT(column2 ORDER BY column2 ASC)
FROM employees
WHERE department = column1;
RETURN @result;
END;
SELECT
department,
group_concat_order_by(department, name) AS employees
FROM employees
GROUP BY department;
| department | employees |
|-----------|---------------------------------------|
| 営業部 | 山田太郎, 高橋由美 |
GROUP_CONCAT関数とORDER BY句以外にも、サブクエリ、WINDOW関数、ユーザー定義関数などの方法を用いて、SQLiteで結合されたデータの順序を制御することができます。
sqlite group-concat
