SQL基礎問題10：非正規化（カンマ区切り）の結合と集計

公開日：2025-11-06　最終更新日：2025-11-06

SQL基礎問題10：非正規化（カンマ区切り）の結合と集計

カンマ区切りで格納された非正規化データを用いて多対多の関連を解消し、特定の有効期間条件のもとで値を集計する問題です。

SQLの練習用になるべく単純なSQLで済む問題を出します。
SQLの練習として取り組んで見てください。

問題

※画像※画像はExcelです。

テーブル：TBLA		テーブル：TBLB		テーブル：TBLC
ID	B_ID	ID	C_IDs	ID	値	終了日
A01	B01	B01	C01,C03,C05,C07,C09	C01	7
A02	B03	B02	C02,C04	C02	15
A03	B04	B03	C01,C10	C03	10	2025/12/11
A04	B11	B04	C02,C03,C06	C04	9
A05	B14	B11	C02,C04,C11	C05	11	2024/11/3
A06	B12	B12	C04,C08	C06	12	2025/10/20
		B13	C07,C09,C11	C07	10
		B14	C12,C13	C08	7
				C09	8
				C10	3

【SQL問題】
テーブルは画像参照
・TBLAのB_IDでTBLBを結合し、TBLBのB.C_IDsでTBLCを結合します。
・C_IDsはカンマ(,)区切りでhttp://TBLC.IDが並んでいます。
・TBLAのIDごとにTBLCの値を合計して出力（画像参照）。
■終了日条件
終了日が未来、または NULL のみ対象とする。

解答SQL

SQLite3

単一SELECT

SELECT A.ID,A.B_ID,B.C_IDs,IFNULL(SUM(C.値), 0) AS 値合計
FROM TBLA AS A
LEFT JOIN TBLB AS B
       ON A.B_ID = B.ID
LEFT JOIN TBLC AS C
       ON (',' || B.C_IDs || ',') LIKE ('%,' || C.ID || ',%')
      AND (C.終了日 > STRFTIME('%Y/%m/%d', DATE('now')) OR C.終了日 IS NULL)
GROUP BY A.ID,A.B_ID,B.C_IDs

サブクエリでJOIN

SELECT A.ID,A.B_ID,B_SUM.C_IDs,IFNULL(B_SUM.合計値, 0) AS 値合計
FROM TBLA AS A
LEFT JOIN (SELECT B.ID AS B_ID,B.C_IDs,SUM(C.値) AS 合計値
           FROM TBLB AS B
           LEFT JOIN TBLC AS C
                  ON (',' || B.C_IDs || ',') LIKE ('%,' || C.ID || ',%')
                 AND (C.終了日 > DATE('now') OR C.終了日 IS NULL)
           GROUP BY B.ID, B.C_IDs
          ) AS B_SUM
       ON A.B_ID = B_SUM.B_ID
GROUP BY A.ID, A.B_ID, B_SUM.C_IDs

CTE (BC_Join)

WITH BC_Join AS
    (SELECT B.ID AS B_ID,B.C_IDs,SUM(C.値) AS 合計値
     FROM TBLB AS B
     LEFT JOIN TBLC AS C
            ON (',' || B.C_IDs || ',') LIKE ('%,' || C.ID || ',%')
           AND (C.終了日 > STRFTIME('%Y/%m/%d', DATE('now')) OR C.終了日 IS NULL)
     GROUP BY B.ID, B.C_IDs
)
SELECT A.ID,A.B_ID,BCJ.C_IDs,IFNULL(BCJ.合計値, 0) AS 値合計
FROM TBLA AS A
LEFT JOIN BC_Join AS BCJ
       ON A.B_ID = BCJ.B_ID
GROUP BY A.ID, A.B_ID, BCJ.C_IDs

SQLの解説

3つのSQL手法の概要

形式	可読性	集計の正確性	実行順序	複雑な処理への適性
単一SELECT	低い	低い	結合 → 集計	単純な処理向き
サブクエでJOIN	中程度	高い	集計 → 結合	段階的な処理向き
CTE (BC_Join)	高い	高い	集計 → 結合	複雑・複数段階の処理向き

単一SELECT

単一 SELECT 形式 (最もシンプルだが非推奨)
最も単純な書き方ですが、カンマ区切りJOINのような複雑な処理では、SQLiteで集計値エラー（全て同じ値になる）を引き起こすため、非推奨です。
構造: 結合と集計を一つのSELECT文で完結させます。
問題点: 結合による行の展開と集計の順序が制御できず、TBLC側での重複結合が原因で、集計結果が誤って全体合計値になるリスクが高いです。

サブクエリでJOIN

サブクエリ形式 (集計の正確性を確保)
集計の重複問題を回避するために、集計を結合に先行させることを強制した形式です。カンマ区切りJOINの集計では、この形式が最も確実で実用的です。
構造: FROM句内にネストされた別のSELECT文（サブクエリ）で中間的な集計を先に行います。
利点: TBLBのキー（B.ID）単位でTBLCの合計値を先に確定させるため、集計のスコープ（適用範囲）が明確になり、TBLAとの結合による影響を受けません。

CTE (BC_Join)

CTE (共通テーブル式) 形式 (最も高い可読性)
サブクエリと同じロジックを、WITH句を使ってクエリの先頭に定義した形式です。
構造: WITH句で論理的な中間テーブル（BC_Join）を定義した後、メインのSELECTで利用します。
利点: 処理のステップが上から下に記述されるため、非常に高い可読性を持ちます。複雑なクエリや複数段階の処理に適しています。

Excel

単一SELECT

