I have data in hour intervals,
I would like to compare each given data to the previous interval and two previous intervals.
I arranged each record using the RANK command and then tried to execute a partition by command and attempted to execute a rank-1 or rank-2 command, but the command was not accepted.
Does anyone have an idea of what I should do?
@amirohOthers might find it easier to help if you included the SQL you are running and the error message you encountered. If you can't share the exact SQL because of sensitive column or table names, simplifying the query and renaming columns would be ideal.
thank you @Tim Armstrong
I hope I can explain the case better,
I have a number of intervals and I want to display the value of the same interval and the value of the previous interval and the value that was before two intervals.
In the end, I would like to compare the value in each interval to the values that were in the previous two / three intervals.
I used the RNAK command to display the serial number of each interval.
Then I used the LAG command to display the previous value.
So far everything worked fine.
As soon as I tried to bring the second and third intervals back I ran into a problem
This is the command I typed (in order to get the value two back intervals):
lag(b.succ,b.rank-2) OVER ( ORDER BY b.dt_half )
And I got the following error:
The offset parameter of LEAD/LAG must be a constant positive integer: lag(b.succ, b.rank - 2)
This is the final result I want to get:
|rank||dt||dt_half||succ||interval -1||interval -2||interval -3|
Thanks in advance
I believe it's a limitation of the LEAD/LAG implementation that the second argument has to be a constant.