SQL Academy · 강의

UNNEST와 집계

배열을 행으로 바꾸고 다시 배열로 되돌립니다.

UNNEST와 집계은(는) CoddyKit의 무료 SQL Academy 강의입니다. 이것은 4개 중 3번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 SQL Academy 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

UNNEST란 무엇인가요

PostgreSQL에서는 하나의 열에 배열을 저장할 수 있습니다. 하지만 때로는 각 요소를 개별적으로 다뤄야 하며, 이때 UNNEST가 사용됩니다.

UNNEST는 배열을 행 집합으로 펼치는 집합 반환 함수입니다. 요소마다 하나의 행이 만들어집니다. 여러 행을 하나로 축약하는 집계와 반대로, 하나의 값을 여러 행으로 펼치는 기능이라고 생각하면 됩니다.

기본 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 텍스트 배열 열이 있습니다.

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;

서수와 함께 사용하는 UNNEST

배열 안에서 요소의 위치가 중요한 경우가 있습니다. PostgreSQL은 WITH ORDINALITY를 제공하여 펼쳐진 각 요소에 행 번호를 연결하므로 배열에서의 원래 인덱스(1부터 시작)를 알 수 있습니다.

SELECT val, pos
FROM UNNEST(ARRAY['first', 'second', 'third']) WITH ORDINALITY AS t(val, pos);

테이블에서 서수 사용하기

배열에 순서가 있는 목록을 저장할 때는 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와 집계를 왕복하는 방식은 배열에서 중복 요소를 제거할 때 실용적으로 사용할 수 있습니다. 배열에 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은 위치에 따라 요소를 짝지습니다. 배열의 길이가 서로 다르면 더 짧은 배열은 더 긴 배열의 남는 위치에 널 값을 생성합니다.

SELECT key, value
FROM UNNEST(
  ARRAY['name',    'city',      'role'],
  ARRAY['Alice',   'Istanbul',  'DBA']
) AS t(key, value);

이해도 확인

PostgreSQL에서 UNNEST와 배열 집계에 대한 이해도를 확인해 보세요.

레슨 요약

이번 레슨에서는 PostgreSQL의 내장 함수를 사용해 배열을 행으로 바꾸고 다시 배열로 되돌리는 방법을 배웠습니다.

UNNEST — 배열을 요소마다 하나의 행으로 펼치며, 리터럴과 테이블 열 모두에서 작동합니다.
WITH ORDINALITY — 펼쳐진 각 요소에 위치 인덱스를 추가하여 배열에서의 원래 순서를 알 수 있게 합니다.
필터링 — 배열을 펼친 후에는 일반 행에서와 마찬가지로 WHERE를 사용할 수 있습니다.
ARRAY_AGG — UNNEST의 반대 작업으로, 행을 다시 배열로 모읍니다. 필요에 따라 ORDER BY 또는 DISTINCT를 함께 사용할 수 있습니다.
병렬 UNNEST — FROM 절의 여러 배열을 나란히 펼쳐 위치에 따라 짝을 맞춥니다.

이러한 UNNEST-처리-재집계 패턴을 익히면 SQL의 풍부한 표현력을 활용해 배열 데이터를 다룰 수 있습니다.

자주 묻는 질문

“UNNEST와 집계” 강의는 무료인가요?

네 — “UNNEST와 집계” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 SQL Academy 강의 전체를 잠금 해제할 수 있습니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

“UNNEST와 집계”에서 뭘 배우나요?

배열을 행으로 바꾸고 다시 배열로 되돌립니다. 브라우저에서 직접 실행하는 실습 코드로 SQL Academy을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

SQL Academy을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 SQL Academy은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 3번째 강의입니다.

“UNNEST와 집계” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 SQL Academy 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 SQL Academy 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← SQL Academy(으)로 돌아가기