最快的方法是找到累积总和变为负数的最后日期。

huangapple go评论85阅读模式
英文:

Fastest way to find the last date in which the running total became negative

问题

I have translated the code for you:

我已经翻译了这段代码:

到目前为止,我可以计算累积总和最近为负的日期,但是我需要知道何时开始为负。是否可以在不拆分成多个临时表/CTE查询以维护性能的情况下实现这一点

declare @CustomerCode int=2134

select CustomerCode, CustomerType, max(PaymentDate) as MaxPaymentDate
from (
    select b.CustomerCode, b.CustomerType, b.PaymentDate from Payments as a
    join Payments as b
    on a.CustomerCode = b.CustomerCode and a.CustomerType = b.CustomerType
    where b.PaymentDate <= a.PaymentDate
        AND a.CustomerCode = @CustomerCode 
    group by b.CustomerCode, b.CustomerType, b.PaymentDate
    having sum(b.paymentamount) <= 0
) as T
group by CustomerCode, CustomerType

I hope this helps! If you have any more specific questions or need further assistance, please feel free to ask.

英文:

What I have so far can calculate the most recent date that the running total was negative, however I need the date of when it became negative. Is this possible without splitting it into multiple temp table / CTE queries to maintain performance?

declare @CustomerCode int=2134

select CustomerCode, CustomerType, max(PaymentDate) as MaxPaymentDate
from (
    select b.CustomerCode, b.CustomerType, b.PaymentDate from Payments as a
    join Payments as b
    on a.CustomerCode = b.CustomerCode and a.CustomerType = b.CustomerType
    where b.PaymentDate &lt;= a.PaymentDate
        AND a.CustomerCode = @CustomerCode 
    group by b.CustomerCode, b.CustomerType, b.PaymentDate
    having sum(b.paymentamount) &lt;= 0
) as T
group by CustomerCode, CustomerType

For example, in the table below I am looking for the row on date 2021-01-07 because the running total of -10 just became negative and it is the most recent time it became negative.

CustomerCode CustomerType PaymentDate PaymentAmount
123 retail 2023-01-01 0
123 retail 2023-01-02 10
123 retail 2023-01-03 -30
123 retail 2023-01-04 10
123 retail 2023-01-05 20
123 retail 2023-01-06 10
123 retail 2023-01-07 -40
123 retail 2023-01-08 -10
123 retail 2023-01-09 10

答案1

得分: 3

以下是您要求的内容的中文翻译:

您的查询最初意图是计算每个客户的付款累计总额。我建议重新编写您的代码,使用窗口函数而不是自连接。这样更整洁,而且效率更高:

select p.*,
    sum(amount) over(order by PaymentDate) sum_amount
from payment p
where CustomerCode = @CustomerCode

对于给定的客户,我们只需过滤负值的结果集,按日期排序并保留顶部行:

select top (1) *
from (
    select p.*,
        sum(amount) over(order by PaymentDate) sum_amount
    from payment p
    where CustomerCode = @CustomerCode
) p
where sum_amount < 0
order by PaymentDate

我不确定您是否想使用分区来计算累计总额(您的查询在 CustomerCodeCustomerType 上进行连接,但仅在 CustomerCode 上进行过滤)。如果您想为多个客户获取相同的结果,我们可以使用以下查询:

select top (1) with ties *
from (
    select p.*,
        sum(amount) over(partition by CustomerCode order by PaymentDate) sum_amount
    from payment p
) p
where sum_amount < 0
order by row_number() over(partition by CustomerCode order by PaymentDate)

更新:您想要每个客户的余额首次变为负数的最新日期(而上述查询提供了最早的日期)。我们可以通过调整 whereorder by 子句来实现:

select top (1) with ties *
from (
    select p.*,
        sum(amount) over(partition by CustomerCode order by PaymentDate) sum_amount
    from payment p
) p
where sum_amount < 0            -- 余额现在为负
  and sum_amount - amount >= 0  -- 以前余额为正
order by row_number() 
    over(partition by CustomerCode order by PaymentDate desc) 
                                -- ^ 按降序排序,以首先列出最新日期

请注意,这是对您提供的代码的中文翻译。

英文:

The initial intent of your query is to compute the running sum of payments for each customer. I would recommend rephrasing your code to use window functions rather than a self-join. This is neater, and much more efficient:

select p.*,
    sum(amount) over(order by PaymentDate) sum_amount
from payment p
where CustomerCode = @CustomerCode

For a given customer, we can just filter the resultset for negative values, order it by date and retain the top row only:

select top (1) *
from (
    select p.*,
        sum(amount) over(order by PaymentDate) sum_amount
    from payment p
    where CustomerCode = @CustomerCode
) p
where sum_amount &lt; 0
order by PaymentDate

