php - 如何从未输入某些日期的数据库表中创建 30 天的数据数组？

Question

假设您有一个简单的表:

ID  IP                  CREATED_DATE
-------------------------------------------------------
1   111.111.111.111     2011-03-28 14:12:31
2   111.111.111.111     2011-03-29 03:38:12
3   222.222.222.222     2011-04-02 12:04:45
4   111.111.111.111     2011-04-02 22:13:23
5   333.333.333.333     2011-04-03 05:53:15
6   222.222.222.222     2011-04-05 02:13:51
7   111.111.111.111     2011-04-07 11:45:34

我需要查询最近 30 天并获取每天的唯一 ip 和总行数，即使那天没有条目。因此，未输入的天数显然只是 0 作为唯一行和总行的数组值。

Answer 1

如果你有一个 integers表格，您可以生成所需时间跨度 中的所有日期，并将这些日期与出现在表格中的每一天的 IP 计数合并:

   SELECT timespan."day",
          COALESCE(num_uniq_ips, 0) AS num_uniq_ips,
          COALESCE(num_records, 0) AS num_records
     FROM (SELECT DATE_SUB(CURRENT_DATE, INTERVAL i DAY) AS "day"
             FROM integers
            WHERE i < 30) timespan
LEFT JOIN (SELECT DATE(created_date) AS "day",
                  COUNT(DISTINCT ip) AS num_uniq_ips,
                  COUNT(1) AS num_records
             FROM so6025149
         GROUP BY 1) tallies
          ON timespan."day" = tallies."day"
 ORDER BY timespan."day" ASC;