在Oracle SQL中创建枢轴表的最有效(快速)方法

Question

给出以下示例数据，但行数非常多：

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

Answer 1

两个最明显的选择是..。嗯，旋转：

SQL> select *
  2  from foo
  3  pivot (
  4    count(st)
  5    for st in ('open' as open, 'wip' as wip, 'closed' as closed)
  6  )
  7  order by ts;

TS               OPEN        WIP     CLOSED
---------- ---------- ---------- ----------
21.08.2022          0          1          2
22.08.2022          0          2          1
23.08.2022          2          1          0

SQL>

或有条件的聚合：

SQL> select ts,
  2    sum(case when st = 'open'   then 1 else 0 end) as open,
  3    sum(case when st = 'wip'    then 1 else 0 end) as wip,
  4    sum(case when st = 'closed' then 1 else 0 end) as closed
  5  from foo
  6  group by ts
  7  order by ts;

TS               OPEN        WIP     CLOSED
---------- ---------- ---------- ----------
21.08.2022          0          1          2
22.08.2022          0          2          1
23.08.2022          2          1          0

SQL>

是比另一个好还是坏(或者别的什么)，我不知道--你必须对真实数据做一些测试。