ROW_NUMBER()基于日期

html5 • • 问答

我有以下数据：

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

我想row_number每次都增加一个值，right(test_date,5) = '07-01'以便我的最终结果如下所示：

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]

但这对我不起作用。

有什么建议？

回答

使用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;

返回：

测试日期 行数
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

回答

DENSE_RANK()如果从日期中减去 6 个月，则可以使用窗口函数来完成：

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

请参阅演示。
结果：

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

以上是ROW_NUMBER()基于日期的全部内容。
THE END
分享
二维码
< <上一篇
下一篇>>