Mysql Не вижу ошибки в запросе

[grey109]

Всем привет!

Решил оптимизировать работу скрипта по сбору и обработке статистики на сайте. Час бьюсь, но не вижу почему неправильно работает запрос.

И так, есть 3 таблицы:

/* в таблицу записываются "сырые" данные о клика по ссылкам */
DROP TABLE IF EXISTS `clicks`;
CREATE TABLE IF NOT EXISTS `clicks` (
  `id_url` int(8) unsigned NOT NULL,
  `referer` varchar(250) COLLATE latin1_general_ci NOT NULL,
  KEY `id_url` (`id_url`),
  KEY `referer` (`referer`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `clicks` (`id_url`, `referer`) VALUES
(123, 'https://google.ru'),
(123, 'https://google.ru'),
(123, 'https://ya.ru');


/* таблица с уникальными адресами откуда были клики - для экономии места в итоговой таблице храним id, а не полный url*/
DROP TABLE IF EXISTS `referers`;
CREATE TABLE IF NOT EXISTS `referers` (
  `referer_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `referer` text COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`referer_id`),
  UNIQUE KEY `referer` (`referer`(255))
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

INSERT INTO `referers` (`referer_id`, `referer`) VALUES
(1, 'https://google.ru'),
(2, 'https://ya.ru');


/* итоговая таблица со статистикой */
DROP TABLE IF EXISTS `statistics`;
CREATE TABLE IF NOT EXISTS `statistics` (
  `id_url` int(8) unsigned NOT NULL,
  `referer_id` int(10) unsigned NOT NULL,
  `total_clicks` mediumint(8) unsigned NOT NULL,
  KEY `id_url` (`id_url`),
  KEY `referer_id` (`referer_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `statistics` (`id_url`, `referer_id`, `total_clicks`) VALUES
(123, 1, 100);

Сам запрос:

/* считаем по какой ссылке, сколько раз кликнули */
SELECT clicks.id_url, count(clicks.referer), referers.referer_id
FROM `clicks`
LEFT JOIN referers ON clicks.referer = referers.referer
LEFT JOIN statistics ON clicks.id_url = statistics.id_url
WHERE statistics.referer_id IS NULL
GROUP BY clicks.id_url, clicks.referer

По моей логике запрос должен вернуть результат "id_url=123, кликов=1, referer_id=2", т.е. информацию о кликах, о которых еще нет данных в таблице statistics. По факту mysql возвращает пустой результат.

Если заменить условие "statistics.referer_id IS NULL" на "referers.referer_id=statistics.referer_id", то запрос работает корректно (id_url=123, кликов=2, referer_id=1).

Ткните меня носом, где моя ошибка?

Спасибо.

 

[Фанат]

а если статистикс подвинуть повыше в запросе?
ну или вообще на время отладки убрать реферерс

 

[grey109]

SELECT clicks.id_url, count(clicks.referer)
FROM `clicks`
LEFT JOIN statistics ON clicks.id_url = statistics.id_url
WHERE statistics.referer_id IS NULL
GROUP BY clicks.id_url, clicks.referer

MySQL вернула пустой результат (т.е. ноль строк).

 

[WMix]

SELECT clicks.*, count(clicks.referer),
referers.referer_id, statistics.total_clicks, statistics.referer_id
FROM `clicks`
LEFT JOIN referers ON clicks.referer = referers.referer
LEFT JOIN statistics ON clicks.id_url = statistics.id_url
GROUP BY clicks.id_url, clicks.referer;

WHERE statistics.referer_id IS NULL ??

SQL Fiddle - Online SQL Compiler for learning & practice

Discover our free online SQL editor enhanced with AI to chat, explain, and generate code. Support SQL Server, MySQL, MariaDB, PostgreSQL, and SQLite.

sqlfiddle.com

 

[Фанат]

Погоди, действительно, почему
referer_id IS NULL а не id_url?

 

[grey109]

referer_id, потому что мне нужно получить адреса сайтов с которых были клики, но для которых еще нет данных в итоговой таблице statistics.

В statistics могут быть данные о клике с одним и тем же id, но источники переходов referer_id будут разные.

Исправил одну ошибку в первоначальной таблице. Вот актуальная версия.

SQL Fiddle - Online SQL Compiler for learning & practice

Discover our free online SQL editor enhanced with AI to chat, explain, and generate code. Support SQL Server, MySQL, MariaDB, PostgreSQL, and SQLite.

sqlfiddle.com

 

[WMix]

SELECT clicks.*, count(clicks.referer), statistics.*
FROM `clicks`
LEFT JOIN referers ON clicks.referer = referers.referer
LEFT JOIN statistics ON clicks.id_url = statistics.id_url and statistics.referer_id = referers.referer_id
where statistics.referer_id IS NULL
GROUP BY clicks.id_url, clicks.referer

 

[grey109]

Спасибо!!!