如何填补 MySQL 中的日期空白? [英] How to fill date gaps in MySQL?

问题描述

如何填补 MySQL 中的日期空白?这是我的查询:

<前>SELECT DATE(posted_at) AS 日期，COUNT(*) AS 总数，SUM(attitude = 'positive') 为正，SUM(attitude = 'neutral') 作为中立，SUM(attitude = 'negative') AS 负数从`消息`WHERE (`messages`.brand_id = 1)AND (`messages`.`spam` = 0AND `messages`.`duplicate` = 0AND `messages`.`ignore` = 0)GROUP BY 日期 ORDER BY 日期

它返回正确的结果集 - 但我想用零填充日期开始和结束之间的空白.我该怎么做?

解决方案

您需要创建一个辅助表并用从 start 到 end 的所有日期填充它，然后只需 LEFT JOIN 与该表:

SELECT d.dt AS 日期，COUNT(*) AS 总数，SUM(attitude = 'positive') 为正，SUM(attitude = 'neutral') 作为中立，SUM(attitude = 'negative') AS 负数FROM 日期 d左加入留言ON m.posted_at >= d.dtAND m.posted_at 

基本上，您在这里需要的是一个虚拟行源.

MySQL 是唯一缺乏生成它的方法的主要系统.

PostgreSQL 实现了一个特殊的函数 generate_series 来做到这一点，而 Oracle 和 SQL Server 可以使用递归(CONNECT BY 和递归CTE，相应地).

How i can fill date gaps in MySQL? Here is my query:

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

It returns proper result set - but i want to fill gaps between dates start and end by zeros. How i can do this?

解决方案

You'll need to create a helper table and fill it with all dates from start to end, then just LEFT JOIN with that table:

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

Basically, what you need here is a dummy rowsource.

MySQL is the only major system which lacks a way to generate it.

PostgreSQL implements a special function generate_series to do that, while Oracle and SQL Server can use recursion (CONNECT BY and recursive CTEs, accordingly).

这篇关于如何填补 MySQL 中的日期空白?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！