英文:
SSMS rolling count based on value resetting at 0
问题
尝试获取一个滚动计数，当值为0时重置。
只需要一些字段。
- 按日期字段和然后ref字段排序
- 当值从0变为>0时，计数从1开始
- 如果值保持不变或增加，计数变为2及以上
- 如果值变为0，计数重置为0
- 如果值再次变为>0，计数又从1开始
日期：
日期 Ref 值 计数
202201 170532 £300.21 1
202202 170532 £600.42 2
202203 170532 £0.00 0
202204 170532 £300.21 1
202205 170532 £600.42 2
202206 170532 £900.63 3
..
尝试了一些我在这里读到的方法，但没有太多帮助。
感谢提供的任何帮助或指导！
英文:
Trying to get a rolling count that resets when 0 is hit.
Only need a few fields.
- Ordered by the date field and then ref field
- when value goes 0 to >0 count begins at 1
- If value remains same or increases count goes to 2 and beyond
- if value goes to 0 count goes back to 0
- repeats if value changes to >0 again at 1
Date:
Date Ref Value Count
202201 170532 £300.21 1
202202 170532 £600.42 2
202203 170532 £0.00 0
202204 170532 £300.21 1
202205 170532 £600.42 2
202206 170532 £900.63 3
..
..
Tried a few things i've read on here but not having much joy.
Thanks for any help or direction provided!
答案1
得分: 1
将你的行首先分组，通过计算数据集中之前出现了多少次0，然后你可以在该组上使用ROW_NUMBER。您还需要使用几个CASE表达式来正确放置0。：
WITH Grps AS (
SELECT Date,
Ref,
Value,
COUNT(CASE Value WHEN 0 THEN 1 END) OVER (ORDER BY Date ASC) AS Grp
FROM (VALUES (202201, 170532, 300.21),
(202202, 170532, 600.42),
(202203, 170532, 0.00),
(202204, 170532, 300.21),
(202205, 170532, 600.42),
(202206, 170532, 900.63)) V(Date, Ref, Value)
)
SELECT Date,
Ref,
Value,
CASE Value WHEN 0 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY CASE Value WHEN 0 THEN 0 END, Grp ORDER BY Date ASC) END AS RN
FROM Grps
ORDER BY Date ASC;
英文:
Put your rows into groups first, by COUNTing how many times 0 has been in the dataset prior, and then you can use a ROW_NUMBER on that group. You'll need to use couple of CASE expressions as well to put 0 in the right place.:
WITH Grps AS(
SELECT Date,
Ref,
Value,
COUNT(CASE Value WHEN 0 THEN 1 END) OVER (ORDER BY Date ASC) AS Grp
FROM (VALUES(202201,170532,300.21),
(202202,170532,600.42),
(202203,170532,0.00 ),
(202204,170532,300.21),
(202205,170532,600.42),
(202206,170532,900.63))V(Date,Ref,Value))
SELECT Date,
Ref,
Value,
CASE Value WHEN 0 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY CASE Value WHEN 0 THEN 0 END, Grp ORDER BY Date ASC) END AS RN
FROM Grps
ORDER BY Date ASC;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论