UNNESTと集約
配列を行に変換し、また配列に戻します。
「UNNESTと集約」はCoddyKit上の無料SQL Academyレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。
UNNEST とは
PostgreSQL では、1つのカラムに配列を格納できます。しかし、配列の各要素を個別に扱いたい場合があります。そのようなときに役立つのが UNNEST です。
UNNEST は集合を返す関数で、配列を複数の行の集合に展開します。要素ごとに1行が生成されます。集約とは逆に、多数の行を1つにまとめるのではなく、1つの値を多数の行に展開するものと考えるとよいでしょう。
UNNEST の基本例
UNNEST の最も簡単な使い方は、配列リテラルを直接渡すことです。各要素が結果セット内の独自の行になります。
ここでは、単純な整数配列を個別の行に展開します。
SELECT UNNEST(ARRAY[10, 20, 30, 40]) AS value;テキスト配列での UNNEST
UNNEST はテキストを含むあらゆる配列型で使用できます。タグ、カテゴリ、または PostgreSQL 配列としてエンコードされたカンマ区切り形式のデータを格納するカラムを扱う場合に便利です。
SELECT UNNEST(ARRAY['apple', 'banana', 'cherry']) AS fruit;テーブルカラムからの UNNEST
UNNEST の本当の力は、実際のテーブルカラムに対して使用したときに発揮されます。テーブルの各行に異なる長さの配列があっても、UNNEST によってそれらすべてを個別の行に展開できます。
この例では、products テーブルに tags という text 配列カラムがあります。
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
tags TEXT[]
);
INSERT INTO products (name, tags) VALUES
('Laptop', ARRAY['electronics', 'computing', 'portable']),
('Shirt', ARRAY['clothing', 'casual']),
('Blender', ARRAY['kitchen', 'electronics']);
SELECT name, UNNEST(tags) AS tag
FROM products;タグの出現回数のカウント
配列カラムを展開すると、結果を他の行と同じように扱い、集約関数を適用できます。ここでは、各タグに関連付けられた商品の数を数えます。
パターンは、サブクエリまたは CTE で UNNEST を使用し、その後に展開した値で GROUP BY するというものです。
SELECT tag, COUNT(*) AS product_count
FROM (
SELECT UNNEST(tags) AS tag
FROM products
) AS expanded
GROUP BY tag
ORDER BY product_count DESC;WITH ORDINALITY を使った UNNEST
配列内の要素の位置が重要になる場合があります。PostgreSQL では WITH ORDINALITY を使用して、展開された各要素に行番号を付けることができます。これにより、配列内での元のインデックス（1始まり）が分かります。
SELECT val, pos
FROM UNNEST(ARRAY['first', 'second', 'third']) WITH ORDINALITY AS t(val, pos);テーブルでの Ordinality の使用
配列に順序付きリストを格納する場合、WITH ORDINALITY が特に役立ちます。たとえば、曲順が重要なプレイリストテーブルなどです。
CREATE TABLE playlists (
id SERIAL PRIMARY KEY,
title TEXT,
tracks TEXT[]
);
INSERT INTO playlists (title, tracks) VALUES
('Morning Mix', ARRAY['Song A', 'Song B', 'Song C']);
SELECT p.title, track, position
FROM playlists p,
UNNEST(p.tracks) WITH ORDINALITY AS t(track, position)
ORDER BY p.id, position;UNNEST 後の絞り込み
UNNEST によって配列の要素が行に変換されるため、通常の WHERE 句で絞り込めます。これにより、配列包含演算子を使わずに、配列に特定の値を含むすべての行を検索できます。
SELECT DISTINCT name
FROM products,
UNNEST(tags) AS tag
WHERE tag = 'electronics';配列への再集約
UNNEST の逆の操作が ARRAY_AGG です。行を展開して変換または絞り込みを行った後、結果を配列に戻してまとめることができます。この「展開、処理、再集約」という往復パターンは、PostgreSQL でよく使われる慣用的な方法です。
SELECT ARRAY_AGG(tag ORDER BY tag) AS sorted_tags
FROM (
SELECT UNNEST(ARRAY['cherry', 'apple', 'banana']) AS tag
) AS t;配列要素の重複除去
unnest-aggregateのラウンドトリップの実用的な用途の一つは、配列から重複する要素を削除することです。配列をUNNESTし、DISTINCTを適用してから、ARRAY_AGGで再び配列にまとめます。
SELECT ARRAY_AGG(DISTINCT tag ORDER BY tag) AS unique_tags
FROM UNNEST(ARRAY['sql', 'database', 'sql', 'postgresql', 'database']) AS tag;複数の配列カラムの結合
FROM句では、複数の配列を並列にUNNESTできます。PostgreSQLは位置に基づいて要素を対応付けます。配列の長さが異なる場合、短い配列では長い配列の余分な位置にNULLが生成されます。
SELECT key, value
FROM UNNEST(
ARRAY['name', 'city', 'role'],
ARRAY['Alice', 'Istanbul', 'DBA']
) AS t(key, value);理解度チェック
PostgreSQLにおけるUNNESTと配列の集約について、理解度を確認しましょう。
レッスンのまとめ
このレッスンでは、PostgreSQLの組み込み関数を使って、配列を行に変換したり、行から配列に戻したりする方法を学びました。
- UNNEST — 配列を要素ごとに1行へ展開します。リテラルとテーブルのカラムのどちらにも使用できます。
- WITH ORDINALITY — 配列内での元の順序が分かるように、展開した各要素へ位置インデックスを付加します。
- フィルタリング — 展開後は、通常の行と同じようにWHEREを使用できます。
- ARRAY_AGG — UNNESTの逆の操作で、行を配列にまとめます。必要に応じてORDER BYやDISTINCTも指定できます。
- 並列UNNEST — FROM句内の複数の配列を横並びに展開し、位置に基づいて対応付けます。
このアンネスト、処理、再集約のパターンを身につけると、SQLの豊かな表現力を活用して配列データを扱えるようになります。
よくある質問
「UNNESTと集約」レッスンは無料ですか？
はい。「UNNESTと集約」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。
「UNNESTと集約」で何を学びますか？
配列を行に変換し、また配列に戻します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Academyを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。
「UNNESTと集約」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Academyレッスンでコードを書いて実行できますか？
はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- 配列型カラムの基本
- 配列内部を検索する
- UNNESTと集約
- 配列と正規化テーブル