Help coding on count historical event within a fix time period, greatly thanks!

Database

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!




3 REPLIES
N/A

Re: Help coding on count historical event within a fix time period, greatly thanks!

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. 

Re: Help coding on count historical event within a fix time period, greatly thanks!

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

N/A

Re: Help coding on count historical event within a fix time period, greatly thanks!

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