每天分组填补空白 [英] Group by day with filled gaps

问题描述

我正在尝试根据本教程

I am trying to generate a daily sales reports for a particular user based on this tutorial Using MySQL to generate daily sales reports with filled gaps. To do this, I have three tables, records table, user table and calendar table

records     user      calendar
id          id        datefield
user_id     
timestamp

下面的查询在特定日期没有可用的情况下，返回总计0和NULL作为user_id，这很不错:

The query below returns 0 as total and NULL as the user_id when data is not available for a particular day which is great:

SELECT calendar.datefield AS DATE,
       IFNULL(COUNT(records.id),0) AS total, records.user_id
FROM records 
RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield)    

WHERE ( 
    calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW()
)
GROUP BY DATE DESC

想法是为特定用户生成此报告，因此我将上述查询修改为以下内容:

The idea is to generate this report for a particular user so I modified the above query to what follows:

SELECT calendar.datefield AS DATE,
           IFNULL(COUNT(records.id),0) AS total, records.user_id
    FROM records 
    RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield)

    WHERE ( 
        calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW()
        AND records.user_id = SOME_EXISTING_USER_ID
    )
    GROUP BY DATE DESC

如果没有记录，但是在没有数据的任何特定日期返回0，则返回空结果.

This return an empty result when there is no record but the idea is to return 0 for any particular day which does not have data.

如何修改第一个查询以使其适合特定用户?

How can I modify the first query to work for a particular user?

推荐答案

哇.自从我在野外看到RIGHT JOIN以来已经有一段时间了！无论如何，尝试像这样将WHERE子句中的用户谓词添加到RIGHT JOIN中:

Wow. Been a while since I've seen a RIGHT JOIN in the wild! Anyway, try adding the user predicate from the WHERE clause into the RIGHT JOIN like this:

SELECT calendar.datefield AS DATE,
           IFNULL(COUNT(records.id),0) AS total, records.user_id
    FROM records 
    RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield) 
                        AND records.user_id = SOME_EXISTING_USER_ID

    WHERE ( 
        calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW()
    )
    GROUP BY DATE DESC;

对我来说，这是显式连接与隐式连接的最大好处之一.

For me this is one of the great benefits of explicit joins vs implicit joins...

这篇关于每天分组填补空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！