Запрос всех прошлых и будущих круглых дней рождения

Я получил даты рождения пользователей в таблице и хочу отобразить список круглых дней рождения на следующие n лет (начиная с произвольной даты x), который выглядит так:

 +----------------------------------------------------------------------------------------+
 | Name   | id | birthdate  | current_age | birthday   | year | month | day | age_at_date |
 +----------------------------------------------------------------------------------------+
 | User 1 | 1  | 1958-01-23 | 59          | 2013-01-23 | 2013 | 1     | 23  | 55          | 
 | User 2 | 2  | 1988-01-29 | 29          | 2013-01-29 | 2013 | 1     | 29  | 25          | 
 | User 3 | 3  | 1963-02-12 | 54          | 2013-02-12 | 2013 | 2     | 12  | 50          | 
 | User 1 | 1  | 1958-01-23 | 59          | 2018-01-23 | 2018 | 1     | 23  | 60          | 
 | User 2 | 2  | 1988-01-29 | 29          | 2018-01-29 | 2018 | 1     | 29  | 30          | 
 | User 3 | 3  | 1963-02-12 | 54          | 2018-02-12 | 2018 | 2     | 12  | 55          | 
 | User 1 | 1  | 1958-01-23 | 59          | 2023-01-23 | 2023 | 1     | 23  | 65          | 
 | User 2 | 2  | 1988-01-29 | 29          | 2023-01-29 | 2023 | 1     | 29  | 35          | 
 | User 3 | 3  | 1963-02-12 | 54          | 2023-02-12 | 2023 | 2     | 12  | 60          | 
 +----------------------------------------------------------------------------------------+

Как видите, я хочу «обернуться» и показать не только день рождения следующего предстоящего раунда, что несложно, но и исторические данные и данные далекого будущего.

Основная идея моего текущего подхода заключается в следующем: я генерирую через generate_series все даты с 1900 по 2100 и соединяю их, сопоставляя день и месяц даты рождения с пользователем. Исходя из этого, я рассчитываю возраст на эту дату, чтобы окончательно выбрать только те дни рождения, которые являются круглыми (делящимися на 5) и дают неотрицательный возраст.

WITH
  test_users(id, name, birthdate) AS (
    VALUES
      (1, 'User 1', '23-01-1958' :: DATE),
      (2, 'User 2', '29-01-1988'),
      (3, 'User 3', '12-02-1963')
  ),
  dates AS (
    SELECT
      s                     AS date,
      date_part('year', s)  AS year,
      date_part('month', s) AS month,
      date_part('day', s)   AS day
    FROM generate_series('01-01-1900' :: TIMESTAMP, '01-01-2100' :: TIMESTAMP, '1 days' :: INTERVAL) AS s
  ),
  birthday_data AS (
    SELECT
      id                                                                                AS member_id,
      test_users.birthdate                                                              AS birthdate,
      (date_part('year', age((test_users.birthdate)))) :: INT                           AS current_age,
      date :: DATE                                                                      AS birthday,
      date_part('year', date)                                                           AS year,
      date_part('month', date)                                                          AS month,
      date_part('day', date)                                                            AS day,
      ROUND(extract(EPOCH FROM (dates.date - birthdate)) / (60 * 60 * 24 * 365)) :: INT AS age_at_date
    FROM test_users, dates
    WHERE
      dates.day = date_part('day', birthdate) AND
      dates.month = date_part('month', birthdate) AND
      dates.year >= date_part('year', birthdate)
  )

SELECT
  test_users.name,
  bd.*
FROM test_users
LEFT JOIN birthday_data bd ON bd.member_id = test_users.id
WHERE
  bd.age_at_date % 5 = 0 AND
  bd.birthday BETWEEN NOW() - INTERVAL '5' YEAR AND NOW() + INTERVAL '10' YEAR
ORDER BY bd.birthday;

Мой текущий подход кажется очень неэффективным и довольно сложным: он занимает> 100 мс. У кого-нибудь есть идея для более компактного и производительного запроса? Я использую Postgresql 9.5.3. Благодарю вас!


postgresql sql date postgresql-9.5
person Stephan    schedule 12.01.2018    source источник
comment
Пожалуйста, покажите ваш запрос, а не просто опишите его. Предоставьте определение таблицы с соответствующими образцами данных и, очевидно, с вашей версией Postgres.   -  person Erwin Brandstetter    schedule 13.01.2018

Ответы (1)


arrow_upward
0
arrow_downward

Может быть, попробуйте присоединиться к серии генерации:

create table bday(id serial, name text, dob date);
insert into bday (name, dob) values ('a', '08-21-1972'::date);
insert into bday (name, dob) values ('b', '03-20-1974'::date);

select * from bday , 
lateral( select generate_series( (1950-y)/5  , (2010-y)/5)*5 + y  as year
         from (select date_part('year',dob)::integer as y) as t2 
       ) as t1;

Это будет генерировать для каждой записи годы между 1950 и 2010 годами.

Вы можете добавить предложение where, чтобы исключить людей, родившихся после 2010 года (они не могут иметь день рождения в диапазоне), или исключить людей, родившихся до 1850 года (они маловероятны...)

-- Редактировать (после вашего редактирования):

Таким образом, ваш generate_series создает более 360 строк в год. За 100 лет это более 30 000. И они присоединяются к каждому пользователю. (3 пользователя => 100 000 строк)

Мой запрос генерирует только строки за нужные годы. За 100 лет это 20 рядов. Это означает 20 строк на пользователя.

Деление на 5 гарантирует, что дата начала будет круглым днем ​​рождения. (1950-y)/5) вычисляет, сколько круглых дней рождения было до 1950 года.

Человеку, родившемуся в 1941 году, нужно пропустить 1941 и 1946 годы, но у него круглый день рождения в 1951 году. Таким образом, разница (9 лет) делится на 5, а затем фактически плюс 1 для учета 0-го числа.

Если человек родился после 1950 года, число отрицательное, а greatest(-1,...)+1 дает 0, начиная с фактического года рождения.

Но на самом деле должно быть

select * from bday , 
lateral( select generate_series( greatest(-1,(1950-y)/5)+1, (2010-y)/5)*5 + y  as year
         from (select date_part('year',dob)::integer as y) as t2 
       ) as t1;

(вы можете делать greatest(0,...)+1, если хотите начать в 5 лет)

person Martin    schedule 13.01.2018