时间戳

首页 > 解决方案 > 使用 outerapply 填充

sql-server - 使用 outerapply 填充

问题描述

我的源数据如下

    declare @dim as table
(
    name varchar(10),period int
)
insert into @dim
select * from
(
    values
        ('a', 202001),('a', 202002),('a', 202003),('a', 202004),
        ('a', 202005),('a', 202006),('a', 202007),('a', 202008),
        ('a', 202009),('a', 202010),('a', 202011),('a', 202012),
        ('b', 202001),('b', 202002),('b', 202003),('b', 202004),
        ('b', 202005),('b', 202006),('b', 202007),('b', 202008),
        ('b', 202009),('b', 202010),('b', 202011),('b', 202012)
) t (one, two)


declare @fact as table
(
    name varchar(max),period bigint,val decimal(19, 2)
)
insert into @fact
select * from
(
    values
        ('a', 202002, 100),
        ('a', 202005, 600),
        ('a', 202010, 700),
        ('b', 202004, 500),
        ('b', 202007, 600),
        ('b', 20208, 1000)
) t (one, two, three)

我想知道是否有可能在outer-apply. 我尝试如下，但没有奏效

select a.name,a.period,x.val, y.FD
from @dim a
outer apply (select * from @fact b where a.name=b.name and a.period=b.period) as x
outer apply (select max(x.val) over (partition by a.name order by a.period ASC ROWS UNBOUNDED PRECEDING)) as y (FD)

我之所以热衷于实现内部填充，outer-apply是因为我可以继续利用outer-apply同一个查询中的填充列来成功apply创建进一步的计算列或转换，而无需任何临时表的帮助，如下所示

select a.name,a.period,x.val, y.FD, z.bucket
from @dim a
outer apply (select * from @fact b where a.name=b.name and a.period=b.period) as x
outer apply (successful fill down) as y (FD)
outer apply (VALUES(CASE WHEN FD>1000 then 'bucket1' else 'bucket2' end) as z(bucket)

我想要的结果

姓名 时期 值 FD
一个 202001 无效的 无效的
一个 202002 100.00 100.00
一个 202003 无效的 100.00
一个 202004 无效的 100.00
一个 202005 600.00 600.00
一个 202006 无效的 600.00
一个 202007 无效的 600.00
一个 202008 无效的 600.00
一个 202009 无效的 600.00
一个 202010 700.00 700.00
一个 202011 无效的 700.00
一个 202012 无效的 700.00
b 202001 无效的 无效的
b 202002 无效的 无效的
b 202003 无效的 无效的
b 202004 500.00 500.00
b 202005 无效的 500.00
b 202006 无效的 500.00
b 202007 600.00 600.00
b 202008 无效的 600.00
b 202009 无效的 600.00
b 202010 无效的 600.00
b 202011 无效的 600.00
b 202012 无效的 600.00

这可以通过遵循但在outer-apply我不想要的之外来实现。

select a.name,a.period,x.val, MAX(x.val) over (partition by a.name order by a.period ASC ROWS UNBOUNDED PRECEDING) as FD
from @dim a
outer apply (select * from @fact b where a.name=b.name and a.period=b.period) as x

标签： sql-servertsqlouter-apply

解决方案

您的问题是您将MAX窗口函数放在APPLY.

当您使用 时APPLY，会（逻辑上）为每一行评估整个子查询。因此，仅包含 a 的子查询只有select一行，并且对外部表的每一行进行评估。

您不能以这种方式使用窗口函数。仅（逻辑上）在加入和分组之后、select阶段中和排序之前评估窗口函数。出现APPLY在较早的加入阶段。

请注意，您的第一个APPLY可以重写为简单的LEFT JOIN.

select
    a.name,
    a.period,
    x.val, 
    FD = max(x.val) over 
         (partition by a.name
         order by a.period ROWS UNBOUNDED PRECEDING)
from @dim a
left join @fact x on a.name = x.name and a.period = x.period

如果您希望在MAX查询的其他部分使用 ，则必须将其放在派生表中。

select *,
  SomeOtherCalculationInvolvingFD
from (
  select
    a.name,
    a.period,
    x.val, 
    FD = max(x.val) over 
         (partition by a.name
         order by a.period ROWS UNBOUNDED PRECEDING)
  from @dim a
  left join @fact x on a.name = x.name and a.period = x.period
) t

我给你一个提示：APPLY非常有用，但不要到处敲击每个钉子。了解它的工作原理，适当使用。

推荐阅读