I have table in my Oracle database (v12.2.0.2.1) that tracks a users attempt to register an event and the timestamp of that attempt. The question I am trying to answer is the time difference between each users attempt.

The original table looks something like this:

Where each attempt is uniquely registered in order by the USER_ACQUISITION_ATTEMPT_ID. The USER_ID is the ID of the user that makes the attempt and the RECORDING_DATE_ATTEMPT is the timestamp.

I've created the following query that will show the time different between each attempt throughout the whole table using the LAG function to compare the dates from one row to the next and then just some math to calculate the time difference.

WITH dat AS 
  (
  SELECT
    a.USER_ACQUISITION_ATTEMPT_ID,  
    a.USER_ID, 
    a.RECORDING_DATE_ATTEMP DAT1,
    a.RECORDING_DATE_ATTEMP DAT2
  FROM 
    USER_ACQUISITION_ATTEMPT a

  UNION ALL

  SELECT
    c.USER_ACQUISITION_ATTEMPT_ID,
    c.USER_ID,    
    c.RECORDING_DATE_ATTEMP DAT1,
    c.RECORDING_DATE_ATTEMP DAT2
  FROM 
    USER_ACQUISITION_ATTEMPT c 
   )
   
SELECT
  USER_ACQUISITION_ATTEMPT_ID,
  USER_ID,
  DAT1,
  LAG(DAT1,1) OVER (ORDER BY DAT1) LAG,
  TO_CHAR(DATE '1900-01-01'   ABS (LAG(DAT1,1) OVER (ORDER BY DAT1) - DAT1), 'HH24:MI:SS') as TIME_DIFF
FROM dat
group by 
  USER_ACQUISITION_ATTEMPT_ID,
  USER_ID,  
  DAT1

Which yields something like this:

A mentioned, I want to look at each user individually. The above query doesn't do that. For example, in the result table you will see the first row has the very first attempt and its timestamp. Under the LAG and TIME_DIFF column, there are nulls because no other attempt came before it. Each subsequent row grabs the last DAT1 and places it in the next row under the LAG column. That works fine until the next user tries an attempt. Where you see the USER_ID change from 189590 on USER_ACQUISITION_ATTEMPT_ID #6 to USER_ID 22809 on USER_ACQUISITION_ATTEMPT_ID #7, the last time stamp from the previous user is used against the first timestamp of the next user which is not what I want. The first attempt for any given user should have NULL for both the LAG and TIME_DIFF columns so that the query can sort of reset the calculation of the time differences for each user.

I hope I have explained the issue clearly, however if clarification is needed, please add some comments.

CodePudding user response：

You don't need the UNION ALL in the common table expression. It's just duplicating the data, which you then use GROUP BY to de-duplicate it.

Removing the UNION ALL means that you can remove the GROUP BY.

Then just use PARTITION BY in the analytic functions.

SELECT
  USER_ACQUISITION_ATTEMPT_ID,
  USER_ID,
  RECORDING_DATE_ATTEMP,
  LAG(RECORDING_DATE_ATTEMP)
    OVER (
      PARTITION BY USER_ID
          ORDER BY RECORDING_DATE_ATTEMP
    )
      AS LAG_RECORDING_DATE_ATTEMP
FROM
  USER_ACQUISITION_ATTEMPT