SQL server运行计数

Question

谁能帮我数一下SQL Server的行数

Id Date   Trend 
A 15-1-20 Uptrend
A 14-1-20 Uptrend
A 13-1-20 Uptrend
A 12-1-20 NULL
A 11-1-20 Uptrend
A 10-1-20 Uptrend
A 09-1-20 NULL

预期的结果

Id Date   Trend    Counttrend
A 15-1-20 Uptrend      3
A 14-1-20 Uptrend      2
A 13-1-20 Uptrend      1
A 12-1-20 NULL        NULL
A 11-1-20 Uptrend      2
A 10-1-20 Uptrend      1
A 09-1-20 NULL        NULL

CREATE TABLE #TREND (ID Varchar(2),[DATE] Date ,TREND Varchar(10))
INSERT INTO #trend
( ID, [DATE], TREND )
VALUES
('A', '01-15-2020', 'Uptrend'), 
('A', '01-14-2020', 'Uptrend'), 
('A', '01-13-20', 'Uptrend'),
('A', '01-12-20', NULL),
('A', '01-11-20', NULL),
('A', '01-10-20', 'Uptrend'),
('A', '01-09-20', 'Uptrend');

Answer 1

试试这个:

SELECT src.Id, src.[Date], src.Trend, 
CASE
WHEN Trend IS NULL THEN NULL
ELSE ROW_NUMBER() OVER (PARTITION BY Id, Trend, MasterSeq-SubSeq ORDER BY [Date])
END AS TrendCnt
FROM (
SELECT *,  
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY [Date]) As MasterSeq,
ROW_NUMBER() OVER(PARTITION BY Id, Trend ORDER BY [Date]) +1 As SubSeq
FROM aaa
) src
ORDER BY [Date] DESC;

Answer 2

您没有明确指定您想要的逻辑。您似乎想要从最近的NULL日期算起的天数。您可以使用窗口函数轻松计算:

select t.*,
(case when trend is not null
then datediff(day,
max(case when trend is null then date end) over (order by date),
date)
end)
from trend t
order by date desc;

下面是一个数据库<>提琴，它与您的问题中的结果相匹配。

Answer 3

用一种稍微不同的方式实现相同的结果:

with cte as (
select *
-- Find the null transitions so we can row number them
, sum(case when Trend is null then 1 else 0 end) over (order by [Date] asc) RowBreak
from @Test
)
select *
-- filter out the case when Trend is null
, case when Trend is not null then row_number() over (partition by RowBreak, Trend order by [Date] asc) else null end
from cte
order by [Date] desc;