=LET(sql,"
SELECT A.ID,A.B_ID,B.C_IDs,IIF(IsNull(SUM(C.値)), 0, SUM(C.値)) AS 値合計
FROM ((?TBLA AS A
LEFT JOIN ?TBLB AS B
       ON A.B_ID = B.ID)
LEFT JOIN ?TBLC AS C
       ON (',' & B.C_IDs & ',') LIKE ('%,' & C.ID & ',%')
      AND (FORMAT(C.終了日,'yyyymmdd') > FORMAT(DATE(),'yyyymmdd') OR C.終了日 =''))
GROUP BY A.ID,A.B_ID,B.C_IDs
",
sql_1,SUBSTITUTE(sql,"?TBLA","[CSV$A2:B8]"),
sql_2,SUBSTITUTE(sql_1,"?TBLB","[CSV$D2:E10]"),
sql_3,SUBSTITUTE(sql_2,"?TBLC","[CSV$G2:I12]"),
QUERY(sql_3))

サブクエリでJOIN

=LET(sql,"
SELECT A.ID,A.B_ID,B_SUM.C_IDs,IIF(IsNull(SUM(B_SUM.合計値)), 0, SUM(B_SUM.合計値)) AS 値合計
FROM ?TBLA AS A
LEFT JOIN (SELECT B.ID AS B_ID,B.C_IDs,SUM(C.値) AS 合計値
           FROM ?TBLB AS B
           LEFT JOIN ?TBLC AS C
                  ON (',' & B.C_IDs & ',') LIKE ('%,' & C.ID & ',%')
                 AND (FORMAT(C.終了日,'yyyymmdd') > FORMAT(DATE(),'yyyymmdd') OR C.終了日 ='')
           GROUP BY B.ID, B.C_IDs
          ) AS B_SUM
       ON A.B_ID = B_SUM.B_ID
GROUP BY A.ID, A.B_ID, B_SUM.C_IDs
",
sql_1,SUBSTITUTE(sql,"?TBLA","[CSV$A2:B8]"),
sql_2,SUBSTITUTE(sql_1,"?TBLB","[CSV$D2:E10]"),
sql_3,SUBSTITUTE(sql_2,"?TBLC","[CSV$G2:I12]"),
QUERY(sql_3))

Excelでは、ウィンドウ関数とCTE、さらに、CASE WHENは使えません。
また、IFNULL関数の違い、日付の扱い等々の違いがあります。

主要データベースにおけるカンマ区切りデータの扱い

以下は生成AIを使って作成しています。多くのDBでの情報につき、著者未確認も含まれます。

DBMS	行展開／文字列分割 (行に展開)	集合化 (複数行 → 文字列)	補足コメント
PostgreSQL	UNNEST(STRING_TO_ARRAY(...))	STRING_AGG(expression, delimiter)	組み込み関数で簡潔。WITH ORDINALITYで順序情報も保持可能。
SQL Server (v.2016以降)	STRING_SPLIT	STRING_AGG(expression, delimiter) (2017以降)	STRING_SPLITは順序保証なし。【重要】SQL Server 2022/Azure SQL Databaseでは enable_ordinal パラメータで順序情報取得可能。
SQLite	json_each または再帰CTE	GROUP_CONCAT(expression, delimiter)	json_eachはJSON形式への事前変換が必要。GROUP_CONCATはSQLite独自の標準関数。
MySQL (v8.0以降)	再帰CTE、JSON_TABLE、SUBSTRING_INDEXの組合せ	GROUP_CONCAT(expression SEPARATOR delimiter)	専用の分割関数なし。JSON_TABLEや再帰CTEが主流。集約はGROUP_CONCAT。
Oracle	REGEXP_SUBSTR + CONNECT BY LEVEL	LISTAGG(expression, delimiter)	REGEXP_SUBSTR、XMLTABLEを利用。PostgreSQLほど簡潔ではないが、機能は強力。集約はLISTAGGが標準。
Access/ADO	VBAコードでループ処理	サブクエリ＋カスタムVBA関数	SQL単体では不可能。VBAコードによる処理が必須となる。

SQLiteでの使用例

※SQLの作成及び解説には生成AIの助けを借りています。特に解説文においてはAI出力を多く使用しています。

同じテーマ「SQL入門」の記事

SQL基礎問題1：最大在庫数を持つ製品の在庫金額
 SQL基礎問題2：文字列「-nn-」のnnが偶数のみ抽出
 SQL基礎問題3：文字列の一部をキーにして集計
 SQL基礎問題4：2つのテーブルの不一致を抽出
 SQL基礎問題5：複数のマスタテーブルの結合
 SQL基礎問題6：成績表から教科ごとの点数ベスト3を抽出
 SQL基礎問題7：成績表から各教科の最高点と最低点を抽出
 SQL基礎問題8：バスケット分析・ペア商品の出現回数
 SQL基礎問題9：特定商品購入者の平均購入金額
SQL基礎問題10：非正規化（カンマ区切り）の結合と集計
SQL基礎問題11：連続期間の開始月と終了月を抽出

アクセスランキング・・・ランキング一覧を見る

1.生成AIパスポート試験練習問題（四肢択一式）｜生成AI活用研究
2.最終行の取得（End,Rows.Count）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.繰り返し処理（For Next)｜VBA入門
6.RangeとCellsの使い方｜VBA入門
7.FILTER関数（範囲をフィルター処理）｜エクセル入門
8.日本の祝日一覧｜Excelリファレンス
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.セルのクリア（Clear,ClearContents）｜VBA入門

ホーム
その他
SQL入門
SQL基礎問題10：非正規化（カンマ区切り）の結合と集計

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.

SQL入門
SQL基礎問題10：非正規化（カンマ区切り）の結合と集計