Coding Interview Prep · 강의

알 수 없는 열을 사용하는 동적 피벗

카테고리를 미리 알 수 없을 때 피벗 열을 생성합니다.

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

어려운 피벗 문제

CASE 집계, SQL 서버의 PIVOT, PostgreSQL의 crosstab 중 어떤 방식을 사용하든 모든 정적 피벗에는 한 가지 한계가 있습니다. 쿼리를 작성할 때 출력 열을 나열해야 한다는 것입니다.

그렇다면 매주 바뀌는 제품 이름이나 현재 활성 상태인 월마다 하나의 열을 만들어야 하는 것처럼 범주를 미리 알 수 없다면 어떻게 해야 할까요? 이것이 동적 피벗이며, 일반 SQL만으로는 실행 시점에 열 목록이 결정되는 결과를 반환할 수 없기 때문에 고급 면접 문제로 자주 출제됩니다.

SQL만으로는 할 수 없는 이유

SQL은 결과 집합 수준에서 정적으로 형식이 지정됩니다. 실행 계획기는 실행 전에 열과 해당 형식을 알아야 합니다. 단일 쿼리로 찾은 각 값에 대해 열 하나를 만들라고 지정할 수는 없습니다.

따라서 보편적인 방법은 두 단계로 SQL 텍스트를 생성하는 것입니다. 먼저 고유한 범주를 조회한 다음, 그 범주로 피벗 쿼리 문자열을 만들고 해당 문자열을 실행합니다.

1단계: 범주 수집하기

첫 단계는 열이 될 고유한 값을 나열하는 일반 쿼리입니다. 일반적으로 열 배치를 일정하게 유지하기 위해 값의 순서를 지정합니다.

이 결과가 문자열 생성 단계의 입력이 됩니다. 실제 시스템에서는 이 쿼리를 실행하고 행을 저장한 다음, 그 값으로 다음 쿼리를 조합합니다.

SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4

2단계: 열 목록 만들기

다음으로 해당 값들을 쉼표로 구분된 CASE 식 목록(또는 PIVOT에 사용할 대괄호로 묶은 이름)으로 변환합니다. 데이터베이스는 SQL 자체에서 이를 수행할 수 있도록 문자열 집계 함수를 제공합니다.

PostgreSQL에서는 string_agg, MySQL에서는 GROUP_CONCAT, SQL 서버에서는 STRING_AGG 또는 이전 방식인 FOR XML PATH 트릭을 사용합니다.

-- Postgres: build the SELECT-list fragment
SELECT string_agg(
  format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
         quarter, quarter),
  ', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;

3단계: 조합하고 실행하기

생성한 조각을 전체 쿼리 문자열에 이어 붙인 다음 동적 실행으로 실행합니다. PostgreSQL의 PL/pgSQL에서는 EXECUTE, SQL 서버에서는 sp_executesql, MySQL에서는 PREPARE/EXECUTE를 사용합니다.

이것이 동적 피벗의 핵심입니다. SQL이 SQL을 작성한 다음 실행합니다.

-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
  FROM (SELECT region, quarter, amount FROM sales) s
  PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;

PostgreSQL 전체 예제

PostgreSQL에서는 세 단계를 DO 블록이나 함수로 묶습니다. string_agg로 열 목록을 만들고, 이를 쿼리에 삽입한 다음 EXECUTE로 실행합니다.

결과 열은 실행 시점까지 알 수 없으므로, 이를 반환하는 함수는 흔히 RETURNS SETOF record를 사용하거나 행을 json으로 반환하며, 호출하는 쪽에서 이를 펼칩니다.

DO $do$
DECLARE
  cols text;
  qry  text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
  INTO cols
  FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

준비된 문을 사용하는 MySQL

MySQL에는 피벗 연산자가 없으므로, 동적 피벗에서는 GROUP_CONCAT으로 조건부 집계 문자열을 만든 다음 준비된 문을 통해 실행합니다.

