Wie füllt man Datumslücken in MySQL-Aggregatabfragen mit Nullen?

Datumslücken in MySQL füllen

Problem:

Aggregierte Daten für bestimmte Daten extrahieren , wird eine MySQL-Abfrage verwendet. Der zurückgegebene Ergebnissatz enthält jedoch nur Datumsangaben, die Daten enthalten, sodass Lücken zwischen Datumsangaben ohne Daten entstehen. Ziel ist es, diese Lücken mit Nullen zu füllen, um einen kontinuierlichen Datumsbereich zu erstellen.

Lösung:

Um dies zu erreichen, ist eine Hilfstabelle erforderlich. Diese Tabelle enthält alle Daten vom gewünschten Startdatum bis zum gewünschten Enddatum.

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

Die Hilfstabelle kann mit einer Abfrage gefüllt werden:

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;

Ersetzen Sie „2023-01 -01‘ und ‚2023-12-31‘ mit Ihrem gewünschten Start- und Enddatum.

Als nächstes wird die Hilfstabelle verwendet So führen Sie einen LEFT JOIN mit der Originalabfrage aus:

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

Diese modifizierte Abfrage gibt den gewünschten Ergebnissatz mit allen Datumsangaben zurück, einschließlich derjenigen ohne Daten in der Originaltabelle, gefüllt mit Nullen.

Das obige ist der detaillierte Inhalt vonWie füllt man Datumslücken in MySQL-Aggregatabfragen mit Nullen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!