我有这个查询:
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信