Imagine, if you will, begin able to calculate the time between visits to a website, transactions in a store, logs from a punch-clock, etc. in just one step. Well, I have found the way!
dateofinterest - lag(dateofinterest,1) over (partition by idfield order by dateofinterest)
Here’s the Step-by-Step
- Use the LAG() function with the first parameter representing the field you want to get the value of and the second parameter being the offset in terms of number of records between current and lagged
- Specify what to PARTITION BY based upon how you want to split the full table. In this example, we use idfield to only lag within a given idfield value
- Specify the ORDER BY based upon what will be used to assign a ranking to records. In this case, it is the dateofinterest such that our rows are chronologically arranged.
- Subtract all this from the current record’s dateofinterest
Other Convenient Uses:
There are bunches more than this but these are the ones that jumped to mind.
- Find the next person to beat in terms of sales or footrace speed or whatever.
- Compare stock prices measured at fixed intervals
- The result of subtracting one date from the other, as above, will give you an interval. If you want to then do further calculations on the results, you’ll need to EXTRACT accordingly.