使用LAG而不使用递归CTE来获取上一行的更新值 [英] Get previous row updated value using LAG Without using Recursive CTE

问题描述

如何使用 LAG 函数获取更新的前一行值(不使用递归CTE ).请检查屏幕截图以获取示例输出

How to use LAG function to get the updated previous row value (without using Recursive CTE). Please check the screenshot for sample output

尝试查询

 Declare @Tbl as Table(SNO Int,Credit Money,Debit Money,PaidDate Date)
Insert into @Tbl
SELECT * FROM (VALUES (1,0,12,'7Jan16'), (2,10,0,'6Jan16'), (3,15,0,'5Jan16'), (4,0,5,'4Jan16'), (5,0,3,'3Jan16'), (6,0,2,'2Jan16'), (7,20,0,'1Jan16')) AS X(SNO,Credit,Debit,PaidDate)

Select
    T.SNO,
    T.Credit,
    T.Debit,
    TotalDebit = Case When Credit < LAG(T.Debit, 1, 0) OVER (ORDER BY SNO) Then Debit + (LAG(T.Debit, 1, 0) OVER (ORDER BY SNO)-Credit) Else Debit End,
    Amount = Case When Credit < LAG(T.Debit, 1, 0) OVER (ORDER BY SNO) Then 0 Else Credit-LAG(T.Debit, 1, 0) OVER (ORDER BY SNO) End,
    T.PaidDate
From @Tbl T

更新: 使用递归CTE可以得到预期的结果，但是当我将查询转换为函数时以及当我加入具有3000条记录的函数时，执行时间会很长.这就是为什么我要转换没有递归CTE部分的查询.

UPDATE: Can get the expected result using recursive CTE, but when i convert the query to function and when i join the function with 3000 record, takes long time to execute. That's why i am trying to convert the query without recursive CTE part.

递归CTE查询:

Declare @Tbl as Table(SNO Int,Credit Money,Debit Money,PaidDate Date)
Insert into @Tbl
SELECT * FROM (VALUES (1,0,12,'7Jan16'), (2,10,0,'6Jan16'), (3,15,0,'5Jan16'), (4,0,5,'4Jan16'), (5,0,3,'3Jan16'), (6,0,2,'2Jan16'), (7,20,0,'1Jan16')) AS X(SNO,Credit,Debit,PaidDate)


;With Temp As(/* Detect Debited amount */
    Select Top 1 SNO,Credit,Debit,Debit As TotalDebit,Credit As Amount,PaidDate From @Tbl
    Union All
    Select
        R.SNO,
        R.Credit,
        R.Debit,
        TotalDebit = Case When R.Credit < RP.TotalDebit Then R.Debit + (RP.TotalDebit-R.Credit) Else R.Debit End,
        Amount = Case When R.Credit < RP.TotalDebit Then 0 Else R.Credit-RP.TotalDebit End,
        R.PaidDate
    From @Tbl R
    Inner Join Temp RP ON R.SNO-1=RP.SNO
)

Select * From Temp

电子表格示例: https://docs.google.com/spreadsheets/d/1FNwzgR4QCnFK4GcX5R edit?usp = sharing

推荐答案

此处的性能受到递归 CTE的影响. CTE本身就是语法糖.

Performance here is suffering from recursive CTE. CTE on it's own is just syntactic sugar.

对于这个特定的示例数据，它无需递归即可工作:

Just for this particular sample data this works without recursion:

Declare @Tbl as Table(SNO Int,Credit Money,Debit Money,PaidDate Date)
Insert into @Tbl
SELECT * FROM (VALUES (1,0,12,'7Jan16'), (2,10,0,'6Jan16'), (3,15,0,'5Jan16'), (4,0,5,'4Jan16'), (5,0,3,'3Jan16'), (6,0,2,'2Jan16'), (7,20,0,'1Jan16')) AS X(SNO,Credit,Debit,PaidDate);

With CTE1 As (
    Select *
      , CASE WHEN Credit > 0 THEN LEAD(1 - SIGN(Credit), 1, 1) OVER (ORDER BY SNO) ELSE 0 END As LastCrPerBlock
    From @Tbl
), CTE2 As (
    Select *
      , SUM(LastCrPerBlock) OVER (ORDER BY SNO DESC ROWS UNBOUNDED PRECEDING) As BlockNumber
    From CTE1
), CTE3 As (
    Select *
      , SUM(Credit - Debit) OVER (PARTITION BY BlockNumber) As BlockTotal
      , SUM(Credit - Debit) OVER (PARTITION BY BlockNumber ORDER BY SNO ROWS UNBOUNDED PRECEDING) As BlockRunningTotal
    From CTE2
)
Select SNO, Credit, Debit
  , CASE WHEN BlockRunningTotal < 0 THEN -BlockRunningTotal ELSE 0 END As TotalDebit
  , CASE WHEN BlockRunningTotal > 0 THEN CASE WHEN Credit < BlockRunningTotal THEN Credit ELSE BlockRunningTotal END ELSE 0 END As Amount
  , PaidDate
From CTE3
Order By SNO;

这可以帮助评估性能，但是如果在任何Debit s块中的总和超过Credit s的总和，它将失败.如果BlockTotal为负数，则必须将其与一个或几个后续块合并，而如果没有迭代或递归，则无法做到这一点.

This can help evaluate performance, but it will fail if in any block total of Debits exceed total of Credits. If BlockTotal is negative then it must be merged with one or several following blocks and that can't be done without iteration or recursion.

在现实生活中，我会将CTE3转储到临时表中并在合并表上循环，直到不再有负数BlockTotal.

In real life I would dump CTE3 into temporary table and cycle over it merging blocks until there are no more negative BlockTotals.

这篇关于使用LAG而不使用递归CTE来获取上一行的更新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！