英文:
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 <= 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
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
我不确定您是否想使用分区来计算累计总额(您的查询在 CustomerCode
和 CustomerType
上进行连接,但仅在 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)
更新:您想要每个客户的余额首次变为负数的最新日期(而上述查询提供了最早的日期)。我们可以通过调整 where
和 order 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 < 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 < 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 < 0 -- the balance is negative now
and sum_amount - amount >= 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 <-- 最近一次数值变为负数的时间
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 < 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 < 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 <-- 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 < 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 < 0
order by grp desc, rn
Result :
CustomerCode CustomerType PaymentDate PaymentAmount
123 retail 2023-01-07 -40
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论