SQL查询记录日期范围内匹配日期的记录数量?
我有一个表看起来像这样的记录:SQL查询记录日期范围内匹配日期的记录数量?
CREATE TABLE sample (
ix int unsigned auto_increment primary key,
start_active datetime,
last_active datetime
);
我需要知道多条记录是如何活跃在各个的最后30天。日子也应该排序递增,以便他们返回最旧到最新。
我使用的MySQL和查询将从PHP运行,但我并不真的需要PHP代码,只是查询。
这里是我的起点:
SELECT COUNT(1) cnt, DATE(?each of last 30 days?) adate
FROM sample
WHERE adate BETWEEN start_active AND last_active
GROUP BY adate;
做一个外连接。
没有表?制作一张桌子。我总是为此保留一张虚拟桌子。
create table artificial_range(
id int not null primary key auto_increment,
name varchar(20) null) ;
-- or whatever your database requires for an auto increment column
insert into artificial_range(name) values (null)
-- create one row.
insert into artificial_range(name) select name from artificial_range;
-- you now have two rows
insert into artificial_range(name) select name from artificial_range;
-- you now have four rows
insert into artificial_range(name) select name from artificial_range;
-- you now have eight rows
--etc.
insert into artificial_range(name) select name from artificial_range;
-- you now have 1024 rows, with ids 1-1024
现在使它方便使用,并且其限制为30天,以期:
编辑:JR Lawhorne指出:
你需要改变 “DATE_ADD” 到“ date_sub“在创建的视图中获取前30天。
谢谢JR!
create view each_of_the_last_30_days as
select date_sub(now(), interval (id - 1) day) as adate
from artificial_range where id < 32;
现在,在您的查询中使用这个(我没有实际测试您查询,我只是假设它工作正常):
编辑:我应该加入另一种方式:
SELECT COUNT(*) cnt, b.adate
FROM each_of_the_last_30_days b
left outer join sample a
on (b.adate BETWEEN a.start_active AND a.last_active)
GROUP BY b.adate;
SQL是相互匹配的存储在数据库中的值很大,但它并没有那么大的匹配值的集合是不在数据库。所以,一个简单的解决方法是创建一个包含你所需要的值的临时表:
CREATE TEMPORARY TABLE days_ago (d SMALLINT);
INSERT INTO days_ago (d) VALUES
(0), (1), (2), ... (29), (30);
现在你可以比较是d
天前就start_active
和每行的last_active
之间的跨度的日期。计算每个值为d
的组中匹配的行数,并且您的计数。
SELECT CURRENT_DATE - d DAYS, COUNT(*) cnt,
FROM days_ago
LEFT JOIN sample ON (CURRENT_DATE - d DAYS BETWEEN start_active AND last_active)
GROUP BY d
ORDER BY d DESC; -- oldest to newest
另注:不能使用在选择列表中的表达式定义的列别名,直到你到GROUP BY
条款。实际上,在标准SQL中,直到ORDER BY
子句才能使用它们,但MySQL支持在GROUP BY
和HAVING
子句中使用别名。
将日期转换为查询中的unix时间戳,即秒,然后查找差异为< =一个月中的秒数。
你可以在这里找到更多的信息: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp
如果您需要查询,请让我知道有帮助,但MySQL有很好的功能,用于处理日期时间。
[编辑]由于我对于真正的问题感到困惑,我需要完成草坪,但在我忘记之前我想写下来。
要按天数计算您希望您的where子句如上所述,以限制为过去30天,但您需要按天分组,并且选择转换每个开始到一个月的某一天,然后对这些数字进行计数。
这假设每次使用将被限制为一天,如果开始日期和结束日期可以跨越几天,则会更棘手。
将哪个日期转换为unix时间戳? 'CURRENT_DATE - INTERVAL 30 DAY','start_active'或'end_active'?这如何帮助OP组对最近30次的每一天的活跃记录*计数? – pilcrow 2009-08-15 01:47:27
将start_active和last_active转换为unix_timestamp,或者,虽然我没有尝试过,但可以使用subtime()来减去时间,然后转换,或者,在我上面建议的页面中,可以使用类似下面的内容:select ID从MESSAGE where SENT_TIME 2009-08-15 02:06:28
@詹姆斯布莱克,你的回答措辞表明你认为OP希望知道与30天的时间窗重叠的记录数 - 但这是错误的问题,他希望每天都知道重叠记录的数量*在过去的30天。 (是的,日期算法在SQL本地类型中很容易,或者使用整数时代戳。)也许您可以发布代码解决方案来说明您的提议? – pilcrow 2009-08-15 04:50:59
您需要将“date_add”更改为“date_sub”才能在创建的视图中获得前30天。 – 2009-08-15 02:46:43