Bagaimana untuk Mengira Peristiwa dengan Cekap dalam Selang Masa Dinamik dalam PostgreSQL?

Cara Mengira Peristiwa mengikut Selang Masa dengan Cekap

Pengenalan

Aplikasi selalunya memerlukan pengekstrakan cerapan daripada data berasaskan peristiwa, seperti mengira peristiwa dalam lingkungan tertentu selang masa. Tugasan ini menimbulkan cabaran apabila saiz selang berubah secara dinamik.

Pernyataan Masalah

Jadual data dengan peristiwa bercap masa memerlukan pertanyaan SQL dinamik yang mengumpulkan acara mengikut selang masa sewenang-wenangnya, termasuk mingguan, harian, setiap jam, atau selang 15 minit.

Penyelesaian

Postgres 14 atau Lebih Baru

Untuk Postgres versi 14 dan以降, fungsi date_bin() menyediakan penyelesaian yang mudah. Untuk mendapatkan semula satu baris setiap slot masa dengan data, gunakan pertanyaan berikut:

SELECT date_bin('15 min', e.ts, '2018-05-01') AS start_time
     , count(e.ts) AS events
FROM   event e
GROUP  BY 1
ORDER  BY 1;

Untuk mendapatkan semua slot masa sejak tarikh tertentu, laraskan pertanyaan seperti berikut:

SELECT start_time, COALESCE(events, 0) AS events
FROM  (
   SELECT generate_series(timestamp '2018-05-01', max(ts), interval '15 min')
   FROM   event
   ) g(start_time)
LEFT   JOIN (
   SELECT date_bin('15 min', e.ts, '2018-05-01'), count(e.ts)
   FROM   event e
   WHERE  e.ts >= '2018-05-01'  -- filter early (optional)
   GROUP  BY 1
   ) e(start_time, events) USING (start_time)
ORDER  BY 1;

Postgres 13 atau Lebih Lama

Untuk versi Postgres yang lebih lama, pertanyaan berikut boleh digunakan:

WITH grid AS (
   SELECT start_time
        , lead(start_time, 1, 'infinity') OVER (ORDER BY start_time) AS end_time
   FROM  (
      SELECT generate_series(min(ts), max(ts), interval '17 min') AS start_time
      FROM   event
      ) sub
   )
SELECT start_time, count(e.ts) AS events
FROM   grid       g
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.end_time
GROUP  BY start_time
ORDER  BY start_time;

Pertanyaan ini menyediakan satu baris untuk setiap slot masa, dengan kiraan 0 untuk slot tanpa acara.

Pengoptimuman

Data selang prasimpan dalam jadual acara boleh meningkatkan prestasi. Walau bagaimanapun, pendekatan ini menggandakan saiz jadual. Jika pertukaran boleh diterima, ia boleh memberikan peningkatan kelajuan yang ketara.

Pertimbangan Tambahan

• Kendalikan had masa atas dan bawah dengan betul.
• Gunakan to_char () berfungsi untuk memformat cap masa untuk tujuan paparan.
• Gunakan fungsi generate_series() untuk menjana set penuh slot masa.

Atas ialah kandungan terperinci Bagaimana untuk Mengira Peristiwa dengan Cekap dalam Selang Masa Dinamik dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!