Clickhouse SQL:将数据从长格式重塑为宽格式

-- 创建包含长格式数据的表
CREATE TABLE long_format_data (
    id Int,
    category String,
    value Float
) ENGINE = MergeTree()
ORDER BY id;

-- 插入长格式数据
INSERT INTO long_format_data (id, category, value)
VALUES
    (1, 'A', 10),
    (1, 'B', 20),
    (2, 'A', 30),
    (2, 'B', 40);

-- 使用转置函数将数据从长格式转换为宽格式
SELECT
    id,
    sumIf(value, category = 'A') AS A,
    sumIf(value, category = 'B') AS B
FROM long_format_data
GROUP BY id;

-- 创建包含宽格式数据的表
CREATE TABLE wide_format_data (
    id Int,
    A Float,
    B Float
) ENGINE = MergeTree()
ORDER BY id;

-- 将转换后的数据插入到宽格式表中
INSERT INTO wide_format_data (id, A, B)
SELECT
    id,
    sumIf(value, category = 'A') AS A,
    sumIf(value, category = 'B') AS B
FROM long_format_data
GROUP BY id;