当前位置: 首页 > 面试题库 >

填补数据库返回的日期中的空白-可能使用纯SQL解决方案吗?

沈单弓
2023-03-14
问题内容

我有这个查询:

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

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

谢谢大家的帮助。


问题答案:

可以通过纯SQL查询完成此操作,还是必须使用某些php逻辑?

是的,最好创建一个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

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

您使用的格式错误。W的大写W在一周中的某天不降低,因此对于月中的 ‘%W%M%Y’‘%d%M%Y’
http://dev.mysql.com/doc/refman/5.5/zh-CN/date-and-time-
functions.html#function_date-
format


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

您使用的GROUP BY DAY(FROM_UNIXTIME(click_date))“ day”
而不是工作日的注释,但您正在显示(或试图显示) “%W” (工作日)-选择一个,不要混合使用。

编辑: 如果您不希望具体化(创建为真实表)数字序列表,则可以动态构建一个。不会很漂亮

注意:下面的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


 类似资料:
  • 问题内容: 我想修改一个熊猫MultiIndex DataFrame,以使每个索引组都包括指定范围之间的日期。我希望每个小组使用值0(或)填写缺少的日期2013-06-11至2013-12-31 。 我已经看到了有关ing的一些讨论,但这是针对简单(非分组)时间序列数据的。 是否有捷径可寻? 以下是我为实现此目的所做的一些尝试。例如:通过堆叠后,我便可以重新索引。 最后几行让我有些难过。我希望我可

  • 问题内容: 我发现了一个类似的问题(重复记录以填补Google BigQuery中日期之间的空白 ),但是存在不同的情况,答案不适用。 我的数据结构如下(基本上是多个产品和合作伙伴的价格变动历史记录): 我需要的是一个查询(特别是用BigQuery Standard SQL编写),该查询在给定日期范围(在这种情况下为)下输出以下结果: 对于产品和合作伙伴的每种组合,基本上都是价格历史记录,其中包含

  • 问题内容: 我有一个带有IDENTITY列的表 在添加/删除了beeing的某些行之后,我以Id值的间隔结尾: 有没有一种简单的方法可以将值压缩为 ? 问题答案: 我认为用相同的模式创建第二张表,从第一张表导入所有数据(当然,除了identity列;让第二张表开始重新编号),然后删除第一张表并将第二张表重命名为原始名称。 如果您有大量的FK关系要与其他表等一起重建,是否容易成为问题。

  • 问题内容: 假设我有两条记录,都有日期和计数: 您将如何选择此项以填补时间空白,并始终保持最近的记录? 因此输出将是: 我还没有找到一个整齐的解决方案。我想可以使用DATEDIFF和for循环来完成此操作,但我希望可以更轻松地完成此操作。 问题答案: 您有2个要解决的问题。第一个问题是如何填补空白。第二个问题是为那些缺少的记录填充“计数”字段。 问题1:这可以通过使用或创建一个来解决。如果可以的话

  • 问题内容: 因此,可以说我有一些类似的记录: 我该如何构造一个查询,该查询将返回2011-01-03和2011-01-05,即未使用的日期。我将博客发布到未来,我想要一个查询来向我显示我还没有发布任何东西的日子。从当前日期到未来的2周。 更新: 我对建立一个永久性的日期表并不感到兴奋。经过考虑之后,解决方案似乎是制作一个小的存储过程来创建一个临时表。就像是: 我猜这是不可能选择 没有 数据的。 问

  • 我正在为班级(老师,课程,时间)安排项目。我使用optaplanner作为spring-boot应用程序的一部分,测试代码正在正确编译和运行,但结果包含空解决方案,在日志输出中我看到以下消息: RTED:花费的时间(11)、最佳得分(0hard/0soft)、环境模式(REPRODUCIBLE)、移动线程计数(NONE)、随机(带有种子0的JDK)。2021-09-28 22:39:26.619信