php - 填补从数据库返回的日期中的空白 - 纯 SQL 解决方案可能吗？

Question

我有这个问题:

SELECT COUNT(*) as clicks, DATE_FORMAT(FROM_UNIXTIME(click_date), '%w %M %Y') as point 
FROM tracking 
WHERE click_date < $end_date AND click_date > $start_date 
GROUP BY DAY(FROM_UNIXTIME(click_date))

$start_date 是两周前，$end_date 是今天。

我正在尝试查找特定日期范围内每天进行的所有点击。我还想包括没有点击的日子。由于我的数据库中自然没有这些条目，所以我需要以某种方式将它们包括在内，我怎样才能最好地做到这一点，同时显示从开始日期到结束日期的所有日期。这就是我目前所拥有的，这两周的日期范围内有很多差距。

Array
(
    [0] => Array
        (
            [clicks] => 17
            [point] => 0 February 2011
        )

    [1] => Array
        (
            [clicks] => 3
            [point] => 1 February 2011
        )

    [2] => Array
        (
            [clicks] => 14
            [point] => 5 February 2011
        )

    [3] => Array
        (
            [clicks] => 1
            [point] => 1 February 2011
        )

    [4] => Array
        (
            [clicks] => 8
            [point] => 2 February 2011
        )

)

这是否可以通过纯 SQL 查询来完成，还是我必须使用一些 php 逻辑？

顺便说一句，为什么我将 2011 年 2 月 0 日 作为我的第一次约会!嗯，我似乎也有重复的日期，这不应该发生，也许我的 GROUP BY 没有正常工作？

感谢大家的帮助。

Answer 1

Can this possibly be done via a pure SQL query or do I have to use some php logic?

是的，最好创建一个 Numbers 表(单列 N)，它只包含数字 0 到 999。它可以用于很多事情，尤其是像下面这样的查询:

SELECT COUNT(t.click_date) as clicks,
    DATE_FORMAT(adddate($start_date, interval N day), '%d %M %Y') as point 
FROM Numbers
LEFT JOIN tracking t
    ON t.click_date >= adddate($start_date, interval N day)
    and t.click_date < adddate($start_date, interval (N+1) day)
WHERE N between 0 and datediff($start_date, $end_date)
GROUP BY N

Btw, why do I have 0 February 2011 as my first date

您使用的格式错误。对于星期几，W 是大写字母，所以 '%W %M %Y' 或 '%d %M %Y' 表示星期几-月。 http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

maybe my GROUP BY isn't working correctly?

您正在使用 GROUP BY DAY(FROM_UNIXTIME(click_date)) 注意 “天” 不是工作日，但您正在显示(或试图)"% W"(工作日)- 选一个，不要混用。

---

编辑:如果您不想具体化(创建一个真实的表)一个 Numbers 序列表，您可以即时构建一个。它不会很漂亮。

注意:下面的 N1、N2 和 N3 组合给出了 0-999 的可能范围

SELECT COUNT(t.click_date) as clicks,
    DATE_FORMAT(adddate($start_date, interval N day), '%d %M %Y') as point 
FROM (
    select N1 * 100 + N2 * 10 + N3 as N
    from (
    select 0 N1 union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all
    select 8 union all select 9) N1
    cross join (
    select 0 N2 union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all
    select 8 union all select 9) N2
    cross join (
    select 0 N3 union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all
    select 8 union all select 9) N3
    ) Numbers
LEFT JOIN tracking t
    ON t.click_date >= adddate($start_date, interval N day)
    and t.click_date < adddate($start_date, interval (N+1) day)
WHERE N between 0 and datediff($start_date, $end_date)
GROUP BY N

编辑#2:一个直接的日期表

将其放在 phpMyAdmin 的新窗口中或作为批处理运行。它创建一个名为 Dates 的表，每个日期从 1900-01-01(或脚本中的更改)到 2300-01-01(或更改)。

DROP PROCEDURE IF EXISTS FillDateTable;

delimiter //
CREATE PROCEDURE FillDateTable()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  drop table if exists datetable;
  create table datetable (thedate datetime primary key, isweekday smallint);

  SET @x := date('1900-01-01');
  REPEAT 
    insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;
    SET @x := date_add(@x, interval 1 day);
    UNTIL @x > date('2300-01-01') END REPEAT;
END//
delimiter ;

CALL FillDateTable;

有了这样一个实用表，你的查询就可以了

SELECT COUNT(t.click_date) as clicks,
    DATE_FORMAT(thedate, '%d %M %Y') as point 
FROM Dates
LEFT JOIN tracking t
    ON t.click_date >= thedate
    and t.click_date < adddate(thedate, interval 1 day)
WHERE thedate between $start_date and $end_date
GROUP BY thedate