I have a table with columns below:
Customer | Time_Start |
---|---|
A | 01/20/2020 01:25:00 |
A | 01/22/2020 14:15:00 |
A | 01/20/2020 03:23:00 |
A | 01/21/2020 20:37:00 |
I am trying to get a table that outputs a table by minute (including zeros) for a given day.
i.e.
Customer | Time_Start | Count |
---|---|---|
A | 01/20/2020 00:01:00 | 5 |
A | 01/20/2020 00:02:00 | 2 |
A | 01/20/2020 00:03:00 | 0 |
A | 01/20/2020 00:04:00 | 12 |
I would like to have it show only 1 day for 1 customer at a time.
Here is what I have so far:
select customer, cast(time_start as time) + ((cast(time_start as time) - cast('00:00:00' as time)) hour(2)) as TimeStampHour, count(*) as count from Table_1 where customer in ('A') group by customer, TimeStampHour order by TimeStampHour
Advertisement
Answer
In Teradata 16.20 this would be a simple task using the new time series aggregation
SELECT customer ,Cast(Begin($Td_TimeCode_Range) AS VARCHAR(16)) ,Count(*) FROM table_1 WHERE customer = 'A' AND time_start BETWEEN TIMESTAMP'2020-01-20 00:00:00' AND Prior(TIMESTAMP'2020-01-20 00:00:00' + INTERVAL '1' DAY) GROUP BY TIME(Minutes(1) AND customer) USING timecode(time_start) FILL (0) ;
Before you must implement it similar to Ramin Faracov answer, create a list of all minutes first and then left join to it. But I prefer doing the count before joining:
WITH all_minutes AS ( -- create a list of all minutes SELECT Begin(pd) AS bucket FROM ( -- EXPAND ON requires FROM and TRUNC materializes the FROM avoiding error -- "9303. EXPAND ON clause must not be specified in a query expression with no table references." SELECT Cast(Trunc(DATE '2020-01-20') AS TIMESTAMP(0)) AS start_date ) AS dt EXPAND ON PERIOD(start_date, start_date + INTERVAL '1' DAY) AS pd BY INTERVAL '1' MINUTE ) SELECT customer ,bucket ,Coalesce(Cnt, 0) FROM all_minutes LEFT JOIN ( SELECT customer ,time_start - (Extract (SECOND From time_start) * INTERVAL '1' SECOND) AS time_minute ,Count(*) AS Cnt FROM table_1 WHERE customer = 'A' AND time_start BETWEEN TIMESTAMP'2020-01-20 00:00:00' AND Prior(TIMESTAMP'2020-01-20 00:00:00' + INTERVAL '1' DAY) GROUP BY customer, time_minute ) AS counts ON counts.time_minute = bucket ORDER BY bucket ;