У меня есть этот запрос:
SELECT COUNT(*) as clicks, DATE_FORMAT(FROM_UNIXTIME(click_date), '%w %M %Y') as point FROM tracking WHERE click_date < $end_date AND click_date > $start_date GROUP BY DAY(FROM_UNIXTIME(click_date)) Где $start_date – две недели назад, а $end_date – сегодняшняя дата.
Я пытаюсь найти все клики, сделанные каждый день для определенного диапазона дат. Я также хочу включить дни, когда не было кликов. Поскольку, естественно, нет записи для них в моей базе данных, мне нужно включить их как-то, как я могу это сделать, показывая все даты с даты начала до конца. Это то, что у меня сейчас есть, много пробелов в течение этого двухнедельного диапазона дат.
Array ( [0] => Array ( [clicks] => 17 [point] => 0 February 2011 ) [1] => Array ( [clicks] => 3 [point] => 1 February 2011 ) [2] => Array ( [clicks] => 14 [point] => 5 February 2011 ) [3] => Array ( [clicks] => 1 [point] => 1 February 2011 ) [4] => Array ( [clicks] => 8 [point] => 2 February 2011 ) )
Может ли это быть сделано с помощью чистого SQL-запроса или мне нужно использовать некоторую php-логику?
Кстати, почему у меня 0 February 2011 как мое первое свидание! Хм, у меня также есть дубликаты дат, этого не должно быть, может быть, моя GROUP BY работает неправильно?
Спасибо всем за любую помощь.
Может ли это быть сделано с помощью чистого SQL-запроса или мне нужно использовать некоторую php-логику?
Да, лучше создать таблицу Numbers (одиночный столбец N), который содержит только цифры от 0 до 999. Он может использоваться для многих вещей, не в последнюю очередь для запроса, такого как:
SELECT COUNT(t.click_date) as clicks, DATE_FORMAT(adddate($start_date, interval N day), '%d %M %Y') as point FROM Numbers LEFT JOIN tracking t ON t.click_date >= adddate($start_date, interval N day) and t.click_date < adddate($start_date, interval (N+1) day) WHERE N between 0 and datediff($start_date, $end_date) GROUP BY N
Кстати, почему у меня есть 0 февраля 2011 года как моя первая дата
Вы используете неправильный формат. Это ВЕРХНИЙ случай W не ниже для дня недели, поэтому «% W% M% Y» или «% d% M% Y» для дня месяца. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
возможно, моя GROUP BY работает неправильно?
Вы используете GROUP BY DAY(FROM_UNIXTIME(click_date)) обратите внимание на «день» , а не на рабочий день, но вы показываете (или пытаетесь) «% W» (день недели) – выбирайте один, не смешивайте их.
Примечание: N1, N2 и N3 ниже объединяются, чтобы дать возможный диапазон 0-999
SELECT COUNT(t.click_date) as clicks, DATE_FORMAT(adddate($start_date, interval N day), '%d %M %Y') as point FROM ( select N1 * 100 + N2 * 10 + N3 as N from ( select 0 N1 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) N1 cross join ( select 0 N2 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) N2 cross join ( select 0 N3 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) N3 ) Numbers LEFT JOIN tracking t ON t.click_date >= adddate($start_date, interval N day) and t.click_date < adddate($start_date, interval (N+1) day) WHERE N between 0 and datediff($start_date, $end_date) GROUP BY N
РЕДАКТИРОВАТЬ № 2: таблица с прямыми датами
Поместите это в новое окно в phpMyAdmin или запустите его как пакет. Он создает таблицу с именем Dates, с каждой датой с 1900-01-01 (или сменой сценария) до 2300-01-01 (или изменения).
DROP PROCEDURE IF EXISTS FillDateTable; delimiter // CREATE PROCEDURE FillDateTable() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN drop table if exists datetable; create table datetable (thedate datetime primary key, isweekday smallint); SET @x := date('1900-01-01'); REPEAT insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end; SET @x := date_add(@x, interval 1 day); UNTIL @x > date('2300-01-01') END REPEAT; END// delimiter ; CALL FillDateTable;
С такой таблицей полезности ваш запрос может быть просто
SELECT COUNT(t.click_date) as clicks, DATE_FORMAT(thedate, '%d %M %Y') as point FROM Dates LEFT JOIN tracking t ON t.click_date >= thedate and t.click_date < adddate(thedate, interval 1 day) WHERE thedate between $start_date and $end_date GROUP BY thedate
На мой взгляд, вам лучше делать этот тип логики в своем коде. Но если вы хотите сделать это в чистом SQL, вы можете построить запрос, чтобы дать вам результаты всех дней между днями и днями … либо путем вставки в временную таблицу, либо в таблицу памяти … затем влево присоединитесь к этим результатам, чтобы вы получали все дни независимо от того, были ли результаты на этот день.
Я бы придерживался логики PHP, зацикливаясь между самой низкой датой и наивысшей датой и увеличивая один день за раз.
Возможно, вы могли бы сделать это в SQL с некоторыми причудливыми объединениями, но я даже не начну считать эту гадость!
BTW, %w – день недели , начиная 0 = воскресенье. Вероятно, вы хотели %d .