将 LAG() 应用于具有空值的多行 [英] Applying LAG() to multiple rows with a null value

问题描述

给定:

with    
m as (
    select  1 ID, cast('03/01/2015' as datetime) PERIOD_START, cast('3/31/2015' as datetime) PERIOD_END
    union all
    select  1 ID, '04/01/2015', '4/28/2015'
    union all
    select  1 ID, '05/01/2015', '5/31/2015'
    union all
    select  1 ID, '06/01/2015', '06/30/2015'
    union all
    select  1 ID, '07/01/2015', '07/31/2015'
)

,
a as (

    SELECT  1 ID, cast('2015-03-13 14:17:00.000' as datetime) AUDIT_TIME, 'READ [2]' STATUS
    UNION ALL
    SELECT  1 ID, '2015-04-27 15:51:00.000' AUDIT_TIME, 'HELD [2]' STATUS
    UNION ALL
    SELECT  1 ID, '2015-07-08 17:54:00.000' AUDIT_TIME, 'COMPLETED [5]' STATUS
)

这个查询:

select  m.ID,PERIOD_START,PERIOD_END
        ,a.AUDIT_TIME,STATUS
from    m
LEFT OUTER JOIN a on m.id=a.id 
    and a.audit_time between m.period_start and m.period_end

生成此记录集:

ID  PERIOD_START    PERIOD_END  AUDIT_TIME  STATUS
1   2015-03-01 00:00:00.000 2015-03-31 00:00:00.000 2015-03-13 14:17:00.000 READ [2]
1   2015-04-01 00:00:00.000 2015-04-28 00:00:00.000 2015-04-27 15:51:00.000 HELD [2]
1   2015-05-01 00:00:00.000 2015-05-31 00:00:00.000 NULL    NULL
1   2015-06-01 00:00:00.000 2015-06-30 00:00:00.000 NULL    NULL
1   2015-07-01 00:00:00.000 2015-07-31 00:00:00.000 2015-07-08 17:54:00.000 COMPLETED [5]

我需要在 5 月和 6 月重复 4/27/15 条目:

I need the 4/27/15 entry repeated for May and June:

ID  PERIOD_START    PERIOD_END  AUDIT_TIME  STATUS
1   2015-03-01 00:00:00.000 2015-03-31 00:00:00.000 2015-03-13 14:17:00.000 READ [2]
1   2015-04-01 00:00:00.000 2015-04-28 00:00:00.000 2015-04-27 15:51:00.000 HELD [2]
1   2015-05-01 00:00:00.000 2015-05-31 00:00:00.000 2015-04-27 15:51:00.000 HELD [2]
1   2015-06-01 00:00:00.000 2015-06-30 00:00:00.000 2015-04-27 15:51:00.000 HELD [2]
1   2015-07-01 00:00:00.000 2015-07-31 00:00:00.000 2015-07-08 17:54:00.000 COMPLETED [5]

使用 LAG() 函数:

select  m.ID,PERIOD_START,PERIOD_END
        ,a.AUDIT_TIME
        ,LAG(audit_time) OVER (partition by m.ID order by period_start) PRIOR_AUDIT_TIME
        ,STATUS
        ,LAG(STATUS) OVER (partition by m.ID order by period_start) PRIOR_STATUS
from    m
LEFT OUTER JOIN a on m.id=a.id 
    and a.audit_time between m.period_start and m.period_end

仅适用于单行:

ID  PERIOD_START    PERIOD_END  AUDIT_TIME  PRIOR_AUDIT_TIME    STATUS  PRIOR_STATUS
1   2015-03-01 00:00:00.000 2015-03-31 00:00:00.000 2015-03-13 14:17:00.000 NULL    READ [2]    NULL
1   2015-04-01 00:00:00.000 2015-04-28 00:00:00.000 2015-04-27 15:51:00.000 2015-03-13 14:17:00.000 HELD [2]    READ [2]
1   2015-05-01 00:00:00.000 2015-05-31 00:00:00.000 NULL    2015-04-27 15:51:00.000 NULL    HELD [2]
1   2015-06-01 00:00:00.000 2015-06-30 00:00:00.000 NULL    NULL    NULL    NULL
1   2015-07-01 00:00:00.000 2015-07-31 00:00:00.000 2015-07-08 17:54:00.000 NULL    COMPLETED [5]   NULL

有没有办法不用游标就能做到这一点?

Is there a way to do this without having to resort to a cursor?

推荐答案

你可以用窗口函数做到这一点:

You can do this with window functions:

with q as (
      select m.ID, PERIOD_START, PERIOD_END, a.AUDIT_TIME, STATUS
      from m LEFT OUTER JOIN
           a
           on m.id = a.id and
              a.audit_time between m.period_start and m.period_end
     )
select q.*,
       max(status) over (partition by id, audit_grp) as imputed_status
from (select q.*,
             max(audit_time) over (partition by id order by period_start) as audit_grp
      from q
     ) q

这个想法是复制 audit_time 值，使用 max() 作为累积窗口函数.然后定义组，因此您也可以获取状态.

The idea is to copy the audit_time value over, using max() as a cumulative window function. This then defines groups, so you can get the status as well.

ANSI 向 LAG() 提供了 IGNORE NULLSs 指令，但 SQL Server(尚)不支持它.

ANSI supplies the IGNORE NULLSs directive to LAG(), but SQL Server does not (yet) support it.

这篇关于将 LAG() 应用于具有空值的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！