중첩 배열 평면화 - Amazon Athena
평면화 함수 사용CROSS JOIN 및 UNNEST 사용

중첩 배열 평면화

중첩 배열로 작업할 때 중첩 배열 요소를 단일 배열로 확장하거나 배열을 여러 행으로 확장해야 하는 경우가 많습니다.

평면화 함수 사용

중첩된 배열의 요소를 여러 값의 단일 배열로 평면화하려면 flatten 함수를 사용합니다. 이 쿼리는 배열의 각 요소에 대한 행을 반환합니다.

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

이 쿼리가 반환하는 값:

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

CROSS JOIN 및 UNNEST 사용

배열을 여러 행으로 평면화하려면 다음 예제에서와 같이 UNNEST 연산자와 함께 CROSS JOIN을 사용합니다.

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은(는) 기본 조인 연산자라서 묵시적이므로 앞에 CROSS JOIN을(를) 붙이지 않고 FROM 절에 사용할 수 있습니다.

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    |
+--------------+

CROSS JOIN 및 UNNEST 고려 사항

UNNEST가 쿼리에 있는 하나 이상의 배열에 사용되고 배열 중 하나가 NULL인 경우 쿼리는 행을 반환하지 않습니다. 빈 문자열인 배열에 UNNEST를 사용하면 빈 문자열이 반환됩니다.

예를 들어 다음 쿼리에서는 두 번째 배열이 null이므로 쿼리는 행을 반환하지 않습니다.

SELECT 
    col1, 
    col2 
FROM UNNEST (ARRAY ['apples','oranges','lemons']) AS t(col1)
CROSS JOIN UNNEST (ARRAY []) AS t(col2)

다음 예제에서는 두 번째 배열이 빈 문자열을 포함하도록 수정됩니다. 각 행에 대해 쿼리는 col1의 값을 반환하고 col2의 값에 대해 빈 문자열을 반환합니다. 첫 번째 배열의 값이 반환되려면 두 번째 배열의 빈 문자열이 필요합니다.

SELECT 
    col1, 
    col2 
FROM UNNEST (ARRAY ['apples','oranges','lemons']) AS t(col1)
CROSS JOIN UNNEST (ARRAY ['']) AS t(col2)