Ответ 1
Если вам не нужны объяснения и подробности, используйте "Черную магическую версию" ниже.
Все запросы, представленные в других ответах, до сих пор работают с условиями, которые нельзя проанализировать - они не могут использовать индекс и должны вычислять выражение для каждой отдельной строки в базовой таблице, чтобы найти совпадающие строки. Не имеет большого значения с маленькими столиками. Вещи (много) с большими столами.
Учитывая следующую простую таблицу:
CREATE TABLE event (
event_id serial PRIMARY KEY
, event_date date
);
запрос
Версии 1. и 2. ниже могут использовать простой индекс вида:
CREATE INDEX event_event_date_idx ON event(event_date);
Но все следующие решения еще быстрее без индекса.
1. Простая версия
SELECT *
FROM (
SELECT ((current_date + d) - interval '1 year' * y)::date AS event_date
FROM generate_series( 0, 14) d
CROSS JOIN generate_series(13, 113) y
) x
JOIN event USING (event_date);
Подзапрос x вычисляет все возможные даты за заданный диапазон лет из CROSS JOIN двух вызовов generate_series(). Выбор осуществляется с помощью окончательного простого соединения.
2. Продвинутая версия
WITH val AS (
SELECT extract(year FROM age(current_date + 14, min(event_date)))::int AS max_year
, extract(year FROM age(current_date, max(event_date)))::int AS min_year
FROM event
)
SELECT e.*
FROM (
SELECT ((current_date + d.d) - interval '1 year' * y.y)::date AS event_date
FROM generate_series(0, 14) d
,(SELECT generate_series(min_year, max_year) AS y FROM val) y
) x
JOIN event e USING (event_date);
Диапазон лет определяется из таблицы автоматически, что сводит к минимуму количество сгенерированных лет.
Вы можете пойти еще дальше и составить список существующих лет, если есть пробелы.
Эффективность зависит от распределения дат. Несколько лет с большим количеством строк делают это решение более полезным. Многие годы с несколькими строками делают его менее полезным.
Простая SQL Fiddle для игры.
3. Черная магия версия
Обновлен 2016, чтобы удалить "сгенерированный столбец", который блокировал бы ГОРЯЧИЕ обновления;более простая и быстрая функция.
Обновлен 2018 для расчета MMDD с выражениями IMMUTABLE чтобы разрешить встраивание функций.
Создайте простую функцию SQL для вычисления integer из шаблона 'MMDD':
CREATE FUNCTION f_mmdd(date) RETURNS int LANGUAGE sql IMMUTABLE AS
'SELECT (EXTRACT(month FROM $1) * 100 + EXTRACT(day FROM $1))::int';
to_char(time, 'MMDD') я использовал to_char(time, 'MMDD'), но переключился на приведенное выше выражение, которое оказалось самым быстрым в новых тестах на Postgres 9.6 и 10:
дБ <> скрипка здесь
Это позволяет встраивать функции, потому что EXTRACT EXTRACT (xyz FROM date) реализован с IMMUTABLE функции IMMUTABLE date_part(text, date) внутри. И он должен быть IMMUTABLE чтобы разрешить его использование в следующем IMMUTABLE индексе выражения из нескольких IMMUTABLE:
CREATE INDEX event_mmdd_event_date_idx ON event(f_mmdd(event_date), event_date);
Многоколонна по ряду причин:
Может помочь с ORDER BY или с выбором из данных лет. Читайте здесь. Почти без дополнительных затрат на индекс. date вписывается в 4 байта, которые в противном случае были бы потеряны из-за выравнивания данных. Читайте здесь.
Кроме того, поскольку оба столбца индекса ссылаются на один и тот же столбец таблицы, недостатка в отношении обновлений HOT нет. Читайте здесь.
Одна табличная функция PL/pgSQL, чтобы управлять ими всеми
В ответ на один из двух запросов на конец года:
CREATE OR REPLACE FUNCTION f_anniversary(date = current_date, int = 14)
RETURNS SETOF event AS
$func$
DECLARE
d int := f_mmdd($1);
d1 int := f_mmdd($1 + $2 - 1); -- fix off-by-1 from upper bound
BEGIN
IF d1 > d THEN
RETURN QUERY
SELECT *
FROM event e
WHERE f_mmdd(e.event_date) BETWEEN d AND d1
ORDER BY f_mmdd(e.event_date), e.event_date;
ELSE -- wrap around end of year
RETURN QUERY
SELECT *
FROM event e
WHERE f_mmdd(e.event_date) >= d OR
f_mmdd(e.event_date) <= d1
ORDER BY (f_mmdd(e.event_date) >= d) DESC, f_mmdd(e.event_date), event_date;
-- chronological across turn of the year
END IF;
END
$func$ LANGUAGE plpgsql;
Звоните по умолчанию: 14 дней, начиная с "сегодня":
SELECT * FROM f_anniversary();
Позвоните на 7 дней, начиная с 2014-08-23:
SELECT * FROM f_anniversary(date '2014-08-23', 7);
SQL Fiddle, сравнивающий EXPLAIN ANALYZE.
29 февраля
При работе с юбилеями или "днями рождения" необходимо определить, как обращаться с особым случаем "29 февраля" в високосные годы.
При тестировании диапазонов дат Feb 29 обычно включается автоматически, даже если текущий год не является високосным. Диапазон дней увеличивается на 1 задним числом, когда он охватывает этот день.
С другой стороны, если текущий год является високосным, и вы хотите искать 15 дней, вы можете получить результаты за 14 дней в високосные годы, если ваши данные взяты из не високосных лет.
Скажем, Боб родился 29 февраля:
Мои запросы 1. и 2. включают 29 февраля только в високосные годы. У Боба день рождения только каждые ~ 4 года.
Мой запрос 3. 29 февраля включает в себя диапазон. У Боба день рождения каждый год.
Там нет волшебного решения. Вы должны определить, что вы хотите для каждого случая.
Тестовое задание
Чтобы подтвердить свою точку зрения, я провел обширный тест со всеми представленными решениями. Я адаптировал каждый из запросов к данной таблице и вывел идентичные результаты без ORDER BY.
Хорошая новость: все они верны и дают одинаковый результат - за исключением запроса Гордона с синтаксическими ошибками и запроса @wildplasser, который завершается неудачно, когда год заканчивается (легко исправить).
Вставьте 108000 строк со случайными датами из 20-го века, который похож на таблицу живых людей (13 лет и старше).
INSERT INTO event (event_date)
SELECT '2000-1-1'::date - (random() * 36525)::int
FROM generate_series (1, 108000);
Удалите ~ 8%, чтобы создать несколько мертвых кортежей и сделать таблицу более "реальной".
DELETE FROM event WHERE random() < 0.08;
ANALYZE event;
В моем тестовом примере было 99289 строк, 4012 просмотров.
C - Catcall
WITH anniversaries as (
SELECT event_id, event_date
,(event_date + (n || ' years')::interval)::date anniversary
FROM event, generate_series(13, 113) n
)
SELECT event_id, event_date -- count(*) --
FROM anniversaries
WHERE anniversary BETWEEN current_date AND current_date + interval '14' day;
C1 - идея Catcall переписана
Помимо незначительных оптимизаций, основным отличием является добавление только точного количества лет date_trunc('year', age(current_date + 14, event_date)) чтобы получить годовщину этого года, что полностью исключает необходимость использования CTE:
SELECT event_id, event_date
FROM event
WHERE (event_date + date_trunc('year', age(current_date + 14, event_date)))::date
BETWEEN current_date AND current_date + 14;
Д - Даниэль
SELECT * -- count(*) --
FROM event
WHERE extract(month FROM age(current_date + 14, event_date)) = 0
AND extract(day FROM age(current_date + 14, event_date)) <= 14;
E1 - Эрвин 1
См. "1. Простая версия" выше.
Е2 - Эрвин 2
См. "2. Расширенная версия" выше.
Е3 - Эрвин 3
См. "3. Черная магия версия" выше.
G - Гордон
SELECT * -- count(*)
FROM (SELECT *, to_char(event_date, 'MM-DD') AS mmdd FROM event) e
WHERE to_date(to_char(now(), 'YYYY') || '-'
|| (CASE WHEN mmdd = '02-29' THEN '02-28' ELSE mmdd END)
,'YYYY-MM-DD') BETWEEN date(now()) and date(now()) + 14;
H - a_horse_with_no_name
WITH upcoming as (
SELECT event_id, event_date
,CASE
WHEN date_trunc('year', age(event_date)) = age(event_date)
THEN current_date
ELSE cast(event_date + ((extract(year FROM age(event_date)) + 1)
* interval '1' year) AS date)
END AS next_event
FROM event
)
SELECT event_id, event_date
FROM upcoming
WHERE next_event - current_date <= 14;
W - wildplasser
CREATE OR REPLACE FUNCTION this_years_birthday(_dut date) RETURNS date AS
$func$
DECLARE
ret date;
BEGIN
ret :=
date_trunc( 'year' , current_timestamp)
+ (date_trunc( 'day' , _dut)
- date_trunc( 'year' , _dut));
RETURN ret;
END
$func$ LANGUAGE plpgsql;
Упрощенно вернуть так же, как и все остальные:
SELECT *
FROM event e
WHERE this_years_birthday( e.event_date::date )
BETWEEN current_date
AND current_date + '2weeks'::interval;
W1 - переписан запрос wildplasser
Вышесказанное страдает от ряда неэффективных деталей (выходящих за рамки этого уже значительного поста). Переписанная версия намного быстрее:
CREATE OR REPLACE FUNCTION this_years_birthday(_dut INOUT date) AS
$func$
SELECT (date_trunc('year', now()) + ($1 - date_trunc('year', $1)))::date
$func$ LANGUAGE sql;
SELECT *
FROM event e
WHERE this_years_birthday(e.event_date)
BETWEEN current_date
AND (current_date + 14);
Результаты теста
Я запустил этот тест с временной таблицей на PostgreSQL 9.1.7. Результаты были собраны с EXPLAIN ANALYZE, лучший из 5.
Результаты
Without index C: Total runtime: 76714.723 ms C1: Total runtime: 307.987 ms -- ! D: Total runtime: 325.549 ms E1: Total runtime: 253.671 ms -- ! E2: Total runtime: 484.698 ms -- min() & max() expensive without index E3: Total runtime: 213.805 ms -- ! G: Total runtime: 984.788 ms H: Total runtime: 977.297 ms W: Total runtime: 2668.092 ms W1: Total runtime: 596.849 ms -- ! With index E1: Total runtime: 37.939 ms --!! E2: Total runtime: 38.097 ms --!! With index on expression E3: Total runtime: 11.837 ms --!!
Все остальные запросы выполняют то же самое с индексом или без него, потому что они используют невыражаемые выражения.
Conclusio
-
Пока что запрос @Daniel был самым быстрым.
-
Подход @wildplassers (переписанный) тоже работает приемлемо.
-
Версия @Catcall - это что-то вроде моего обратного подхода. Производительность быстро выходит из-под контроля с большими столами.
Переписанная версия работает довольно хорошо. Выражение, которое я использую, напоминает более простую версию функции @wildplassserthis_years_birthday(). -
Моя "простая версия" быстрее даже без индекса, потому что она требует меньше вычислений.
-
С индексом "расширенная версия" примерно так же быстро, как и "простая версия", потому что
min()иmax()становятся очень дешевыми с индексом. Оба существенно быстрее, чем остальные, которые не могут использовать индекс. -
Моя "версия черной магии" самая быстрая с индексом или без него. И это очень просто позвонить.
Обновленная версия (после бенчмарка) пока немного быстрее. -
В реальной таблице индекс будет иметь еще большее значение. Чем больше столбцов, тем больше таблица, а последовательное сканирование дороже, а размер индекса остается прежним.