第三天本月最佳 - MySQL

发布于 2024-09-16 17:21:03 字数 396 浏览 4 评论 0原文

我正在开发事件的重复应用程序。我的日期范围是 2010 年 1 月 1 日到 2011 年 12 月 31 日。我想高效地返回每个月的所有第三个星期四（任意）。我可以在代码中非常简单地完成此操作，但需要注意的是它必须在存储过程中完成。最终我想要这样的东西：

CALL return_dates(event_id);

event_id 的 start_date 为 1/1/2010 ， end_date 为 12/31/2011 。结果集类似于：

1/20/2010
2/14/2010
3/17/2010
4/16/2010
5/18/2010
etc.

我只是好奇最有效的方法是什么，考虑到我在实际使用中可能会得到一个非常大的结果集。

原文

I'm working on a recurrence application for events. I have a date range of say, January 1 2010 to December 31 2011. I want to return all of the 3rd Thursdays (arbitrary) of the each month, efficiently. I could do this pretty trivially in code, the caveat is that it must be done in a stored procedure. Ultimately I'd want something like:

CALL return_dates(event_id);

That event_id has a start_date of 1/1/2010 and end_date of 12/31/2011. Result set would be something like:

1/20/2010
2/14/2010
3/17/2010
4/16/2010
5/18/2010
etc.

I'm just curious what the most efficient method of doing this would be, considering I might end up with a very large result set in my actual usage.

分享到QQ

分享到微博

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

发布评论

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

半边脸i 2024-09-23 17:21:03

想到的一个想法 - 您可以创建一个表并在其中存储您感兴趣的日期。

回复收藏 0 原文

酒与心事 2024-09-23 17:21:03

好吧，我还没有测试过它，但我认为最有效的方法是通过一个统计表，无论如何，它在数据库中都是一个有用的东西：

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[num_seq]') AND type in (N'U'))
DROP TABLE [dbo].[num_seq];

SELECT TOP 100000 IDENTITY(int,1,1) AS n
INTO num_seq
FROM MASTER..spt_values a, MASTER..spt_values b;

CREATE UNIQUE CLUSTERED INDEX idx_1 ON num_seq(n);

然后您可以使用它来建立两者之间的日期范围日期。速度很快是因为
它只是使用索引（实际上通常比循环更快，所以我相信）

create procedure getDates
    @eventId int
AS
begin

declare @startdate datetime
declare @enddate datetime

--- get the start and end date, plus the start of the month with the start date in
select @startdate=startdate, 
       @enddate=enddate
       from events where eventId=@eventId

  select
         @startdate+n AS date,
       from
         dbo.num_seq tally
       where
        tally.n<datediff(@monthstart, @enddate) and
        Datepart(dd,@startdate+n) between 15 and 21 and
        Datepart(dw, @startdate+n) = '<day>'

除了获取开始日期和结束日期之外，每个月的第三个 x id 必须在 15 日和 21 日之间（含）。
该范围内的日期名称必须是唯一的，以便我们可以立即找到它。

如果您想要第二个日期名称，只需适当修改范围或使用参数来计算它。

它使用开始日期构建日期表，然后添加天数（通过统计表中的数字列表）直到到达结束日期。

希望有帮助！

Ok, I haven't tested it, but I think the most efficient way of doing it is via a tally table which is a useful thing to have in the db anyway:

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[num_seq]') AND type in (N'U'))
DROP TABLE [dbo].[num_seq];

SELECT TOP 100000 IDENTITY(int,1,1) AS n
INTO num_seq
FROM MASTER..spt_values a, MASTER..spt_values b;

CREATE UNIQUE CLUSTERED INDEX idx_1 ON num_seq(n);

You can then use this to build up the date range between the two dates. It's fast because
it just uses the index (in fact often faster than a loop, so I'm led to believe)

create procedure getDates
    @eventId int
AS
begin

declare @startdate datetime
declare @enddate datetime

--- get the start and end date, plus the start of the month with the start date in
select @startdate=startdate, 
       @enddate=enddate
       from events where eventId=@eventId

  select
         @startdate+n AS date,
       from
         dbo.num_seq tally
       where
        tally.n<datediff(@monthstart, @enddate) and
        Datepart(dd,@startdate+n) between 15 and 21 and
        Datepart(dw, @startdate+n) = '<day>'

Aside from getting the start and end dates, the third x id each month must be between the 15th and the 21st inclusive.
The day names in that range must be unique, so we can locate it straight away.

If you wanted the second dayname, just modify the range appropriately or use a parameter to calculate it.

It constucts a date table using the startdate, and then adding days on (via the list of numbers in the tally table) until it reaches the end date.

Hope it helps!

回复收藏 0 原文

~没有更多了~