Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Compare data to a number of previous intervals

Compare data to a number of previous intervals

New Contributor

Hello,
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?

 

thanks

Amir

3 REPLIES 3

Re: Compare data to a number of previous intervals

Master Collaborator

@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.

Re: Compare data to a number of previous intervals

New Contributor

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:

rankdtdt_halfsuccinterval -1interval -2interval -3
128/02/201907:3076.84   
228/02/201908:0076.9276.84  
328/02/201908:3073.0576.9276.84 
428/02/201909:0066.6673.0576.9276.84
528/02/201909:3044.166.6673.0576.92
628/02/201910:0064.6544.166.6673.05
728/02/201910:3065.8864.6544.166.66

 

Thanks in advance

Amir

Re: Compare data to a number of previous intervals

Master Collaborator

I believe it's a limitation of the LEAD/LAG implementation that the second argument has to be a constant.