SQL 文を使用した複数行データの 1 行へのマージ - MaxCompute

MaxCompute SQL を使用して、複数行の値を単一の行にマージします。ほとんどのユースケースをカバーする2つのパターンを次に示します。

文字列連結: wm_concat を使用して、複数行の値を1つのカンマ区切りの文字列に折りたたみ、列でグループ化します。
条件付き集約: CASE WHEN と SUM を使用して、行全体でカテゴリ別に値をカウントまたは合計します。これは、ナローテーブルをワイドテーブルに変換する標準的なSQLアプローチです。

サンプルデータ

このページの例では、students という名前のテーブルを使用します。

クラス	性別	名前
1	M	LiLei
1	F	HanMM
1	M	Jim
1	F	HanMM
2	F	Kate
2	M	Peter

行の値を文字列に連結

wm_concat を使用して、複数行の値を単一の文字列に集約し、列でグループ化します。

構文：

wm_concat([DISTINCT] '<delimiter>', <column>)

パラメータ	必須	説明
`DISTINCT`	いいえ	連結前に重複値を削除します。
`<delimiter>`	はい	連結された値の間に配置される区切り文字列。
`<column>`	はい	値が連結される列。

関数の完全なリファレンスについては、「集約関数」をご参照ください。

基本的な連結

クラス別に名前をグループ化し、すべての値をカンマで連結します。

SELECT class, wm_concat(',', name) AS names
FROM students
GROUP BY class;

class	names
1	LiLei,HanMM,Jim,HanMM
2	Kate,Peter

重複排除を伴う連結

連結前に重複値を削除するには、DISTINCT を追加します。

SELECT class, wm_concat(DISTINCT ',', name) AS names
FROM students
GROUP BY class;

class	names
1	LiLei,HanMM,Jim
2	Kate,Peter

カテゴリ別に値をカウント (条件付き集約)

CASE WHEN と SUM を使用して、行の値を列にピボットします。これは、グループごとに各条件を満たす行数をカウントします。

次のステートメントは、各クラスの男性と女性の数をカウントします。

SELECT
  class,
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS cnt_m,
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS cnt_f
FROM students
GROUP BY class;

class	cnt_m	cnt_f
1	2	2
2	1	1

さらにカテゴリを追加するには、SELECT 句に SUM(CASE WHEN ...) 式を追加します。