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

Question

У меня есть этот SELECT:

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 июня в моем примере.

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

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

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

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

Answer 1

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