PIVOT 및 UNPIVOT 예

PIVOT 및 UNPIVOT은 각각 쿼리 출력을 행에서 열로, 열에서 행으로 회전하는 FROM 절의 파라미터입니다. 테이블 쿼리 결과를 읽기 쉬운 형식으로 나타냅니다. 다음 예제에서는 테스트 데이터와 쿼리를 사용하여 사용 방법을 보여줍니다.

이러한 파라미터 및 기타 파라미터에 대한 자세한 내용은 FROM 절 단원을 참조하세요.

PIVOT 예

샘플 테이블과 데이터를 설정하고 이를 사용하여 후속 예제 쿼리를 실행합니다.

CREATE TABLE part (
    partname varchar,
    manufacturer varchar,
    quality int,
    price decimal(12, 2)
);

INSERT INTO part VALUES ('prop', 'local parts co', 2, 10.00);
INSERT INTO part VALUES ('prop', 'big parts co', NULL, 9.00);
INSERT INTO part VALUES ('prop', 'small parts co', 1, 12.00);

INSERT INTO part VALUES ('rudder', 'local parts co', 1, 2.50);
INSERT INTO part VALUES ('rudder', 'big parts co', 2, 3.75);
INSERT INTO part VALUES ('rudder', 'small parts co', NULL, 1.90);

INSERT INTO part VALUES ('wing', 'local parts co', NULL, 7.50);
INSERT INTO part VALUES ('wing', 'big parts co', 1, 15.20);
INSERT INTO part VALUES ('wing', 'small parts co', NULL, 11.80);

price에 대한 AVG 집계가 있는 partname에 대한 PIVOT.

SELECT *
FROM (SELECT partname, price FROM part) PIVOT (
    AVG(price) FOR partname IN ('prop', 'rudder', 'wing')
);

쿼리 결과는 다음과 같이 출력됩니다.

  prop   |  rudder  |  wing
---------+----------+---------
 10.33   | 2.71     | 11.50

이전 예에서 결과는 열로 변환됩니다. 다음 예는 평균 가격을 열이 아닌 행으로 반환하는 GROUP BY 쿼리를 보여줍니다.

SELECT partname, avg(price)
FROM (SELECT partname, price FROM part)
WHERE partname IN ('prop', 'rudder', 'wing')
GROUP BY partname;

쿼리 결과는 다음과 같이 출력됩니다.

 partname |  avg
----------+-------
 prop     | 10.33
 rudder   |  2.71
 wing     | 11.50

암시적 열로 manufacturer가 있는 PIVOT 예.

SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
    count(*) FOR quality IN (1, 2, NULL)
);

쿼리 결과는 다음과 같이 출력됩니다.

 manufacturer      | 1  | 2  | null
-------------------+----+----+------
 local parts co    | 1  | 1  |  1
 big parts co      | 1  | 1  |  1
 small parts co    | 1  | 0  |  2

PIVOT 정의에서 참조되지 않는 입력 테이블 열은 암시적으로 결과 테이블에 추가됩니다. 이는 앞의 예에서 manufacturer 열의 경우입니다. 이 예는 또한 NULL이 IN 연산자에 유효한 값임을 보여줍니다.

위의 예에서 PIVOT은 GROUP BY가 포함된 다음 쿼리와 유사한 정보를 반환합니다. 차이점은 PIVOT이 열 2와 제조업체 small parts co에 대해 값 0을 반환한다는 것입니다. GROUP BY 쿼리에는 해당 행이 없습니다. 대부분의 경우 행에 지정된 열에 대한 입력 데이터가 없으면 PIVOT은 NULL을 삽입합니다. 그러나 count 집계는 NULL을 반환하지 않고 0이 기본값입니다.

SELECT manufacturer, quality, count(*)
FROM (SELECT quality, manufacturer FROM part)
WHERE quality IN (1, 2) OR quality IS NULL
GROUP BY manufacturer, quality
ORDER BY manufacturer;

쿼리 결과는 다음과 같이 출력됩니다.

 manufacturer        | quality | count
---------------------+---------+-------
 big parts co        |         |     1
 big parts co        |       2 |     1
 big parts co        |       1 |     1
 local parts co      |       2 |     1
 local parts co      |       1 |     1
 local parts co      |         |     1
 small parts co      |       1 |     1
 small parts co      |         |     2

PIVOT 연산자는 집계 표현식과 IN 연산자의 각 값에 대한 선택적 별칭을 허용합니다. 별칭을 사용하여 열 이름을 사용자 지정합니다. 집계 별칭이 없는 경우 IN 목록 별칭이 사용됩니다. 그렇지 않으면 이름을 구분하기 위해 집계 별칭이 밑줄과 함께 열 이름에 추가됩니다.

SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
    count(*) AS count FOR quality IN (1 AS high, 2 AS low, NULL AS na)
);

쿼리 결과는 다음과 같이 출력됩니다.

 manufacturer      | high_count  | low_count | na_count
-------------------+-------------+-----------+----------
 local parts co    |           1 |         1 |        1
 big parts co      |           1 |         1 |        1
 small parts co    |           1 |         0 |        2

다음 샘플 테이블과 데이터를 설정하고 이를 사용하여 후속 예제 쿼리를 실행합니다. 데이터는 여러 호텔의 예약 날짜를 나타냅니다.

CREATE TABLE bookings (
    booking_id int,
    hotel_code char(8),
    booking_date date,
    price decimal(12, 2)
);

INSERT INTO bookings VALUES (1, 'FOREST_L', '02/01/2023', 75.12);
INSERT INTO bookings VALUES (2, 'FOREST_L', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (3, 'FOREST_L', '02/04/2023', 85.54);

INSERT INTO bookings VALUES (4, 'FOREST_L', '02/08/2023', 75.00);
INSERT INTO bookings VALUES (5, 'FOREST_L', '02/11/2023', 75.00);
INSERT INTO bookings VALUES (6, 'FOREST_L', '02/14/2023', 90.00);

INSERT INTO bookings VALUES (7, 'FOREST_L', '02/21/2023', 60.00);
INSERT INTO bookings VALUES (8, 'FOREST_L', '02/22/2023', 85.00);
INSERT INTO bookings VALUES (9, 'FOREST_L', '02/27/2023', 90.00);

INSERT INTO bookings VALUES (10, 'DESERT_S', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (11, 'DESERT_S', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (12, 'DESERT_S', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (13, 'DESERT_S', '02/05/2023', 75.00);
INSERT INTO bookings VALUES (14, 'DESERT_S', '02/06/2023', 34.00);
INSERT INTO bookings VALUES (15, 'DESERT_S', '02/09/2023', 85.00);

INSERT INTO bookings VALUES (16, 'DESERT_S', '02/12/2023', 23.00);
INSERT INTO bookings VALUES (17, 'DESERT_S', '02/13/2023', 76.00);
INSERT INTO bookings VALUES (18, 'DESERT_S', '02/14/2023', 85.00);

INSERT INTO bookings VALUES (19, 'OCEAN_WV', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (20, 'OCEAN_WV', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (21, 'OCEAN_WV', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (22, 'OCEAN_WV', '02/06/2023', 75.00);
INSERT INTO bookings VALUES (23, 'OCEAN_WV', '02/09/2023', 34.00);
INSERT INTO bookings VALUES (24, 'OCEAN_WV', '02/12/2023', 85.00);

INSERT INTO bookings VALUES (25, 'OCEAN_WV', '02/13/2023', 23.00);
INSERT INTO bookings VALUES (26, 'OCEAN_WV', '02/14/2023', 76.00);
INSERT INTO bookings VALUES (27, 'OCEAN_WV', '02/16/2023', 85.00);

INSERT INTO bookings VALUES (28, 'CITY_BLD', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (29, 'CITY_BLD', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (30, 'CITY_BLD', '02/04/2023', 85.00);

INSERT INTO bookings VALUES (31, 'CITY_BLD', '02/12/2023', 75.00);
INSERT INTO bookings VALUES (32, 'CITY_BLD', '02/13/2023', 34.00);
INSERT INTO bookings VALUES (33, 'CITY_BLD', '02/17/2023', 85.00);

INSERT INTO bookings VALUES (34, 'CITY_BLD', '02/22/2023', 23.00);
INSERT INTO bookings VALUES (35, 'CITY_BLD', '02/23/2023', 76.00);
INSERT INTO bookings VALUES (36, 'CITY_BLD', '02/24/2023', 85.00);

이 샘플 쿼리에서는 예약 기록을 집계하여 각 주의 합계를 산출합니다. 각 주의 종료일은 열 이름이 됩니다.

SELECT * FROM
    (SELECT
       booking_id,
       (date_trunc('week', booking_date::date) + '5 days'::interval)::date as enddate,
       hotel_code AS "hotel code"
FROM bookings
) PIVOT (
    count(booking_id) FOR enddate IN ('2023-02-04','2023-02-11','2023-02-18') 
);

쿼리 결과는 다음과 같이 출력됩니다.

 hotel code | 2023-02-04  | 2023-02-11 | 2023-02-18
------------+-------------+------------+----------
 FOREST_L   |           3 |          2 |        1
 DESERT_S   |           4 |          3 |        2
 OCEAN_WV   |           3 |          3 |        3
 CITY_BLD   |           3 |          1 |        2

Amazon Redshift는 여러 열에서 피벗하기 위한 CROSSTAB을 지원하지 않습니다. 그러나 다음과 같은 쿼리를 사용하여 PIVOT을 사용한 집계와 유사한 방식으로 행 데이터를 열로 변경할 수 있습니다. 이전 예와 동일한 예약 샘플 데이터를 사용합니다.

SELECT 
  booking_date,
  MAX(CASE WHEN hotel_code = 'FOREST_L' THEN 'forest is booked' ELSE '' END) AS FOREST_L,
  MAX(CASE WHEN hotel_code = 'DESERT_S' THEN 'desert is booked' ELSE '' END) AS DESERT_S,
  MAX(CASE WHEN hotel_code = 'OCEAN_WV' THEN 'ocean is booked' ELSE '' END)  AS OCEAN_WV
FROM bookings
GROUP BY booking_date
ORDER BY booking_date asc;

샘플 쿼리를 실행하면 예약된 호텔을 나타내는 짧은 문구 옆에 예약 날짜가 표시됩니다.

 booking_date  | forest_l         | desert_s         | ocean_wv
---------------+------------------+------------------+--------------------
 2023-02-01    | forest is booked | desert is booked |  ocean is booked
 2023-02-02    | forest is booked | desert is booked |  ocean is booked
 2023-02-04    | forest is booked | desert is booked |  ocean is booked
 2023-02-05    |                  | desert is booked |        
 2023-02-06    |                  | desert is booked |         

다음은 PIVOT에 대한 사용 참고 사항입니다.

UNPIVOT 예

샘플 데이터를 설정하고 이를 사용하여 후속 예제를 실행합니다.

CREATE TABLE count_by_color (quality varchar, red int, green int, blue int);

INSERT INTO count_by_color VALUES ('high', 15, 20, 7);
INSERT INTO count_by_color VALUES ('normal', 35, NULL, 40);
INSERT INTO count_by_color VALUES ('low', 10, 23, NULL);

빨간색, 녹색 및 파란색 입력 열의 UNPIVOT.

SELECT *
FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

쿼리 결과는 다음과 같이 출력됩니다.

 color | cnt
-------+-----
 red   |  15
 red   |  35
 red   |  10
 green |  20
 green |  23
 blue  |   7
 blue  |  40

기본적으로 입력 열의 NULL 값은 건너뛰고 결과 행을 생성하지 않습니다.

다음은 INCLUDE NULLS가 있는 UNPIVOT의 예입니다.

SELECT *
FROM (
    SELECT red, green, blue
    FROM count_by_color
) UNPIVOT INCLUDE NULLS (
    cnt FOR color IN (red, green, blue)
);

결과 출력값은 다음과 같습니다.

 color | cnt
-------+-----
 red   |  15
 red   |  35
 red   |  10
 green |  20
 green |
 green |  23
 blue  |   7
 blue  |  40
 blue  |  

INCLUDING NULLS 파라미터가 설정되면 NULL 입력 값이 결과 행을 생성합니다.

암시적 열로 quality가 있는 The following query shows UNPIVOT.

SELECT *
FROM count_by_color UNPIVOT (
    cnt FOR color IN (red, green, blue)
);

쿼리 결과는 다음과 같이 출력됩니다.

 quality | color | cnt
---------+-------+-----
 high    | red   |  15
 normal  | red   |  35
 low     | red   |  10
 high    | green |  20
 low     | green |  23
 high    | blue  |   7
 normal  | blue  |  40

UNPIVOT 정의에서 참조되지 않는 입력 테이블의 열은 암시적으로 결과 테이블에 추가됩니다. 이 예에서는 quality 열의 경우입니다.

다음 예는 IN 목록의 값에 대한 별칭이 있는 UNPIVOT을 보여줍니다.

SELECT *
FROM count_by_color UNPIVOT (
    cnt FOR color IN (red AS r, green AS g, blue AS b)
);

이전 쿼리 결과는 다음과 같이 출력됩니다.

 quality | color | cnt
---------+-------+-----
 high    | r     |  15
 normal  | r     |  35
 low     | r     |  10
 high    | g     |  20
 low     | g     |  23
 high    | b     |   7
 normal  | b     |  40

UNPIVOT 연산자는 각 IN 목록 값에서 선택적 별칭을 허용합니다. 각 별칭을 통해 각 value 열의 데이터를 사용자 지정할 수 있습니다.

다음은 UNPIVOT에 대한 사용 참고 사항입니다.