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:
x
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
;