X天范围内的组
问题描述:
我有一组或多组记录,并且我想对它们进行计数并将它们组合在一定范围内,例如,我想,以计算团体创建的X天X天范围内的组
e.g. SELECT COUNT(*) FROM `table` GROUP BY /*`created` 3 days/*
答
,你可以这样做SELECT COUNT(*) FROM table GROUP BY FLOOR(created/3)
记录......我想。
虽然如果created
是日期字段,你必须做一些更拉坯得到它变成一个数值这个工作。
答
以下是带日期的示例。
create table t1(created date not null);
insert
into t1(created) values (date '2011-01-09')
,(date '2011-01-10')
,(date '2011-01-11')
,(date '2011-01-12')
,(date '2011-01-13')
,(date '2011-01-14')
,(date '2011-01-15')
,(date '2011-01-16')
,(date '2011-01-17')
,(date '2011-01-18')
,(date '2011-01-19')
,(date '2011-01-20');
select floor(datediff(now(), created)/3) * 3 as days_ago
,min(created)
,max(created)
,count(*)
from t1
group
by floor(datediff(now(), created)/3);
+----------+--------------+--------------+----------+
| days_ago | min(created) | max(created) | count(*) |
+----------+--------------+--------------+----------+
| 0 | 2011-01-18 | 2011-01-20 | 3 |
| 3 | 2011-01-15 | 2011-01-17 | 3 |
| 6 | 2011-01-12 | 2011-01-14 | 3 |
| 9 | 2011-01-09 | 2011-01-11 | 3 |
+----------+--------------+--------------+----------+
4 rows in set (0.00 sec)
答
Thanks @Ronnis,我用你的例子,最后解决我的问题。
而且还有一个小错误,我发现,例如,我添加了一行
insert into t1(created) values (date '2011-01-21')
现在,我得到:
+----------+--------------+--------------+----------+
| days_ago | min(created) | max(created) | count(*) |
+----------+--------------+--------------+----------+
| 1986 | 2011-01-20 | 2011-01-21 | 2 |
| 1989 | 2011-01-17 | 2011-01-19 | 3 |
| 1992 | 2011-01-14 | 2011-01-16 | 3 |
| 1995 | 2011-01-11 | 2011-01-13 | 3 |
| 1998 | 2011-01-09 | 2011-01-10 | 2 |
+----------+--------------+--------------+----------+
正如你可以看到,天分为2,3, 3,3,2这不是我所期望的。
更改SQL作为
select floor(datediff(created, '2011-01-09')/3) * 3 as days_before
,min(created)
,max(created)
,count(*)
from t1
group by floor(datediff(created, '2011-01-09')/3);
从创建开始日期刚刚得到DATEDIFF,现在我得到:
+-------------+--------------+--------------+----------+
| days_before | min(created) | max(created) | count(*) |
+-------------+--------------+--------------+----------+
| 0 | 2011-01-09 | 2011-01-11 | 3 |
| 3 | 2011-01-12 | 2011-01-14 | 3 |
| 6 | 2011-01-15 | 2011-01-17 | 3 |
| 9 | 2011-01-18 | 2011-01-20 | 3 |
| 12 | 2011-01-21 | 2011-01-21 | 1 |
+-------------+--------------+--------------+----------+
这可能更适合。