Bagaimana untuk Mengisi Jurang Tarikh dalam Pertanyaan Agregat MySQL dengan Sifar?

Mengisi Jurang Tarikh dalam MySQL

Masalah:

Untuk mengekstrak data agregat untuk tarikh tertentu , pertanyaan MySQL digunakan. Walau bagaimanapun, set hasil yang dikembalikan hanya termasuk tarikh yang mempunyai data, meninggalkan jurang antara tarikh tanpa data. Matlamatnya adalah untuk mengisi jurang ini dengan sifar untuk mencipta julat tarikh yang berterusan.

Penyelesaian:

Untuk mencapai ini, jadual pembantu diperlukan. Jadual ini akan mengandungi semua tarikh dari tarikh mula yang diingini hingga tarikh tamat yang diingini.

CREATE TABLE dates (
  dt DATE NOT NULL,
  PRIMARY KEY (dt)
);

Jadual pembantu boleh diisi menggunakan pertanyaan:

INSERT INTO dates (dt)
SELECT DATE(posted_at)
FROM messages
WHERE brand_id = 1
AND spam = 0
AND duplicate = 0
AND ignore = 0
GROUP BY DATE(posted_at)
UNION
SELECT DATE('2023-01-01') + INTERVAL i-1 DAY
FROM (
  SELECT 1 AS i 
  UNION ALL
  SELECT i + 1 FROM <table> WHERE i < DATEDIFF('2023-12-31', '2023-01-01')
) AS i;

Ganti '2023-01 -01' dan '2023-12-31' dengan tarikh mula dan tamat yang anda inginkan.

Seterusnya, jadual pembantu digunakan untuk melakukan LEFT JOIN dengan pertanyaan asal:

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 yang diubah suai ini akan mengembalikan set hasil yang diingini dengan semua tarikh, termasuk yang tiada data dalam jadual asal, diisi dengan sifar.

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