填补数据库返回的日期中的空白-可能使用纯SQL解决方案吗? [英] Filling Gaps in Dates Returned from Database - pure SQL solution possible?

问题描述

我有这个查询:

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是今天的日期.

Where $start_date is two weeks ago and $end_date is today's date.

我正在尝试查找特定日期范围内每天的所有点击.我也想包括没有点击的日子.由于我的数据库中自然没有这些条目，因此我需要向他们包括一些方法，如何最好地做到这一点，同时显示从开始日期到结束日期的所有日期.这是我目前所拥有的，这两个星期的日期范围有很多差距.

I am trying find all clicks made each day for a particular date range. I also want to include days where there has been no clicks. Since naturally there isn't an entry for these in my database I need to include them some how, how can I best do this whilst showing all dates from start date to end date. This what I currently have, lots of gaps for this two week date range.

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逻辑?

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

顺便说一句，为什么我以0 February 2011作为我的初次约会！嗯，我似乎也有重复的日期，这不应该发生，也许我的GROUP BY工作不正常?

Btw, why do I have 0 February 2011 as my first date! Hmm, I also seem to have duplicate dates, that shouldn't happen, maybe my GROUP BY isn't working correctly?

谢谢大家的帮助.

推荐答案

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

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

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

Yes, it is better to create a Numbers table (single column N) that contains nothing but the numbers 0 to 999. It can be used for many things, not least a query like the below:

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

顺便说一句，为什么我将2011年2月0日作为第一次约会

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

You're using the wrong format. It's UPPER case W not lower for day-of-week, so '%W %M %Y' or '%d %M %Y' for day-of-month. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

也许我的GROUP BY无法正常工作?

maybe my GROUP BY isn't working correctly?

您使用的是GROUP BY DAY(FROM_UNIXTIME(click_date))笔记天" ，而不是工作日，但是您正在显示(或尝试)％W" (工作日)-选择一个，不要混在一起.

You are using GROUP BY DAY(FROM_UNIXTIME(click_date)) note "day" not weekday, but you are displaying (or trying to) "%W" (weekday) - pick one, don't mix them.

---

编辑:如果您不希望具体化(创建为真实表)Numbers序列表，则可以即时构造一个.不会很漂亮.

---

If you prefer not to materialize (create as a real table) a Numbers sequence table, you can construct one on the fly. It won't be pretty.

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

Note: N1, N2 and N3 below combine to give a possible range of 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:一个直接的Dates表

EDIT #2: A straight Dates table

将此内容放置在phpMyAdmin的新窗口中或批量运行.它将创建一个名为Dates的表，其中每个日期都是从1900-01-01天(或在脚本中更改)到2300-01-01天(或更改).

Put this in a new window in phpMyAdmin or run it as a batch. It creates a table named Dates, with every single date from day 1900-01-01 (or change in the script) to 2300-01-01 (or change).

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;

有了这样的实用程序表，您的查询就可以

With such a utility table, your query can be just

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

这篇关于填补数据库返回的日期中的空白-可能使用纯SQL解决方案吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！