Выберите все месяцы в данном промежутке даты, включая тех с 0 значениями
Я пытаюсь записать запрос MySQL для получения среднего значения в месяц, в течение всех месяцев между на данные даты. Моя идея - это:
Запрос, что-то как
SELECT AVG(value1) as avg_value_1,
AVG(value2) as avg_value_2,
MONTH(save_date) as month,
YEAR(save_date) as year
FROM myTable
WHERE save_date BETWEEN '2009-01-01' AND '2009-07-01'
GROUP BY YEAR(save_date), MONTH(save_date)
avg_value_1 | avg_value_2 | month | year
5 | 4 | 1 | 2009
2 | 1 | 2 | 2009
7 | 5 | 3 | 2009
0 | 0 | 4 | 2009 <---
6 | 5 | 5 | 2009
3 | 6 | 6 | 2009
Вы видите, никакие значения не вводились в течение апреля 2009, все же я хочу, чтобы это обнаружилось как 0, 0 значений в выводе. Какие-либо идеи о том, как достигнуть этого? Это может быть сделано в MySQL?
2 ответа
Я согласен с ответом Ливена, создайте таблицу, содержащую все месяцы, которые вам могут потребоваться, и используйте это, чтобы «LEFT JOIN» к вашей таблице результатов. Помните, это действительно крошечная таблица, всего 365 (ish) строк данных в год, которые у вас есть ... И вы можете легко написать код для первоначального заполнения этой таблицы
Мы делаем это здесь, и это дает много преимуществ , например, представьте себе таблицу ежемесячных данных со следующими полями (и любыми другими, которые вы можете придумать!), полностью заполненными для всех месяцев в заданном диапазоне:
- Дата (например, 2009-04-01)
- День (Например, 1)
- День недели (например, среда)
- Месяц (например, 4)
- Год (например, 2009)
- Финансовый год (например, 2009/10)
- Финансовый квартал (например, 2009Q1)
- Календарный квартал (например, 2009Q2)
Затем объедините это с вашим запросом выше, как показано ниже:
SELECT `DT`.`myYear`, `DT`.`myMonth`,
AVG(`myTable`.`value1`) as avg_value_1,
AVG(`myTable`.`value2`) as avg_value_2
FROM `dateTable` as DT
LEFT JOIN `myTable`
ON `dateTable`.`myDate` = `myTable`.`save_date`
WHERE `dateTable`.`myDate` BETWEEN '2009-01-01' AND '2009-07-01'
GROUP BY `DT`.`myYear`, `DT`.`myMonth`
В моем коде SQL могут быть некоторые ошибки, поскольку я не смог создать тестовые таблицы, но, надеюсь, вы получите основную и переделку под свои нужды!
Используя это, вы можете изменить ваше предложение «GROUP BY» на то, что есть в таблице «dateTable», что позволит вам легко составлять отчеты по финансовым кварталам, месяцам, дням, дням недели и т. Д.
Надеюсь, это поможет!
Вероятно, самый простой способ - создать таблицу дат, содержащую месяцы и годы, и объединить ее с вашим окончательным результатом.