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

Pengiraan Peristiwa yang Cekap mengikut Selang Masa

Pernyataan Masalah

Pertimbangkan jadual Acara yang mengandungi peristiwa bertanda masa. Objektifnya adalah untuk melaporkan kiraan peristiwa yang berlaku dalam selang masa tertentu, seperti hari, jam atau selang yang ditentukan pengguna. Matlamatnya adalah untuk menentukan pendekatan yang paling cekap untuk menjana maklumat ini secara dinamik melalui satu pertanyaan SQL dalam Postgres.

Penyelesaian

Menggunakan Date_bin() (Postgres 14 atau Lebih Baru)

Postgres 14 memperkenalkan Fungsi 'date_bin()', menyediakan penyelesaian yang mudah untuk masalah ini:

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;

Menjana Set Penuh Baris (Postgres 13 atau Lebih Lama)

Untuk yang lebih awal versi Postgres, pertanyaan berikut menjana set penuh slot masa dan melakukan LEFT JOIN untuk mengira peristiwa dalam setiap selang:

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;

Pertimbangan

• Postgres 9.6 atau lebih baru diperlukan untuk 'generate_series()'.
• Untuk set data yang besar, mencipta medan 'Selang' yang berasingan dalam jadual dan pra-menyimpan nilai selang masa boleh meningkatkan prestasi, tetapi memerlukan storan tambahan ruang.
• Apabila menentukan selang masa, gunakan sintaks Postgres yang sesuai, seperti 'selang '17 min''.
• Operator 'ANTARA' boleh digunakan untuk menapis acara dalam sesuatu tertentu julat masa, tetapi pastikan pengendalian yang betul bagi sempadan atas dan bawah.
• Gunakan 'to_char()' untuk memformat lajur mula_masa untuk tujuan paparan tanpa menjejaskan operasi ORDER BY.

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