Amazon Athena
ユーザーガイド

ネストされた配列のフラット化

ネストされた配列を使用する場合、必要に応じて、ネストされた配列の要素を単一の配列に展開したり、配列を複数の行に展開したりすることがあります。

ネストされた配列の要素を単一の値配列にフラット化するには、flatten 関数を使用します。次のクエリは、配列の要素ごとに行を返します。

SELECT flatten(ARRAY[ ARRAY[1,2], ARRAY[3,4] ]) AS items

このクエリは以下を返します。

+-----------+ | items | +-----------+ | [1,2,3,4] | +-----------+

配列を複数の行にフラット化するには、次の例に示すように、CROSS JOIN 演算子を UNNEST 演算子と組み合わせて使用します。

WITH dataset AS ( SELECT 'engineering' as department, ARRAY['Sharon', 'John', 'Bob', 'Sally'] as users ) SELECT department, names FROM dataset CROSS JOIN UNNEST(users) as t(names)

このクエリは以下を返します。

+----------------------+ | department | names | +----------------------+ | engineering | Sharon | +----------------------| | engineering | John | +----------------------| | engineering | Bob | +----------------------| | engineering | Sally | +----------------------+

キーと値のペアの配列をフラット化するには、次の例に示すように、選択したキーを列に入れ替えます。

WITH dataset AS ( SELECT 'engineering' as department, ARRAY[ MAP(ARRAY['first', 'last', 'age'],ARRAY['Bob', 'Smith', '40']), MAP(ARRAY['first', 'last', 'age'],ARRAY['Jane', 'Doe', '30']), MAP(ARRAY['first', 'last', 'age'],ARRAY['Billy', 'Smith', '8']) ] AS people ) SELECT names['first'] AS first_name, names['last'] AS last_name, department FROM dataset CROSS JOIN UNNEST(people) AS t(names)

このクエリは以下を返します。

+--------------------------------------+ | first_name | last_name | department | +--------------------------------------+ | Bob | Smith | engineering | | Jane | Doe | engineering | | Billy | Smith | engineering | +--------------------------------------+

従業員の一覧から、総合スコアが最も高い従業員を選択します。UNNEST は、デフォルトの結合演算子で暗黙的に指定されるため、FROM 句において先行 CROSS JOIN なしで使用できます。

WITH dataset AS ( SELECT ARRAY[ CAST(ROW('Sally', 'engineering', ARRAY[1,2,3,4]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))), CAST(ROW('John', 'finance', ARRAY[7,8,9]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))), CAST(ROW('Amy', 'devops', ARRAY[12,13,14,15]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))) ] AS users ), users AS ( SELECT person, score FROM dataset, UNNEST(dataset.users) AS t(person), UNNEST(person.scores) AS t(score) ) SELECT person.name, person.department, SUM(score) AS total_score FROM users GROUP BY (person.name, person.department) ORDER BY (total_score) DESC LIMIT 1

このクエリは以下を返します。

+---------------------------------+ | name | department | total_score | +---------------------------------+ | Amy | devops | 54 | +---------------------------------+

従業員の一覧から、個別のスコアが最も高い従業員を選択します。

WITH dataset AS ( SELECT ARRAY[ CAST(ROW('Sally', 'engineering', ARRAY[1,2,3,4]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))), CAST(ROW('John', 'finance', ARRAY[7,8,9]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))), CAST(ROW('Amy', 'devops', ARRAY[12,13,14,15]) AS ROW(name VARCHAR, department VARCHAR, scores ARRAY(INTEGER))) ] AS users ), users AS ( SELECT person, score FROM dataset, UNNEST(dataset.users) AS t(person), UNNEST(person.scores) AS t(score) ) SELECT person.name, score FROM users ORDER BY (score) DESC LIMIT 1

このクエリは以下を返します。

+--------------+ | name | score | +--------------+ | Amy | 15 | +--------------+

このページの内容: