概要
データ分析の世界では、時に「非正規化」されたデータ、特にカンマ区切り（CSV形式など）で複数の値が1つのフィールドに格納されているデータに遭遇します。このようなデータは、そのままでは集計や分析が困難ですが、SQLの強力な機能を用いることで、効率的に処理し、価値あるインサイトを引き出すことが可能です。本記事では、非正規化されたカンマ区切りデータを対象に、SQLの基本的な結合（JOIN）と集計（GROUP BY, COUNT, SUMなど）を組み合わせた実践的な問題解決アプローチを、具体的なSQLコードと共に解説します。このスキルを習得することで、より複雑なデータ構造にも対応できるようになり、データ分析の幅が格段に広がります。
詳細解説
非正規化されたカンマ区切りデータとは、例えば「商品A,商品B,商品C」のように、1つのフィールドに複数の商品名やタグなどがカンマで区切られて格納されている状態を指します。リレーショナルデータベースの正規化の原則に反するため、一般的には避けるべき設計ですが、レガシーシステムや特定のアプリケーションの都合上、このようなデータ形式が存在することは珍しくありません。
このようなデータを扱う際の主な課題は以下の2点です。
1. **個々の要素へのアクセス**: カンマ区切り文字列を個々の要素に分解する必要があります。
2. **集計と結合**: 分解した要素を基に、他のテーブルと結合したり、出現頻度を集計したりすることが困難になります。
これらの課題を解決するために、SQLではいくつかのテクニックを組み合わせます。
1. カンマ区切り文字列の分解
多くのデータベースシステムでは、文字列を区切り文字で分割するための関数が提供されています。代表的なものとしては、以下のような関数があります。
* **SQL Server**: `STRING_SPLIT()` 関数
* **PostgreSQL**: `string_to_array()` 関数と `unnest()` 関数
* **MySQL**: `FIND_IN_SET()` 関数や、ユーザー定義関数（UDF）を作成する方法、あるいは `SUBSTRING_INDEX()` 関数を繰り返し使う方法などがあります。
* **Oracle**: `REGEXP_SUBSTR()` 関数や `XMLTABLE` を利用する方法などがあります。
これらの関数を利用することで、カンマ区切りの文字列を、本来あるべき「行」として扱うことができるようになります。例えば、`STRING_SPLIT()` を使うと、「商品A,商品B,商品C」という文字列が、「商品A」、「商品B」、「商品C」という個別の行に分解されます。
2. 分解したデータの結合と集計
文字列を分解し、個々の要素を行として扱えるようになったら、あとは通常のSQLと同様に、他のテーブルとの結合や集計が可能になります。
* **結合 (JOIN)**: 分解した各要素（例: 商品名）をキーとして、別の商品マスタテーブルなどと結合し、商品名に対応する詳細情報（価格、カテゴリなど）を取得できます。
* **集計 (GROUP BY)**: 分解した要素ごとにグループ化し、出現回数（COUNT）や合計値（SUM）などを集計します。例えば、どの商品が最も多く購入されているか、といった分析が可能になります。
このプロセスは、一度分解してしまえば、あとは標準的なSQL構文で記述できるため、理解しやすいのが特徴です。
サンプルコード
ここでは、架空の「注文履歴」テーブルと「商品マスタ」テーブルを例に、非正規化されたカンマ区切りデータを処理する具体的なSQLコードを示します。
**前提となるテーブル構造:**
* `orders` テーブル:
* `order_id` (INT, 主キー)
* `customer_id` (INT)
* `order_date` (DATE)
* `product_ids` (VARCHAR) – カンマ区切りで商品IDが格納されている (例: “P001,P003”)
* `products` テーブル:
* `product_id` (VARCHAR, 主キー)
* `product_name` (VARCHAR)
* `category` (VARCHAR)
**目的:**
1. 各注文に含まれる商品IDを個別の行に分解し、注文IDと紐付ける。
2. 分解した商品IDを `products` テーブルと結合し、商品名を取得する。
3. 最も多く注文されている商品を、その注文回数と共にリストアップする。
—
**SQL Server を使用する場合の例:**
— 1. product_ids を分解し、個別の行に展開する (CROSS APPLY と STRING_SPLIT を使用)
WITH SplitProducts AS (
SELECT
o.order_id,
o.customer_id,
o.order_date,
sp.value AS product_id
FROM
orders AS o
CROSS APPLY
STRING_SPLIT(o.product_ids, ‘,’) AS sp
)
— 2. 分解した product_id を products テーブルと結合し、商品名を取得する
SELECT
sp.order_id,
sp.customer_id,
sp.order_date,
sp.product_id,
p.product_name,
p.category
FROM
SplitProducts AS sp
JOIN
products AS p ON sp.product_id = p.product_id;
— 3. 最も多く注文されている商品をカウントする
WITH SplitProducts AS (
SELECT
o.order_id,
sp.value AS product_id
FROM
orders AS o
CROSS APPLY
STRING_SPLIT(o.product_ids, ‘,’) AS sp
)
SELECT
sp.product_id,
p.product_name,
COUNT(sp.product_id) AS order_count
FROM
SplitProducts AS sp
JOIN
products AS p ON sp.product_id = p.product_id
GROUP BY
sp.product_id, p.product_name
ORDER BY
order_count DESC;
—
**PostgreSQL を使用する場合の例:**
— 1. product_ids を分解し、個別の行に展開する (unnest と string_to_array を使用)
WITH SplitProducts AS (
SELECT
o.order_id,
o.customer_id,
o.order_date,
unnest(string_to_array(o.product_ids, ‘,’)) AS product_id
FROM
orders AS o
)
— 2. 分解した product_id を products テーブルと結合し、商品名を取得する
SELECT
sp.order_id,
sp.customer_id,
sp.order_date,
sp.product_id,
p.product_name,
p.category
FROM
SplitProducts AS sp
JOIN
products AS p ON sp.product_id = p.product_id;
— 3. 最も多く注文されている商品をカウントする
WITH SplitProducts AS (
SELECT
o.order_id,
unnest(string_to_array(o.product_ids, ‘,’)) AS product_id
FROM
orders AS o
)
SELECT
sp.product_id,
p.product_name,
COUNT(sp.product_id) AS order_count
FROM
SplitProducts AS sp
JOIN
products AS p ON sp.product_id = p.product_id
GROUP BY
sp.product_id, p.product_name
ORDER BY
order_count DESC;
—
**MySQL を使用する場合の例 (FIND_IN_SET を利用):**
※ `FIND_IN_SET` は、文字列中に特定の値が含まれているかを判定する関数であり、直接的な分解関数ではありません。ここでは、分解された結果を一時テーブルなどに格納するか、あるいはより高度なテクニック（JSON関数や再帰CTEなど、MySQLのバージョンに依存）が必要になる場合があります。
以下は、`FIND_IN_SET` を使って、特定の注文に特定の商品が含まれているかを確認する例ですが、直接的な分解・集計には工夫が必要です。
より一般的なMySQLでの分解には、ループ処理やユーザー定義関数が使われることが多いですが、ここでは標準SQLに近いアプローチとして、 概念を示すに留めます。
**MySQLでの実用的な分解には、以下のようなアプローチが考えられます:**
1. **ストアドプロシージャ/関数**: カンマ区切り文字列をループで処理し、結果をテーブル変数や一時テーブルに格納する関数を作成する。
2. **JSON関数 (MySQL 5.7以降)**: 文字列をJSON配列に変換し、`JSON_TABLE` などで展開する。
3. **再帰CTE (MySQL 8.0以降)**: 文字列を再帰的に分割していく。
ここでは、概念的な理解を助けるため、`FIND_IN_SET` の使い方を例示し、その限界についても触れます。
— FIND_IN_SET を使った、特定の注文に特定の商品が含まれているかの確認例
SELECT
o.order_id,
o.customer_id,
o.order_date
FROM
orders AS o
WHERE
FIND_IN_SET(‘P001’, o.product_ids) > 0; — P001 が product_ids に含まれる注文を抽出
— 最も多く注文されている商品をカウントするためのMySQLでの一般的なアプローチ（抜粋）
— この処理は、上記SQL ServerやPostgreSQLのような単一クエリで直接行うのが難しいため、
— 一度分解して一時テーブルに入れるなどの工夫が必要です。
— 例として、概念的なイメージを以下に示します。
— 1. 注文履歴テーブルから、各商品IDを個別の行として取得する（※実際には上記のように分解処理が必要）
— （ここでは、分解済みのデータが ‘TempSplitProducts’ という一時テーブルにあると仮定）
/*
SELECT
temp.product_id,
p.product_name,
COUNT(temp.product_id) AS order_count
FROM
TempSplitProducts AS temp
JOIN
products AS p ON temp.product_id = p.product_id
GROUP BY
temp.product_id, p.product_name
ORDER BY
order_count DESC;
*/
**注意点:** データベースシステムによって、文字列分割関数やその使い方が異なります。ご自身の利用環境に合わせて、適切な関数を選んでください。
実務アドバイス
* **パフォーマンスへの影響**: カンマ区切り文字列の分割処理は、特にデータ量が多い場合にパフォーマンスに影響を与える可能性があります。可能であれば、アプリケーション側でデータを正規化してからデータベースに格納するか、あるいはデータベース側で正規化されたテーブル構造に変更することを検討してください。
* **データの一貫性**: カンマ区切り文字列内の値のフォーマット（例: スペースの有無、大文字/小文字）に一貫性がないと、結合や集計がうまくいかないことがあります。`TRIM()` 関数や `UPPER()`/`LOWER()` 関数などを活用して、データを整形することを忘れないでください。
* **代替手段の検討**: データベースによっては、JSON型や配列型をサポートしており、これらの型を用いることで、より効率的かつ安全に複数値を格納・処理できる場合があります。
* **再帰CTEの活用**: MySQL 8.0以降やSQL Server、PostgreSQLなどの新しいバージョンでは、再帰CTE（Common Table Expression）を利用して、カンマ区切り文字列をより柔軟に分解できます。これは、特定の関数が利用できない場合や、より複雑な分割ロジックが必要な場合に有効な手段です。
* **パフォーマンスチューニング**: 上記のような分解処理を頻繁に行う必要がある場合は、一時テーブルや共通テーブル式（CTE）を効果的に使用し、クエリの可読性とパフォーマンスを向上させましょう。また、必要に応じてインデックスの検討も行います。
まとめ
非正規化されたカンマ区切りデータは、一見すると扱いにくいように思えますが、SQLの文字列操作関数や結合、集計機能を組み合わせることで、強力なデータ分析ツールに変貌させることができます。本記事で紹介した `STRING_SPLIT` (SQL Server)、`unnest` (PostgreSQL) などの関数は、このようなデータ構造を扱う上での強力な味方となります。
これらのテクニックを習得することで、レガシーシステムから得られるデータや、外部から受け取った非構造化データに対しても、データ分析の第一歩を踏み出すことが可能になります。常にパフォーマンスとデータの一貫性を意識しながら、これらの手法を実践してみてください。
