Форум пользователей MySQL

ixman · 16.05.2017 09:40:09

Здравствуйте. Возникла проблема, знаний на её решение не хватает, поэтому решил обратиться к вам Надеюсь на вашу помощь. А теперь к сути проблемы.

1) Есть табличка со статистическими данными, которая будет хранить очень много записей. Всего скорее записей будет доходить до нескольких миллионов в день. Среди данных есть IP адреса, хранящиеся в числовом виде. Сама таблица пока в таком виде

2) Далее табличка с базой стран мира

3) Ещё таблица с базой IP адресов, где поле id соответствует полю id в табличке стран мира. Выглядит так:

Задача выборки состоит в следующем:

1. определить к какой стране относится IP адрес

2. посчитать сколько записей к какой стране относиться

3. посчитать долю процента каждой страны от общего числа записей

4. всё это дело сгруппировать по странам

Итог в общем-то хотелось бы получить примерно такой:

Что я пытался сделать.

Определение страны по сути шло с самой базой IP адресов и выглядит так

SELECT `name_ru`, `code` FROM `c_list` WHERE `id`= (SELECT `id` FROM (SELECT * FROM `ip_list` WHERE `start` <= '20185088' ORDER BY `start` DESC LIMIT 1) AS T WHERE `end` >= '20185088')

Далее результат я пытался получить таким способом

SELECT `v`.`ip`, (SELECT `id` FROM (SELECT * FROM `ip_list` AS `p` WHERE `start` <= `v`.`ip` ORDER BY `start` DESC LIMIT 1) AS `t` WHERE `end` >= `v`.`ip`) AS `cid` FROM `s_stat_visitors` AS `v` WHERE `v`.`sid` = '648'

Но в таком случае, если не ошибаюсь, ошибка номер 1054, где не известное поле `v`.`ip` в подзапросе на определение страны.

Ещё пробовал через LEFT JOIN

SELECT `c`.`name_ru`, `c`.`code` FROM `s_stat_visitors` AS `v` LEFT JOIN `c_list` AS `c` ON `c`.`id` = (SELECT `id` FROM (SELECT * FROM `ip_list` WHERE `start` <= `v`.`ip` ORDER BY `start` DESC LIMIT 1) AS T WHERE `end` >= `v`.`ip`) WHERE `v`.`sid` = '648' GROUP BY `с`.`code`

Но проблема остаётся такой же.

Вопрос почему в такой выборке не хочет видеть `v`.`ip` в подзапросе?

Буду рад любым советам и предложениям.

vasya · 17.05.2017 15:59:13

ixman написал:
Определение страны по сути шло с самой базой IP адресов и выглядит так

SELECT `name_ru`, `code` FROM `c_list` WHERE `id`= (SELECT `id` FROM (SELECT * FROM `ip_list` WHERE `start` <= '20185088' ORDER BY `start` DESC LIMIT 1) AS T WHERE `end` >= '20185088')

А зачем лишний подзапрос? Можно

SELECT `name_ru`, `code` FROM `c_list` WHERE `id`= (SELECT `id` FROM `ip_list` WHERE `start` <= '20185088' AND`end` >= '20185088'  ORDER BY `start` DESC LIMIT 1)

ixman написал:
Далее результат я пытался получить таким способом

SELECT `v`.`ip`, (SELECT `id` FROM (SELECT * FROM `ip_list` AS `p` WHERE `start` <= `v`.`ip` ORDER BY `start` DESC LIMIT 1) AS `t` WHERE `end` >= `v`.`ip`) AS `cid` FROM `s_stat_visitors` AS `v` WHERE `v`.`sid` = '648'

Но в таком случае, если не ошибаюсь, ошибка номер 1054, где не известное поле `v`.`ip` в подзапросе на определение страны.

Вопрос почему в такой выборке не хочет видеть `v`.`ip` в подзапросе?

сначала выполняется самый вложенный подзапрос, а в нем `v`.`ip` не определена. Т.е. ошибка та же, что и в предыдущем варианте - лишний вложенный подзапрпос.

SELECT  (SELECT id FROM `ip_list` AS `p` WHERE `start` <= `v`.`ip` AND `end` >= `v`.`ip` ORDER BY `start` DESC LIMIT 1) AS `страна`, count(*) as `посетители` FROM `s_stat_visitors` AS `v` group by 1; -- выдаст код страны из таблицы s_stat_visitors и кол-во посетителей

ixman · 19.05.2017 12:55:54

vasya написал:
А зачем лишний подзапрос? Можно

Говорю же с базой так шло, думал им виднее как должно быть.

Но я проблему решил немного иначе, я в таблицу с статистическими данными добавил поле с id страны. Оно будет определяться и записываться в момент сбора статистики. Так и выборка легче и подзапросов лишних не надо

Форум пользователей MySQL

#1 16.05.2017 09:40:09

Сложная выборка, помогите советом

#2 17.05.2017 15:59:13

Re: Сложная выборка, помогите советом

ixman написал:

ixman написал:

#3 19.05.2017 12:55:54

Re: Сложная выборка, помогите советом

vasya написал:

Board footer