MySQL - подсчет по месяцам (включая отсутствующие записи)

У меня есть этот ВЫБОР:

SELECT
  DATE_FORMAT(`created`, '%Y-%m') as byMonth,
  COUNT(*) AS Total 
FROM 
  `qualitaet`
WHERE
  `created` >= MAKEDATE(year(now()-interval 1 year),1) + interval 5 month
AND
  `status`=1
GROUP BY 
  YEAR(`created`), MONTH(`created`)
ORDER BY 
  YEAR(`created`) ASC

и получить этот результат:

| byMonth | Total |
| 2015-06 |   2   |
| 2015-09 |  12   |
| 2015-10 |   3   |
| 2015-12 |   8   |
| 2016-01 |   1   |

см. SQL-Fiddle здесь

Предложение WHERE важно, потому что в моем примере оно мне нужно как текущий финансовый год, начинающийся 1 июня.

Как видите, у меня нет записей за июль, август и ноябрь. Но мне нужны эти записи с нулевым значением в сумме.

Итак, мой результат должен выглядеть так:

| byMonth | Total |
| 2015-06 |   2   |
| 2015-07 |   0   |
| 2015-08 |   0   |
| 2015-09 |  12   |
| 2015-10 |   3   |
| 2015-11 |   0   |
| 2015-12 |   8   |
| 2016-01 |   1   |

есть ли способ получить этот результат?


mysql join count fiscal
person bernte    schedule 01.01.2016    source источник
comment
вам нужно будет либо создать таблицу календаря и использовать этот источник данных для левого соединения, чтобы получить набор результатов, либо использовать генератор последовательности, чтобы построить последовательность месяцев и снова левое соединение. См. этот пост на создание календарной таблицы. или это для генератора последовательности в mysql. оба похожи, один из них впоследствии получает многоразовую таблицу календаря   -  person amdixon    schedule 01.01.2016
comment
... или решить проблему отсутствия результатов на уровне представления (например, с помощью PHP)   -  person Strawberry    schedule 01.01.2016
comment
да я пропустил это ;)   -  person amdixon    schedule 01.01.2016

Ответы (1)


arrow_upward
2
arrow_downward

Вам нужно сгенерировать все нужные даты, а затем оставить свои данные для дат. Также обратите внимание, что важно поместить некоторые предикаты в предложение ON левого соединения, а другие — в предложение WHERE:

SELECT
  CONCAT(y, '-', LPAD(m, 2, '0')) as byMonth,
  COUNT(`created`) AS Total 
FROM (
  SELECT year(now())     AS y UNION ALL
  SELECT year(now()) - 1 AS y 
) `years`
CROSS JOIN (
  SELECT  1 AS m UNION ALL
  SELECT  2 AS m UNION ALL
  SELECT  3 AS m UNION ALL
  SELECT  4 AS m UNION ALL
  SELECT  5 AS m UNION ALL
  SELECT  6 AS m UNION ALL
  SELECT  7 AS m UNION ALL
  SELECT  8 AS m UNION ALL
  SELECT  9 AS m UNION ALL
  SELECT 10 AS m UNION ALL
  SELECT 11 AS m UNION ALL
  SELECT 12 AS m
) `months`
LEFT JOIN `qualitaet` q
ON YEAR(`created`) = y 
  AND MONTH(`created`) = m
  AND `status` = 1
WHERE STR_TO_DATE(CONCAT(y, '-', m, '-01'), '%Y-%m-%d') 
    >= MAKEDATE(year(now()-interval 1 year),1) + interval 5 month
  AND STR_TO_DATE(CONCAT(y, '-', m, '-01'), '%Y-%m-%d') 
    <= now()
GROUP BY y, m
ORDER BY y, m

Как работает вышеперечисленное?

  • CROSS JOIN создает декартово произведение между всеми доступными годами и всеми доступными месяцами. Это то, что вам нужно, вам нужны все комбинации года и месяца без пробелов.
  • LEFT JOIN добавляет к результату все qualitaet записей (если они существуют) и соединяет их с декартовым произведением год-месяц, полученным ранее. Здесь важно использовать такие предикаты, как предикат status = 1.
  • COUNT(created) подсчитывает только ненулевые значения created, т. е. когда LEFT JOIN не создает строк для любого заданного года-месяца, мы хотим получить в результате 0, а не 1, т. е. мы не хотим подсчитывать значение NULL.

Примечание о производительности

В приведенном выше примере интенсивно используются строковые операции и арифметика даты и времени в ваших предикатах ON и WHERE. Это не будет работать для большого количества данных. В этом случае вам следует предварительно обрезать и проиндексировать год-месяцы в таблице qualitaet и работать только с этими значениями.

person Lukas Eder    schedule 01.01.2016
comment
Привет, результат такой же, как мой первый результат :( - person bernte; 01.01.2016
comment
и теперь он начинается в 2016-01 - person bernte; 01.01.2016
comment
теперь он выглядит почти идеально! Только 2015-06 | 2 не хватает :( - person bernte; 01.01.2016
comment
@bernte: Ну, меня немного смутил ваш расчет интервала. В любом случае, если вы знаете дату начала, просто укажите ее... В конце концов, мое решение просто показывает идеи о том, как решить проблему такого рода. Возможно есть более простые решения. - person Lukas Eder; 01.01.2016
comment
Я сделал это, потому что не хочу менять код каждый год. с расчетом интервала он автоматически переходит в следующий финансовый год - person bernte; 01.01.2016
comment
Да я вижу. Я думаю, тогда имеет смысл не жестко кодировать годы в таблице years. Смотрите обновление, которое генерирует последние два года - person Lukas Eder; 01.01.2016
comment
можно что-нибудь сказать о спектакле? на таблице qualitaet много строк. LPAD не убийца? может быть, лучше, когда я делаю это как год, месяц, всего? - person bernte; 01.01.2016
comment
@bernte: LPAD здесь не убийца, потому что вы получите не более 12 строк. Убийцами являются YEAR(created) и MONTH(created), а также весь предикат WHERE. Вероятно, будет лучше, если вы сохраните столбец created также как индексированный столбец created-year-month VARCHAR(6). - person Lukas Eder; 01.01.2016