如何填補 MySQL 查詢中的日期空白？

填充MySQL 中的日期間隙

在MySQL 中使用基於日期的查詢時，您可能會遇到資料中存在間隙的情況對於特定日期。如果您想要呈現資料的全面視圖或執行依賴完整日期集的計算，這可能會出現問題。

帶日期間隙的查詢：

為了示範該問題，請考慮以下查詢：

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

此查詢返回按日期分組的查詢結果，顯示總計數和態度總和每個日期。但是，如果有任何日期沒有數據，它們將不會包含在結果中，從而導致資料集中出現空白。

填補空白：

填補這些與零的間隙，我們可以利用輔助表來產生開始日期和結束日期之間的完整日期集。可以使用以下查詢建立輔助表：

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

此查詢遞歸地向當前日期添加一天，直到達到未來的一年。

查詢輔助表：

現在，我們可以使用LEFT JOIN 來填充輔助表與我們的主查詢日期間隙：

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

此查詢將傳回一組完整的日期，包括那些沒有資料的日期，並用零填滿間隙。

以上是如何填補 MySQL 查詢中的日期空白？的詳細內容。更多資訊請關注PHP中文網其他相關文章！