这是我的桌子。
Price Volume Month
--------------------------
600 60 April
500 50 April
450 0 April
400 0 April
300 40 April
95 0 April
200 40 March
100 40 March
90 0 March
80 0 March
70 10 March
60 0 March
对于每个月，我需要在“交易量”列中选择前两个连续的零之前的所有行，该行按价格降序排列。例如，这是我想要得到的：
Price Volume Month rn
--------------------------------
600 60 April 0
500 50 April 0
200 40 March 0
100 40 March 0
我知道如何完成而不按月分组（感谢stackoverflow.com链接）。
这是代码：
with
flagged as (
select price,
volume,
case
when volume + lead(volume) over (order by price desc) = 0 then 1
else 0
end as rn, month
from [AnalysisDatabase].[dbo].[tblLagExample]),
counted as (
select price,
volume,
sum(rn) over (order by price desc) as cn ,-- this is running sum
rn, month
from flagged)
select price, volume, cn, month, rn
from counted
where cn = 0 and rn = 0
order by price desc
有关如何按月分组的任何建议。
你快到了。您所需要的只是添加PARTITION BY month到您的OVER子句中。
这是SQL Fiddl e。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句