如何填补 MySQL 查询中的日期空白？-mysql教程-PHP中文网

如何填补 MySQL 查询中的日期空白？

Susan Sarandon

发布： 2024-12-24 21:37:45

原创

145 人浏览过

How to Fill Date Gaps in MySQL Queries?

填充 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中文网其他相关文章！