У меня есть таблица
___________________________________________ id | user | Visitor | timestamp ___________________________________________ 13 |username |abc | 2014-01-15 15:01:44 14 |username |abc | 2014-01-15 15:01:44 15 |username |abc | 2014-01-18 15:01:44 16 |username |abc | 2014-01-18 15:01:44 ___________________________________________ Я использовал QUERY для COUNT нет посетителя USER abc в течение последних 7 дней с СЕГОДНЯ.
SELECT DATE(`timestamp`) as `date`, COUNT(*) as `count` FROM `table` WHERE (`timestamp` >= (NOW() - INTERVAL 7 DAY)) AND (`user` = 'username') GROUP BY `date`;
Он получает следующий результат:
______________________________ date | count ______________________________ 2014-01-15 | 2 2014-01-18 | 2
Но мне нужно:
______________________________ date | count ______________________________ 2014-01-15 | 2 2014-01-16 | 0 // Make 0 for the day which is not present 2014-01-17 | 0 // Make 0 for the day which is not present 2014-01-18 | 2
Что будет для этого вопросом?
Используйте subquer для создания таблицы со всеми днями на прошлой неделе, затем присоедините это к своей таблице:
SELECT `date`, IFNULL(COUNT(*), 0) as `count` FROM (SELECT DATE(NOW()) AS `date` UNION SELECT DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) UNION SELECT DATE(DATE_SUB(NOW(), INTERVAL 2 DAY)) UNION SELECT DATE(DATE_SUB(NOW(), INTERVAL 3 DAY)) UNION SELECT DATE(DATE_SUB(NOW(), INTERVAL 4 DAY)) UNION SELECT DATE(DATE_SUB(NOW(), INTERVAL 5 DAY)) UNION SELECT DATE(DATE_SUB(NOW(), INTERVAL 6 DAY)) UNION SELECT DATE(DATE_SUB(NOW(), INTERVAL 7 DAY))) AS days LEFT JOIN `table` ON DATE(`timestamp`) = `date` WHERE (`timestamp` >= (NOW() - INTERVAL 7 DAY)) AND (`user` = 'username') GROUP BY `date`;
Это хорошо известная проблема о разрыве – и у нее много ответов на SO.
Во-первых, самый очевидный способ – использовать таблицу, которая будет удерживать все даты подряд (для текущего года в качестве образца). Например, пусть это будет таблица dates с полем record_date (она содержит дату), тогда ваш запрос будет выглядеть так:
SELECT DATE(`timestamp`) as `date`, COUNT(`dates`.`id`) as `count` FROM `dates` LEFT JOIN `table` ON `dates`.`record_date` = DATE(`table`.`timestamp`) WHERE (`timestamp` >= (NOW() - INTERVAL 7 DAY)) AND (`user` = 'username') GROUP BY `dates`.`record_date`
Так вы будете возвращать нули через LEFT JOIN .
Существует еще один, более сложный способ добиться этого с помощью запроса генератора последовательности. На самом деле в MySQL нет последовательностей, но вы можете генерировать последовательные значения из CROSS JOIN а затем применять их к выбору даты. Например:
SELECT DATE_ADD(CURDATE() - INTERVAL 1 WEEK, INTERVAL sequence.id DAY) FROM (SELECT (two_1.id + two_2.id + two_4.id + two_8.id + two_16.id) AS id FROM (SELECT 0 AS id UNION ALL SELECT 1 AS id) AS two_1 CROSS JOIN (SELECT 0 AS id UNION ALL SELECT 2 AS id) AS two_2 CROSS JOIN (SELECT 0 AS id UNION ALL SELECT 4 AS id) AS two_4 CROSS JOIN (SELECT 0 AS id UNION ALL SELECT 8 AS id) AS two_8 CROSS JOIN (SELECT 0 AS id UNION ALL SELECT 16 AS id) AS two_16 ) AS sequence WHERE sequence.id<7
– будут отображаться даты на предыдущей неделе, поэтому вы сможете использовать это вместо создания и заполнения временной таблицы, как в первую очередь. Хорошая вещь в этом вопросе – это статичность, поэтому вам не придется добавлять другую часть UNION если вы хотите увеличить интервал выбора.