Fetch Rows Based On Condition

I am using PostgreSQL on Amazon Redshift. My table is : drop table APP_Tax; create temp table APP_Tax(APP_nm varchar(100),start timestamp,end1 timestamp); insert into APP_Tax val

Solution 1:

There are two reasons that row y is not returned is due to the condition:

• b.start > a.start means that a row will never join with itself
• The GROUP BY will return only one record per APP_nm value, yet all rows have the same value.

However, there are further logic errors in the query that will not successfully handle. For example, how does it know when a "new" session begins?

The logic you seek can be achieved in normal PostgreSQL with the help of a DISTINCT ON function, which shows one row per input value in a specific column. However, DISTINCT ON is not supported by Redshift.

Some potential workarounds: DISTINCT ON like functionality for Redshift

The output you seek would be trivial using a programming language (which can loop through results and store variables) but is difficult to apply to an SQL query (which is designed to operate on rows of results). I would recommend extracting the data and running it through a simple script (eg in Python) that could then output the Start & End combinations you seek.

This is an excellent use-case for a Hadoop Streaming function, which I have successfully implemented in the past. It would take the records as input, then 'remember' the start time and would only output a record when the desired end-logic has been met.

Solution 2:

Sounds like what you are after is "sessionisation" of the activity events. You can achieve that in Redshift using Windows Functions.

The complete solution might look like this:

SELECTstartAS session_start,
  session_end
FROM (
       SELECTstart,
         end1,
         lead(end1, 1)
         OVER (
           ORDERBY end1) AS session_end,
         session_boundary
       FROM (
              SELECTstart,
                end1,
                CASEWHEN session_switch =0AND reverse_session_switch =1THEN'start'ELSE'end'ENDAS session_boundary
              FROM (
                     SELECTstart,
                       end1,
                       CASEWHEN datediff(seconds, end1, lead(start, 1)
                       OVER (
                         ORDERBY end1 ASC)) >10THEN1ELSE0ENDAS session_switch,
                       CASEWHEN datediff(seconds, lead(end1, 1)
                       OVER (
                         ORDERBY end1 DESC), start) >10THEN1ELSE0ENDAS reverse_session_switch
                     FROM app_tax
                   )
                AS sessioned
              WHERE session_switch !=0OR reverse_session_switch !=0UNIONSELECTstart,
                end1,
                'start'FROM (
                     SELECTstart,
                       end1,
                       row_number()
                       OVER (PARTITIONBY APP_nm
                         ORDERBY end1 ASC) AS row_num
                     FROM APP_Tax
                   ) AS with_row_number
              WHERE row_num =1
            ) AS with_boundary
     ) AS with_end
WHERE session_boundary ='start'ORDERBYstartASC
;

Here is the breadkdown (by subquery name):

1. sessioned - we first identify the switch rows (out and in), the rows in which the duration between end and start exceeds limit.
2. with_row_number - just a patch to extract the first row because there is no switch into it (there is an implicit switch that we record as 'start')
3. with_boundary - then we identify the rows where specific switches occur. If you run the subquery by itself it is clear that session start when session_switch = 0 AND reverse_session_switch = 1, and ends when the opposite occurs. All other rows are in the middle of sessions so are ignored.
4. with_end - finally, we combine the end/start of 'start'/'end' rows into (thus defining session duration), and remove the end rows

with_boundary subquery answers your initial question, but typically you'd want to combine those rows to get the final result which is the session duration.