Bagaimana untuk Mengisi Jurang Tarikh dalam Pertanyaan MySQL?

Mengisi Jurang Tarikh dalam MySQL

Apabila bekerja dengan pertanyaan berasaskan tarikh dalam MySQL, anda mungkin menghadapi situasi di mana terdapat jurang dalam data untuk tarikh tertentu. Ini boleh menjadi masalah jika anda ingin membentangkan paparan data yang komprehensif atau melakukan pengiraan yang bergantung pada mempunyai set tarikh yang lengkap.

Pertanyaan dengan Jurang Tarikh:

Untuk menunjukkan isu tersebut, pertimbangkan pertanyaan berikut:

SELECT DATE(posted_at) AS date,
    COUNT(*) AS total,
    SUM(attitude = 'positive') AS positive,
    SUM(attitude = 'neutral') AS neutral,
    SUM(attitude = 'negative') AS negative
    FROM `messages`
    WHERE (`messages`.brand_id = 1)
    AND (`messages`.`spam` = 0
    AND `messages`.`duplicate` = 0
    AND `messages`.`ignore` = 0)
    GROUP BY date ORDER BY date

Pertanyaan ini mengembalikan hasil yang dikumpulkan mengikut tarikh, menunjukkan jumlah kiraan dan jumlah sikap untuk setiap tarikh. Walau bagaimanapun, jika terdapat sebarang tarikh tanpa data, tarikh tersebut tidak akan disertakan dalam keputusan, menyebabkan jurang dalam set data.

Mengisi Jurang:

Untuk mengisi jurang dengan sifar ini, kita boleh menggunakan jadual pembantu untuk menjana set tarikh lengkap antara tarikh mula dan tamat. Jadual pembantu boleh dibuat menggunakan pertanyaan berikut:

SELECT date AS dt FROM (
    SELECT CURDATE() AS date
    UNION ALL
    SELECT date + INTERVAL 1 DAY FROM dates
    FROM dates
    WHERE date < DATE_ADD(CURDATE(), INTERVAL 1 YEAR)
) dates

Pertanyaan ini secara rekursif menambah satu hari pada tarikh semasa sehingga mencapai satu tahun ke hadapan.

Pertanyaan dengan Jadual Pembantu:

Kini, kami boleh menyertai jadual pembantu dengan pertanyaan utama kami menggunakan LEFT JOIN untuk mengisi jurang tarikh:

SELECT  d.dt AS date,
        COUNT(*) AS total,
        SUM(attitude = 'positive') AS positive,
        SUM(attitude = 'neutral') AS neutral,
        SUM(attitude = 'negative') AS negative
FROM    dates d
LEFT JOIN
        messages m
ON      m.posted_at >= d.dt
        AND m.posted_at < d.dt + INTERVAL 1 DAYS
        AND spam = 0
        AND duplicate = 0
        AND ignore = 0
GROUP BY
        d.dt
ORDER BY
        d.dt

Pertanyaan ini akan mengembalikan set tarikh lengkap, termasuk tarikh tanpa data dan mengisi jurang dengan sifar.

Atas ialah kandungan terperinci Bagaimana untuk Mengisi Jurang Tarikh dalam Pertanyaan MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!