LAG function behaviour on first row and group of repeated partition

Question

I am trying to use the LAG() function in MSSQL and I am getting a weird behavior. The table looks like this:

    ID  TotalReadings    Month     Device
0    1    4           January         M  
1    1    4           January         D  
2    1    4           January         T  
2    1    4           January         L  
2    1    2           February        M  
2    1    2           February        D  
2    1    2           February        L  
0    1    2           February        T  
1    1    6           March           M  
2    1    6           March           D  
2    1    6           March           L  
2    1    6           March           T  
2    1    6           April           M  
2    1    6           April           D  
2    1    6           April           T  
2    1    6           April           L

What I did was:

Select *,
    CASE 
        WHEN 
             ISNULL(LAG(TotalReadings) OVER (PARTITION BY ID ORDER BY Month ), 0) < TotalReadings THEN 'Increase' 
        WHEN
             ISNULL(LAG(TotalReadings) OVER (PARTITION BY ID ORDER BY Month), 0) = TotalReadings THEN 'Neutral'
        WHEN
             ISNULL(LAG(TotalReadings) OVER (PARTITION BY ID ORDER BY Month), 0) > TotalReadings THEN 'Decrease'
    END As Trend 
from table

and got:

    ID  TotalReadings    Month     Device   Trend
0    1    4           January         M      Increase
1    1    4           January         D      Neutral
2    1    4           January         T      Neutral
2    1    4           January         L      Neutral
2    1    2           February        M      Decrease
2    1    2           February        D      Neutral
2    1    2           February        L      Neutral
0    1    2           February        T      Neutral
1    1    6           March           M      Increase
2    1    6           March           D      Neutral
2    1    6           March           L      Neutral
2    1    6           March           T      Neutral
2    1    6           April           M      Neutral
2    1    6           April           D      Neutral
2    1    6           April           T      Neutral
2    1    6           April           L      Neutral

But what I really want is to have first grouping by Month with trend named "Start" since there is no previous value to compare with, and remaining should take into account the similar TOtalReadings, which on monthly basis is the same, so trend should not be correct just for first row at beginning of new month row but for all: like this:

    ID  TotalReadings    Month     Device   Trend
0    1    4           January         M      Start
1    1    4           January         D      Start
2    1    4           January         T      Start
2    1    4           January         L      Start
2    1    2           February        M      Decrease
2    1    2           February        D      Decrease
2    1    2           February        L      Decrease
0    1    2           February        T      Decrease
1    1    6           March           M      Increase
2    1    6           March           D      Increase
2    1    6           March           L      Increase
2    1    6           March           T      Increase
2    1    6           April           M      Neutral
2    1    6           April           D      Neutral
2    1    6           April           T      Neutral
2    1    6           April           L      Neutral

any clue?

`OVER (PARTITION BY ID, month ORDER BY Month ),`.. but need to work for `start` — Pugal, Oct 02 '18 at 07:58
It will not give the same as last table. It works only for first row of a say in a month with 4 rows. Then becomes neutral as it compares with elements of the month itself. — tavalendo, Oct 02 '18 at 08:17
@IdontKnowEnglish - any column you place in the `ORDER BY` of a window function that *also* appears in the `PARTITION BY` is pointless. By the partitioning, you know that all rows within a single partition share a single value for that column. — Damien_The_Unbeliever, Oct 02 '18 at 09:01
@Damien_The_Unbeliever.. let me try your suggestion... partitioning by id is returns that all rows as one partitions. but (id, month) partition returns n number of partitions what are presented. — Pugal, Oct 02 '18 at 09:15
@IdontKnowEnglish - yes, and there'll be one partition for January, a separate partition for February, etc. So when you then do `order by Month`, what are you expecting it to do? — Damien_The_Unbeliever, Oct 02 '18 at 09:17
@Damien_The_Unbeliever.. okay.. then i need to study about `over()` clause. right..!! — Pugal, Oct 02 '18 at 09:25
Is your query actually working? You can't just order by month names. All columns have ID = 1 so I can't see any partitioning. — Salman A, Oct 02 '18 at 10:39
I used partition by Device and ID and I do get output as expected. See my answer/query below. — WhoamI, Oct 02 '18 at 11:12
@feijao.. see [my updated post](https://stackoverflow.com/questions/52604080/lag-function-behaviour-on-first-row-and-group-of-repeated-partition/52605911#52605911) — Pugal, Oct 02 '18 at 12:30

score 0 · Answer 1 · answered Oct 02 '18 at 08:34

Here you go:

    create table #t
    (id int, totalreadings int, month int, device char(1))

    insert into #t
    values
    (1,4,1,'M'),
    (1,4,1,'D'),
    (1,4,1,'T'),
    (1,4,1,'L'),
    (1,2,2,'M'),
    (1,2,2,'D'),
    (1,2,2,'L'),
    (1,2,2,'T'),
    (1,6,3,'M'),
    (1,6,3,'D'),
    (1,6,3,'L'),
    (1,6,3,'T'),
    (1,6,4,'M'),
    (1,6,4,'D'),
    (1,6,4,'L'),
    (1,6,4,'T')


    Select *,
        CASE 
            WHEN 
                 LAG(TotalReadings) OVER (PARTITION BY ID,device ORDER BY Month ) < TotalReadings THEN 'Increase' 
            WHEN
                 LAG(TotalReadings) OVER (PARTITION BY ID,device ORDER BY Month) = TotalReadings THEN 'Neutral'
            WHEN
                 LAG(TotalReadings) OVER (PARTITION BY ID,device ORDER BY Month) > TotalReadings THEN 'Decrease'
            ELSE 'Start'
        END As Trend 
    from #t
    order by month

id  totalreadings   month   device  Trend
1   4   1   D   Start
1   4   1   L   Start
1   4   1   M   Start
1   4   1   T   Start
1   2   2   T   Decrease
1   2   2   M   Decrease
1   2   2   L   Decrease
1   2   2   D   Decrease
1   6   3   D   Increase
1   6   3   L   Increase
1   6   3   M   Increase
1   6   3   T   Increase
1   6   4   T   Neutral
1   6   4   M   Neutral
1   6   4   L   Neutral
1   6   4   D   Neutral

Thanks! Do you know how to control if say there are more columns in the table that are like "Device" with varying values, yet the end result should be the trend column as above with TotalReadings taken into account? I have the table in an extend way. SHould I add new column names under Partition by? — tavalendo, Oct 02 '18 at 11:43
Lets take an example..if each device has part and you want to know the trend analysis for each device and each part, you need to include those columns in partition by clause. Please accept my answer if that helped... :) — WhoamI, Oct 02 '18 at 15:27

