第一行和重复分区组上的滞后函数行为

干得好:

    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

如果添加 identity 列,则可以使用此代码

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

编辑1: 不需要标识列

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')