MYSQL:对 "event"表中的条目进行计数，按日期和行计数分组 - 用于日期期间

Question

我有这个查询来按日期计算表格中的“ View ”。它显然只给我有事件的日期，但我希望我可以让 MySQL 在选定的时间段内返回数据。例如，30 天的 30 行，为没有匹配行的日期创建空数据。

我希望避免使用 PHP 来解决这个问题，所以我想我会在这里征求有关如何通过智能查询完成此操作的建议。

查询:

SELECT count(ID) AS views, date AS view_date
FROM logging.views 
WHERE date < DATE_SUB(NOW(),INTERVAL 30 DAY) 
GROUP BY DAY(date)";

下面的示例只给我 7 行，显然是因为在选定的 30 天间隔中只有条目匹配 7 个不同的日期。我希望我能得到 30 行，代表设定时间间隔内的所有日期。

输出:

Array
(
    [0] => Array
        (
            [date] => 2010-09-01 22:04:19
            [views] => 20
        )

    [1] => Array
        (
            [date] => 2010-08-13 00:15:54
            [views] => 4
        )

    [2] => Array
        (
            [date] => 2010-08-24 17:46:53
            [views] => 2
        )

    [3] => Array
        (
            [date] => 2010-08-25 07:18:11
            [views] => 35
        )

    [4] => Array
        (
            [date] => 2010-08-26 08:02:47
            [views] => 12
        )

    [5] => Array
        (
            [date] => 2010-08-27 09:56:05
            [views] => 29
        )

    [6] => Array
        (
            [date] => 2010-08-28 12:46:17
            [views] => 30
        )

)

Answer 1

在 SQL 中针对此问题的常见解决方案是创建一个包含这 30 天的临时表，并将其LEFT OUTER JOIN 到您的views 表。

SELECT COUNT(v.ID) AS views, d.date AS view_date
FROM logging.alldays d
LEFT OUTER JOIN logging.views v ON d.date = v.date
WHERE d.date < DATE_SUB(NOW(),INTERVAL 30 DAY) 
GROUP BY d.date

或者将其设为非临时表并预先填充 future 100 年的所有日期。这只有 36525 行，这对于 RDBMS 来说很容易。

---

But what if I'd wanted to query by the hour, or minute?

那么临时表方法可能会更容易。

基本上，SQL 擅长将数据集连接到其他数据集——而不是将数据连接到空气中。所以你必须在某处制作一组数据才能加入它。

某些品牌的 SQL 数据库具有生成表示某些有用值范围的临时数据集的内置函数。例如，PostgreSQL 有 generate_series() .不幸的是，MySQL 不提供这种类型的功能。