Pugal · Answer 2 · 2018-10-03T07:20:46.740

If you add a identity column, then you can use this code

create table #order ( i int identity(1,1),  ID  int, TotalReadings    int, Month     varchar(20), Device varchar(1))
insert #order values
(  1  ,  4           ,'January'         ,'M' ) 
,(  1  ,  4           ,'January'         ,'D' ) 
,(  1  ,  4           ,'January'         ,'T' ) 
,(  1  ,  4           ,'January'         ,'L' ) 
,(  1  ,  2           ,'February'       ,'M'  )
,(  1  ,  2           ,'February'       ,'D'  )
,(  1  ,  2           ,'February'       ,'L'  )
,(  1  ,  2           ,'February'       ,'T'  )
,(  1  ,  6           ,'March'           ,'M' ) 
,(  1  ,  6           ,'March'           ,'D' ) 
,(  1  ,  6           ,'March'           ,'L' ) 
,(  1  ,  6           ,'March'           ,'T' ) 
,(  1  ,  6           ,'April'           ,'M' ) 
,(  1  ,  6           ,'April'           ,'D' ) 
,(  1  ,  6           ,'April'           ,'T' ) 
,(  1  ,  6           ,'April'           ,'L' )

Select *
    ,CASE 
        WHEN 
             lag(TotalReadings, 4) OVER (PARTITION BY ID ORDER BY i,id, Month)  < TotalReadings THEN 'Increase' 
        WHEN                                                                   
             lag(TotalReadings, 4) OVER (PARTITION BY ID ORDER BY i,id, Month)  = TotalReadings THEN 'Neutral'
        WHEN                                                                   
             lag(TotalReadings, 4) OVER (PARTITION BY ID ORDER BY i,id, Month)  > TotalReadings THEN 'Decrease'
        WHEN                                                                   
             lag(TotalReadings, 4) OVER (PARTITION BY ID ORDER BY i,id, Month)  is null THEN 'start'
    END As Trend 
from #order
order by i

Edit 1: NO NEED OF IDENTITY COLUMN

Select *
    ,CASE 
        WHEN 
             lag(TotalReadings, 4) OVER (PARTITION BY ID ORDER BY id, MONTH(MONTH + ' 1 2014') )  < TotalReadings THEN 'Increase' 
        WHEN                                                         
             lag(TotalReadings, 4) OVER (PARTITION BY ID ORDER BY id, MONTH(Month + ' 1 2014') )  = TotalReadings THEN 'Neutral'
        WHEN                                                         
             lag(TotalReadings, 4) OVER (PARTITION BY ID ORDER BY id, MONTH(Month + ' 1 2014') )  > TotalReadings THEN 'Decrease'
        WHEN                                                          
             lag(TotalReadings, 4) OVER (PARTITION BY ID ORDER BY id, MONTH(Month + ' 1 2014') )  is null THEN 'start'
    END As Trend 
from #order
order by MONTH(Month + ' 1 2014')

LAG function behaviour on first row and group of repeated partition

2 Answers2