如何用零填充 MySQL 聚合查询中的日期间隙？

填充 MySQL 中的日期空白

问题：

提取特定日期的聚合数据，使用 MySQL 查询。但是，返回的结果集仅包含有数据的日期，在没有数据的日期之间留有间隙。目标是用零填充这些间隙以创建连续的日期范围。

解决方案：

要实现此目的，需要一个辅助表。此表将包含从所需开始日期到所需结束日期的所有日期。

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

可以使用查询填充辅助表：

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;

替换 '2023-01 -01' 和 '2023-12-31' 以及您想要的开始和结束日期。

接下来，辅助表用于与原始查询执行 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中文网其他相关文章！