언피펏(unpivot)

언피벗은 데이터 분석에서 많이 쓰이는 방식이다. 개념을 알기전 피벗이 뭔지 살펴보자

피벗(pivot)의 개념 : 데이터를 가로로 펼치기

피벗은 세로로 긴 데이터를 가로로 넓게 펼치는 작업이다. 학생들 월별 성적 데이터가 있다고 쳐보자.

 

피벗 전 데이터

학생명 | 월   | 점수
김철수 | 1월  | 85
김철수 | 2월  | 90
김철수 | 3월  | 88
이영희 | 1월  | 92
이영희 | 2월  | 87
이영희 | 3월  | 95

 

피벗 후 데이터 

학생명 | 1월점수 | 2월점수 | 3월점수
김철수 | 85     | 90     | 88
이영희 | 92     | 87     | 95

 

이렇게 피벗은 분석하기 편하게 데이터를 재배열 하는 것이다.

 

언피벗은 피벗의 정반대 개념이다. 가로로 넓게 펼쳐진 데이터를 세로로 길게 늘리는 작업이다. 가로로 피벗된 데이터를 다시 원래로 돌린다. 가로로 펼쳐진 데이터는 사람이 보기에 편하지만 프로그램이나 데이터베이스가 처리하기에는 불편하다. 그래서 쓴다. 특히 다음과 같은 경우에 언피벗이 필요하다.

 

언피벗이 필요한 상황들:

• 월별 데이터를 시계열 분석하고 싶을 때
• 여러 카테고리의 값을 하나의 기준으로 집계하고 싶을 때
• 조건부 필터링이나 그룹화를 적용하고 싶을 때
• 차트나 그래프로 시각화하고 싶을 때

https://mercury-lee.tistory.com/190

폐쇄할 따릉이 정류소 찾기 2

내가 이 문제에서 언피벗을 썼다. 우선 쿼리부터 보자.

WITH station_usage AS (
  SELECT 
    COALESCE(rent_station_id, return_station_id) AS station_id,
    SUM(
      CASE 
        WHEN strftime("%Y-%m", COALESCE(rent_at, return_at)) = "2018-10" THEN 1 
        ELSE 0 
      END
    ) AS usage_2018,
    SUM(
      CASE 
        WHEN strftime("%Y-%m", COALESCE(rent_at, return_at)) = "2019-10" THEN 1 
        ELSE 0 
      END
    ) AS usage_2019
  FROM (
    SELECT rent_station_id, NULL as return_station_id, rent_at, NULL as return_at
    FROM rental_history 
    WHERE strftime("%Y-%m", rent_at) IN ("2018-10", "2019-10")
    UNION ALL
    SELECT NULL as rent_station_id, return_station_id, NULL as rent_at, return_at
    FROM rental_history 
    WHERE strftime("%Y-%m", return_at) IN ("2018-10", "2019-10")
  ) combined_data
  GROUP BY COALESCE(rent_station_id, return_station_id)
  HAVING usage_2018 > 0 AND usage_2019 > 0
)
SELECT 
  su.station_id,
  s.name,
  s.local,
  ROUND((su.usage_2019 * 100.0 / su.usage_2018), 2) AS usage_pct
FROM station_usage su
JOIN station s ON s.station_id = su.station_id
WHERE (su.usage_2019 * 100.0 / su.usage_2018) <= 50.0
ORDER BY usage_pct ASC;

원본 rental_history 테이블을 다시 살펴보면, 이는 일종의 "피벗된" 형태라고 할 수 있다. 

rental_id | rent_station_id | return_station_id | rent_at | return_at
1001      | 101            | 102              | 2018-10-01 | 2018-10-01

이 행에는 반납, 대여 두 정보가 동시에 들어있다. 하지만 언피벗을 한다면?

station_id | event_type | event_date
101        | 대여       | 2018-10-01
102        | 반납       | 2018-10-01

이런신이 된다. 각 행이 하나의 명확한 의미를 지니게 된다.

실제 SQL에서 언피벗 구현 하기

UION ALL 쓰기

SELECT rent_station_id AS station_id, 'rent' AS event_type , rent_at AS event_date
FROM rental_history
UNION ALL
SELECT return_station_id AS station_id, 'return' AS event_type, return_at AS event_date
FROM rental_history

CROSS JOIN 활용

SELECT 
    CASE 
        WHEN event_type = 'rent' THEN rent_station_id
        WHEN event_type = 'return' THEN return_station_id
    END AS station_id,
    event_type,
    CASE 
        WHEN event_type = 'rent' THEN rent_at
        WHEN event_type = 'return' THEN return_at
    END AS event_date
FROM rental_history
CROSS JOIN (SELECT 'rent' AS event_type UNION ALL SELECT 'return') AS events
WHERE 
    (event_type = 'rent' AND rent_station_id IS NOT NULL) OR
    (event_type = 'return' AND return_station_id IS NOT NULL)

일반적으로 UNION ALL을 더 많이 쓴다.