Row Tagging With Additional Session Number Column

General
Enthusiast

Row Tagging With Additional Session Number Column

Dear All

The following statement returns the results shown below:

SELECT
Stbx_Id

,COALESCE(MIN(End_Ts)
OVER (ORDER BY Stbx_Id ASC, Start_Ts ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
, Start_Ts - INTERVAL '1' HOUR) AS Prev_End_Ts

,CAST(EXTRACT(DAY FROM (Start_Ts - Prev_End_Ts DAY(4) TO SECOND)) * 86400 AS INTEGER)
+ CAST(EXTRACT(HOUR FROM (Start_Ts - Prev_End_Ts DAY(4) TO SECOND)) * 3600 AS INTEGER)
+ CAST(EXTRACT(MINUTE FROM (Start_Ts - Prev_End_Ts DAY(4) TO SECOND)) * 60 AS INTEGER)
+ CAST(EXTRACT(SECOND FROM (Start_Ts - Prev_End_Ts DAY(4) TO SECOND)) AS INTEGER) AS Diff

FROM Fact_Table Tab

WHERE
Tab.Start_Dt IN (DATE '2014-12-29')
AND Tab.Stbx_Id IN (1000008)

ORDER BY ROW_NUMBER () OVER (PARTITION BY Stbx_Id ORDER BY Start_Ts ASC)

Stbx_Id   Prev_End_Ts          Diff_Start_Ts_Prev_End_Ts
1000008 29.12.2014 08:56:29 3'600
1000008 29.12.2014 11:28:52 4'473
1000008 29.12.2014 12:52:17 9'578
1000008 29.12.2014 15:56:05 209
1000008 29.12.2014 16:02:42 0
1000008 29.12.2014 16:48:07 2'338
1000008 29.12.2014 17:30:45 10
1000008 29.12.2014 18:57:15 0
1000008 29.12.2014 20:02:24 1
1000008 29.12.2014 20:12:46 0
1000008 29.12.2014 21:49:13 0
1000008 29.12.2014 22:15:08 12

My requirement is to append an additional row called Session_Num according to the following business rule (in pseudo code):

for (i = 1; i <= num_rows; i++) {
if (Diff >= 3600) {
Session_Num = Session_Num(row[i-1]) + 1
} else {
Session_Num = Session_Num(row[i-1])
}
}

num_row: number of rows in result set


In other words, if the difference is ge 3600 (1 hour) then increase the session counter, otherwise use the old counter. The result should look like this:


Stbx_Id   Prev_End_Ts          Diff_Start_Ts_Prev_End_Ts  Session_Num
1000008 29.12.2014 08:56:29 3'600 1
1000008 29.12.2014 11:28:52 4'473 2
1000008 29.12.2014 12:52:17 9'578 3
1000008 29.12.2014 15:56:05 209 3
1000008 29.12.2014 16:02:42 0 3
1000008 29.12.2014 16:48:07 2'338 4
1000008 29.12.2014 17:30:45 10 4
1000008 29.12.2014 18:57:15 0 4
1000008 29.12.2014 20:02:24 1 4
1000008 29.12.2014 20:12:46 0 4
1000008 29.12.2014 21:49:13 0 4
1000008 29.12.2014 22:15:08 12 4

Is it possible to achieve this result by a modification of the query above?



Any help is highly appreciated.

Best regards,

  Christoph

2 REPLIES
Junior Contributor

Re: Row Tagging With Additional Session Number Column

Hi Christoph,

the example data doesn't match the query/your description, but what you want is the SESSIONIZE function in Aster :-)

In Teradata you need nested OLAP:

SELECT
SUM(CASE WHEN diff >= 3600 THEN 1 ELSE 0 end)
OVER (PARTITION BY Stbx_Id
ORDER BY Start_Ts
ROWS UNBOUNDED PRECEDING) AS Session_Num
FROM
(your select)

Btw, you should PARTITION BY instead of ORDER BY Stbx_Id

Enthusiast

Re: Row Tagging With Additional Session Number Column

Hi Dieter

Your hint to use nested OLAP works very well. And sorry for the mismatch between the query and my description. Also the sessionization I show in my first post is not based on a value of 3600 but 1800 seconds.

Thanks a lot for the help!

Christoph