Микрозаметка: MySQL - Итераторы/Генерация диапазонов дат, чисел и тд

Эта заметка навеяна топиком "подсчет количества событий календаря в каждом месяце года". В ней нет ничего нового, это просто микрозаметка о возможных решениях.
Хотя задача того топика очень типична и вполне спокойно решалась обычным проходом с case или 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,
sum(CASE when t.`start_date`<'2010-04-01' and t.end_date>='2010-03-01' then 1 else 0 end) AS mar,
sum(CASE when t.`start_date`<'2010-05-01' and t.end_date>='2010-04-01' then 1 else 0 end) AS apr,
sum(CASE when t.`start_date`<'2010-06-01' and t.end_date>='2010-05-01' then 1 else 0 end) AS may,
sum(CASE when t.`start_date`<'2010-07-01' and t.end_date>='2010-06-01' then 1 else 0 end) AS jun,
sum(CASE when t.`start_date`<'2010-08-01' and t.end_date>='2010-07-01' then 1 else 0 end) AS jul,
sum(CASE when t.`start_date`<'2010-09-01' and t.end_date>='2010-08-01' then 1 else 0 end) AS aug,
sum(CASE when t.`start_date`<'2010-10-01' and t.end_date>='2010-09-01' then 1 else 0 end) AS sep,
sum(CASE when t.`start_date`<'2010-11-01' and t.end_date>='2010-10-01' then 1 else 0 end) AS oct,
sum(CASE when t.`start_date`<'2010-12-01' and t.end_date>='2010-11-01' then 1 else 0 end) AS nov,
sum(CASE when t.`start_date`<'2011-01-01' and t.end_date>='2010-12-01' then 1 else 0 end) AS dec
FROM events t


Но я счел нужным написать о некоторых возможностях избежать излишнюю ручную работу. Например, если нам необходимо бы было агрегировать не за год и не за два, а, скажем, за последние 5 лет помесячно. Согласитесь, в таком случае 60 строк c if'ами было бы как минимум тяжело читать.

Товарищ JayDi шел в правильном направлении говоря о табличке для дат, но ее бы пришлось отдельно создавать да еще и с конкретными условиями(подневно,помесячно или посекундно и тд)... Этот способ даже используют иногда.

В принципе я не имею ничего против создания temporary tables, но в данном случае они совершенно излишни. В Oracle, например, генерацию таких временных диапазонов-счетчиков можно сделать как угодно, например, "select rownum from dual connect by rownum < N", или создав pipelined-функцию, или используя типы из dbms_sql, или используя коллекции. В mysql, к сожалению, нет ни "connect by", ни pipelined functions, ни вообще функций позволяющих возвращать собственный тип-запись, ни возвращающих курсоры, но есть information_schema - аналог ораклового data dictionary, а ораклисты довольно часто используют для тестовых прогонов запросы вида "select rownum from all_objects". Таким же образом можем поступить и мы:

Выборка для группировки и джойна для 10 месяцев с начала 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


Здесь используется просто в качестве генератора строк табличка information_schema.column, которую можно заменить на любую другую, которая в основном не используется и в которой гарантированно достаточно строк для нашего запроса(в моем случае, например, их 5281). Первая закомментированная строка должна быть выполнена для обнуления переменной-счетчика.
Получим:

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

Теперь эту таблицу вы можете сджойнить с вашей таблицей по вашим условиям. В случае, если заранее не знаете необходимого кол-ва месяцев(строк), то вместо limit ограничьте условиями минимальной и максимальной даты.

Другой вариант решения: классическое решение "в лоб" - использование курсоров. Ну, что может быть логичнее, чем создать процедуру с использованием курсоров в случае, если нам необходимо собрать более сложные аггрегаты, чем встроенные? Этот вопрос я даже не буду описывать(можете почитать, например, тут), но не забывайте про них(в том топике никто об этом даже не вспомнил...)