- 카테고리:
PIVOT¶
입력 식의 한 열에서 고유 값을 여러 열로 변환하고, 필요한 경우 나머지 열 값에 대해 결과를 집계하여 테이블을 회전합니다. 쿼리에서 테이블 이름 또는 하위 쿼리 뒤에 FROM 절에 지정됩니다.
연산자는 기본 제공 집계 함수 AVG, COUNT, MAX, MIN, SUM 을 지원합니다.
PIVOT 을 사용하여 좁은 테이블(예: empid
, month
, sales
)을 더 넓은 테이블(예: empid
, jan_sales
, feb_sales
, mar_sales
)로 변환할 수 있습니다.
- 참고 항목:
구문¶
SELECT ...
FROM ...
PIVOT ( <aggregate_function> ( <pivot_column> )
FOR <value_column> IN (
<pivot_value_1> [ , <pivot_value_2> ... ]
| ANY [ ORDER BY ... ]
| <subquery>
)
[ DEFAULT ON NULL (<value>) ]
)
[ ... ]
매개 변수¶
aggregate_function
pivot_column
에서 그룹화된 값을 결합하기 위한 집계 함수입니다.pivot_column
집계될 소스 테이블 또는 하위 쿼리의 열입니다.
value_column
열 이름이 생성될 값이 포함된 소스 테이블 또는 하위 쿼리의 열입니다.
pivot_value_N
쿼리 결과에서 머리글로 피벗할 피벗 열의 값 목록입니다.
ANY [ ORDER BY ... ]
피벗 열의 모든 고유 값을 기준으로 피벗할 수 있습니다. 출력에서 피벗 열의 순서를 제어하려면 ANY 키워드 뒤에 ORDER BY 절을 지정합니다. 피벗 열에 NULLs가 포함된 경우 NULL도 피벗 값으로 처리됩니다.
subquery
하위 쿼리에서 발견된 모든 값에 대해 피벗을 실행합니다. 하위 쿼리에 ORDER BY 절이 포함된 경우 DISTINCT 키워드는 필수입니다. 하위 쿼리는 단일 열을 반환하는 상관관계가 없는 하위 쿼리여야 합니다. 피벗은 하위 쿼리에서 반환된 모든 고유 값에 대해 수행됩니다. 상관관계가 없는 하위 쿼리에 대한 정보는 하위 쿼리 관련 작업하기 섹션을 참조하십시오.
DEFAULT ON NULL
(value
)피벗 결과의 모든 NULL 값을 지정된 기본값으로 바꿉니다. 기본값은 피벗 및 집계 열에 종속되지 않는 모든 스칼라 식이 될 수 있습니다.
사용법 노트¶
Snowflake는 동적 피벗 을 지원합니다. 동적 피벗 쿼리는 피벗 값을 명시적으로 지정하는 대신 ANY 키워드 또는 PIVOT 하위 절의 하위 쿼리를 사용합니다.
뷰 정의에서 동적 피벗을 사용하는 경우 기본 데이터가 변경되어 피벗 출력 열이 변경되면 뷰에 대한 쿼리가 실패할 수 있습니다.
동적 피벗은 저장 프로시저 또는 사용자 정의 함수(UDF) 본문에서는 지원되지 않습니다.
동적 피벗을 사용하지 않는 피벗 쿼리는 중복 열이 있는 출력을 반환할 수 있습니다. 중복된 열이 있는 출력은 피하는 것이 좋습니다. 동적 피벗 쿼리는 중복된 열을 제거합니다.
동적 피벗을 사용하지 않는 피벗 쿼리가 VARIANT 열을 다른 데이터 타입으로 CAST 시도하면 실패할 수 있습니다. 동적 피벗 쿼리에는 이러한 제한이 없습니다.
현재 PIVOT 의미 체계는 다중 집계를 허용하지 않지만, UNION 연산자 와 함께 PIVOT 을 사용하면 비슷한 결과를 얻을 수 있습니다. 예를 보려면 UNION 을 사용한 다중 집계가 있는 동적 피벗 를 참조하십시오.
예¶
PIVOT 예제에서는 다음 quarterly_sales
테이블을 사용합니다.
CREATE OR REPLACE TABLE quarterly_sales(
empid INT,
amount INT,
quarter TEXT)
AS SELECT * FROM VALUES
(1, 10000, '2023_Q1'),
(1, 400, '2023_Q1'),
(2, 4500, '2023_Q1'),
(2, 35000, '2023_Q1'),
(1, 5000, '2023_Q2'),
(1, 3000, '2023_Q2'),
(2, 200, '2023_Q2'),
(2, 90500, '2023_Q2'),
(1, 6000, '2023_Q3'),
(1, 5000, '2023_Q3'),
(2, 2500, '2023_Q3'),
(2, 9500, '2023_Q3'),
(3, 2700, '2023_Q3'),
(1, 8000, '2023_Q4'),
(1, 10000, '2023_Q4'),
(2, 800, '2023_Q4'),
(2, 4500, '2023_Q4'),
(3, 2700, '2023_Q4'),
(3, 16000, '2023_Q4'),
(3, 10200, '2023_Q4');
다음 예제에서는 PIVOT 을 사용합니다.
모든 고유 열 값에 대한 동적 피벗이 자동으로 수행됩니다¶
테이블 quarterly_sales
가 주어지면 ANY 키워드를 사용하여 amount
열을 피벗해 모든 고유 분기에 대한 직원당 총 매출을 합산하고 피벗 열이 순서대로 정렬되도록 ORDER BY를 지정합니다.
SELECT *
FROM quarterly_sales
PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
ORDER BY empid;
+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
| 1 | 10400 | 8000 | 11000 | 18000 |
| 2 | 39500 | 90700 | 12000 | 5300 |
| 3 | NULL | NULL | 2700 | 28900 |
+-------+-----------+-----------+-----------+-----------+
하위 쿼리를 사용하여 열 값에 동적 피벗하기¶
quarterly_sales
테이블 외에 ad_campaign_types_by_quarter
테이블이 특정 분기에 실행된 광고 유형을 추적한다고 가정해 보겠습니다. 이 테이블의 구조와 데이터는 다음과 같습니다.
CREATE OR REPLACE TABLE ad_campaign_types_by_quarter(
quarter VARCHAR,
television BOOLEAN,
radio BOOLEAN,
print BOOLEAN)
AS SELECT * FROM VALUES
('2023_Q1', TRUE, FALSE, FALSE),
('2023_Q2', FALSE, TRUE, TRUE),
('2023_Q3', FALSE, TRUE, FALSE),
('2023_Q4', TRUE, FALSE, TRUE);
피벗 쿼리에서 하위 쿼리를 사용하여 특정 광고 캠페인이 진행된 분기의 매출 합계를 확인할 수 있습니다. 예를 들어, 다음 피벗 쿼리는 TV 광고 캠페인이 있는 분기에 대한 데이터만 반환합니다.
SELECT *
FROM quarterly_sales
PIVOT(SUM(amount) FOR quarter IN (
SELECT DISTINCT quarter
FROM ad_campaign_types_by_quarter
WHERE television = TRUE
ORDER BY quarter))
ORDER BY empid;
+-------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q4' |
|-------+-----------+-----------|
| 1 | 10400 | 18000 |
| 2 | 39500 | 5300 |
| 3 | NULL | 28900 |
+-------+-----------+-----------+
UNION 을 사용한 다중 집계가 있는 동적 피벗¶
UNION 연산자 를 사용하여 단일 결과 세트에 여러 집계를 표시할 수 있습니다. 이 예제에서는 동적 피벗과 UNION 연산자를 사용하여 각 분기의 각 직원에 대해 다음 정보를 표시합니다.
AVG 함수를 사용한 평균 판매 금액입니다.
MAX 함수를 사용하여 가장 높은 가치를 지닌 판매.
MIN 함수를 사용하여 가장 낮은 값으로 판매합니다.
COUNT 함수를 사용한 판매 수입니다.
SUM 함수를 사용하여 모든 판매에 대한 총액입니다.
쿼리를 실행합니다.
SELECT 'Average sale amount' AS aggregate, *
FROM quarterly_sales
PIVOT(AVG(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Highest value sale' AS aggregate, *
FROM quarterly_sales
PIVOT(MAX(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Lowest value sale' AS aggregate, *
FROM quarterly_sales
PIVOT(MIN(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Number of sales' AS aggregate, *
FROM quarterly_sales
PIVOT(COUNT(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Total amount' AS aggregate, *
FROM quarterly_sales
PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
ORDER BY aggregate, empid;
+---------------------+-------+--------------+--------------+--------------+--------------+
| AGGREGATE | EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|---------------------+-------+--------------+--------------+--------------+--------------|
| Average sale amount | 1 | 5200.000000 | 4000.000000 | 5500.000000 | 9000.000000 |
| Average sale amount | 2 | 19750.000000 | 45350.000000 | 6000.000000 | 2650.000000 |
| Average sale amount | 3 | NULL | NULL | 2700.000000 | 9633.333333 |
| Highest value sale | 1 | 10000.000000 | 5000.000000 | 6000.000000 | 10000.000000 |
| Highest value sale | 2 | 35000.000000 | 90500.000000 | 9500.000000 | 4500.000000 |
| Highest value sale | 3 | NULL | NULL | 2700.000000 | 16000.000000 |
| Lowest value sale | 1 | 400.000000 | 3000.000000 | 5000.000000 | 8000.000000 |
| Lowest value sale | 2 | 4500.000000 | 200.000000 | 2500.000000 | 800.000000 |
| Lowest value sale | 3 | NULL | NULL | 2700.000000 | 2700.000000 |
| Number of sales | 1 | 2.000000 | 2.000000 | 2.000000 | 2.000000 |
| Number of sales | 2 | 2.000000 | 2.000000 | 2.000000 | 2.000000 |
| Number of sales | 3 | 0.000000 | 0.000000 | 1.000000 | 3.000000 |
| Total amount | 1 | 10400.000000 | 8000.000000 | 11000.000000 | 18000.000000 |
| Total amount | 2 | 39500.000000 | 90700.000000 | 12000.000000 | 5300.000000 |
| Total amount | 3 | NULL | NULL | 2700.000000 | 28900.000000 |
+---------------------+-------+--------------+--------------+--------------+--------------+
조인 쿼리를 사용한 동적 피벗¶
조인이 있는 쿼리에서 피벗하려면 피벗 쿼리에 공통 테이블 식(CTE) 을 사용할 수 있습니다.
예를 들어, 간단한 테이블이 직원과 관리자를 매핑한다고 가정해 보겠습니다.
CREATE OR REPLACE TABLE emp_manager(
empid INT,
managerid INT)
AS SELECT * FROM VALUES
(1, 7),
(2, 8),
(3, 9);
SELECT * from emp_manager;
+-------+-----------+
| EMPID | MANAGERID |
|-------+-----------|
| 1 | 7 |
| 2 | 8 |
| 3 | 9 |
+-------+-----------+
emp_manager
테이블과 quarterly_sales
테이블을 조인하고 quarterly_sales
테이블의 amount
열을 피벗하는 쿼리를 실행합니다.
WITH
src AS
(
SELECT *
FROM quarterly_sales
PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
)
SELECT em.managerid, src.*
FROM emp_manager em
JOIN src ON em.empid = src.empid
ORDER BY empid;
+-----------+-------+-----------+-----------+-----------+-----------+
| MANAGERID | EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-----------+-------+-----------+-----------+-----------+-----------|
| 7 | 1 | 10400 | 8000 | 11000 | 18000 |
| 8 | 2 | 39500 | 90700 | 12000 | 5300 |
| 9 | 3 | NULL | NULL | 2700 | 28900 |
+-----------+-------+-----------+-----------+-----------+-----------+
피벗 열에 대한 지정된 열 값 목록에서 피벗¶
테이블 quarterly_sales
가 주어졌을 때 amount
열을 피벗하여 지정된 분기의 직원당 총 매출을 합산합니다.
SELECT *
FROM quarterly_sales
PIVOT(SUM(amount) FOR quarter IN (
'2023_Q1',
'2023_Q2',
'2023_Q3'))
ORDER BY empid;
+-------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' |
|-------+-----------+-----------+-----------|
| 1 | 10400 | 8000 | 11000 |
| 2 | 39500 | 90700 | 12000 |
| 3 | NULL | NULL | 2700 |
+-------+-----------+-----------+-----------+
다음 쿼리를 실행하여 amount
열의 모든 분기를 피벗할 수 있습니다.
SELECT *
FROM quarterly_sales
PIVOT(SUM(amount) FOR quarter IN (
'2023_Q1',
'2023_Q2',
'2023_Q3',
'2023_Q4'))
ORDER BY empid;
+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
| 1 | 10400 | 8000 | 11000 | 18000 |
| 2 | 39500 | 90700 | 12000 | 5300 |
| 3 | NULL | NULL | 2700 | 28900 |
+-------+-----------+-----------+-----------+-----------+
AS 절을 사용하여 출력의 열 이름을 수정할 수 있습니다. 예를 들어, 열 이름을 줄여 따옴표 없이 표시하려면 다음 쿼리를 실행합니다.
SELECT *
FROM quarterly_sales
PIVOT(SUM(amount) FOR quarter IN (
'2023_Q1',
'2023_Q2',
'2023_Q3',
'2023_Q4')) AS p (employee, q1, q2, q3, q4)
ORDER BY employee;
+----------+-------+-------+-------+-------+
| EMPLOYEE | Q1 | Q2 | Q3 | Q4 |
|----------+-------+-------+-------+-------|
| 1 | 10400 | 8000 | 11000 | 18000 |
| 2 | 39500 | 90700 | 12000 | 5300 |
| 3 | NULL | NULL | 2700 | 28900 |
+----------+-------+-------+-------+-------+
SELECT 목록에 특정 피벗 열을 나열하고 열 이름을 변경할 수도 있습니다.
SELECT empid,
"'2023_Q1'" AS q1,
"'2023_Q2'" AS q2,
"'2023_Q3'" AS q3,
"'2023_Q4'" AS q4
FROM quarterly_sales
PIVOT(sum(amount) FOR quarter IN (
'2023_Q1',
'2023_Q2',
'2023_Q3',
'2023_Q4'))
ORDER BY empid;
+-------+-------+-------+-------+-------+
| EMPID | Q1 | Q2 | Q3 | Q4 |
|-------+-------+-------+-------+-------|
| 1 | 10400 | 8000 | 11000 | 18000 |
| 2 | 39500 | 90700 | 12000 | 5300 |
| 3 | NULL | NULL | 2700 | 28900 |
+-------+-------+-------+-------+-------+
NULL 값의 기본값으로 피벗하기¶
쿼리가 NULL 값을 반환하는 경우 DEFAULT ON NULL 을 사용하여 기본값으로 바꿀 수 있습니다. 예를 들어, 동적 피벗을 사용하고 다음 쿼리를 실행하여 NULL 값을 기본값인 0
으로 바꿀 수 있습니다.
SELECT *
FROM quarterly_sales
PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter)
DEFAULT ON NULL (0))
ORDER BY empid;
+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
| 1 | 10400 | 8000 | 11000 | 18000 |
| 2 | 39500 | 90700 | 12000 | 5300 |
| 3 | 0 | 0 | 2700 | 28900 |
+-------+-----------+-----------+-----------+-----------+
지정된 열 목록과 함께 DEFAULT ON NULL 을 사용할 수도 있습니다.
SELECT *
FROM quarterly_sales
PIVOT(SUM(amount)
FOR quarter IN (
'2023_Q1',
'2023_Q2')
DEFAULT ON NULL (0))
ORDER BY empid;
+-------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' |
|-------+-----------+-----------|
| 1 | 10400 | 8000 |
| 2 | 39500 | 90700 |
| 3 | 0 | 0 |
+-------+-----------+-----------+
여러 열이 포함된 피벗 예제¶
피벗 쿼리는 여러 열에서 작동할 수 있습니다. 이 예제를 실행하기 전에 quarterly_sales
테이블에 열을 추가하고 열을 임의의 값으로 채우십시오.
먼저 quarterly_sales
테이블에 각 판매에 적용된 할인을 추적하는 열을 추가합니다.
ALTER TABLE quarterly_sales ADD COLUMN discount_percent INT DEFAULT 0;
각 판매의 할인율을 지정하는 0
~ 5
사이의 임의의 값으로 새 열을 채웁니다.
UPDATE quarterly_sales SET discount_percent = UNIFORM(0, 5, RANDOM());
테이블을 쿼리하여 임의의 값이 추가된 새 열을 표시합니다.
SELECT * FROM quarterly_sales;
+-------+--------+---------+------------------+
| EMPID | AMOUNT | QUARTER | DISCOUNT_PERCENT |
|-------+--------+---------+------------------|
| 1 | 10000 | 2023_Q1 | 0 |
| 1 | 400 | 2023_Q1 | 1 |
| 2 | 4500 | 2023_Q1 | 4 |
| 2 | 35000 | 2023_Q1 | 2 |
| 1 | 5000 | 2023_Q2 | 2 |
| 1 | 3000 | 2023_Q2 | 1 |
| 2 | 200 | 2023_Q2 | 2 |
| 2 | 90500 | 2023_Q2 | 1 |
| 1 | 6000 | 2023_Q3 | 1 |
| 1 | 5000 | 2023_Q3 | 3 |
| 2 | 2500 | 2023_Q3 | 1 |
| 2 | 9500 | 2023_Q3 | 3 |
| 3 | 2700 | 2023_Q3 | 1 |
| 1 | 8000 | 2023_Q4 | 1 |
| 1 | 10000 | 2023_Q4 | 4 |
| 2 | 800 | 2023_Q4 | 3 |
| 2 | 4500 | 2023_Q4 | 5 |
| 3 | 2700 | 2023_Q4 | 3 |
| 3 | 16000 | 2023_Q4 | 0 |
| 3 | 10200 | 2023_Q4 | 1 |
+-------+--------+---------+------------------+
이제 새 열이 추가되고 채워졌으므로 다음 예제를 실행합니다.
CTE 를 사용하여 피벗 쿼리에서 열 제외¶
공통 테이블 식(CTE) 을 사용하여 피벗 쿼리에서 열을 제외할 수 있습니다.
다음 예제는 CTE 를 사용하여 피벗 쿼리에서 discount_percent
열을 제외하는 예제입니다.
WITH
sales_without_discount AS
(SELECT * EXCLUDE(discount_percent) FROM quarterly_sales)
SELECT *
FROM sales_without_discount
PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
ORDER BY empid;
+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
| 1 | 10400 | 8000 | 11000 | 18000 |
| 2 | 39500 | 90700 | 12000 | 5300 |
| 3 | NULL | NULL | 2700 | 28900 |
+-------+-----------+-----------+-----------+-----------+
CTE 를 사용하여 amount
열을 제외시키고 각 직원이 각 분기에 제공한 평균 할인을 표시할 수 있습니다.
WITH
sales_without_amount AS
(SELECT * EXCLUDE(amount) FROM quarterly_sales)
SELECT *
FROM sales_without_amount
PIVOT(AVG(discount_percent) FOR quarter IN (ANY ORDER BY quarter))
ORDER BY empid;
+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
| 1 | 0.500000 | 1.500000 | 2.000000 | 2.500000 |
| 2 | 3.000000 | 1.500000 | 2.000000 | 4.000000 |
| 3 | NULL | NULL | 1.000000 | 1.333333 |
+-------+-----------+-----------+-----------+-----------+
다차원 피벗 쿼리 실행¶
다차원 피벗 쿼리는 둘 이상의 열에서 피벗됩니다. 이 예제는 amount
열 및 discount_percentage
열을 피벗합니다. 이 쿼리는 분기별 모든 직원의 모든 매출 합계와 분기별 모든 매출의 최대 할인율을 반환합니다.
쿼리에서 SELECT 목록은 $col_position
매개 변수를 사용하여 반환된 열에 대해 SUM 및 MAX 함수를 순서대로 실행하고 반환된 열의 이름을 지정합니다. FROM 절의 하위 쿼리는 피벗 작업을 위한 데이터를 제공합니다. 출력에는 모든 직원에 대한 판매 결과가 표시되므로 하위 쿼리에는 empid
열이 포함되지 않습니다.
SELECT SUM($1) AS q1_sales_total,
SUM($2) AS q2_sales_total,
SUM($3) AS q3_sales_total,
SUM($4) AS q4_sales_total,
MAX($5) AS q1_maximum_discount,
MAX($6) AS q2_maximum_discount,
MAX($7) AS q3_maximum_discount,
MAX($8) AS q4_maximum_discount
FROM
(SELECT amount,
quarter AS quarter_amount,
quarter AS quarter_discount,
discount_percent
FROM quarterly_sales)
PIVOT (
SUM(amount)
FOR quarter_amount IN (
'2023_Q1',
'2023_Q2',
'2023_Q3',
'2023_Q4'))
PIVOT (
MAX(discount_percent)
FOR quarter_discount IN (
'2023_Q1',
'2023_Q2',
'2023_Q3',
'2023_Q4'));
+----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+
| Q1_SALES_TOTAL | Q2_SALES_TOTAL | Q3_SALES_TOTAL | Q4_SALES_TOTAL | Q1_MAXIMUM_DISCOUNT | Q2_MAXIMUM_DISCOUNT | Q3_MAXIMUM_DISCOUNT | Q4_MAXIMUM_DISCOUNT |
|----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------|
| 49900 | 98700 | 25700 | 52200 | 4 | 2 | 3 | 5 |
+----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+