SQL Server 更新序列表中的间隙

bt1cpqcv 于 2023-02-07 发布在其他

关注(0)|答案(2)|浏览(106)

我有一个包含员工银行数据的表

Employee      |Bank          |Date           |Delta
---------------------------------------------------
Smith         |Vacation      |2023-01-01     |15.0
Smith         |Vacation      |2023-01-02     |Null
Smith         |Vacation      |2023-01-03     |Null
Smith         |Vacation      |2023-01-04     |7.5

我想写一条语句，以便用1月1日的Delta值更新2023-01-02和2023-01-03。实际上，我想使用不大于该行日期的最近一行的值。
完成后，我希望该表如下所示：

Employee      |Bank          |Date           |Delta
---------------------------------------------------
Smith         |Vacation      |2023-01-01     |15.0
Smith         |Vacation      |2023-01-02     |15.0
Smith         |Vacation      |2023-01-03     |15.0
Smith         |Vacation      |2023-01-04     |7.5

源表有一个唯一索引，由Employee、Bank和Date降序排列组成。表中最多可以有20亿行。
我目前用以下内容更新了该表，但我想知道是否有更有效的方法来这样做？

WITH cte_date
     AS (SELECT dd.date_key, 
                db.balance_key, 
                feb.employee_key
         FROM shared.dim_date dd
              CROSS JOIN
         (
             SELECT DISTINCT 
                    employee_key
             FROM wfms.fact_employee_balance
         ) feb
              CROSS JOIN wfms.dim_balance db
         WHERE dd.date BETWEEN DATEFROMPARTS(DATEPART(YY, GETDATE()) - 2, 12, 31) AND GETDATE())
     SELECT dd.*, 
            t.delta
     INTO wfms.test2
     FROM cte_date dd
          LEFT JOIN wfms.test1 t ON dd.balance_key = t.balance_key
                                  AND dd.employee_key = t.employee_key
                                  AND t.date_key =  (SELECT TOP 1 tt1.date_key
                                                    FROM    wfms.test1 tt1
                                                    WHERE   tt1.balance_key = t.balance_key
                                                    AND     tt1.employee_key = t.employee_key
                                                    AND     tt1.date_key < dd.date_key);

sql-server

来源：https://stackoverflow.com/questions/75342403/update-gaps-in-sequential-table

2条答案

按热度按时间

lsmd5eda1#

只是为了好玩，我想测试一个想法。
目前，让我们假设差距不是太大...在这个例子中是7天。
相对于批次，lag() over()方法为22%，而Cross Apply为78%。

- 再一次，只是为了好玩**

Select Employee
      ,Bank
      ,Date
      ,Delta = coalesce(A.Delta
                       ,lag(Delta,1) over (partition by Employee,Bank order by date)
                       ,lag(Delta,2) over (partition by Employee,Bank order by date)
                       ,lag(Delta,3) over (partition by Employee,Bank order by date)
                       ,lag(Delta,4) over (partition by Employee,Bank order by date)
                       ,lag(Delta,5) over (partition by Employee,Bank order by date)
                       ,lag(Delta,6) over (partition by Employee,Bank order by date)
                       ,lag(Delta,7) over (partition by Employee,Bank order by date)
                       )
 From  YourTable A

- 对战**

Select Employee
      ,Bank
      ,Date
      ,Delta = coalesce(A.Delta,B.Delta)
 From  YourTable A
 Cross Apply ( Select top 1 Delta 
                From  YourTable 
                Where Employee=A.Employee
                  and A.Bank = Bank
                  and Delta is not null
                  and A.Date>=Date
                 Order By Date desc
             ) B

- 更新**

20天的结果相同

赞(0）回复(0）举报 2023-02-07

dtcbnfnu2#

这里是另一种方法。使用sum()和窗口函数找到行的组"Grp"（1行不为空，后续行为空）。最后Grp的max(Delta)返回不为空的值。

select  Employee, Bank, [Date], max  (max(Delta)) 
                                over (partition by Employee, Bank, Grp)
from 
(
    select  *, Grp = sum  (case when Delta is not null then 1 else 0 end) 
                     over (partition by Employee,Bank 
                               order by [Date]) 
    from    YourTable
) t
group by Employee, Bank, [Date], Grp

赞(0）回复(0）举报 2023-02-07

我来回答

SQL Server 更新序列表中的间隙

2条答案

相关问题

热门标签

最新问答