Microcast: Iterators / Generating Date Ranges, Numbers, etc.

This article is inspired by the topic " counting the number of calendar events in each month of the year ." There is nothing new in it, it's just a micro-note about possible solutions.
Although the task of that topic is very typical and was solved quite calmly with the usual passage with case or if:

SELECT
sum (
CASE
when t.`start_date`< '2010-02-01' and t.end_date> '2010-01-01' then 1
else 0
end
)
AS jan,
sum (
CASE
when t.`start_date`< '2010-03-01' and t.end_date> '2010-02-01' then 1
else 0
end
)
AS feb,
...
FROM test t


But I found it necessary to write about some possibilities to avoid unnecessary manual work. For example, if we needed to aggregate not for a year and not for two, but, say, for the last 5 years, monthly. Agree, in that case 60 lines with if would be at least hard to read.

JayDi was going in the right direction talking about the date plate, but it would have to be separately created, even with specific conditions (daily, monthly or per second, etc.) ... This method is even used sometimes .

In principle, I have nothing against the creation of temporary tables, but in this case they are completely unnecessary. In Oracle, for example, generation of such time ranges-counters can be done as you please, for example, " select rownum <N ", either by creating a pipelined function, or by using types from dbms_sql, or using collections . In mysql, unfortunately, there is no “connect by”, but there is information_schema, an analogue of the oracle data dictionary, and oraklists quite often use queries like " select rownum from all_objects " for test runs. We can do the same in the same way:

Sample for grouping and join for 10 months from the beginning of 2010:

-- set @rownumber:=0;
select
case
when @rownumber is null
then @rownumber:=1
else @rownumber:=@rownumber+1
end n,
DATE_FORMAT(
date_add( '2010-01-01' , interval @rownumber month ),
'%Y.%m' ) month
from
information_schema.columns t
limit 10

* This source code was highlighted with Source Code Highlighter .

Here we simply use the information_schema.column as a row generator, which can be replaced by any other, which is mostly not used and in which there are guaranteed enough rows for our query (in my case, for example, there are 5281 of them). The first commented line must be executed to reset the counter variable.
We get:

 n month
 1 2010.01
 2 2010.02
 3 2010.03
 4 2010.04
 5th 2010.05
 6 2010.06
 7 2010.07
 8 2010.08
 9th 2010.09
 10 2010.10

Now you can join this table with your table according to your conditions. If you do not know in advance the required number of months (lines), then instead of limit, limit the conditions to the minimum and maximum dates.
')
Another solution: the solution "in the forehead" - the use of cursors. Well, what could be more logical than creating a procedure using cursors if we need to assemble more complex aggregates than built-in ones? I will not even describe this question (you can read, for example, here ), but do not forget about them (in that topic, no one even remembered about it ...)