How can I generate zeros for missing id values?

Question

I'm trying to generate an output that fills in missing counts with 0s.

I'm using Oracle SQL. So far, my solution is based on Grouping records hour by hour or day by day and filling gaps with zero or null with small additions.

WITH TEMP
     AS ( SELECT MINDT + ( (LEVEL - 1) / 24) DDD
               FROM (SELECT TRUNC (MIN (MY_TIMESTAMP), 'HH24') MINDT,
                            TRUNC (MAX (MY_TIMESTAMP), 'HH24') MAXDT
                       FROM MAIN_TABLE.TABLE_VIEW THV
                      WHERE MY_TIMESTAMP BETWEEN TO_DATE ('08/01/2018:00:00:00',
                                                           'MM/DD/YYYY:HH24:MI:SS')
                                                    AND TO_DATE (
                                                           '08/03/2018:23:59:59',
                                                           'MM/DD/YYYY:HH24:MI:SS')) V
         CONNECT BY MINDT + ( (LEVEL - 1) / 24) <= MAXDT)
  SELECT TO_CHAR (TRUNC (D1, 'HH24'), 'YYYY-MM-DD HH24'), COUNT (D2), ID
    FROM (SELECT NVL (MY_TIMESTAMP, DDD) D1,
                 MY_TIMESTAMP D2,
                 THV.ID ID
            FROM MAIN_TABLE.TABLE_VIEW THV
                 RIGHT OUTER JOIN
                    (SELECT DDD FROM TEMP) AD
                 ON DDD = TRUNC (MY_TIMESTAMP, 'HH24')
           WHERE MY_TIMESTAMP BETWEEN TO_DATE ('08/01/2018:00:00:00',
                                                      'MM/DD/YYYY:HH24:MI:SS')
                                         AND TO_DATE ('08/03/2018:23:59:59',
                                                      'MM/DD/YYYY:HH24:MI:SS'))
GROUP BY ID, TRUNC (D1, 'HH24')
ORDER BY ID, TRUNC (D1, 'HH24')

Right now I'm getting:

CNT   ID        DT
4     1         2018-08-01 00
1     1         2018-08-01 01
1     1         2018-08-01 04
20    1         2018-08-01 05
76    1         2018-08-01 07

But what I want is:

CNT   ID        DT
4     1         2018-08-01 00
1     1         2018-08-01 01
0     1         2018-08-01 02
0     1         2018-08-01 03
1     1         2018-08-01 04
20    1         2018-08-01 05
0     1         2018-08-01 06
76    1         2018-08-01 07

Any help would be appreciated.

score 0 · Answer 1 · answered Jan 29 '19 at 19:27

It works pretty smooth if you have a table to join with that has all the hours you expect to have in the results. For a table to have all the hours, it would just have 24 records.

It can be a temp table, but if it was a real table, it would simplify your report to a standard query. And if this report is used regularly, why not have an extra table? I've seen DBAs have a generic "numbers" table with lots of numbers in it for tricks like this (to get 0-23, query the table where n between 0 and 23). Another example, if you want every individual date for a 90 day period, can use a numbers table for 0-89 and add that value to a start date to be able to join on every possible date in that period.

How can I generate zeros for missing id values?

1 Answers1