CODEWITHSUNDEEP
sqloracle-databaseoracle11gwindow-functions
subi_speedrunner
subi_speedrunner

Reputation: 921

Analytic function - Comparing values using LAG()

Assume following data:

| Col1  |   Col2      |
| 3     |  20-dec-15  |
| 4     |  20-dec-15  |
| 8     |  25-dec-15  |
|10     |  25-dec-15  |

I have to compare the values of column Col1 for a particular date.

For Example: For 20-dec-15 changes occured as 3 changed to 4.

I have to solve this using an analytical function.

Following is the query which I am using

decode(LAG(Col1,1,Col1) OVER (partition by Col2 order by Col2),Col1,0,1) Changes

As Col2 is date column, Partition by date is not working for me. Can we apply date column as Partition?

Expected Result should be:

| Changes  | 
| 0        |
| 1        |
| 0        |
| 1        |

Here 1 means Change occured while comparing for same date.

Upvotes: 0

Views: 571

Answers (1)

user330315
user330315

Reputation:

You need to use trunc() in order to reset the time part to 00:00:00 but you should still keep order by col2 so that all rows on the same day are ordered by the time part:

I also prefer an explicit case for this kind of comparison, personally I find the decode() really hard to read:

select case 
         when col1 = lag(col1,1,col1) over (partition by trunc(col2) order by col2) then 0 
         else 1
       end as changes
from the_table;

Upvotes: 1

Related Questions