Given the following example data but with a very large number of rows:

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;

what would be the most efficient (fast) way to create the following (a row for each ts with columns for each st value) pivot table:

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

CodePudding user response：

Two most obvious options are ... well, pivoting:

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>

or conditional aggregation:

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>

Is one better or worse than another (or something else), I can't tell - you'd have to do some test on real data.

CodePudding user response：

You can use PIVOT:

SELECT *
FROM   foo
PIVOT (
  COUNT(*)
  FOR st IN ('open' AS open, 'wip' AS wip, 'closed' AS closed)
)

or conditional aggregation:

SELECT ts,
       COUNT(CASE st WHEN 'open'   THEN 1 END) AS open,
       COUNT(CASE st WHEN 'wip'    THEN 1 END) AS wip,
       COUNT(CASE st WHEN 'closed' THEN 1 END) AS closed
FROM   foo
GROUP BY ts;

Which, for your sample data, both output:

TS	OPEN	WIP	CLOSED
2022-08-23 10:33:15	2	1	0
2022-08-22 10:33:15	0	2	1
2022-08-21 10:33:15	0	1	2

If you want to aggregate by day (and not by second) then you can use TRUNC:

SELECT *
FROM   (SELECT TRUNC(ts) AS ts, st FROM foo)
PIVOT (
  COUNT(*)
  FOR st IN ('open' AS open, 'wip' AS wip, 'closed' AS closed)
)

or

SELECT TRUNC(ts) AS ts,
       COUNT(CASE st WHEN 'open'   THEN 1 END) AS open,
       COUNT(CASE st WHEN 'wip'    THEN 1 END) AS wip,
       COUNT(CASE st WHEN 'closed' THEN 1 END) AS closed
FROM   foo
GROUP BY TRUNC(ts);

Which both output:

TS	OPEN	WIP	CLOSED
2022-08-21 00:00:00	0	1	2
2022-08-22 00:00:00	0	2	1
2022-08-23 00:00:00	2	1	0

db<>fiddle here