Menanyakan semua ulang tahun yang lalu dan yang akan datang

Saya mendapatkan tanggal lahir pengguna dalam sebuah tabel dan ingin menampilkan daftar ulang tahun bulat untuk n tahun ke depan (dimulai dari tanggal sembarang x) yang terlihat seperti ini:

 +----------------------------------------------------------------------------------------+
 | 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          | 
 +----------------------------------------------------------------------------------------+

Seperti yang Anda lihat, saya ingin "mengelilingi" dan tidak hanya menampilkan ulang tahun putaran berikutnya yang akan datang, yang mudah, tetapi juga data historis dan masa depan yang jauh.

Ide inti dari pendekatan saya saat ini adalah sebagai berikut: Saya menghasilkan melalui generate_series semua tanggal dari tahun 1900 hingga 2100 dan menggabungkannya dengan mencocokkan hari dan bulan dari tanggal lahir dengan pengguna. Berdasarkan hal tersebut, saya menghitung umur pada tanggal tersebut untuk akhirnya memilih hanya tanggal lahir tersebut, yang bulat (dapat dibagi 5) dan menghasilkan umur yang tidak negatif.

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;

Pendekatan saya saat ini tampaknya sangat tidak efisien dan agak rumit: Dibutuhkan >100 md. Adakah yang punya ide untuk kueri yang lebih ringkas dan berkinerja baik? Saya menggunakan PostgreSql 9.5.3. Terima kasih!


postgresql sql date postgresql-9.5
person Stephan    schedule 12.01.2018    source sumber
comment
Tolong tunjukkan pertanyaan Anda, jangan hanya menjelaskannya. Berikan definisi tabel dengan data sampel yang cocok dan, tentu saja, versi Postgres Anda.   -  person Erwin Brandstetter    schedule 13.01.2018

Jawaban (1)


arrow_upward
0
arrow_downward

Mungkin mencoba bergabung dengan seri generate:

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;

Ini akan menghasilkan tahun antara 1950 dan 2010 untuk setiap entri.

Anda dapat menambahkan klausa di mana untuk mengecualikan orang yang lahir setelah tahun 2010 (mereka tidak dapat memiliki tanggal lahir dalam rentang tersebut) Atau mengecualikan orang yang lahir sebelum tahun 1850 (kemungkinan kecilnya...)

-- Edit (setelah Anda mengedit):

Jadi generate_series Anda membuat 360+ baris per tahun. Dalam 100 tahun itu berarti lebih dari 30.000. Dan mereka bergabung dengan setiap pengguna. (3 pengguna => 100.000 baris)

Permintaan saya hanya menghasilkan baris selama bertahun-tahun yang dibutuhkan. Dalam 100 tahun itu berarti 20 baris. Itu berarti 20 baris per pengguna.

Dengan membaginya dengan 5, maka dipastikan tanggal mulainya adalah ulang tahun yang bulat. (1950-y)/5) menghitung berapa banyak ulang tahun bulat yang terjadi sebelum tahun 1950.

Seseorang yang lahir pada tahun 1941 harus melewati tahun 1941 dan 1946, tetapi mempunyai hari lahir bulat pada tahun 1951. Jadi selisihnya (9 tahun) dibagi 5, lalu ditambah 1 untuk menghitung angka 0.

Jika orang tersebut lahir setelah tahun 1950, angkanya negatif, dan greatest(-1,...)+1 menghasilkan 0, dimulai dari tahun ulang tahun sebenarnya.

Tapi sebenarnya memang seharusnya begitu

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;

(Anda mungkin melakukan greatest(0,...)+1 jika ingin memulai pada usia 5 tahun)

person Martin    schedule 13.01.2018