行列转换

行转列（PIVOT）和列转行（UNPIVOT）是数据透视分析的核心技术。

📊 支持情况

1. 行转列（PIVOT）

Oracle PIVOT

-- 基本语法
SELECT *
FROM (
    SELECT dept_id, month, amount
    FROM monthly_sales
)
PIVOT (
    SUM(amount)
    FOR month IN ('2024-01', '2024-02', '2024-03')
);

Oracle PIVOT 完整示例

SELECT *
FROM (
    SELECT
        dept_id,
        TO_CHAR(sale_date, 'YYYY-MM') AS month,
        sales_amount
    FROM sales
)
PIVOT (
    SUM(sales_amount)
    FOR month IN (
        '2024-01' AS jan,
        '2024-02' AS feb,
        '2024-03' AS mar
    )
)
ORDER BY dept_id;

MySQL CASE WHEN 实现

SELECT
    dept_id,
    SUM(CASE WHEN month = '2024-01' THEN amount ELSE 0 END) AS jan,
    SUM(CASE WHEN month = '2024-02' THEN amount ELSE 0 END) AS feb,
    SUM(CASE WHEN month = '2024-03' THEN amount ELSE 0 END) AS mar
FROM monthly_sales
GROUP BY dept_id;

ClickHouse 实现

SELECT
    dept_id,
    sumIf(amount, month = '2024-01') AS jan,
    sumIf(amount, month = '2024-02') AS feb,
    sumIf(amount, month = '2024-03') AS mar
FROM monthly_sales
GROUP BY dept_id;

2. 列转行（UNPIVOT）

Oracle UNPIVOT

SELECT *
FROM (
    SELECT * FROM monthly_pivot
)
UNPIVOT (
    amount FOR month IN (jan, feb, mar)
);

MySQL UNION ALL 实现

SELECT dept_id, '2024-01' AS month, jan AS amount FROM monthly_pivot
UNION ALL
SELECT dept_id, '2024-02' AS month, feb AS amount FROM monthly_pivot
UNION ALL
SELECT dept_id, '2024-03' AS month, mar AS amount FROM monthly_pivot;

Hive / MaxCompute

-- 使用lateral view + explode
SELECT
    dept_id,
    month,
    amount
FROM monthly_pivot
LATERAL VIEW explode(map(
    '2024-01', jan,
    '2024-02', feb,
    '2024-03', mar
)) t AS month, amount;

3. 动态行列转换

动态列（MySQL）

-- 使用预处理语句
SET @sql = NULL;

SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'SUM(CASE WHEN month = ''',
            month,
            ''' THEN amount ELSE 0 END) AS `',
            month, '`'
        )
    ) INTO @sql
FROM monthly_sales;

SET @sql = CONCAT('SELECT dept_id, ', @sql, ' FROM monthly_sales GROUP BY dept_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

动态列（Oracle）

-- 使用PIVOT XML
SELECT *
FROM (
    SELECT dept_id, month, amount FROM monthly_sales
)
PIVOT XML (
    SUM(amount)
    FOR month IN (SELECT DISTINCT month FROM monthly_sales)
);

4. 实战案例

场景1：销售报表透视

-- 原始数据
-- +----------+----------+-------+
-- | dept_id  | month    | amount|
-- +----------+----------+-------+
-- | 1        | 2024-01  | 50000 |
-- | 1        | 2024-02  | 60000 |
-- | 2        | 2024-01  | 45000 |
-- | 2        | 2024-02  | 55000 |
-- +----------+----------+-------+

-- MySQL
SELECT
    dept_id,
    SUM(CASE WHEN month = '2024-01' THEN amount ELSE 0 END) AS jan_sales,
    SUM(CASE WHEN month = '2024-02' THEN amount ELSE 0 END) AS feb_sales,
    SUM(amount) AS total_sales
FROM monthly_sales
GROUP BY dept_id;

-- 输出
-- +----------+-----------+-----------+-------------+
-- | dept_id  | jan_sales | feb_sales | total_sales|
-- +----------+-----------+-----------+-------------+
-- | 1        | 50000     | 60000     | 110000      |
-- | 2        | 45000     | 55000     | 100000      |
-- +----------+-----------+-----------+-------------+

场景2：多维度透视

-- 按产品和月份交叉
SELECT
    product_name,
    SUM(CASE WHEN month = '2024-01' THEN amount ELSE 0 END) AS jan,
    SUM(CASE WHEN month = '2024-02' THEN amount ELSE 0 END) AS feb,
    SUM(CASE WHEN month = '2024-03' THEN amount ELSE 0 END) AS mar
FROM sales
GROUP BY product_name;

场景3：带小计的透视

-- MySQL
SELECT
    CASE WHEN GROUPING(dept_id) = 1 THEN '总计' ELSE dept_id END AS dept_id,
    SUM(CASE WHEN month = '2024-01' THEN amount ELSE 0 END) AS jan,
    SUM(CASE WHEN month = '2024-02' THEN amount ELSE 0 END) AS feb,
    SUM(CASE WHEN month = '2024-03' THEN amount ELSE 0 END) AS mar,
    SUM(amount) AS total
FROM monthly_sales
GROUP BY dept_id WITH ROLLUP;

场景4：宽表转长表

-- 原始宽表
-- +----------+------+------+------+
-- | user_id  | jan  | feb  | mar  |
-- +----------+------+------+------+
-- | 1        | 100  | 150  | 200  |
-- +----------+------+------+------+

-- MySQL
SELECT user_id, '2024-01' AS month, jan AS amount FROM sales
UNION ALL
SELECT user_id, '2024-02' AS month, feb AS amount FROM sales
UNION ALL
SELECT user_id, '2024-03' AS month, mar AS amount FROM sales;

-- 输出
-- +----------+----------+-------+
-- | user_id  | month    | amount|
-- +----------+----------+-------+
-- | 1        | 2024-01  | 100   |
-- | 1        | 2024-02  | 150   |
-- | 1        | 2024-03  | 200   |
-- +----------+----------+-------+

5. Hive lateral view

数组展开

-- 原始数据：每行一个用户多个标签
-- +----------+-------------------+
-- | user_id  | tags              |
-- +----------+-------------------+
-- | 1        | ["vip","male"]    |
-- | 2        | ["female","young"]|
-- +----------+-------------------+

SELECT
    user_id,
    tag
FROM user_tags
LATERAL VIEW explode(tags) t AS tag;

-- 输出
-- +----------+-------+
-- | user_id  | tag   |
-- +----------+-------+
-- | 1        | vip   |
-- | 1        | male  |
-- | 2        | female|
-- | 2        | young |
-- +----------+-------+

Map展开

-- 原始数据：属性map
SELECT
    user_id,
    attr_key,
    attr_value
FROM users
LATERAL VIEW explode(attributes) t AS attr_key, attr_value;

⚠️ 注意事项

1. 数据类型

-- 确保转换后的数据类型一致
-- 可能需要显式转换
SUM(CAST(CASE WHEN month = '2024-01' THEN amount ELSE 0 END AS DECIMAL(10,2)))

2. NULL值

-- 使用0填充NULL
SUM(CASE WHEN month = '2024-01' THEN COALESCE(amount, 0) ELSE 0 END)

3. 性能优化

-- ✅ 建议：先过滤再转换
SELECT
    dept_id,
    SUM(CASE WHEN month = '2024-01' THEN amount ELSE 0 END) AS jan
FROM (
    SELECT * FROM monthly_sales WHERE year = 2024
) t
GROUP BY dept_id;

-- ❌ 避免：转换后再过滤
SELECT * FROM (
    SELECT
        dept_id,
        SUM(CASE WHEN month = '2024-01' THEN amount ELSE 0 END) AS jan
    FROM monthly_sales
    GROUP BY dept_id
) t
WHERE jan > 0;

📝 练习题

-- 建表
CREATE TABLE monthly_sales (
    dept_id INT,
    product_name STRING,
    month STRING,
    amount DECIMAL(10,2)
);

INSERT INTO monthly_sales VALUES
(1, 'A产品', '2024-01', 10000),
(1, 'A产品', '2024-02', 15000),
(1, 'A产品', '2024-03', 12000),
(1, 'B产品', '2024-01', 8000),
(1, 'B产品', '2024-02', 9000),
(1, 'B产品', '2024-03', 11000),
(2, 'A产品', '2024-01', 12000),
(2, 'A产品', '2024-02', 13000),
(2, 'A产品', '2024-03', 14000);

要求：

1. 将数据转换为按月份列的透视表
2. 将数据转换为按产品列的透视表
3. 实现宽表转长表（反透视）

-- 参考答案（MySQL）
-- 1. 按月份透视
SELECT
    dept_id,
    SUM(CASE WHEN month = '2024-01' THEN amount ELSE 0 END) AS jan,
    SUM(CASE WHEN month = '2024-02' THEN amount ELSE 0 END) AS feb,
    SUM(CASE WHEN month = '2024-03' THEN amount ELSE 0 END) AS mar,
    SUM(amount) AS total
FROM monthly_sales
GROUP BY dept_id;

-- 2. 按产品透视
SELECT
    dept_id,
    SUM(CASE WHEN product_name = 'A产品' THEN amount ELSE 0 END) AS product_a,
    SUM(CASE WHEN product_name = 'B产品' THEN amount ELSE 0 END) AS product_b
FROM monthly_sales
GROUP BY dept_id;

-- 3. 反透视（宽表转长表）
SELECT dept_id, '2024-01' AS month, jan AS amount FROM (
    SELECT
        dept_id,
        SUM(CASE WHEN month = '2024-01' THEN amount ELSE 0 END) AS jan
    FROM monthly_sales GROUP BY dept_id
) t
UNION ALL
SELECT dept_id, '2024-02' AS month, feb AS amount FROM (
    SELECT
        dept_id,
        SUM(CASE WHEN month = '2024-02' THEN amount ELSE 0 END) AS feb
    FROM monthly_sales GROUP BY dept_id
) t
UNION ALL
SELECT dept_id, '2024-03' AS month, mar AS amount FROM (
    SELECT
        dept_id,
        SUM(CASE WHEN month = '2024-03' THEN amount ELSE 0 END) AS mar
    FROM monthly_sales GROUP BY dept_id
) t;