Bagaimana untuk mengumpulkan Data Siri Masa ke dalam Selang 5 Minit Menggunakan SQL?

Mengagregatkan Data Siri Masa: Selang 5 Minit dalam SQL

Menganalisis data siri masa selalunya memerlukan pengumpulan data ke dalam selang waktu tertentu. Panduan ini menunjukkan cara mengumpulkan data ke dalam selang 5 minit menggunakan SQL, menangani senario di mana data perlu diagregatkan dalam jangka masa yang ditetapkan. Contoh menggunakan data daripada jadual 'masa' dan 'id', mengira kejadian nama 'John'. Cabarannya terletak pada perpindahan daripada kumpulan cap waktu individu kepada pengagregatan selang 5 minit.

Penyelesaian untuk Sistem Pangkalan Data Berbeza

Pendekatan optimum berbeza-beza bergantung pada sistem pangkalan data anda. Berikut ialah penyelesaian untuk PostgreSQL dan MySQL:

PostgreSQL

PostgreSQL menawarkan pendekatan yang fleksibel menggunakan extract('epoch') untuk mendapatkan cap waktu Unix (saat sejak zaman) dan INTERVAL:

<code class="language-sql">SELECT
    date_trunc('minute', timestamp) + INTERVAL '5 minutes' * (extract(minute from timestamp)::int / 5) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;</code>

Pertanyaan ini mula-mula memangkas cap masa kepada minit menggunakan date_trunc. Kemudian, ia mengira selang 5 minit dengan menambah gandaan 5 minit berdasarkan minit cap masa asal.

MySQL

MySQL menyediakan penyelesaian yang lebih mudah menggunakan UNIX_TIMESTAMP() dan pembahagian integer:

<code class="language-sql">SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp) / 300) * 300) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;</code>

Pertanyaan ini menukar cap masa kepada cap masa Unix, melaksanakan pembahagian integer sebanyak 300 (saat dalam 5 minit), dan kemudian menukar hasil kembali kepada cap masa menggunakan FROM_UNIXTIME().

Kedua-dua pertanyaan mengumpulkan hasil mengikut selang 5 minit yang dikira dan nama, memberikan output agregat yang diingini. Ingat untuk menggantikan ... dengan klausa WHERE khusus anda. Klausa ORDER BY memastikan pembentangan kronologi hasil.

Atas ialah kandungan terperinci Bagaimana untuk mengumpulkan Data Siri Masa ke dalam Selang 5 Minit Menggunakan SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!