I prepared a sql that counts transactions on a per minute basis. But I want to get the minutes with no transactions as 0. I couldn't find any solution.

My sql:

SELECT SUM(CASE
             WHEN UPPER(situation) = 'OK' THEN
              1
             ELSE
              0
           END) AS OK,
       SUM(CASE
             WHEN UPPER(situation) = 'NOK' THEN
              1
             ELSE
              0
           END) AS NOK,
       TO_CHAR(q1.start_date, 'DD/MM/YYYY HH24:MI') AS StartDate,
       TO_CHAR(q1.start_date, 'YYYY/MM/DD HH24:MI') AS FormatStartDate
  FROM table1 q1
 WHERE q1.start_date >=
       TO_DATE(TO_CHAR((sysdate - 4 / 24), 'dd/mm/yyyy hh24:mi'),
               'dd/mm/yyyy hh24:mi')
   AND q1.start_date <=
       TO_DATE(TO_CHAR((sysdate - 0 / 24), 'dd/mm/yyyy hh24:mi'),
               'dd/mm/yyyy hh24:mi')
   AND q1.type = 'TEST'
 GROUP BY q1.type,
          TO_CHAR(q1.start_date, 'DD/MM/YYYY HH24:MI'),
          TO_CHAR(q1.start_date, 'YYYY/MM/DD HH24:MI')
 ORDER BY FormatStartDate

Sql Result:

As you can see in the result, the lines 26/04/2022 11:53 and 26/04/2022 11:55 are missing. I want to see these lines in the result even if it is 0.

CodePudding user response：

In a data warehouse you're have a date dimension table, with 1 record per minute (or whatever the relevant interval is for your business case. Left join to date dimension table so you get a record for each minute. Either create that table, or generate it as part of the query using CONNECT BY LEVEL.

Example using CONNECT BY - this is dummy data as you didn't provide any sample data.

/* generate some dummy data*/
with test_data (id, entry_date) as
(
  SELECT 1, TO_DATE('27-APR-2022 09:52','DD-MON-YYYY HH24:MI') FROM DUAL UNION ALL
  SELECT 2, TO_DATE('27-APR-2022 10:00','DD-MON-YYYY HH24:MI') FROM DUAL UNION ALL
  SELECT 3, TO_DATE('27-APR-2022 10:00','DD-MON-YYYY HH24:MI') FROM DUAL UNION ALL
  SELECT 4, TO_DATE('27-APR-2022 09:58','DD-MON-YYYY HH24:MI') FROM DUAL 
), 
/* determine upper and lower bound for generating the minutes table */
test_data_boundaries (min_dt, max_dt) as
(
  SELECT MIN(entry_date), MAX(entry_date) FROM test_data
),
/* generate rows with 1 minute interval */
all_minutes (dt) as
(
  SELECT min_dt   (LEVEL - 1)/(60 * 24) FROM test_data_boundaries connect by LEVEL <=  ((max_dt - min_dt) * 60 * 24)   1 
)
/* join test_data to minutes table */

SELECT
  COUNT(t.id),
  TO_CHAR(m.dt,'DD-MON-YYYY HH24:MI')
  FROM
  all_minutes m
  LEFT OUTER JOIN test_data t ON t.entry_date = m.dt
  GROUP BY m.dt
  ORDER BY m.dt;


1   27-APR-2022 09:52
0   27-APR-2022 09:53
0   27-APR-2022 09:54
0   27-APR-2022 09:55
0   27-APR-2022 09:56
0   27-APR-2022 09:57
1   27-APR-2022 09:58
0   27-APR-2022 09:59
2   27-APR-2022 10:00

CodePudding user response：

You can generate a calendar and then use a PARTITIONed OUTER JOIN to generate all the rows:

SELECT q1.type,
       COALESCE(q1.ok, 0) AS ok,
       COALESCE(q1.nok, 0) AS nok,
       TO_CHAR(c.dt, 'YYYY/MM/DD HH24:MI') AS FormatStartDate
FROM   ( SELECT TRUNC(SYSDATE - INTERVAL '4' HOUR, 'MI')
                  (LEVEL - 1) * INTERVAL '1' MINUTE AS dt
         FROM   DUAL
         CONNECT BY LEVEL <= 4 * 60   1
       ) c
       LEFT OUTER JOIN (
         SELECT type,
                TRUNC(start_date, 'MI') AS start_date,
                COUNT(CASE UPPER(situation) WHEN 'OK' THEN 1 END) AS ok,
                COUNT(CASE UPPER(situation) WHEN 'NOK' THEN 1 END) AS nok
         FROM   table1
         WHERE  UPPER(situation) IN ('OK', 'NOK')
         AND    TRUNC(start_date, 'MI')
                  BETWEEN TRUNC(SYSDATE - INTERVAL '4' HOUR, 'MI')
                  AND     SYSDATE
         GROUP BY
               type,
               TRUNC(start_date, 'MI')
       ) q1
       PARTITION BY (q1.type)
       ON (c.dt = q1.start_date)
ORDER BY q1.type, c.dt

Which, for the sample data:

CREATE TABLE table1 (type, start_date, situation) AS
SELECT 'type1', TRUNC(SYSDATE, 'MI') - INTERVAL '04:00' HOUR TO MINUTE, 'OK' FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 'type1', TRUNC(SYSDATE, 'MI') - INTERVAL '03:59' HOUR TO MINUTE, 'OK' FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT 'type1', TRUNC(SYSDATE, 'MI') - INTERVAL '03:57' HOUR TO MINUTE, 'OK' FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 'type1', TRUNC(SYSDATE, 'MI') - INTERVAL '03:55' HOUR TO MINUTE, 'OK' FROM DUAL CONNECT BY LEVEL <= 4;

Outputs:

TYPE	OK	NOK	FORMATSTARTDATE
type1	2	0	2022/04/27 05:11
type1	3	0	2022/04/27 05:12
type1	0	0	2022/04/27 05:13
type1	1	0	2022/04/27 05:14
type1	0	0	2022/04/27 05:15
type1	4	0	2022/04/27 05:16
type1	0	0	2022/04/27 05:17
type1	0	0	2022/04/27 05:18
...	...	...	...

db<>fiddle here