window function on session

Database
Enthusiast

window function on session

Wanted to create new sessions based on inactivity of 30 minutes.

 

I have below table:

 

sl.nouseridtimestamp

EXPECTED O/P:

S_ID

My O/P:

S_ID

1u15th apr 06:3011
2u15th Apr 06:4011
3u15th Apr 07:4022
4u15th Apr 08:3533
5u25th Apr 08:3041
6u25th Apr 08:5841
7u25th Apr 09:4052

 

My query as below:

SELECT UID, TIMESTAMP,
    SUM(NEW_SN) OVER (PARTITION BY UID ORDER BY TIMESTAMP rows unbounded preceding)+1 AS S_ID
    FROM    (
        SELECT UID, TIMESTAMP,
            CASE WHEN (TIMESTAMP - PREV_TS) Second(4,6) >=1800 THEN 1 ELSE 0
        END AS NEW_SN
       FROM                                    (
                                SELECT UID,TIMESTAMP, 
                                MAX(TIMESTAMP)
                                OVER(PARTITION BY UID ORDER BY TIMESTAMP
                                ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PREV_TS
                                FROM TABLE                                     ) AS A         )B

I tried giving order by serial no. But gives, vague results.

 

Please help!


Accepted Solutions
Junior Contributor

Re: window function on session

Your query can be further simplified to

SELECT UID, TIMESTAMP,
   Sum(CASE WHEN (TIMESTAMP < PREV_TS + INTERVAL '1800' SECOND -- better use this calculation to avoid an Interval Overflow error
            THEN 0 -- within 1800 seconds
            ELSE 1 -- greater than 1800 or PREV_TS IS NULL
       END)
   Over (ORDER BY TIMESTAMP ROWS Unbounded Preceding) AS S_ID
FROM 
 (
   SELECT UID,TIMESTAMP, 
      -- TD16.10 finally supports LAG 
      -- Lag(TIMESTAMP)
      -- Over(PARTITION BY UID
      --      ORDER BY TIMESTAMP) AS PREV_TS    
      Max(TIMESTAMP)
      Over(PARTITION BY UID
           ORDER BY TIMESTAMP
           ROWS BETWEEN 1 Preceding AND 1 Preceding) AS PREV_TS
   FROM TABLE
 ) AS A

 

1 ACCEPTED SOLUTION
8 REPLIES
Teradata Employee

Re: window function on session

In the outer SUM, instead of "PARTITION BY UID ORDER BY TIMESTAMP" it seems you just want "ORDER BY UID, TIMESTAMP".

Highlighted
Enthusiast

Re: window function on session

Thanks for your reply.. But still, the output is not as expected.

 

I have tried it ordering by uid,timestamp too. But gives me the below result. I would need the next sequence number if uid is different.

 

Do I need to do something with sl.no, in this case?

 

sl.nouseridtimestampEXPECTED O/P

MY O/P AFTER REMOVING PARTITIONS

ordering by uid,ts

1u15th apr 06:3011
2u15th Apr 06:4011
3u15th Apr 07:4022
4u15th Apr 08:3533
5u25th Apr 08:3043
6u25th Apr 08:5843
7u25th Apr 09:4054
Teradata Employee

Re: window function on session

Missed that.

Add WHEN PREV_TS IS NULL THEN 1 to the CASE statement (and remove the +1 from the outer query) so result increments for each new UID.

 

Junior Contributor

Re: window function on session

Your query can be further simplified to

SELECT UID, TIMESTAMP,
   Sum(CASE WHEN (TIMESTAMP < PREV_TS + INTERVAL '1800' SECOND -- better use this calculation to avoid an Interval Overflow error
            THEN 0 -- within 1800 seconds
            ELSE 1 -- greater than 1800 or PREV_TS IS NULL
       END)
   Over (ORDER BY TIMESTAMP ROWS Unbounded Preceding) AS S_ID
FROM 
 (
   SELECT UID,TIMESTAMP, 
      -- TD16.10 finally supports LAG 
      -- Lag(TIMESTAMP)
      -- Over(PARTITION BY UID
      --      ORDER BY TIMESTAMP) AS PREV_TS    
      Max(TIMESTAMP)
      Over(PARTITION BY UID
           ORDER BY TIMESTAMP
           ROWS BETWEEN 1 Preceding AND 1 Preceding) AS PREV_TS
   FROM TABLE
 ) AS A

 

Enthusiast

Re: window function on session

Again the same. Not getting in sequence.

 

It follows 1 and 2 for an UID and again starts with 1,2,.. for another UID but I wanted it to be 1 and 2 for an UID and starts with 3 for next UID.

 

Enthusiast

Re: window function on session

It doesnot work actually.

 

We have something like this below:

s.no    u_id    ts          Expected o/p

1         u1       07:00    1

2         u1       07:20    1

3         u1       07:59    2

4         u2       04:00    3

5         u2       04:05    3

6         u2       04:50    4

7         u3       05:35    1

 

O/P needs to be based on interval of 30 minutes but in sequence partitioning by uid. (as above)

We get the o/p again starting from 1 when next uid comes. Please help.

Junior Contributor

Re: window function on session

My Select will never restart with 1 because there's no PARTITION BY in the outer Select.

Maybe you simply have to add UID to ORDER BY:

  Over (ORDER BY UID, Timestamp ROWS Unbounded Preceding) AS S_ID

You better explain your logic in detail.

 

 

Enthusiast

Re: window function on session

This query works perfectly fine.

 

Thank you Dnoeth for your prompt solution, as always!