英文:
Can SQL dense rank work for count of (non distinct) value changes with respect to time
问题
使用SQL，我有一个按年份列出的课程学分清单。我想要统计“credits”属性在多年间出现了多少次。如果一个值不连续地出现了两次，我希望将其计算在内。
如下所示，您可以看到在时间范围内有3个不同的CREDITS值（5、7.5、6.5），但如果学分在多年间的值是（5、7.5、6.5，然后回到5），我希望结果为4。
密集排名（Dense rank）不起作用，不会给我想要的结果，因为它只会在时间范围内提供不同的值，而不是在给定时间范围内考虑时间因素的4个（非唯一）值。在SQL中是否可以实现这个目标？
英文:
Using SQL I have a list of credits for a course by year. I want to get a count of how many values the 'credits' attribute has been over years. If a value occurs twice (non consecutively) I want it counted.
As follows, you can see there are 3 distinct values for CREDITS (5, 7.5, 6.5) over the timeframe but if credits have been values (5, 7.5, 6.5 and then back to 5) over the years, I want 4 as the result.
Dense rank is not working and does not give me what I want as just gives me distinct values in the timeframe, rather than 4 (non distinct) values across the timeframe given, with respect to time. Is this doable in SQL?
答案1
得分: 1
你可以使用带有CASE表达式的运行总和，该表达式检查credits值是否已更改：
select year_, credits,
sum(case when credits<>pre_credits then 1 else 0 end)
over (order by year_ desc) + 1 res_rnk
from
(
select *,
lag(credits, 1, credits) over (order by year_ desc) pre_credits
from tbl
) t
order by year_ desc
英文:
You can use a running sum with a case expression that checks if the credits value has been changed:
select year_, credits,
sum(case when credits<>pre_credits then 1 else 0 end)
over (order by year_ desc) + 1 res_rnk
from
(
select *,
lag(credits, 1, credits) over (order by year_ desc) pre_credits
from tbl
) t
order by year_ desc
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论