Count the number of rows in 30-day cells

Question

Each row in my table has a date timestamp, and now I want to query the database to calculate how many rows there are in the last 30 days, 30 days before, etc. Until a 30-day container appears, returning to the beginning of the table.

I have successfully completed this query using Python and having executed several queries. But I'm pretty sure that this can be done in a single MySQL query.

Answer 1

There are no stored procedures, temporary tables, only one query and an effective execution plan with an index in the date column:

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); 

It should be pretty obvious what happens besides this spell:

 floor(dateDiff('2012-12-31', dateStampColumn) / 30) 

This expression appears several times, and it is evaluated by the number of 30-day periods ago by dateStampColumn . dateDiff returns the difference in days, divides it by 30 to get it in 30-day periods, and pass it to floor() to round it to the nearest integer. As soon as we get this number, we can GROUP BY it, and then we will do some math to translate this number back to the start and end dates of the period.

Replace '2012-12-31' with now() if you want. Here are some sample data:

 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'); 

And the result:

 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 

Period terms

included.

Play with this in SQL Fiddle .

There's a bit of potential dumbness in that any 30-day period with zero matching lines will not be included in the result. If you could join this against the period table, that could be fixed. However, MySQL has nothing like PostgreSQL generate_series () , so you have to deal with it in your application or try this smart hack .

Answer 2

If you just need to count the intervals where there is at least one line, you can use this:

 select datediff(curdate(), `date`) div 30 as block, count(*) as rows_per_block from your_table group by block 

And it also shows the start date and end date:

 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 

but if you also need to show all the intervals, you can use a solution like this:

 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 

but this requires that you have already prepared the numbers table, or see the fiddle here for a solution without a numbers table.

Answer 3

Try the following:

 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

But you need to create a temp temporary table as follows:

 CREATE TABLE TEMP (Digit int); INSERT INTO Temp VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 

Answer 4

Could you try the following:

 SELECT Count(*) FROM yourtable where dateColumn between Now() and Now() - Interval 30 Day 

This requires some kind of cycle to better respond to all 30-day intervals. Since you also need a 30-day interval between min (Date) in the table and the last point of the loop :) Or at least another table that shows the dates of each 30-day interval, and then joins.

Only every calendar month is counted here. Not exactly what you need.

 SELECT extract(month from datecolumn), count(*) FROM yourtable GROUP BY extract(month from datecolumn); 

Given the thought to my last comment and Stefan's comment, here is a long code, but with the correct results. Based on my own sample data and compatible with MYSQL with interval . If you need to use with SQL Server, use DateADD or the equivalence function.

• SQLFIDDLE

Sample data:

 ID_MAIN FIELD1 FILTER ---------------------------------------- 1 red August, 05 2012 00:00:00+0000 2 blue September, 15 2012 00:00:00+0000 3 pink September, 20 2012 00:00:00+0000 4 blue September, 27 2012 00:00:00+0000 5 blue October, 02 2012 00:00:00+0000 6 blue October, 16 2012 00:00:00+0000 7 blue October, 22 2012 00:00:00+0000 8 pink November, 12 2012 00:00:00+0000 9 pink November, 28 2012 00:00:00+0000 10 pink December, 01 2012 00:00:00+0000 11 pink December, 08 2012 00:00:00+0000 12 pink December, 22 2012 00:00:00+0000 

Query:

 set @i:= 0; SELECT MIN(filter) INTO @mindt FROM MAIN ; select count(a.id_main), y.dateInterval, (y.dateInterval - interval 29 day) as lowerBound from main a join ( SELECT date_format(Now(),'%Y-%m-%d') as dateInterval from dual union all select x.dateInterval from ( SELECT date_format( DATE(DATE_ADD(Now(), INTERVAL @i: =@i-29 DAY)),'%Y-%m-%d') AS dateInterval FROM Main, (SELECT @i:=0) r HAVING datediff(dateInterval,@mindt) >= 30 order by dateInterval desc) as x) as y on a.filter <= y.dateInterval and a.filter > (y.dateInterval - interval 29 day) group by y.dateInterval order by y.dateInterval desc ; 

Results:

 COUNT(A.ID_MAIN) DATEINTERVAL LOWERBOUND ---------------------------------------------- 2 2012-12-30 2012-12-01 3 2012-12-01 2012-11-02 2 2012-11-02 2012-10-04 4 2012-10-04 2012-09-05 

Answer 5

Create a stored procedure to count the number of rows for 30 days.

Run this procedure first, and then call the same procedure when you want to generate data.

 DELIMITER $$ DROP PROCEDURE IF EXISTS `sp_CountDataByDays`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_CountDataByDays`() BEGIN CREATE TEMPORARY TABLE daterange ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, fromDate DATE, toDate DATE, PRIMARY KEY (`id`) ); SELECT DATEDIFF(CURRENT_DATE(), dteCol) INTO @noOfDays FROM yourTable ORDER BY dteCol LIMIT 1; SET @counter = -1; WHILE (@noOfDays > @counter) DO INSERT daterange (toDate, fromDate) VALUES (DATE_SUB(CURRENT_DATE(), INTERVAL @counter DAY), DATE_SUB(CURRENT_DATE(), INTERVAL @counter: =@counter + 30 DAY)); END WHILE; SELECT d.id, d.fromdate, d.todate, COUNT(d.id) rowcnt FROM daterange d INNER JOIN yourTable a ON a.dteCol BETWEEN d.fromdate AND d.todate GROUP BY d.id; DROP TABLE daterange; END$$ DELIMITER ; 

Then CALL do the procedure:

 CALL sp_CountDataByDays(); 

You get the output as shown below:

 ID From Date To Date Row Count 1 2012-12-06 2013-01-05 17668 2 2012-11-06 2012-12-06 2845 3 2012-10-07 2012-11-06 2276 4 2012-09-07 2012-10-07 4561 5 2012-08-08 2012-09-07 5415 6 2012-07-09 2012-08-08 8954 7 2012-06-09 2012-07-09 4387 8 2012-05-10 2012-06-09 7911 9 2012-04-10 2012-05-10 7935 10 2012-03-11 2012-04-10 2566