英文:
SQL - Can I create a continuous Count which resets when a value hits 0
问题
得到这个数据集，我需要 SQL 代码来返回 "MonthCount"。
它根据 DateYYYYMM 和帐号编号返回一个不为 0 的 Payment 计数。
当 Payment 变为 0 时，计数应为 0。当 payment 重新开始时，计数从 1 开始。
英文:
got this data set and i need sql code to return "MonthCount".
It returns a count against the DateYYYYMM and Account Number where Payment is not 0.
When Payment goes to 0 the count should be 0. When payment starts again the count starts again from 1.
Big thanks in advance
I've tried various LAG/Case statements but I can only get a return of a count when Payment isn't 0.
答案1
得分: 1
你可以使用条件聚合来生成行的分组。然后，你可以使用ROW_NUMBER()来生成你想要的计数。例如：
select row_number() over(partition by g order by dateyyyymm) - 1 as monthcount
from (
select sum(case when payment = 0 then 1 else 0 end)
over(order by dateyyyymm) as g
from t
) x
英文:
You can use conditional aggregation to produce groups of rows. Then you can use ROW_NUMBER() to generate the count you want. For example:
select row_number() over(partition by g order by dateyyyymm) - 1 as monthcount
from (
select sum(case when payment = 0 then 1 else 0 end)
over(order by dateyyyymm) as g
from t
) x
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论