代码之家 › 专栏 › 技术社区 › gingerbreadboy

跨多行的SQL Lag()

azure-sql-database sql

2

gingerbreadboy · 技术社区 · 7 年前

我在试着填充 NULL 使用MS Azure SQL的前一个非空结果的结果。

根据下面的查询,如果 Status 列不替换 无效的 前一个非空值的值?文件 Lag(...) 建议阅读上一个结果行,以便“2015-04-03” athlete:7 应该从中复制值 2015-04-02 依次从 2015-04-01 . 但在这种情况下 Lag 似乎获取的是表值而不是合并结果行值。知道为什么吗?

我见过 alternative ways 执行此操作 从上面填充 行为,但他们并不能真正解释发生了什么,我正在努力理解他们,有谁能解释(像我是傻瓜)如何得到我需要的行为?

有益的是,当我们过渡到下一个运动员时,他们的第一天总是有状态的。

Declare @AvailabilityDates table
      ( row_index int,
        AthleteId int,
        AvailabilityDate date,
        Status int);

select 
    row_index , 
    AthleteId ,
    AvailabilityDate,
    COALESCE(
            Status, 
            LAG(Status) over (order by row_index)       
        ) as Status
from @AvailabilityDates 


row_index   AthleteId   AvailabilityDate    Status
1           7           2015-04-01          2
2           7           2015-04-02          2
3           7           2015-04-03          NULL
4           7           2015-04-04          NULL
5           7           2015-04-05          3
6           7           2015-04-06          3
7           7           2015-04-07          NULL
8           7           2015-04-08          NULL
9           7           2015-04-09          NULL
10          9           2015-04-01          2
11          9           2015-04-02          2
12          9           2015-04-03          NULL
13          9           2015-04-04          NULL
14          9           2015-04-05          NULL
15          9           2015-04-06          3
16          9           2015-04-07          4
17          9           2015-04-08          4
18          9           2015-04-09          NULL
19          9           2015-04-10          NULL

3 回复 | 直到 7 年前

1

Gordon Linoff 7 年前

这个 lag() 使用“before”数据计算 滞后() . 它不使用计算上一行的结果。

注意,在下面的所有建议中,我将 athleteid . 鉴于数据的性质,这似乎是合理的。

你真正想要的是:

select . . ., 
       LAG(Status IGNORE NULLS) over (partition by athleteid order by row_index)  
from @AvailabilityDates ;

您可以与Microsoft讨论如何实现此ISO/ANSI标准功能。

没有这个,一种流行的方法使用 CROSS APPLY :

select . . .,
       ad2.Status
from @AvailabilityDates ad OUTER APPLY
     (select top (1) status
      from @AvailabilityDates ad2
      where ad2.athleteid = ad.athleteid and
            ad2.status is not null and 
            ad2.row_index <= ad.row_index
      order by ad2.row_index desc
     ) ad2;

或者,如果只有一两个 NULL 在一行中,您可以扩展 COALESCE() :

select . . .,
       coalesce(status,
                lag(status, 1) over (partition by athleteid order by row_index),
                lag(status, 2) over (partition by athleteid order by row_index),
                lag(status, 3) over (partition by athleteid order by row_index)
               )

2

0

David Browne - Microsoft 7 年前

有谁能解释一下(像我是傻瓜一样)我该怎么做?

我也被窗口功能弄糊涂了。并且经常依赖于使用超级强大的,并且(对我来说)易于理解的应用操作符。

use tempdb

drop table if exists t

create table t(row_index int, AthleteId int, AvailabilityDate datetime, Status int)

insert into t
(row_index   ,AthleteId   ,AvailabilityDate    ,Status)
values
(1  ,7 ,'2015-04-01',2    ),
(2  ,7 ,'2015-04-02',2     ),
(3  ,7 ,'2015-04-03',NULL ),
(4  ,7 ,'2015-04-04',NULL ),
(5  ,7 ,'2015-04-05',3     ),
(6  ,7 ,'2015-04-06',3     ),
(7  ,7 ,'2015-04-07',NULL ),
(8  ,7 ,'2015-04-08',NULL ),
(9  ,7 ,'2015-04-09',NULL ),
(10 ,9 ,'2015-04-01',2     ),
(11 ,9 ,'2015-04-02',2     ),
(12 ,9 ,'2015-04-03',NULL ),
(13 ,9 ,'2015-04-04',NULL ),
(14 ,9 ,'2015-04-05',NULL ),
(15 ,9 ,'2015-04-06',3     ),
(16 ,9 ,'2015-04-07',4     ),
(17 ,9 ,'2015-04-08',4     ),
(18 ,9 ,'2015-04-09',NULL ),
(19 ,9 ,'2015-04-10',NULL );


select t.*, s.status s2
from t 
outer apply 
 (
    select top 1 status 
    from t t2 
    where t2.AthleteId = t.AthleteId 
      and t2.row_index < t.row_index 
      and status is not null 
    order by row_index desc
  ) s

3

0

gingerbreadboy 7 年前

作为@jnevill的道具,他在上面关于创建分区的评论直接指向了我。

我的解决方案是创建一个sum列 _grp 是电流的总和 status 与上一个 _玻璃钢 . 因此,任何与 null 会有相同的 _玻璃钢 所有先前的空值都返回到最后一个非空值 地位 . 然后我们把最大的 地位 从基于 _玻璃钢 ,但在任何 _玻璃钢 分区应该只有一个非空 地位 ,分区中的第一个(也就是最后一个显式定义的 地位 ). 我希望这是有意义的:)

use tempdb

drop table if exists t

create table t(row_index int, AthleteId int, AvailabilityDate datetime, Status int)

insert into t
(row_index   ,AthleteId   ,AvailabilityDate    ,Status)
values
(1  ,7 ,'2015-04-01',2    ),
(2  ,7 ,'2015-04-02',2     ),
(3  ,7 ,'2015-04-03',NULL ),
(4  ,7 ,'2015-04-04',NULL ),
(5  ,7 ,'2015-04-05',3     ),
(6  ,7 ,'2015-04-06',3     ),
(7  ,7 ,'2015-04-07',NULL ),
(8  ,7 ,'2015-04-08',NULL ),
(9  ,7 ,'2015-04-09',NULL ),
(10 ,9 ,'2015-04-01',2     ),
(11 ,9 ,'2015-04-02',2     ),
(12 ,9 ,'2015-04-03',NULL ),
(13 ,9 ,'2015-04-04',NULL ),
(14 ,9 ,'2015-04-05',NULL ),
(15 ,9 ,'2015-04-06',3     ),
(16 ,9 ,'2015-04-07',4     ),
(17 ,9 ,'2015-04-08',4     ),
(18 ,9 ,'2015-04-09',NULL ),
(19 ,9 ,'2015-04-10',NULL );

-- this CTE sums the row by row status (anything with null status is the same _grp as the row above) which creates a suitable partition
   _partitioned (row_index, AthleteId, AvailabilityDate, Status, _grp) AS 
    (
    SELECT
        row_index,
        AthleteId,
        AvailabilityDate,
        Status,
        SUM(Status) OVER (ORDER BY row_index) as _grp
    FROM t
    )

SELECT 
    AthleteId, 
    SportId, 
    AvailabilityDate, 
    Status,
-- this gets the max status in the partition which we created above
    MAX(Status) over (partition by _partitioned.grp order by _partitioned.row_index) as DerivedStatus   
FROM 
    _partitioned
    ORDER BY row_index