我有一组记录,希望按照某个范围进行计数和分组,例如,我想要按照X天的时间段来计算被创建的记录数。
e.g. SELECT COUNT(*) FROM `table` GROUP BY /*`created` 3 days/*
以下是一个与日期相关的示例。
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)
您可以执行类似以下代码的操作
SELECT COUNT(*) FROM table GROUP BY FLOOR(created / 3)
... 我认为是这样的。
尽管如果created
是日期字段,则需要进行一些调整,将其转换为数字值才能使此操作生效。
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);
只需从创建日期到开始日期获取日期差,现在我得到了:
+-------------+--------------+--------------+----------+
| 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 |
+-------------+--------------+--------------+----------+