[sqlite3] グループごとの文字列結合 (GROUP_CONCAT)

[SQL] グループごとの文字列結合 (GROUP_CONCAT)

今回はSQLの GROUP_CONCAT 関数について解説する。 これは、GROUP BY でグループ化した行の文字列を、カンマ区切りなどで結合して1つの文字列にする集約関数である。

「1つの記事に紐づく複数のタグを1行で表示したい」といったケースで非常に便利である。

準備

本サイトではpythonのライブラリであるsqlite3とipython-sqlを利用して、Jupyter Notebook上でSQLを実行・解説する。 また、データフレームの表示には Polars を利用する。

github

• githubのjupyter notebook形式のファイルはこちら

google colaboratory

• google colaboratory で実行する場合はこちら

筆者の環境

筆者の環境である。

!sw_vers

ProductName:		macOS
ProductVersion:		15.5
BuildVersion:		24F74

!python -V

Python 3.12.12

必要なライブラリを読み込む。

import sqlite3
import polars as pl

%load_ext sql
%config SqlMagic.feedback = True

%config SqlMagic.style = '_DEPRECATED_DEFAULT'

データの準備

サンプルとして、記事テーブル Posts と タグテーブル Tags を作成する。

import os

if os.path.exists("data.db"):
    os.remove("data.db")

os.system("touch data.db")
os.system("chmod 664 data.db")

# データベース接続
%sql sqlite:///data.db

'Connected: @data.db'

%%sql
CREATE TABLE Posts (
    id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE TABLE Tags (
    post_id INTEGER,
    tag_name TEXT
);

INSERT INTO Posts (id, title) VALUES
    (1, 'SQL入門'),
    (2, 'Pythonデータ分析');

INSERT INTO Tags (post_id, tag_name) VALUES
    (1, 'Database'),
    (1, 'Beginner'),
    (1, 'SQL'),
    (2, 'Python'),
    (2, 'DataScience'),
    (2, 'Pandas');

 * sqlite:///data.db
Done.
Done.
2 rows affected.
6 rows affected.





[]

%%sql
SELECT * FROM Posts;

 * sqlite:///data.db
Done.

%%sql
SELECT * FROM Tags;

 * sqlite:///data.db
Done.

課題：記事ごとにタグをカンマ区切りで表示する

通常通り JOIN すると、タグの数だけ行が増えてしまう。 これを記事1つにつき1行にまとめたい。

query = """
SELECT
    p.title,
    GROUP_CONCAT(t.tag_name, ', ') as tags
FROM
    Posts p
JOIN
    Tags t ON p.id = t.post_id
GROUP BY
    p.id;
"""

with sqlite3.connect("data.db") as conn:
    df = pl.read_database(query, connection=conn)
    display(df)

shape: (2, 2)

解説

• GROUP_CONCAT(カラム名, 区切り文字): 指定したカラムの値を、指定した区切り文字で結合する。
• GROUP BY p.id: 記事ごとにグループ化する。

これにより、1行の中で複数の値をリストのように表現できる。

注意点：DBによる違い

文字列結合関数はデータベース製品によって名前や構文が異なる。

• MySQL / SQLite: GROUP_CONCAT
• PostgreSQL: STRING_AGG
• Oracle: LISTAGG

使用するデータベースのドキュメントを確認すること。

まとめ

• GROUP_CONCAT を使うと、グループ内の値を1つの文字列に結合できる。
• レポート出力や、アプリケーション側での表示用にデータを整形する際に非常に便利である。