I am not totally sure if you want to use a partition to compute the running sum (your query joins on CustomerCode and CustomerType, but filters on CustomerCode only). If you wanted the same result for multiple customers, we would use:

select top (1) with ties *
from (
    select p.*,
        sum(amount) over(partition by CustomerCode order by PaymentDate) sum_amount
    from payment p
) p
where sum_amount &lt; 0
order by row_number() over(partition by CustomerCode order by PaymentDate) 

Update: you want the latest date when each customer's balance became negative (whereas above queries give you the earliest date). We can just tweak the where and order by clauses for this:

select top (1) with ties *
from (
    select p.*,
        sum(amount) over(partition by CustomerCode order by PaymentDate) sum_amount
    from payment p
) p
where sum_amount &lt; 0            -- the balance is negative now
  and sum_amount - amount &gt;= 0  -- and it was positive before
order by row_number() 
    over(partition by CustomerCode order by PaymentDate desc) 
                                -- ^ descending sort to put latest dates first

答案2

得分: 1

如果我理解正确,您正在寻找最近一次数值变为负数的时间:

因此,使用此查询,我们可以明确地说,最近一次数值变为负数的时间是 2023-01-07

select *
from (
  select p.*,
        sum(PaymentAmount) over(order by PaymentDate) as running_sum
  from Payments p
  where CustomerCode = 123
) as s

结果:

CustomerCode	PaymentDate	PaymentAmount	running_sum
123	        retail	    2023-01-01	0	        0
123	        retail	    2023-01-02	10	        10
123	        retail	    2023-01-03	-30	        -20
123	        retail	    2023-01-04	10	        -10
123	        retail	    2023-01-05	20	        10
123	        retail	    2023-01-06	10	        20
123	        retail	    2023-01-07	-40	        -20     &lt;-- 最近一次数值变为负数的时间
123	        retail	    2023-01-08	-10	        -30
123	        retail	    2023-01-09	10	        80
123	        retail	    2023-01-09	100	        80

要获取预期的日期,我们必须分组连续的行,然后从最近的组中选择第一个日期:

select top (1) CustomerCode, CustomerType, PaymentDate, PaymentAmount
from (
      select *, sum(case when falling = 1 then 0 else 1 end) over (order by PaymentDate) as grp
      from (
             select *, case when running_sum &lt; 0 then 1 else null end as falling
             from (
                 select p.*, sum(PaymentAmount) over(order by PaymentDate) as running_sum,
                 row_number() over(order by PaymentDate) as rn
                 from Payments p
                 where CustomerCode = 123
             ) as c1
      ) as c2
) as c3
where running_sum &lt; 0
order by grp desc, rn

结果:

CustomerCode	CustomerType	PaymentDate	PaymentAmount
123	            retail	        2023-01-07	-40

演示在这里

英文:

If I understand correctly, you are looking for the most recent time it changed to a negative value :

So using this query we can clearly say that the most recent time it changed to a negative is 2023-01-07 :

select *
from (
  select p.*,
        sum(PaymentAmount) over(order by PaymentDate) as running_sum
  from Payments p
  where CustomerCode = 123
) as s

Result :

CustomerCode	PaymentDate	    PaymentAmount	running_sum
123	retail	    2023-01-01	    0	            0
123	retail	    2023-01-02	    10	            10
123	retail	    2023-01-03	    -30	            -20
123	retail	    2023-01-04	    10	            -10
123	retail	    2023-01-05	    20	            10
123	retail	    2023-01-06	    10	            20
123	retail	    2023-01-07	    -40	            -20     &lt;-- most recent time it changed to a negative value
123	retail	    2023-01-08	    -10	            -30
123	retail	    2023-01-09	    10	            80
123	retail	    2023-01-09	    100	            80

o obtain the expected date, we must group successive rows and then select the first date from the most recent group :

select top (1) CustomerCode, CustomerType, PaymentDate, PaymentAmount
from (
	  select *, sum(case when falling = 1 then 0 else 1 end) over (order by PaymentDate) as grp
	  from (
		     select *, case when running_sum &lt; 0 then 1 else null end as falling
		     from (
		         select p.*, sum(PaymentAmount) over(order by PaymentDate) as running_sum,
		         row_number() over(order by PaymentDate) as rn
		         from Payments p
		         where CustomerCode = 123
		     ) as c1
	  ) as c2
) as c3
where running_sum &lt; 0
order by grp desc, rn

Result :

CustomerCode	CustomerType	PaymentDate	PaymentAmount
123	            retail	        2023-01-07	-40

Demo here

huangapple
  • 本文由 发表于 2023年5月18日 06:03:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/76276475.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定