Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 47
Доброго времени суток. Я попробовал использовать Вашу функцию. По-моему, она работает не совсем корректно. У меня событие длится с 2009-01-01 (это start_date) до 2010-12-01 (end_date).
Выполнил запрос:
SELECT
SUM(isEventInMonth(start_date,end_date,2,2009)) AS jan
FROM EVENTS WHERE YEAR(start_date) = 2009 OR YEAR(end_date) = 2009
Выдает 0. А должно быть 1, потому что ведь в феврале событие тоже длится.
По-моему эту Вашу функцию надо поправить вот так:
CREATE DEFINER=`root`@`localhost` FUNCTION `isEventInMonth`(
startdate DATE,
enddate DATE,
_month INT(1),
_year INT(1)
) RETURNS int(1)
BEGIN
DECLARE results INT(1);
IF YEAR(startdate) = YEAR(enddate) THEN
SET results = IF( MONTH(startdate) = _month
AND MONTH(enddate) = _month
OR
( MONTH(startdate) MONTH(enddate)
AND (MONTH(startdate) = _month)
)
,1,0);
ELSEIF YEAR(startdate) = _year THEN
SET results = IF( MONTH(startdate) = _month ,1,0);
END IF;
RETURN results;
END;
А вообще, это по-моему какая-то программистская вакханалия, или я чего-то не понял.
Можно ведь для каждого месяца делать простой запрос, типа:
SELECT count(*)
FROM `events`
WHERE start_date > 20091231 and end_date < 20101231
Выполнил запрос:
SELECT
SUM(isEventInMonth(start_date,end_date,2,2009)) AS jan
FROM EVENTS WHERE YEAR(start_date) = 2009 OR YEAR(end_date) = 2009
Выдает 0. А должно быть 1, потому что ведь в феврале событие тоже длится.
По-моему эту Вашу функцию надо поправить вот так:
CREATE DEFINER=`root`@`localhost` FUNCTION `isEventInMonth`(
startdate DATE,
enddate DATE,
_month INT(1),
_year INT(1)
) RETURNS int(1)
BEGIN
DECLARE results INT(1);
IF YEAR(startdate) = YEAR(enddate) THEN
SET results = IF( MONTH(startdate) = _month
AND MONTH(enddate) = _month
OR
( MONTH(startdate) MONTH(enddate)
AND (MONTH(startdate) = _month)
)
,1,0);
ELSEIF YEAR(startdate) = _year THEN
SET results = IF( MONTH(startdate) = _month ,1,0);
END IF;
RETURN results;
END;
А вообще, это по-моему какая-то программистская вакханалия, или я чего-то не понял.
Можно ведь для каждого месяца делать простой запрос, типа:
SELECT count(*)
FROM `events`
WHERE start_date > 20091231 and end_date < 20101231
Запостив свой комментарий, понял, что видимо просто символы «больше» и «меньше» не запостились в некоторых местах у вас в функции. Как, впрочем, и у меня. Видимо, НЛО съело их :)
Поправьте плиз свой вариант, а то ведь люди скопируют неверно :)
CREATE FUNCTION `isEventInMonth`(
startdate DATE,
enddate DATE,
_month INT(1),
_year INT(1)
) RETURNS int(1)
BEGIN
DECLARE results INT(1);
IF YEAR(startdate) = YEAR(enddate) THEN
SET results = IF( MONTH(startdate) = _month
AND MONTH(enddate) = _month
OR
( MONTH(startdate) <> MONTH(enddate)
AND (MONTH(startdate) <= _month
AND MONTH(enddate) >= _month)
)
,1,0);
ELSEIF YEAR(startdate) = _year THEN
SET results = IF( MONTH(startdate) <= _month ,1,0);
ELSE
SET results = IF( MONTH(enddate) >= _month ,1,0);
END IF;
RETURN results;
END;
Поправьте плиз свой вариант, а то ведь люди скопируют неверно :)
CREATE FUNCTION `isEventInMonth`(
startdate DATE,
enddate DATE,
_month INT(1),
_year INT(1)
) RETURNS int(1)
BEGIN
DECLARE results INT(1);
IF YEAR(startdate) = YEAR(enddate) THEN
SET results = IF( MONTH(startdate) = _month
AND MONTH(enddate) = _month
OR
( MONTH(startdate) <> MONTH(enddate)
AND (MONTH(startdate) <= _month
AND MONTH(enddate) >= _month)
)
,1,0);
ELSEIF YEAR(startdate) = _year THEN
SET results = IF( MONTH(startdate) <= _month ,1,0);
ELSE
SET results = IF( MONTH(enddate) >= _month ,1,0);
END IF;
RETURN results;
END;
просто меня учили, что из php один запрос лучше 12, а мне надо было выводить за текущий год и за следующий, = 24 запроса не есть гууд.
за замечание большое спасибо, исправил.
за замечание большое спасибо, исправил.
как по мне вопрос лучшей производительности одного или двенадцати запросов никак не связан с языком программирования. В частности для пхп пофиг сколько из него сделают запросов. Зато иногда бывают такие JOIN'ы что 12 простых запросов отработают быстрее. Да еще и функции… Кип ит симпл :)
Если Вас заботит производительность данной функциональности, рекомендую сделать следующее:
1. Используйте 12 простых запросов, но при этом применяя mysqli multiquery
2. Используйте 12 простых запросов, но при этом применяя mysqli prepared statements
3. Выберите из этих решений то, которое работает на Ваших наборах данных быстрее.
4. Потом сравните это решение (с 12 простыми запросами) с Вашим сложным решением (с одним запросом и функциями).
5. Если будет не сложно, потом пожалуйста напишите сюда цифры замеров производительности, что получилось на Ваших наборах данных, интересно будет посмотреть.
1. Используйте 12 простых запросов, но при этом применяя mysqli multiquery
2. Используйте 12 простых запросов, но при этом применяя mysqli prepared statements
3. Выберите из этих решений то, которое работает на Ваших наборах данных быстрее.
4. Потом сравните это решение (с 12 простыми запросами) с Вашим сложным решением (с одним запросом и функциями).
5. Если будет не сложно, потом пожалуйста напишите сюда цифры замеров производительности, что получилось на Ваших наборах данных, интересно будет посмотреть.
В поставленной задаче требуется лишь один проход, так что 12 проходов явно будет дольше, если таблица не секционирована по датам так чтобы запросы можно было выполнять параллельно, но это уже отдельная и достаточно большая тема.
Да, то, что одна выборка, в общем случае, быстрее, чем 12 или 24 — с этим нельзя спорить, это — истина.
Но в тех случаях, когда данных очень мало (всего десятки записей, как то часто бывает в подобных системах с ивентами), обработка результата выборки с помощью функции может оказаться дороже, чем несколько простых выборок. То есть, имеет смысл попробовать разные решения, и выбрать то, которое будет работать эффективнее на типичном наборе данных текущего проекта.
Также для эксперимента можно попробовать несколько (хоть все 12 или 24) простых запросов, объединенных в один через UNION ALL. Но сказать однозначно что выгоднее (на очень малых наборах данных) можно лишь проведя серию экспериментов с разными решениями, таково мое мнение.
Впрочем, ниже привели пример запроса с CASE, вот это намного выгоднее, чем использование функции, особенно если AND заменят на BETWEEN, хоть и придется хардкодить данные. Вероятно, это лучший вариант с точки зрения производительности.
Но в тех случаях, когда данных очень мало (всего десятки записей, как то часто бывает в подобных системах с ивентами), обработка результата выборки с помощью функции может оказаться дороже, чем несколько простых выборок. То есть, имеет смысл попробовать разные решения, и выбрать то, которое будет работать эффективнее на типичном наборе данных текущего проекта.
Также для эксперимента можно попробовать несколько (хоть все 12 или 24) простых запросов, объединенных в один через UNION ALL. Но сказать однозначно что выгоднее (на очень малых наборах данных) можно лишь проведя серию экспериментов с разными решениями, таково мое мнение.
Впрочем, ниже привели пример запроса с CASE, вот это намного выгоднее, чем использование функции, особенно если AND заменят на BETWEEN, хоть и придется хардкодить данные. Вероятно, это лучший вариант с точки зрения производительности.
с чего Вы взяли, что функции тормозят запрос? об этом я нигде прочитать не смог и на собственном опыте не смог выявить.
По поводу данных — я программист а не предсказатель и сколько там данных будет не знаю, могу догадываться, что будет много, так как это единая БД для более десятка сайтов. Да и вообще думается мне, что для небольшого набора данных все едино — их даже и в расчет не беру. BETWEEN — тут не катит, посмотрите какое там вхождение в интервал. Писать UNION 12 запросам — это не наш метод.
По поводу нескольких запросов — стараюсь большую часть работы с данными БД возложить на саму MYSQL и возвращать уже необходимый результат, она в общем и потому СУБД, что с этим справляется хорошо. Посему часто пользуюсь и VIEW и процедурами и функциями и тригерами. Очень неудобно, что во view нельзя делать подзапросы, но это тема для другого разговора.
По поводу данных — я программист а не предсказатель и сколько там данных будет не знаю, могу догадываться, что будет много, так как это единая БД для более десятка сайтов. Да и вообще думается мне, что для небольшого набора данных все едино — их даже и в расчет не беру. BETWEEN — тут не катит, посмотрите какое там вхождение в интервал. Писать UNION 12 запросам — это не наш метод.
По поводу нескольких запросов — стараюсь большую часть работы с данными БД возложить на саму MYSQL и возвращать уже необходимый результат, она в общем и потому СУБД, что с этим справляется хорошо. Посему часто пользуюсь и VIEW и процедурами и функциями и тригерами. Очень неудобно, что во view нельзя делать подзапросы, но это тема для другого разговора.
Я не утверждаю, что во всех случаях функции тормозят. Но в том или ином конкретном случае могут тормозить. Это можно выяснить поставив эксперимент, и сделав замеры на Ваших данных.
По поводу программиста-предсказалеля. Если для Вас важен вопрос производительности, то при проектировании хранилища данных и составлении запросов первый же вопрос, который должен быть задан, это какими объемами данных придется оперировать.
По поводу программиста-предсказалеля. Если для Вас важен вопрос производительности, то при проектировании хранилища данных и составлении запросов первый же вопрос, который должен быть задан, это какими объемами данных придется оперировать.
Функции и процедуры будут работать медленнее единого блока SQL по определению — в силу того, что все они выполняются в отдельности и к ним нельзя применять оптимизации и разные планы выполнения, их нельзя свернуть или развернуть для использования специфичных индексов или уменьшения обрабатываемых данных. Исключение, пожалуй, может составить только код, результат которого зависит исключительно от входных параметров — тогда со стороны СУБД возможны оптимизации и кеширование результатов.
я же писал, у меня будет не 12, а 24 прохода, так как вывести надо по 2 годам — текущему и следующему, а это явно будет хуже, чем один простой запрос.
Один оптимизированный запрос лучше в случаях, если это не порождает долгих блокировок
По месяцам:
SELECT
MONTHNAME(STR_TO_DATE(DATE_FORMAT(d.start_date, '01.%m.%Y'), '%d.%m.%Y')) AS R_MONTH_NAME,
COUNT(*) AS R_COUNT
FROM
dates d
GROUP BY
R_MONTH_NAME
По месяцам с ограничением длительности события на год/месяц/день (оставить одно условие для нужного эффекта):
SELECT
MONTHNAME(STR_TO_DATE(DATE_FORMAT(d.start_date, '01.%m.%Y'), '%d.%m.%Y')) AS R_MONTH_NAME,
COUNT(*) AS R_COUNT
FROM
dates d
WHERE
1 = 1
/* в один год */
/* AND STR_TO_DATE(DATE_FORMAT(d.start_date, '01.01.%Y'), '%d.%m.%Y') = STR_TO_DATE(DATE_FORMAT(d.end_date, '01.01.%Y'), '%d.%m.%Y') */
/* в один месяц */
/* AND STR_TO_DATE(DATE_FORMAT(d.start_date, '01.%m.%Y'), '%d.%m.%Y') = STR_TO_DATE(DATE_FORMAT(d.end_date, '01.%m.%Y'), '%d.%m.%Y') */
/* в один день */
/* AND STR_TO_DATE(DATE_FORMAT(d.start_date, '%d.%m.%Y'), '%d.%m.%Y') = STR_TO_DATE(DATE_FORMAT(d.end_date, '%d.%m.%Y'), '%d.%m.%Y') */
GROUP BY
R_MONTH_NAME
SELECT
MONTHNAME(STR_TO_DATE(DATE_FORMAT(d.start_date, '01.%m.%Y'), '%d.%m.%Y')) AS R_MONTH_NAME,
COUNT(*) AS R_COUNT
FROM
dates d
GROUP BY
R_MONTH_NAME
По месяцам с ограничением длительности события на год/месяц/день (оставить одно условие для нужного эффекта):
SELECT
MONTHNAME(STR_TO_DATE(DATE_FORMAT(d.start_date, '01.%m.%Y'), '%d.%m.%Y')) AS R_MONTH_NAME,
COUNT(*) AS R_COUNT
FROM
dates d
WHERE
1 = 1
/* в один год */
/* AND STR_TO_DATE(DATE_FORMAT(d.start_date, '01.01.%Y'), '%d.%m.%Y') = STR_TO_DATE(DATE_FORMAT(d.end_date, '01.01.%Y'), '%d.%m.%Y') */
/* в один месяц */
/* AND STR_TO_DATE(DATE_FORMAT(d.start_date, '01.%m.%Y'), '%d.%m.%Y') = STR_TO_DATE(DATE_FORMAT(d.end_date, '01.%m.%Y'), '%d.%m.%Y') */
/* в один день */
/* AND STR_TO_DATE(DATE_FORMAT(d.start_date, '%d.%m.%Y'), '%d.%m.%Y') = STR_TO_DATE(DATE_FORMAT(d.end_date, '%d.%m.%Y'), '%d.%m.%Y') */
GROUP BY
R_MONTH_NAME
Самый большой вопрос по теме, как оптимальнее показать в текущем месяце событие, начинающееся в прошлом месяце и заканчивающееся в следующем. У Вас же просто группировка по месяцу start_date.
С помощью дополнительного условия «текущий_месяц BETWEEN месяц_начала AND месяц_окончания». Собственно, для вывода количества событий в календаре этого условия будет достаточно (оно захватит в себя все активные события этого месяца).
Конструкция вида «STR_TO_DATE(DATE_FORMAT(date, 'date_format'), 'date_format')» представляет из себя аналог функции обрезания даты (по аналогии с другими СУБД, например, функции TRUNC() для Oracle).
А для оптимизации работы можно было бы хранить значение текущего месяца в отдельном поле — тогда появилась бы возможность использовать еще и индексы.
Конструкция вида «STR_TO_DATE(DATE_FORMAT(date, 'date_format'), 'date_format')» представляет из себя аналог функции обрезания даты (по аналогии с другими СУБД, например, функции TRUNC() для Oracle).
А для оптимизации работы можно было бы хранить значение текущего месяца в отдельном поле — тогда появилась бы возможность использовать еще и индексы.
Всё равно GROUP BY R_MONTH_NAME привяжет запись только к старовому месяцу, а не к каждому из тех, для которых это событие было активным
Выводит список месяцев и кол-во активных событий (которые начались, закончились или продолжаются в соответствующий период):
SELECT
MONTHNAME(d.month) AS R_MONTH_NAME,
(SELECT
COUNT(*)
FROM
myevents e
WHERE
d.month BETWEEN STR_TO_DATE(DATE_FORMAT(e.start_date, '01.%m.%Y'), '%d.%m.%Y') AND STR_TO_DATE(DATE_FORMAT(e.end_date, '01.%m.%Y'), '%d.%m.%Y'))
AS R_COUNT
FROM
dates d
Здесь:
dates — таблица или запрос, возвращающий список месяцев, за который надо вывести статистику
myevents — таблица со списком событий
SELECT
MONTHNAME(d.month) AS R_MONTH_NAME,
(SELECT
COUNT(*)
FROM
myevents e
WHERE
d.month BETWEEN STR_TO_DATE(DATE_FORMAT(e.start_date, '01.%m.%Y'), '%d.%m.%Y') AND STR_TO_DATE(DATE_FORMAT(e.end_date, '01.%m.%Y'), '%d.%m.%Y'))
AS R_COUNT
FROM
dates d
Здесь:
dates — таблица или запрос, возвращающий список месяцев, за который надо вывести статистику
myevents — таблица со списком событий
Не надо делать лишних конвертаций str_to_date и date_to_str, лучше воспользоваться last_day — будет почти в два раза быстрее.
кажется у Вас все еще сложнее
У меня-то как раз простой запрос на объединение двух таблиц с одним BETWEEN-условием, работающий при любых периодах для вывода и без каких-либо ограничений по использованию. Если использовать совет xtender'а по замене STR_TO_DATE(DATE_FORMAT()) на LAST_DAY, то код визуально уменьшится и будет выполняться в 2-3 раза быстрее.
А вот у вас — куча левого кода, велосипед для определения, находится ли дата между двумя другими датами (самопальная реализация BETWEEN), куча ограничений на использование данной методики и КУЧА SQL-кода. Одним словом — костыль, которому просто не место в продакшене (коллеги будут без ума от счастья увидев код функции isEventInMonth).
А вот у вас — куча левого кода, велосипед для определения, находится ли дата между двумя другими датами (самопальная реализация BETWEEN), куча ограничений на использование данной методики и КУЧА SQL-кода. Одним словом — костыль, которому просто не место в продакшене (коллеги будут без ума от счастья увидев код функции isEventInMonth).
В таких случаях лучше сначала генерировать таблицу для группировки. Покажу на примере для 10 месяцев с начала 2010:
Здесь используется просто в качестве генератора строк табличка information_schema.column, которая значения не имеет — я ее использую просто как пустышку. Первая закомментированная строка должна быть выполнена для обнуления переменной-счетчика.
Получим:
Теперь эту таблицу вы можете сджойнить с вашей таблицей по вашим условиям. В случае, если заранее не знаете необходимого кол-ва месяцев(строк), то добавьте условия минимальной и максимальной даты.
--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, которая значения не имеет — я ее использую просто как пустышку. Первая закомментированная строка должна быть выполнена для обнуления переменной-счетчика.
Получим:
n month 1 2010.02 2 2010.03 3 2010.04 4 2010.05 5 2010.06 6 2010.07 7 2010.08 8 2010.09 9 2010.10 10 2010.11
Теперь эту таблицу вы можете сджойнить с вашей таблицей по вашим условиям. В случае, если заранее не знаете необходимого кол-ва месяцев(строк), то добавьте условия минимальной и максимальной даты.
Итоговый запрос будет вида:
set @rownumber:=null;
select DATE_FORMAT(t.fday,'%Y.%m') as mon,count(e.id)
from
(
select
@rownumber:=if(@rownumber is null,0,@rownumber+1) n,
date_add('2010-01-01', interval @rownumber month) fday,
date_add('2010-01-01', interval @rownumber+1 month) lday
from information_schema.columns t
limit 12
) t
left join events e
on e.`start_date`<t.lday and e.end_date>=t.fday
group by t.fday
я боюсь такой запрос мои коллеги по работе не поймут.
Надо заставлять :) Все-таки умение работать с бд — это очень важная часть и, как правило, именно там возникают проблемы архитектуры
да в том-то и дело, что все эти фреймворки сводят к минимуму общение с БД. Да и задачи зачастую не те стоят — небольшие проекты с небольшим кол-вом данных. В общем мозги костенеть начинают. Часто слышишь — НАфига париться если можно 20 запросов написать.
У меня вот вопрос возник — есть ли на хабре блог куда такие задачи можно постить — поставил задачу, показал как ее решил и коллективное и сознательное поправило. Такие задачи часто встречаются — когда ты вроде и сделал, но чувствуешь, что что-то не то. Или это не в формате Хабра?
У меня вот вопрос возник — есть ли на хабре блог куда такие задачи можно постить — поставил задачу, показал как ее решил и коллективное и сознательное поправило. Такие задачи часто встречаются — когда ты вроде и сделал, но чувствуешь, что что-то не то. Или это не в формате Хабра?
И, кстати, функция не нужна. Достаточно «case when then» — так будет быстрее.
пример:
пример:
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
...
FROM test t
функция нужна, я что-то не понял чего сложного в функции та? неушто она такая сложная оказалась? функция нужна для лаконичной записи самого кверика. мне всегда казалось, что их для этого и создали.
Функция изначально будет работать дольше. Наоборот, она не сложная, она просто не нужна — достаточно одного простого case. В крайнем случае, если уж сильно хочется, то могли бы создать процедуру с курсорами, которая делала бы сложные однопроходные выборки, которые нужны. Или создали бы аггрегирующую UDF.
ну данный запрос работает неправильно, а вы попробуйте написать правильный запрос — получите такооой кверик, что первый кто на него взглянет впадет в ступор, а так все упрятоно в функцию. И я не уверен, что функция будет работать медленее
В чем он работает неправильно? Единственно, что в нем нужно заменить ">" на ">=".
И в какой ступор вы впадаете увидев:
Тут не более страшно, чем у вас. С 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 test t
* This source code was highlighted with Source Code Highlighter.Тут не более страшно, чем у вас. С If будет еще и короче.
подумал еще раз над Вашим запросом, потестировал его — все верно. приношу свои извинения за свою дремучесть и большое спасибо за наставление на путь истиный и за потраченое время. Я вставлю запрос как вариант решения.
Это, пожалуй, лучший вариант.
Его еще можно немного улучшить, если заменить пары условий с AND на условия с BETWEEN.
И «else 0» тут не обязательно, можно опустить.
И если «else 0» опустить, то в этом случае вместо SUM, скорее всего, можно будет использовать COUNT.
Его еще можно немного улучшить, если заменить пары условий с AND на условия с BETWEEN.
И «else 0» тут не обязательно, можно опустить.
И если «else 0» опустить, то в этом случае вместо SUM, скорее всего, можно будет использовать COUNT.
BETWEN нельзя — не то вхождение, ну уж count() и подавно
И с BETWEEN можно, но придется по-другому переписать запрос. При этом он будет менее нагляден (придется хардкодить порядковые номера первых/последних дней месяцев начиная с прошлого года), это недостаток конечно, но количество условий в запросе будет меньше. Впрочем да, на использовании BETWEEN я не настаиваю в данном случае, наглядность важнее копеечной выгоды от уменьшения количества условий на небольших наборах данных.
А вон COUNT вместо SUM должен прекрасно работать даже на этом примере (с условиями с AND), но только если убрать «else 0». Попробуйте.
А вон COUNT вместо SUM должен прекрасно работать даже на этом примере (с условиями с AND), но только если убрать «else 0». Попробуйте.
Можно и count, я показывал для наглядности и читабельности, чтобы можно было понять остальные возможности — легко добавить вариации и условия.
А так можно и просто count(if(… and ...,1,null))
А так можно и просто count(if(… and ...,1,null))
Да, все правильно. А еще можно к Вашему решению добавить дополнительное условие в конце:
Это для того, чтобы не делать выборку событий, которые прошли в прошлом году, или начинаются лишь в следующем году. А то иначе для этих лишних данных придется впустую вызывать кучу проверок, учитывать их при вычислении сумм и т.д.
Кстати, ниже я привел еще несколько альтернативных решений (заинтересовала меня эта задачка).
WHERE start_date < '2011-01-01' AND end_date >= '2010-01-01'
Это для того, чтобы не делать выборку событий, которые прошли в прошлом году, или начинаются лишь в следующем году. А то иначе для этих лишних данных придется впустую вызывать кучу проверок, учитывать их при вычислении сумм и т.д.
Кстати, ниже я привел еще несколько альтернативных решений (заинтересовала меня эта задачка).
а почему функция будет работать дольше?
В общем, различных вариантов решения данной задачи довольно много.
Вот, кстати, альтернативное решение.
Если что, заранее прошу прощения, я его не проверял, так что потенциально возможны опечатки/ошибки, но смысл, думаю, должен быть понятен.
Конечно, вряд ли это будет работать быстрее, чем предыдущее решение с кучей условий, нужно проверять.
Вот, кстати, альтернативное решение.
Если что, заранее прошу прощения, я его не проверял, так что потенциально возможны опечатки/ошибки, но смысл, думаю, должен быть понятен.
SELECT
SUM(x >> 11 & 1) AS jan
SUM(x >> 10 & 1) AS feb,
SUM(x >> 9 & 1) AS mar,
SUM(x >> 8 & 1) AS apr,
SUM(x >> 7 & 1) AS may,
SUM(x >> 6 & 1) AS jun,
SUM(x >> 5 & 1) AS jul,
SUM(x >> 4 & 1) AS aug,
SUM(x >> 3 & 1) AS sep,
SUM(x >> 2 & 1) AS oct,
SUM(x >> 1 & 1) AS nov,
SUM(x & 1) AS dec
FROM (
SELECT
IF(start_date >= '2010-01-01', 0xfff >> MONTH(start_date) - 1, 0xfff) &
IF(end_date < '2011-01-01', 0xfff << 12 - MONTH(start_date), 0xfff) AS x
FROM test
WHERE start_date < '2010-01-01' AND end_date >= '2010-01-01'
);
Конечно, вряд ли это будет работать быстрее, чем предыдущее решение с кучей условий, нужно проверять.
Упс, сразу заметил опечатку, условие в подзапросе должно быть:
WHERE start_date < '2011-01-01' AND end_date >= '2010-01-01'
Зачем городить огород? это тоже самое что и с кейсом, но добавляя подзапрос и лишние битовые операции. При том что и так есть даты начала и конца, незачем генерировать еще и массив битов для каждого месяца.
Еще одно альтернативное решение, подобное тому, что было ранее (но с одним условием с BETWEEN, вместо двух условий с AND, как я и писал выше).
Возможно, оно более наглядное, хотя оно скорее всего и не лучше по производительности, нужно проверять.
Возможно, оно более наглядное, хотя оно скорее всего и не лучше по производительности, нужно проверять.
SELECT
COUNT(IF(201001 BETWEEN a AND b, 1, NULL)) AS jan,
COUNT(IF(201002 BETWEEN a AND b, 1, NULL)) AS feb,
COUNT(IF(201003 BETWEEN a AND b, 1, NULL)) AS mar,
COUNT(IF(201004 BETWEEN a AND b, 1, NULL)) AS apr,
COUNT(IF(201005 BETWEEN a AND b, 1, NULL)) AS may,
COUNT(IF(201006 BETWEEN a AND b, 1, NULL)) AS jun,
COUNT(IF(201007 BETWEEN a AND b, 1, NULL)) AS jul,
COUNT(IF(201008 BETWEEN a AND b, 1, NULL)) AS aug,
COUNT(IF(201009 BETWEEN a AND b, 1, NULL)) AS sep,
COUNT(IF(201010 BETWEEN a AND b, 1, NULL)) AS oct,
COUNT(IF(201011 BETWEEN a AND b, 1, NULL)) AS nov,
COUNT(IF(201012 BETWEEN a AND b, 1, NULL)) AS dec
FROM (
SELECT EXTRACT(YEAR_MONTH FROM start_date) AS a, EXTRACT(YEAR_MONTH FROM end_date) AS b
FROM test
WHERE start_date < '2011-01-01' AND end_date >= '2010-01-01'
)
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Подсчет количества событий календаря в каждом месяце года