GROUP_CONCAT에는 길이 제한(group_concat_max_len)이 있습니다. 면접에서 이 점을 언급할 수 있으며, 범주가 많다면 제한을 늘리십시오.

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN quarter=''', quarter,
         ''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
                  ' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SQL 삽입 공격 위험

데이터 값을 실행 가능한 SQL에 이어 붙이므로 동적 피벗에는 삽입 공격 위험이 있습니다. 범주 값에 따옴표나 악의적인 텍스트가 포함되면 생성된 쿼리가 깨지거나 공격자에게 장악될 수 있습니다.

항상 데이터베이스 엔진의 안전한 도우미를 사용해 식별자와 리터럴을 이스케이프하십시오. PostgreSQL에서는 format('%I', ...)와 %L, SQL 서버에서는 QUOTENAME을 사용합니다. 원시 값을 문자열에 그대로 삽입하지 마십시오.

-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)

알 수 없는 열 반환하기

두 번째 어려운 점은 호출하는 쪽에서 결과 구조를 미리 알 수 없다는 것입니다. 면접에서 인정받는 일반적인 방법은 다음과 같습니다.

행을 JSON으로 반환하고 애플리케이션 계층에서 키를 펼칩니다.
프로시저가 쿼리를 출력하거나 생성하게 한 다음, 두 번째 단계에서 실행합니다.
범주를 파악한 후 애플리케이션 코드(판다스, BI 도구)에서 최종 피벗을 수행합니다.

하나의 정적 호출에서 임의의 열을 반환하는 깔끔한 방법은 없습니다.

실습 예제: 제품별 피벗

제품이 생겼다가 사라지고, 보고서에는 현재 sales에 있는 각 제품마다 하나의 수익 열이 필요하다고 가정해 보겠습니다. 목록을 고정해서 작성할 수 없으므로 생성해야 합니다. PostgreSQL에서는 읽기 쉽게 처리할 수 있습니다. string_agg와 안전한 인용을 사용해 CASE 조각을 만들고, 이를 쿼리에 삽입한 다음 EXECUTE합니다.

면접관에게 다음 순서로 설명하십시오. 제품을 찾고, 각 제품을 인용된 열로 형식화하고, 조합한 다음 실행합니다. 같은 구조는 어떤 데이터베이스 엔진에도 적용되며, 도우미만 달라집니다.

DO $do$
DECLARE cols text; qry text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
           product, product), ', ')
  INTO cols
  FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

동적 피벗을 피해야 하는 경우

역량이 뛰어난 지원자는 SQL에서 언제 이 방식을 사용하지 않아야 하는지도 알고 있습니다. 동적 SQL은 읽고, 테스트하고, 보안을 유지하고, 캐시하기가 더 어렵습니다. 다음과 같이 답하는 편이 더 나은 경우가 많습니다.

SQL에서는 긴 형식으로 반환하고 애플리케이션이나 보고 계층에서 피벗합니다.
범주 집합이 작고 변경이 느리다면 정적 피벗을 사용하고 가끔 갱신합니다.

정말로 개방적이고 계속 변하는 범주 집합에만 동적 피벗을 사용하십시오.

빠른 확인

동적 피벗이 존재하는 핵심 이유를 테스트해 보십시오.

요약

동적 피벗은 알 수 없는 열 집합을 처리합니다.

결과 열은 실행 전에 고정되어야 하므로 정적 피벗으로는 처리할 수 없습니다.
패턴은 다음과 같습니다. 고유한 범주를 조회하고, 피벗 SQL 문자열을 만든 다음, 동적으로 실행합니다.
string_agg/GROUP_CONCAT/STRING_AGG를 사용해 열 목록을 만듭니다.
SQL 삽입 공격을 막기 위해 값을 이스케이프합니다(%I/%L, QUOTENAME).
긴 형식으로 반환한 다음 애플리케이션 계층에서 피벗하는 편이 더 깔끔한 경우가 많습니다.

자주 묻는 질문

“알 수 없는 열을 사용하는 동적 피벗” 강의는 무료인가요?

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

“알 수 없는 열을 사용하는 동적 피벗”에서 뭘 배우나요?

카테고리를 미리 알 수 없을 때 피벗 열을 생성합니다. 브라우저에서 직접 실행하는 실습 코드로 Coding Interview Prep을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

Coding Interview Prep을(를) 시작하는 데 경험이 필요한가요?

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

“알 수 없는 열을 사용하는 동적 피벗” 강의는 얼마나 걸리나요?

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

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

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

이 강의의 모든 강의

← Coding Interview Prep(으)로 돌아가기