SQLinfo.ru - Все о MySQL Фестиваль «Российские интернет-технологии» 2017

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 16.05.2017 09:40:09

ixman
Участник
Откуда: СССР
Зарегистрирован: 15.05.2017
Сообщений: 2

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

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

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

http://dl4.joxi.net/drive/2017/05/16/0018/1416/1205640/40/db5fe129ac.png

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

http://dl4.joxi.net/drive/2017/05/16/0018/1416/1205640/40/33eda4e9ba.png

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

http://dl3.joxi.net/drive/2017/05/16/0018/1416/1205640/40/5b33ec551e.png


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

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

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

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

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

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

http://dl3.joxi.net/drive/2017/05/16/0018/1416/1205640/40/972f282b53.png


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

Определение страны по сути шло с самой базой 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` в подзапросе?

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

Неактивен

 

#2 17.05.2017 15:59:13

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5164

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

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 и кол-во посетителей

Неактивен

 

#3 19.05.2017 12:55:54

ixman
Участник
Откуда: СССР
Зарегистрирован: 15.05.2017
Сообщений: 2

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

vasya написал:

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

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

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson