ROW_NUMBER()基于日期

Question

我有以下数据:

test_date
2018-07-01
2018-07-02
...
2019-06-30
2019-07-01
2019-07-02
...
2020-06-30
2020-07-01

我想每次增加right(test_date,5) = '07-01'的row_number值，这样我的最终结果看起来像这样:

test_date    row_num
2018-07-01         1
2018-07-02         1
...                1
2019-06-30         1
2019-07-01         2
2019-07-02         2
...                2
2020-06-30         2
2020-07-01         3

我试着这样做:

, ROW_NUMBER() OVER (
PARTITION BY CASE WHEN RIGHT(a.[test_date],5) = '07-01' THEN 1 ELSE 0 END
ORDER BY a.[test_date]
) AS [test2]

但我没有成功。

有什么建议吗?

Answer 1

使用datepart来识别正确的日期，然后在每次更改时将1加到总和中(假设每个日期永远不会超过1行)。

declare @Test table (test_date date);
insert into @Test (test_date)
values
('2018-07-01'),
('2018-07-02'),
('2019-06-30'),
('2019-07-01'),
('2019-07-02'),
('2020-06-30'),
('2020-07-01');
select *
, sum(case when datepart(month,test_date) = 7 and datepart(day,test_date) = 1 then 1 else 0 end) over (order by test_date asc) row_num
from @Test
order by test_date asc;

的回报:

<表类>test_daterow_numtbody><<tr>2018-07-0112018-07-0212019-06-3012019-07-0122019-07-0222020-06-3022020-07-013

您可以使用DENSE_RANK()窗口函数，如果您从日期减去6个月:

SELECT test_date,
DENSE_RANK() OVER (ORDER BY YEAR(DATEADD(month, -6, test_date))) row_num
FROM tablename

Results:

test_date  | row_num
---------- | -------
2018-07-01 |       1
2018-07-02 |       1
2019-06-30 |       1
2019-07-01 |       2
2019-07-02 |       2
2020-06-30 |       2
2020-07-01 |       3

构建基于月=7和日=2的运行总数

declare @Test table (mykey int,test_date date);
insert into @Test (mykey,test_date)
values
(1,'2018-07-01'),
(2,'2018-07-02'),
(3,'2019-06-30'),
(4,'2019-07-01'),
(5,'2019-07-02'),
(6,'2020-06-30'),
(7,'2020-07-01');
select mykey,test_date, 
sum(case when DatePart(Month,test_date)=7 and DatePart(Day,test_date)=2 then 1 else 0 end) over (order by mykey) RunningTotal from @Test
order by mykey