Oracle SQL中高效创建数据透视表的最优方法咨询

给定如下Oracle表结构及数据（实际为超大行数数据集）：

CREATE TABLE foo (
    ts DATE NOT NULL,
    st VARCHAR2(10) NOT NULL CHECK (st IN ('open', 'wip', 'closed')) 
);
INSERT INTO foo VALUES (SYSDATE-2, 'closed');
INSERT INTO foo VALUES (SYSDATE-2, 'closed');
INSERT INTO foo VALUES (SYSDATE-2, 'wip');
INSERT INTO foo VALUES (SYSDATE-1, 'closed');
INSERT INTO foo VALUES (SYSDATE-1, 'wip');
INSERT INTO foo VALUES (SYSDATE-1, 'wip');
INSERT INTO foo VALUES (SYSDATE, 'wip');
INSERT INTO foo VALUES (SYSDATE, 'open');
INSERT INTO foo VALUES (SYSDATE, 'open');
COMMIT;
SELECT * FROM  foo ORDER BY ts;

需要生成每行对应一个ts、各st值作为列的透视表，目标结果如下：

ts         open   wip    closed
---------- ------ ------ ------
21.08.2022 0      1      2
22.08.2022 0      2      1
23.08.2022 2      1      0

最优方案：条件聚合（大数据量首选） #

条件聚合是处理这类统计需求性能最高的方式，尤其适合超大数据集。它的执行逻辑简洁，直接按ts分组后统计各状态的数量，避免了额外的语法转换开销。

SELECT
    TO_CHAR(ts, 'DD.MM.YYYY') AS ts,
    COUNT(CASE WHEN st = 'open' THEN 1 END) AS open,
    COUNT(CASE WHEN st = 'wip' THEN 1 END) AS wip,
    COUNT(CASE WHEN st = 'closed' THEN 1 END) AS closed
FROM foo
GROUP BY ts
ORDER BY ts;

也可以用SUM函数实现等价效果，逻辑更直观：

SELECT
    TO_CHAR(ts, 'DD.MM.YYYY') AS ts,
    SUM(CASE WHEN st = 'open' THEN 1 ELSE 0 END) AS open,
    SUM(CASE WHEN st = 'wip' THEN 1 ELSE 0 END) AS wip,
    SUM(CASE WHEN st = 'closed' THEN 1 ELSE 0 END) AS closed
FROM foo
GROUP BY ts
ORDER BY ts;

优势 #

• 兼容性强：无需依赖Oracle专属的PIVOT语法，在其他数据库也能通用；
• 性能稳定：执行计划直接聚焦分组统计，没有额外的中间转换步骤，大数据量下比PIVOT更快。

备选方案：Oracle PIVOT语法 #

如果偏好Oracle原生的透视语法，也可以使用PIVOT实现，但在超大数据集下性能略逊于条件聚合：

SELECT
    TO_CHAR(ts, 'DD.MM.YYYY') AS ts,
    COALESCE(open, 0) AS open,
    COALESCE(wip, 0) AS wip,
    COALESCE(closed, 0) AS closed
FROM (
    SELECT ts, st FROM foo
)
PIVOT (
    COUNT(st)
    FOR st IN ('open' AS open, 'wip' AS wip, 'closed' AS closed)
)
ORDER BY ts;

这里用COALESCE将NULL值转换为0，确保结果和目标格式一致。

内容的提问来源于stack exchange，提问作者doberkofler