Timeslice/ reporting 'empty' bins as zero

aklaer

Hello,
first: my SQL and vertica knowledge is quite limited.
I have a table that contains data samples with a timestamp, and I am counting the number of data items e.g. on a 5min interval using the time_slice function like this:

select time_slice(to_timestamp(timestamp_utc_s),$interval), 'my_table=' || count(*) from dataset.my_table group by 1 order by 1 desc;

Sample Result:
2022-04-11 11:10:00 | my_table=1000
2022-04-11 11:05:00 | my_table=1000
2022-04-11 11:00:00 | my_table=196
2022-04-11 09:50:00 | my_table=588
2022-04-11 09:45:00 | my_table=1000
2022-04-11 09:40:00 | my_table=1000
2022-04-11 09:35:00 | my_table=1000

This is working nicely, however when I have intervals without samples at all, those bins are not reported with a counter of 0, but they are omitted.
Like in the example above between 09:50 and 11:00 .

I am looking for some magic that would make these bins appear with a count of zero :-)

Any hints?

marcothesane

Try creating a helper table out of dataset.my_table and left join with that.

Your query does not seem to be syntactically correct, so I won't bother with it, but just continue from your result table as your intermediate result. Put your original query instead of my UNION ALL SELECT expression into my first CTE called intermediate_result .

WITH                                                                                                                                                        
-- your sample result as a Common Table Expression (CTE) ....
intermediate_result(ts,rs) AS (
          SELECT TIMESTAMP '2022-04-11 11:10:00','my_table=1000'
UNION ALL SELECT TIMESTAMP '2022-04-11 11:05:00','my_table=1000'
UNION ALL SELECT TIMESTAMP '2022-04-11 11:00:00','my_table=196'
UNION ALL SELECT TIMESTAMP '2022-04-11 09:50:00','my_table=588'
UNION ALL SELECT TIMESTAMP '2022-04-11 09:45:00','my_table=1000'
UNION ALL SELECT TIMESTAMP '2022-04-11 09:40:00','my_table=1000'
UNION ALL SELECT TIMESTAMP '2022-04-11 09:35:00','my_table=1000'
)
,
-- next CTE ..
ts(ts) AS (
  SELECT
    tms AS ts
  FROM intermediate_result
  TIMESERIES tms AS '5 MINUTES' OVER(ORDER BY ts)
)
SELECT
  ts.ts
, NVL(rs,'my_table=0') AS rs
FROM ts
LEFT JOIN intermediate_result USING(ts)
ORDER BY 1;
-- out          ts          |      rs       
-- out ---------------------+---------------
-- out  2022-04-11 09:35:00 | my_table=1000
-- out  2022-04-11 09:40:00 | my_table=1000
-- out  2022-04-11 09:45:00 | my_table=1000
-- out  2022-04-11 09:50:00 | my_table=588
-- out  2022-04-11 09:55:00 | my_table=0
-- out  2022-04-11 10:00:00 | my_table=0
-- out  2022-04-11 10:05:00 | my_table=0
-- out  2022-04-11 10:10:00 | my_table=0
-- out  2022-04-11 10:15:00 | my_table=0
-- out  2022-04-11 10:20:00 | my_table=0
-- out  2022-04-11 10:25:00 | my_table=0
-- out  2022-04-11 10:30:00 | my_table=0
-- out  2022-04-11 10:35:00 | my_table=0
-- out  2022-04-11 10:40:00 | my_table=0
-- out  2022-04-11 10:45:00 | my_table=0
-- out  2022-04-11 10:50:00 | my_table=0
-- out  2022-04-11 10:55:00 | my_table=0
-- out  2022-04-11 11:00:00 | my_table=196
-- out  2022-04-11 11:05:00 | my_table=1000
-- out  2022-04-11 11:10:00 | my_table=1000

aklaer

Thanks very much for the hint with the helper table, I will give that a try!

aklaer

ok, your hint was great, problem solved :-)