Как сгруппировать строки по каждой дате, которой нет в записях?

Question

[Станислав Почепко]

Добрый день. Подскажите, как сделать группировку строк по дате (Y-m-d H:i:s) по каждым дням.

SELECT COUNT(id), DATE_FORMAT(date, %d) as dat
FROM table
GROUP BY 'dat'

Что бы было вида

01 - 5
02 - 6
03 - 8
04 - 0
05 - 0
06 - 0
07 - 12
08 - 0
...
29 - 7
30 - 0
31 - 12

То есть, когда в таблице нет записи в дни 4, 5, 6, 8, 30 - выводило 0.

Answer 1

[Макс]

IMHO напрямую никак. строка не возникнет из неоткуда.
вот если сгенерировать "табличку" с датами и с ней сделать LEFT JOIN - тогда будут пустые строки (с NULL, не 0).
собственно, все что для этого надо - это таблица-справочник с датами.

Answer 2

[heartdevil]

Привет.

Вот пример того, что вы хотите.

Алгоритм такой же как описал Максим. По другому можно с юнионами, но там границы месяца будут не точные.

-- Генерируем временную таблицу с датами для примера
CREATE TEMPORARY TABLE temp_t (tdate DATE);
INSERT INTO temp_t (tdate) VALUES ('2015-09-02');
INSERT INTO temp_t (tdate) VALUES ('2015-09-02');
INSERT INTO temp_t (tdate) VALUES ('2015-09-05');
INSERT INTO temp_t (tdate) VALUES ('2015-09-05');
INSERT INTO temp_t (tdate) VALUES ('2015-09-07');

-- Пишим процедуру генерации дней. На вход идет дата и по ней генерируется таблица с 1 по последний день месяца этой даты
DROP PROCEDURE IF EXISTS buildMonthTable;
DELIMITER $$
CREATE PROCEDURE buildMonthTable(IN indate DATE)
BEGIN
DECLARE lastday DATE DEFAULT LAST_DAY(indate);
DECLARE startday DATE DEFAULT DATE_ADD(indate, INTERVAL - DAYOFMONTH(indate) +1 DAY);
DROP TABLE IF EXISTS temp_month_days;
CREATE TEMPORARY TABLE temp_month_days (mday DATE);
WHILE (lastday >= startday) DO
   INSERT INTO temp_month_days VALUES (startday);
   SET startday = DATE(DATE_ADD(startday, INTERVAL +1 DAY));
 END WHILE;
 END$$
DELIMITER;

-- Вызываем процедуру для генерации таблицы для текущего месяца
CALL buildMonthTable(CURDATE())

-- Соединяем две таблицы и группируем по датам
SELECT tm.mday, COUNT(tt.tdate) AS COUNT FROM temp_month_days AS tm
LEFT JOIN temp_t tt ON tm.mday = tt.tdate
GROUP BY tm.mday

PS: такая генерация почему-то занимает некоторое время. Хотя, вроде, небольшая табличка.

Comments

[Станислав Почепко]

Думаю лучше это уже алгоритмические языком обработать

[heartdevil]

Станислав Почепко: Дело ваше). Как я понял, вам нужно было решение запросом, я написал запросом. А подпилить, думаю, проблем не составит. Попробуйте позапускать скрипт у себя. И в php. Посмотрите, как проще будет