Подсчет количества событий календаря в каждом месяце года

    Постановка задачи:
    вывести количество событий (events) каждого месяца года.

    Каждое событие имеет два поля
    — start_date — дата начала события
    — end_date — дата завершения события

    Структура таблички с событиями календаря:
    CREATE TABLE `events` (
      `id` int(11) unsigned NOT NULL auto_increment,
      `start_date` date default NULL,
      `end_date` date default NULL,
      `created` datetime default NULL,
      `modified` datetime default NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8


    * This source code was highlighted with Source Code Highlighter.


    Ожидаемый результат:
    January 21 February 34 March 47
    May 8 June 12 July 23
    August 56 September 11 October 35
    November 34 Desember 24


    Возможные варианты:

    — MONTH(start_date) = MONTH(end_date), YEAR(start_date) = YEAR(end_date);
    Событие начинается и заканчивается в и том же месяце одного года.

    — MONTH(start_date) < MONTH(end_date), YEAR(start_date) = YEAR(end_date);
    Событие начинается в одном месяце, а заканчивается в другом месяце одного года.

    — MONTH(start_date) = MONTH(end_date), YEAR(start_date) < YEAR(end_date);
    Событие начинается и заканчивается в одном и том же месяце но разных лет (просто длится год).

    — MONTH(start_date) < MONTH(end_date), YEAR(start_date) < YEAR(end_date);
    Событие начинается и заканчивается в разных месяцах разных лет (началось в декабре, закончилось в январе).

    Решение:
    Решить данную задачу можно двумя способами: вывести значения в строку (каждый столбец — месяц), либо в столбик (каждая строка — месяц с количеством событий).
    Намучившись с JOINами было принято решение выводить результаты в строку (неограниченная длительность события стала проблеммой в использовании JOIN). То есть решили решать задачу в «лоб», ограничивать события по году и проверять в каких месяцах проходит данное событие. Для проверки вхождения была написана MYSQL function.

    Реализация:
    DELIMITER $$

    DROP FUNCTION IF EXISTS `isEventInMonth`$$

    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$$

    DELIMITER ;


    * This source code was highlighted with Source Code Highlighter.


    Эта функция на вход получает дату начала события, дату конца события, месяц и год, по которым необходимо выполнить проверку, и в качестве результата возвращает 0 либо 1. Иными словами, функция проверяет вхождение текущего месяца в интервал начала и конца события.

    Использование:

    SELECT
    SUM(isEventInMonth(start_date,end_date,1,2011)) AS jan,
    SUM(isEventInMonth(start_date,end_date,2,2011)) AS feb,
    SUM(isEventInMonth(start_date,end_date,3,2011)) AS mar,
    SUM(isEventInMonth(start_date,end_date,4,2011)) AS apr,
    SUM(isEventInMonth(start_date,end_date,5,2011)) AS may,
    SUM(isEventInMonth(start_date,end_date,6,2011)) AS jun,
    SUM(isEventInMonth(start_date,end_date,7,2011)) AS jul,
    SUM(isEventInMonth(start_date,end_date,8,2011)) AS aug,
    SUM(isEventInMonth(start_date,end_date,9,2011)) AS sep,
    SUM(isEventInMonth(start_date,end_date,10,2011)) AS 'oct',
    SUM(isEventInMonth(start_date,end_date,11,2011)) AS nov,
    SUM(isEventInMonth(start_date,end_date,12,2011)) AS 'dec'
    FROM EVENTS WHERE YEAR(start_date) = 2011 OR YEAR(end_date) = 2011


    * This source code was highlighted with Source Code Highlighter.


    Результат будет в строку, в каждом столбце количество событий в месяц конкретного года.

    Резюмируя:
    Реализация не притендует на элегантность, я думаю, что кому-нибудь станет полезной при реализации такой стандартной функциональности как календарь событий.
    Вопросы и улучшения приветствуются.
    PS. не ищите в табличке поля title и description — это переводимые поля и в данной таблице их нет.

    UPD:

    Пользователь xtender предложил более правильный вариант решения задачи.

    SELECT<br>
    sum(CASE when t.`start_date`<'2010-02-01' and t.end_date>='2010-01-01' then 1 else 0 end) AS jan,<br>
    sum(CASE when t.`start_date`<'2010-03-01' and t.end_date>='2010-02-01' then 1 else 0 end) AS feb,<br>
    sum(CASE when t.`start_date`<'2010-04-01' and t.end_date>='2010-03-01' then 1 else 0 end) AS mar,<br>
    sum(CASE when t.`start_date`<'2010-05-01' and t.end_date>='2010-04-01' then 1 else 0 end) AS apr,<br>
    sum(CASE when t.`start_date`<'2010-06-01' and t.end_date>='2010-05-01' then 1 else 0 end) AS may,<br>
    sum(CASE when t.`start_date`<'2010-07-01' and t.end_date>='2010-06-01' then 1 else 0 end) AS jun,<br>
    sum(CASE when t.`start_date`<'2010-08-01' and t.end_date>='2010-07-01' then 1 else 0 end) AS jul,<br>
    sum(CASE when t.`start_date`<'2010-09-01' and t.end_date>='2010-08-01' then 1 else 0 end) AS aug,<br>
    sum(CASE when t.`start_date`<'2010-10-01' and t.end_date>='2010-09-01' then 1 else 0 end) AS sep,<br>
    sum(CASE when t.`start_date`<'2010-11-01' and t.end_date>='2010-10-01' then 1 else 0 end) AS oct,<br>
    sum(CASE when t.`start_date`<'2010-12-01' and t.end_date>='2010-11-01' then 1 else 0 end) AS nov,<br>
    sum(CASE when t.`start_date`<'2011-01-01' and t.end_date>='2010-12-01' then 1 else 0 end) AS dec<br>
    FROM events t
    <br>
    <br>
    * This source code was highlighted with Source Code Highlighter.

    Похожие публикации

    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 47

      +4
      Событие не может длиться больше года?
        0
        нет не может, я не указал это в постановке задачи, но в данном случае можно немного исправить функцию.
        0
        Доброго времени суток. Я попробовал использовать Вашу функцию. По-моему, она работает не совсем корректно. У меня событие длится с 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
          +1
          Запостив свой комментарий, понял, что видимо просто символы «больше» и «меньше» не запостились в некоторых местах у вас в функции. Как, впрочем, и у меня. Видимо, НЛО съело их :)
          Поправьте плиз свой вариант, а то ведь люди скопируют неверно :)
          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;
            0
            просто меня учили, что из php один запрос лучше 12, а мне надо было выводить за текущий год и за следующий, = 24 запроса не есть гууд.
            за замечание большое спасибо, исправил.
              +2
              как по мне вопрос лучшей производительности одного или двенадцати запросов никак не связан с языком программирования. В частности для пхп пофиг сколько из него сделают запросов. Зато иногда бывают такие JOIN'ы что 12 простых запросов отработают быстрее. Да еще и функции… Кип ит симпл :)
                0
                это Вы зря 8) я думаю один простой запрос, а в этом случае он простой, будет быстрее чем 12 простых. И в что касается пхп тут многое зависит от типа конекшена.
                0
                Если Вас заботит производительность данной функциональности, рекомендую сделать следующее:

                1. Используйте 12 простых запросов, но при этом применяя mysqli multiquery
                2. Используйте 12 простых запросов, но при этом применяя mysqli prepared statements
                3. Выберите из этих решений то, которое работает на Ваших наборах данных быстрее.
                4. Потом сравните это решение (с 12 простыми запросами) с Вашим сложным решением (с одним запросом и функциями).
                5. Если будет не сложно, потом пожалуйста напишите сюда цифры замеров производительности, что получилось на Ваших наборах данных, интересно будет посмотреть.
                  0
                  В поставленной задаче требуется лишь один проход, так что 12 проходов явно будет дольше, если таблица не секционирована по датам так чтобы запросы можно было выполнять параллельно, но это уже отдельная и достаточно большая тема.
                    0
                    Да, то, что одна выборка, в общем случае, быстрее, чем 12 или 24 — с этим нельзя спорить, это — истина.

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

                    Также для эксперимента можно попробовать несколько (хоть все 12 или 24) простых запросов, объединенных в один через UNION ALL. Но сказать однозначно что выгоднее (на очень малых наборах данных) можно лишь проведя серию экспериментов с разными решениями, таково мое мнение.

                    Впрочем, ниже привели пример запроса с CASE, вот это намного выгоднее, чем использование функции, особенно если AND заменят на BETWEEN, хоть и придется хардкодить данные. Вероятно, это лучший вариант с точки зрения производительности.
                      0
                      с чего Вы взяли, что функции тормозят запрос? об этом я нигде прочитать не смог и на собственном опыте не смог выявить.
                      По поводу данных — я программист а не предсказатель и сколько там данных будет не знаю, могу догадываться, что будет много, так как это единая БД для более десятка сайтов. Да и вообще думается мне, что для небольшого набора данных все едино — их даже и в расчет не беру. BETWEEN — тут не катит, посмотрите какое там вхождение в интервал. Писать UNION 12 запросам — это не наш метод.
                      По поводу нескольких запросов — стараюсь большую часть работы с данными БД возложить на саму MYSQL и возвращать уже необходимый результат, она в общем и потому СУБД, что с этим справляется хорошо. Посему часто пользуюсь и VIEW и процедурами и функциями и тригерами. Очень неудобно, что во view нельзя делать подзапросы, но это тема для другого разговора.
                        0
                        Я не утверждаю, что во всех случаях функции тормозят. Но в том или ином конкретном случае могут тормозить. Это можно выяснить поставив эксперимент, и сделав замеры на Ваших данных.

                        По поводу программиста-предсказалеля. Если для Вас важен вопрос производительности, то при проектировании хранилища данных и составлении запросов первый же вопрос, который должен быть задан, это какими объемами данных придется оперировать.
                          0
                          Функции и процедуры будут работать медленнее единого блока SQL по определению — в силу того, что все они выполняются в отдельности и к ним нельзя применять оптимизации и разные планы выполнения, их нельзя свернуть или развернуть для использования специфичных индексов или уменьшения обрабатываемых данных. Исключение, пожалуй, может составить только код, результат которого зависит исключительно от входных параметров — тогда со стороны СУБД возможны оптимизации и кеширование результатов.
                      0
                      я же писал, у меня будет не 12, а 24 прохода, так как вывести надо по 2 годам — текущему и следующему, а это явно будет хуже, чем один простой запрос.
                      0
                      Один оптимизированный запрос лучше в случаях, если это не порождает долгих блокировок
                    0
                    По месяцам:

                    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
                      +1
                      Самый большой вопрос по теме, как оптимальнее показать в текущем месяце событие, начинающееся в прошлом месяце и заканчивающееся в следующем. У Вас же просто группировка по месяцу start_date.
                        0
                        С помощью дополнительного условия «текущий_месяц BETWEEN месяц_начала AND месяц_окончания». Собственно, для вывода количества событий в календаре этого условия будет достаточно (оно захватит в себя все активные события этого месяца).

                        Конструкция вида «STR_TO_DATE(DATE_FORMAT(date, 'date_format'), 'date_format')» представляет из себя аналог функции обрезания даты (по аналогии с другими СУБД, например, функции TRUNC() для Oracle).

                        А для оптимизации работы можно было бы хранить значение текущего месяца в отдельном поле — тогда появилась бы возможность использовать еще и индексы.
                          0
                          Всё равно GROUP BY R_MONTH_NAME привяжет запись только к старовому месяцу, а не к каждому из тех, для которых это событие было активным
                            0
                            Выводит список месяцев и кол-во активных событий (которые начались, закончились или продолжаются в соответствующий период):

                            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 — таблица со списком событий
                              0
                              Не надо делать лишних конвертаций str_to_date и date_to_str, лучше воспользоваться last_day — будет почти в два раза быстрее.
                                0
                                кажется у Вас все еще сложнее
                                  0
                                  У меня-то как раз простой запрос на объединение двух таблиц с одним BETWEEN-условием, работающий при любых периодах для вывода и без каких-либо ограничений по использованию. Если использовать совет xtender'а по замене STR_TO_DATE(DATE_FORMAT()) на LAST_DAY, то код визуально уменьшится и будет выполняться в 2-3 раза быстрее.

                                  А вот у вас — куча левого кода, велосипед для определения, находится ли дата между двумя другими датами (самопальная реализация BETWEEN), куча ограничений на использование данной методики и КУЧА SQL-кода. Одним словом — костыль, которому просто не место в продакшене (коллеги будут без ума от счастья увидев код функции isEventInMonth).
                        +2
                        В таких случаях лучше сначала генерировать таблицу для группировки. Покажу на примере для 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, которая значения не имеет — я ее использую просто как пустышку. Первая закомментированная строка должна быть выполнена для обнуления переменной-счетчика.
                        Получим:
                        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
                        

                        Теперь эту таблицу вы можете сджойнить с вашей таблицей по вашим условиям. В случае, если заранее не знаете необходимого кол-ва месяцев(строк), то добавьте условия минимальной и максимальной даты.
                          0
                          Итоговый запрос будет вида:
                          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

                            0
                            я боюсь такой запрос мои коллеги по работе не поймут.
                              0
                              Надо заставлять :) Все-таки умение работать с бд — это очень важная часть и, как правило, именно там возникают проблемы архитектуры
                                0
                                да в том-то и дело, что все эти фреймворки сводят к минимуму общение с БД. Да и задачи зачастую не те стоят — небольшие проекты с небольшим кол-вом данных. В общем мозги костенеть начинают. Часто слышишь — НАфига париться если можно 20 запросов написать.

                                У меня вот вопрос возник — есть ли на хабре блог куда такие задачи можно постить — поставил задачу, показал как ее решил и коллективное и сознательное поправило. Такие задачи часто встречаются — когда ты вроде и сделал, но чувствуешь, что что-то не то. Или это не в формате Хабра?
                                  0
                                  Ну или QA или sql.ru
                          +1
                          И, кстати, функция не нужна. Достаточно «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


                            0
                            функция нужна, я что-то не понял чего сложного в функции та? неушто она такая сложная оказалась? функция нужна для лаконичной записи самого кверика. мне всегда казалось, что их для этого и создали.
                              +1
                              Функция изначально будет работать дольше. Наоборот, она не сложная, она просто не нужна — достаточно одного простого case. В крайнем случае, если уж сильно хочется, то могли бы создать процедуру с курсорами, которая делала бы сложные однопроходные выборки, которые нужны. Или создали бы аггрегирующую UDF.
                              0
                              ну данный запрос работает неправильно, а вы попробуйте написать правильный запрос — получите такооой кверик, что первый кто на него взглянет впадет в ступор, а так все упрятоно в функцию. И я не уверен, что функция будет работать медленее
                                +4
                                В чем он работает неправильно? Единственно, что в нем нужно заменить ">" на ">=".
                                И в какой ступор вы впадаете увидев:
                                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 будет еще и короче.
                                  +1
                                  подумал еще раз над Вашим запросом, потестировал его — все верно. приношу свои извинения за свою дремучесть и большое спасибо за наставление на путь истиный и за потраченое время. Я вставлю запрос как вариант решения.
                                    0
                                    Всегда рад помочь.
                                    –1
                                    Это, пожалуй, лучший вариант.

                                    Его еще можно немного улучшить, если заменить пары условий с AND на условия с BETWEEN.
                                    И «else 0» тут не обязательно, можно опустить.
                                    И если «else 0» опустить, то в этом случае вместо SUM, скорее всего, можно будет использовать COUNT.
                                      0
                                      BETWEN нельзя — не то вхождение, ну уж count() и подавно
                                        0
                                        И с BETWEEN можно, но придется по-другому переписать запрос. При этом он будет менее нагляден (придется хардкодить порядковые номера первых/последних дней месяцев начиная с прошлого года), это недостаток конечно, но количество условий в запросе будет меньше. Впрочем да, на использовании BETWEEN я не настаиваю в данном случае, наглядность важнее копеечной выгоды от уменьшения количества условий на небольших наборах данных.

                                        А вон COUNT вместо SUM должен прекрасно работать даже на этом примере (с условиями с AND), но только если убрать «else 0». Попробуйте.
                                        0
                                        Можно и count, я показывал для наглядности и читабельности, чтобы можно было понять остальные возможности — легко добавить вариации и условия.
                                        А так можно и просто count(if(… and ...,1,null))
                                          0
                                          Да, все правильно. А еще можно к Вашему решению добавить дополнительное условие в конце:

                                          WHERE start_date < '2011-01-01' AND end_date >= '2010-01-01'
                                          


                                          Это для того, чтобы не делать выборку событий, которые прошли в прошлом году, или начинаются лишь в следующем году. А то иначе для этих лишних данных придется впустую вызывать кучу проверок, учитывать их при вычислении сумм и т.д.

                                          Кстати, ниже я привел еще несколько альтернативных решений (заинтересовала меня эта задачка).
                                  0
                                  а почему функция будет работать дольше?
                                    0
                                    В общем, различных вариантов решения данной задачи довольно много.
                                    Вот, кстати, альтернативное решение.
                                    Если что, заранее прошу прощения, я его не проверял, так что потенциально возможны опечатки/ошибки, но смысл, думаю, должен быть понятен.

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


                                    Конечно, вряд ли это будет работать быстрее, чем предыдущее решение с кучей условий, нужно проверять.
                                      0
                                      Упс, сразу заметил опечатку, условие в подзапросе должно быть:

                                      WHERE start_date < '2011-01-01' AND end_date >= '2010-01-01'
                                      
                                        0
                                        Зачем городить огород? это тоже самое что и с кейсом, но добавляя подзапрос и лишние битовые операции. При том что и так есть даты начала и конца, незачем генерировать еще и массив битов для каждого месяца.
                                        0
                                        Еще одно альтернативное решение, подобное тому, что было ранее (но с одним условием с 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' 
                                        )
                                        
                                          0
                                          Зачем проверять :) тут опять добавляется подзапрос + 2 функции

                                        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                        Самое читаемое