Эта заметка навеяна топиком "подсчет количества событий календаря в каждом месяце года". В ней нет ничего нового, это просто микрозаметка о возможных решениях.
Хотя задача того топика очень типична и вполне спокойно решалась обычным проходом с case или if:
Но я счел нужным написать о некоторых возможностях избежать излишнюю ручную работу. Например, если нам необходимо бы было агрегировать не за год и не за два, а, скажем, за последние 5 лет помесячно. Согласитесь, в таком случае 60 строк c if'ами было бы как минимум тяжело читать.
JayDi шел в правильном направлении говоря о табличке для дат, но ее бы пришлось отдельно создавать да еще и с конкретными условиями(подневно, помесячно или посекундно и тд)… Этот способ даже используют иногда.
В принципе я не имею ничего против создания temporary tables, но в данном случае они совершенно излишни. В Oracle, например, генерацию таких временных диапазонов-счетчиков можно сделать как угодно, например, "select rownum from dual connect by rownum<N", или создав pipelined-функцию, или используя типы из dbms_sql, или используя коллекции. В mysql, к сожалению, нет «connect by», но есть information_schema — аналог ораклового data dictionary, а ораклисты довольно часто используют для тестовых прогонов запросы вида "select rownum from all_objects". Таким же образом можем поступить и мы:
Выборка для группировки и джойна для 10 месяцев с начала 2010:
Здесь используется просто в качестве генератора строк табличка information_schema.column, которую можно заменить на любую другую, которая в основном не используется и в которой гарантированно достаточно строк для нашего запроса(в моем случае, например, их 5281). Первая закомментированная строка должна быть выполнена для обнуления переменной-счетчика.
Получим:
Теперь эту таблицу вы можете сджойнить с вашей таблицей по вашим условиям. В случае, если заранее не знаете необходимого кол-ва месяцев(строк), то вместо limit ограничьте условиями минимальной и максимальной даты.
Другой вариант решения: решение «в лоб» — использование курсоров. Ну, что может быть логичнее, чем создать процедуру с использованием курсоров в случае, если нам необходимо собрать более сложные аггрегаты, чем встроенные? Этот вопрос я даже не буду описывать(можете почитать, например, тут), но не забывайте про них(в том топике никто об этом даже не вспомнил...)
Хотя задача того топика очень типична и вполне спокойно решалась обычным проходом с 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,
...
FROM test t
Но я счел нужным написать о некоторых возможностях избежать излишнюю ручную работу. Например, если нам необходимо бы было агрегировать не за год и не за два, а, скажем, за последние 5 лет помесячно. Согласитесь, в таком случае 60 строк c if'ами было бы как минимум тяжело читать.
JayDi шел в правильном направлении говоря о табличке для дат, но ее бы пришлось отдельно создавать да еще и с конкретными условиями(подневно, помесячно или посекундно и тд)… Этот способ даже используют иногда.
В принципе я не имею ничего против создания temporary tables, но в данном случае они совершенно излишни. В Oracle, например, генерацию таких временных диапазонов-счетчиков можно сделать как угодно, например, "select rownum from dual connect by rownum<N", или создав pipelined-функцию, или используя типы из dbms_sql, или используя коллекции. В mysql, к сожалению, нет «connect by», но есть 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
* This source code was highlighted with Source Code Highlighter.Здесь используется просто в качестве генератора строк табличка 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 ограничьте условиями минимальной и максимальной даты.
Другой вариант решения: решение «в лоб» — использование курсоров. Ну, что может быть логичнее, чем создать процедуру с использованием курсоров в случае, если нам необходимо собрать более сложные аггрегаты, чем встроенные? Этот вопрос я даже не буду описывать(можете почитать, например, тут), но не забывайте про них(в том топике никто об этом даже не вспомнил...)
