실전 활용 사례

-- 지역별-카테고리별 매출 크로스탭
SELECT
    COALESCE(region, '** 전체 **') AS region,
    FORMAT_NUMBER(electronics, 0) AS electronics,
    FORMAT_NUMBER(accessories, 0) AS accessories,
    FORMAT_NUMBER(furniture, 0) AS furniture,
    FORMAT_NUMBER(COALESCE(electronics, 0) + COALESCE(accessories, 0) + COALESCE(furniture, 0), 0) AS total
FROM (
    SELECT region, category, amount
    FROM sales
)
PIVOT (
    SUM(amount)
    FOR category IN ('Electronics' AS electronics, 'Accessories' AS accessories, 'Furniture' AS furniture)
)
ORDER BY region NULLS LAST;

-- 설문 응답이 컬럼으로 펼쳐진 형태를 정규화
-- 원본: | user_id | q1_answer | q2_answer | q3_answer |
SELECT *
FROM survey_responses
UNPIVOT (
    answer FOR question IN (q1_answer AS 'Q1', q2_answer AS 'Q2', q3_answer AS 'Q3')
)
ORDER BY user_id, question;
-- 결과: | user_id | question | answer |

-- 센서 데이터를 센서별 컬럼으로 피벗
SELECT *
FROM (
    SELECT
        DATE_TRUNC('HOUR', timestamp) AS hour,
        sensor_id,
        AVG(value) AS avg_value
    FROM sensor_readings
    GROUP BY 1, 2
)
PIVOT (
    AVG(avg_value)
    FOR sensor_id IN ('temp_01' AS temperature, 'humid_01' AS humidity, 'press_01' AS pressure)
)
ORDER BY hour;

-- 시간대별-요일별 매출 히트맵
SELECT *
FROM (
    SELECT
        HOUR(order_time) AS hour_of_day,
        DAYOFWEEK(order_date) AS day_of_week,
        amount
    FROM orders
)
PIVOT (
    SUM(amount)
    FOR day_of_week IN (
        1 AS sunday,
        2 AS monday,
        3 AS tuesday,
        4 AS wednesday,
        5 AS thursday,
        6 AS friday,
        7 AS saturday
    )
)
ORDER BY hour_of_day;