All Forums Database
wangguojie2006 8 posts Joined 09/13
13 Jan 2015
Help coding on count historical event within a fix time period, greatly thanks!

Hi all,
I have a teradata sql coding question need your help. Any idea is welcome.
My data looks like below, sort by user_id and eventtime.
user_id             eventtime
1                       1:00:00
1                       1:30:00
1                        1:45:00
1                      1:50:00
1                       3:00:00
2                      1:00:00
2                      1:30:00
2                       1:45:00
Now I want to create a new variable called "prev_1h_event_cnt", which counts how many event occurred within 1 hour time window right before the eventtime on this row, for each user. E.g., for row #4, how many event occurred within 1 hour time window before 1:50:00? Count = 3. When it comes to a new user, I need to reset this count to be zero. 
So the result would look like below:

user_id             eventtime              prev_1h_event_cnt
1                       1:00:00                 0
1                       1:30:00                 1
1                        1:45:00                2
1                      1:50:00                  3
1                       3:00:00                 0
2                      1:00:00                  0
2                      1:30:00                  1
2                       1:45:00                 2
Please help to give some idea on how to make this happen. Greatly appreciated!

 

dnoeth 4628 posts Joined 11/04
13 Jan 2015

And for user_1 1 and eventtime 2:40 it should return 3, too?
I don't think there's an easy way to get this result, even a cursor or recursive query will fail.
Some fancy SQL using cross joins might work, but performance will be horrible for larger tables.
This would be a task for a cumulative count using RANGE, but this syntax is not implemented in Teradata. 

Dieter

vrushang 4 posts Joined 11/10
13 Jan 2015

select user_Id,  

rank() over(partition by user_id, extract(hour from logon_ts) order by logon_ts) -1 as rk 

from table_name;

 

-Vrushang

dnoeth 4628 posts Joined 11/04
13 Jan 2015

Hi Vrushang,
based on the description the result should be the number of rows within 60 minutes before the current row's eventtime, i.e. a moving count (not cumulative as I wrote in my first post).
Without OLAP (but possibly very bad performance):

SELECT t1.user_id, t2.eventtime, COUNT(*) -1
FROM tab AS t1 JOIN tab AS t2
ON t1.user_id = t2.user_id
AND t1.eventtime BETWEEN t2.eventtime - INTERVAL '1' HOUR AND t2.eventtime
GROUP BY 1,2

 

Dieter

You must sign in to leave a comment.