3-й ‹day_of_week› месяца - MySQL

Я работаю над повторяющимся приложением для событий. У меня есть диапазон дат, скажем, с 1 января 2010 года по 31 декабря 2011 года. Я хочу эффективно возвращать все 3-й четверг (произвольный) каждого месяца. Я мог бы сделать это довольно просто в коде, но с оговоркой, что это должно выполняться в хранимой процедуре. В конечном итоге я хотел бы что-то вроде:

CALL return_dates(event_id);

Этот event_id имеет start_date 01.01.2010 и end_date 31.12.2011. Набор результатов будет выглядеть примерно так:

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

Мне просто любопытно, каким будет наиболее эффективный способ сделать это, учитывая, что я могу получить очень большой набор результатов при моем фактическом использовании.


mysql sql stored-procedures recurrence datetime-generation
person JamesB41    schedule 30.08.2010    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Одна идея, которая приходит на ум - вы можете создать таблицу и хранить там интересующие вас даты.

person Leonid    schedule 30.08.2010
comment
Я бы предпочел сделать это динамически. Я полагаю, что это крайняя возможность, но количество записей может быть абсурдно большим. - person JamesB41; 30.08.2010
comment
Количество записей зависит от диапазона, который вы хотите поддерживать. Это не должно быть вопросом пространства, если вам нужно поддерживать всего несколько тысяч лет. Эффективность здесь не должна иметь значения. Если запросы представляют собой диапазоны, а диапазон составляет приблизительно год, то хранение дат в таблице кажется приемлемым и эффективным решением. - person Leonid; 30.08.2010
comment
Не вижу ничего плохого, если вы храните в таблице строку в месяц в течение 2000 лет. Это 24000 строк за 2000 лет, и у вас будут эффективные запросы диапазона. - person Leonid; 30.08.2010
comment
Верно, но это для одного события. Теперь предположим, что у меня есть 3 миллиона событий. - person JamesB41; 30.08.2010
comment
Кажется, я понимаю, что ты сейчас говоришь. Все еще кажется немного hokey. Это выполнило бы то, что я хочу сделать, но я все же хотел бы найти элегантное решение. - person JamesB41; 30.08.2010
comment
Что ж, в одном случае у вас есть результаты, и вам просто нужно получить их с жесткого диска или из кэша, что более вероятно, если к вашей таблице регулярно обращаются и кэширование настроено правильно. 3 миллиона событий мне мало что говорят, так как это относительно времени, в которое они запрашиваются. В какой интервал времени вы ожидаете, что произойдет 3 миллиона событий? - person Leonid; 30.08.2010
comment
По сути, вам нужно ответить на вопрос: что эффективнее - хранить даты в базе данных и обращаться к ним или каждый раз вычислять их с помощью цикла for. Это также может сильно зависеть от того, сколько вычислений вам нужно сделать для каждого запроса. Я определенно выбираю таблицу БД, потому что так меньше шансов ошибиться в расчетах — вы можете увидеть фактические данные и легко найти ошибку, вы можете оперировать данными с помощью запросов. - person Leonid; 30.08.2010
comment
С циклом for: вы можете получить первый день месяца и выяснить, какой сегодня день недели, а затем выполнить простой расчет. Я считаю, что хранимые процедуры MySQL + SQL должны предоставить все эти инструменты. В качестве альтернативы вы также можете углубиться в детали и определить, какой год является високосным, а какой нет, тогда вам нужно будет определить день недели только для первого месяца в заданном диапазоне. DAYOFWEEK() — это одна из функций, которую вы можете использовать. - person Leonid; 30.08.2010
comment
Достаточно справедливо, похоже, мне придется так или иначе переборщить. - person JamesB41; 30.08.2010

arrow_upward
0
arrow_downward

Хорошо, я не проверял это, но я думаю, что наиболее эффективный способ сделать это - использовать таблицу подсчета, которую в любом случае полезно иметь в БД:

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 числом включительно. Названия дней в этом диапазоне должны быть уникальными, чтобы мы могли сразу их найти.

Если вам нужно второе имя дня, просто измените диапазон соответствующим образом или используйте параметр для его расчета.

Он создает таблицу дат, используя дату начала, а затем добавляя дни (через список чисел в таблице подсчета), пока не будет достигнута дата окончания.

Надеюсь, поможет!

person mr_miles    schedule 01.09.2010