Timeslice/ reporting 'empty' bins as zero

4 years ago
Apr 11, 2022 2:41 PM UTC
3 replies
27 views

aklaer
New Participant

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;

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?

+1

marcothesane
Participating Frequently
4 years ago
Apr 12, 2022 2:58 PM UTC

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