A组按范围

发布于 2025-01-29 10:10:14 字数 928 浏览 3 评论 0 原文

我正在用T-SQL查询来分区/A组的日期表中的T-SQL查询。 新的时期定义为从一个月的16日到下个月的15 。

因此，如果表格看起来像这样：

2022-05-13
2022-05-14
2022-05-15
2022-05-16
2022-05-17
2022-05-18
2022-05-19
2022-05-20
2022-05-21
2022-05-22
2022-05-23
2022-05-24
2022-05-25
2022-05-26
..
..

预期的结果是：

2022-05-13   1
2022-05-14   1
2022-05-15   1
2022-05-16   2
2022-05-17   2
2022-05-18   2
2022-05-19   2
2022-05-20   2
2022-05-21   2
2022-05-22   2
2022-05-23   2
2022-05-24   2
2022-05-25   2
2022-05-26   2
..
..
2022-06-15   2
2022-06-16   3

我怀疑我需要在这里有一个窗口功能，但是我对这些功能非常不经验。从编程上讲，我会在每个日期循环，然后lookahead到周期日期（下个月的16日），但我需要在SQL中进行此循环。

我尝试过的是：

WITH T AS
(
SELECT Date, ROW_NUMBER() OVER (ORDER BY Date) AS Period
FROM dbo.Dates
WHERE DayOfMonth = 16

)
SELECT D.Date, Period  FROM dbo.Dates D
LEFT JOIN T ON D.Date = T.Date

但是它不完整，并且在联接后不会填写空值。

原文

I'm struggling with a T-SQL query to partitions/group a dates table into periods. A new period is defined as from 16th of a month to 15th the next month.

So if the table looks like this:

2022-05-13
2022-05-14
2022-05-15
2022-05-16
2022-05-17
2022-05-18
2022-05-19
2022-05-20
2022-05-21
2022-05-22
2022-05-23
2022-05-24
2022-05-25
2022-05-26
..
..

The expected result would be:

2022-05-13   1
2022-05-14   1
2022-05-15   1
2022-05-16   2
2022-05-17   2
2022-05-18   2
2022-05-19   2
2022-05-20   2
2022-05-21   2
2022-05-22   2
2022-05-23   2
2022-05-24   2
2022-05-25   2
2022-05-26   2
..
..
2022-06-15   2
2022-06-16   3

I suspect I need a window function here of sorts, but I am very inexperienced with those. Programatically I would loop each date, and lookahead to the period date (16th of next month), but I need this in SQL.

What I have tried is this:

WITH T AS
(
SELECT Date, ROW_NUMBER() OVER (ORDER BY Date) AS Period
FROM dbo.Dates
WHERE DayOfMonth = 16

)
SELECT D.Date, Period  FROM dbo.Dates D
LEFT JOIN T ON D.Date = T.Date

However it is incomplete and will not fill out the null values after the join.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

入画浅相思 2025-02-05 10:10:14

使用窗口函数非常简单。

您需要减去15天，然后完成该月末。

然后，您使用 dense_rank 获得排名

SELECT *,
  DENSE_RANK() OVER (ORDER BY EOMONTH(DATEADD(day, -15, d.date)))
FROM Dates d;

日期	等级
2022-05-13	1
2022-05-14	1
2022-05-15	1
2022-05-16	2
2022-05-17	2
2022-05-18	2
2022-- 05-19	2
2022-05-20	2
2022-05-21	2
2022-05-22	2
2022-05-23	2
2022-05-24	2
2022-05-25	2
2022-05-26	2

It's pretty simple using window functions.

You need to subtract 15 days, then get the end of that month.

Then you use DENSE_RANK to get the ranking

SELECT *,
  DENSE_RANK() OVER (ORDER BY EOMONTH(DATEADD(day, -15, d.date)))
FROM Dates d;

db<>fiddle

Date	Rank
2022-05-13	1
2022-05-14	1
2022-05-15	1
2022-05-16	2
2022-05-17	2
2022-05-18	2
2022-05-19	2
2022-05-20	2
2022-05-21	2
2022-05-22	2
2022-05-23	2
2022-05-24	2
2022-05-25	2
2022-05-26	2