统计30天箱中的行数
我的表中的每一行都有一个日期时间戳，我希望从现在开始查询数据库，以计算在过去30天和之前30天内有多少行，依此类推。直到有一个30天的仓位返回到表的开头。
我已经使用Python成功地执行了这个查询，并执行了几个查询。但我几乎可以肯定，这可以在一个MySQL查询中完成。
回答 5
Stack Overflow用户
发布于 2013-01-05 10:19:53
没有存储过程、临时表、只有一个查询，以及在date列上有索引的高效执行计划：
select
subdate(
'2012-12-31',
floor(dateDiff('2012-12-31', dateStampColumn) / 30) * 30 + 30 - 1
) as "period starting",
subdate(
'2012-12-31',
floor(dateDiff('2012-12-31', dateStampColumn) / 30) * 30
) as "period ending",
count(*)
from
YOURTABLE
group by floor(dateDiff('2012-12-31', dateStampColumn) / 30);这里发生的事情应该很明显，除了这个咒语：
floor(dateDiff('2012-12-31', dateStampColumn) / 30)该表达式出现了多次，它的计算结果是30天前dateStampColumn的周期数。dateDiff返回以天为单位的差值，将其除以30得到30天内的差值，然后将其全部提供给floor()以将其舍入为整数。一旦我们有了这个数字，我们就可以GROUP BY它，然后我们做一些数学运算，把这个数字转换回周期的开始和结束日期。
如果愿意，可以用now()替换'2012-12-31'。以下是一些示例数据：
CREATE TABLE YOURTABLE
(`Id` int, `dateStampColumn` datetime);
INSERT INTO YOURTABLE
(`Id`, `dateStampColumn`)
VALUES
(1, '2012-10-15 02:00:00'),
(1, '2012-10-17 02:00:00'),
(1, '2012-10-30 02:00:00'),
(1, '2012-10-31 02:00:00'),
(1, '2012-11-01 02:00:00'),
(1, '2012-11-02 02:00:00'),
(1, '2012-11-18 02:00:00'),
(1, '2012-11-19 02:00:00'),
(1, '2012-11-21 02:00:00'),
(1, '2012-11-25 02:00:00'),
(1, '2012-11-25 02:00:00'),
(1, '2012-11-26 02:00:00'),
(1, '2012-11-26 02:00:00'),
(1, '2012-11-24 02:00:00'),
(1, '2012-11-23 02:00:00'),
(1, '2012-11-28 02:00:00'),
(1, '2012-11-29 02:00:00'),
(1, '2012-11-30 02:00:00'),
(1, '2012-12-01 02:00:00'),
(1, '2012-12-02 02:00:00'),
(1, '2012-12-15 02:00:00'),
(1, '2012-12-17 02:00:00'),
(1, '2012-12-18 02:00:00'),
(1, '2012-12-19 02:00:00'),
(1, '2012-12-21 02:00:00'),
(1, '2012-12-25 02:00:00'),
(1, '2012-12-25 02:00:00'),
(1, '2012-12-26 02:00:00'),
(1, '2012-12-26 02:00:00'),
(1, '2012-12-24 02:00:00'),
(1, '2012-12-23 02:00:00'),
(1, '2012-12-31 02:00:00'),
(1, '2012-12-30 02:00:00'),
(1, '2012-12-28 02:00:00'),
(1, '2012-12-28 02:00:00'),
(1, '2012-12-30 02:00:00');结果是：
period starting period ending count(*)
2012-12-02 2012-12-31 17
2012-11-02 2012-12-01 14
2012-10-03 2012-11-01 5期间终结点是包含的。
在SQL Fiddle中使用它。
有一些潜在的好处，因为任何30天内没有匹配行的任何时间段都不会包括在结果中。如果您可以将此连接到一个期间表中，则可以消除这种情况。然而，MySQL没有任何类似PostgreSQL的generate_series()，所以你必须在你的应用程序中处理它，或者尝试this clever hack。
Stack Overflow用户
发布于 2012-12-30 21:06:45
如果您只需要计算至少有一行的时间间隔，则可以使用以下命令：
select
datediff(curdate(), `date`) div 30 as block,
count(*) as rows_per_block
from
your_table
group by
block这里还显示了开始日期和结束日期：
select
datediff(curdate(), d) div 30 as block,
date_sub(curdate(),
INTERVAL (datediff(curdate(), `date`) div 30)*30 DAY) as start_block,
date_sub(curdate(),
INTERVAL (1+datediff(curdate(), `date`) div 30)*30-1 DAY) as end_block,
count(*)
from your_table
group by block但是，如果您还需要显示所有间隔，则可以使用如下解决方案：
select
num,
date_sub(curdate(),
INTERVAL (num+1)*30-1 DAY) as start_block,
date_sub(curdate(),
INTERVAL num*30 DAY) as end_block,
count(`date`)
from
numbers left join your_table
on `date` between date_sub(curdate(),
INTERVAL (num+1)*30-1 DAY) and
date_sub(curdate(),
INTERVAL num*30 DAY)
where num<=(datediff(curdate(), (select min(`date`) from your_table) ) div 30)
group by num但这要求您已经准备好了一个numbers表，或者查看fiddle here以了解没有numbers表的解决方案。
Stack Overflow用户
发布于 2012-12-30 19:02:42
试试这个：
SELECT
DATE_FORMAT(t1.`Date`, '%Y-%m-%d'),
COUNT(t2.Id)
FROM
(
SELECT SUBDATE(CURDATE(), ID) `Date`
FROM
(
SELECT t2.digit * 10 + t1.digit + 1 AS id
FROM TEMP AS t1
CROSS JOIN TEMP AS t2
) t
WHERE Id <= 30
) t1
LEFT JOIN YOURTABLE t2 ON DATE(t1.`Date`) = DATE(t2.dateStampColumn)
GROUP BY t1.`Date`;SQL Fiddle Demo
但是，您需要创建一个临时表Temp，如下所示：
CREATE TABLE TEMP
(Digit int);
INSERT INTO Temp VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);https://stackoverflow.com/questions/14090016
复制相似问题
腾讯云开发者
