SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 05.12.2016 07:14:58

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

выборка, группировка

Есть таблицы
product
http://dl2.joxi.net/drive/2016/12/05/0006/1977/436153/53/bee68150bd.jpg
записи
http://dl2.joxi.net/drive/2016/12/05/0006/1977/436153/53/9bf92f7bae.jpg

product_to_category
http://dl1.joxi.net/drive/2016/12/05/0006/1977/436153/53/bd7531b484.jpg
записи
http://dl1.joxi.net/drive/2016/12/05/0006/1977/436153/53/57370bbf43.jpg

надо сделать выборку поличество продуктов у категории по городам

то есть
у city_id = 1 по category_id = 1  => 1 продукт
у city_id = 1 по category_id = 2 => 2 продукта

Неактивен

 

#2 05.12.2016 11:23:36

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

Re: выборка, группировка

select city_id, category_id, count(*) from product join product_to_category on id=product_id group by city_id, category_id;

Неактивен

 

#3 29.12.2016 08:09:26

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: выборка, группировка

чет не получается у меня количество посчитать правильно вот запрос

SELECT v.city_id, v2c.category_id, c.parent_id AS parent_category_id, v.id
FROM product v
JOIN product_to_category v2c ON v.id = v2c.product_id
JOIN category c ON v2c.category_id = c.id
JOIN city city ON v.city_id = city.id
WHERE v.publish =1 AND v.city_id =501 AND c.parent_id =10 AND v.id
IN ( SELECT product_id FROM product_to_category WHERE category_id IN ( SELECT id FROM category WHERE parent_id = c.parent_id) )

вот какой запрос без количества

результат http://dl3.joxi.net/drive/2016/12/29/0006/1977/436153/53/081ab32e60.jpg


надо получить группировку по вакансии http://dl3.joxi.net/drive/2016/12/29/0006/1977/436153/53/42d5fc1276.jpg вот так будет и потом количество если добавить выборку уже должна быть 1 строка count = 2 чет не получается 2ку получить, а сейчас получаю 9

Отредактированно x-miller-x (29.12.2016 08:16:19)

Неактивен

 

#4 29.12.2016 09:35:37

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

Re: выборка, группировка

select count(*) from (select ... from (ваш запрос) t2 group by .. ) t1;

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

Неактивен

 

#5 29.12.2016 10:04:37

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: выборка, группировка

join city убрал остальное вроде нужное или как еще оптимизировать? + если убрать вот это условие AND v.city_id =501
AND c.parent_id =10 и захочу взять по всем категориям и городам количество получить он возвращает только одну строку

Отредактированно x-miller-x (29.12.2016 10:07:54)

Неактивен

 

#6 29.12.2016 10:16:49

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: выборка, группировка

еще один момент если в таблице много записей вот этот момент жестко тормозит
v.id
IN ( SELECT product_id FROM product_to_category WHERE category_id IN ( SELECT id FROM category WHERE parent_id = c.parent_id) )

Неактивен

 

#7 29.12.2016 11:43:02

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

Re: выборка, группировка

если "надо сделать выборку поличество продуктов у категории по городам", то подзапрос не нужен
покажите структуру таблиц в виде
show create table `имя таблицы`;
и набор тестовых данных
insert into..
и какой должен быть результат.

Неактивен

 

#8 29.12.2016 11:55:06

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: выборка, группировка

в первом посте есть структура таблицы product и product_to_category
третья таблица category,  :
http://dl4.joxi.net/drive/2016/12/29/0006/1977/436153/53/960d48ccd6.jpg


запрос изначально был такой

Код:

SELECT v.city_id, v2c.category_id, c.parent_id AS parent_category_id, v.id, count(*) as count
FROM product v
JOIN product_to_category v2c ON v.id = v2c.product_id
JOIN category c ON v2c.category_id = c.id
WHERE v.publish =1 AND  v.id
IN ( SELECT product_id FROM product_to_category WHERE category_id IN ( SELECT id FROM category WHERE parent_id = c.parent_id) )
GROUP BY v.city_id, c.parent_id

результат такой http://dl3.joxi.net/drive/2016/12/29/0006/1977/436153/53/0f6c22d33a.jpg

надо сделать выборку количества по city_id, parent_category_id
http://dl3.joxi.net/drive/2016/12/29/0006/1977/436153/53/b58d85828e.jpg

примерно так, но он как то криво работает когда много записей

Отредактированно x-miller-x (29.12.2016 12:13:19)

Неактивен

 

#9 29.12.2016 12:22:31

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

Re: выборка, группировка

у вас в запросе v2c.category_id и v.id выбираются случайным образом при группировке

"запрос изначально был такой" - это не постановка задачи. подозреваю, что запрос кривой, но я ведь не знаю, что нужно сделать.

Неактивен

 

#10 29.12.2016 12:27:28

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: выборка, группировка

вот пример с ошибкой без группировки и count(*) такой результат
у запроса

Код:

SELECT v.city_id, v2c.category_id, c.parent_id AS parent_category_id, v.id
FROM product v
JOIN product_to_category v2c ON v.id = v2c.product_id
JOIN category c ON v2c.category_id = c.id
JOIN city city ON v.city_id = city.id
WHERE v.publish =1 AND v.city_id =501 AND c.parent_id =10 AND v.id
IN ( SELECT product_id FROM product_to_category WHERE category_id IN ( SELECT id FROM category WHERE parent_id = c.parent_id) )

http://dl4.joxi.net/drive/2016/12/29/0006/1977/436153/53/0cc70be22e.jpg

а с группировкой получается

Код:

SELECT v.city_id, v2c.category_id, c.parent_id AS parent_category_id, count(*) as count
FROM product v
JOIN product_to_category v2c ON v.id = v2c.product_id
JOIN category c ON v2c.category_id = c.id
JOIN city city ON v.city_id = city.id
WHERE v.publish =1 AND v.city_id =501 AND c.parent_id =10 AND v.id
IN ( SELECT product_id FROM product_to_category WHERE category_id IN ( SELECT id FROM category WHERE parent_id = c.parent_id) )
GROUP BY v.city_id, c.parent_id

получается так
http://dl3.joxi.net/drive/2016/12/29/0006/1977/436153/53/283c12737a.jpg
а надо вместо count=9, 2 так как всего 2 продукта 9 записей

Неактивен

 

#11 29.12.2016 12:39:34

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

Re: выборка, группировка

тогда группировку нужно делать ещё и по продукту
GROUP BY v.city_id, c.parent_id, v.id

Неактивен

 

#12 29.12.2016 13:05:50

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: выборка, группировка

пробовал получается так http://dl3.joxi.net/drive/2016/12/29/0006/1977/436153/53/7b0e3bb4f6.jpg

Неактивен

 

#13 29.12.2016 13:10:25

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: выборка, группировка

Код:

SELECT COUNT(*) FROM (SELECT v.city_id, v2c.category_id, c.parent_id AS parent_category_id,v.id
FROM product v
JOIN product_to_category v2c ON v.id = v2c.product_id
JOIN company_category c ON v2c.category_id = c.id
WHERE v.publish =1
AND v.city_id =501
AND v.id
IN (

SELECT product_id
FROM product_to_category
WHERE category_id
IN (

SELECT id
FROM company_category
WHERE parent_id = c.parent_id
)
)
GROUP BY v.city_id, c.parent_id,  v.id) t1

так тоже не канает

Отредактированно x-miller-x (29.12.2016 13:10:56)

Неактивен

 

#14 29.12.2016 13:12:08

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

Re: выборка, группировка

count(distinct v.id)
GROUP BY v.city_id, c.parent_id

Неактивен

 

#15 30.12.2016 12:08:09

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: выборка, группировка

ага спасибо, теперь работает корректно, еще можете подсказать как оптимизировать такой запрос

Код:

SELECT count(*)
FROM `product`
WHERE ((((`product`.`id` in (
                    select product_id from product_to_city
                    join city on (city.id = product_to_city.city_id)
                    where city.id = 1 or city.parent_id = 1
                )))) AND (`product`.`publish` = 1 AND (`product`.`delete` = 0 OR `product`.`delete`
is null))) AND (product.id in (select product_id from product_to_category
where category_id in (select id from category where parent_id in
(11))))

p.s. индексы все расставлены

переписал на exists но тут вот эта часть медленно выполняется

Код:

select product_id from product_to_city
                    join city on (city.id = product_to_city.city_id)
                    where city.id = 1 or city.parent_id = 1

в таблице product_to_city 1,3 млн записей

Отредактированно x-miller-x (30.12.2016 14:07:10)

Неактивен

 

#16 30.12.2016 17:16:17

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

Re: выборка, группировка

показывайте структуру и план запроса

Неактивен

 

#17 06.02.2017 09:06:42

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: выборка, группировка

Код:

SELECT `product`.`id`, `product`.`name`, `product`.`condition`, `product`.`city_id`,  `product`.`price`,`user`.`name`  AS `user_name`, `city`.`name` AS `city_name`
FROM `product_to_category` `p2c`
JOIN `product` `product` ON product.id = p2c.product_id
LEFT JOIN `city` `city` ON city.id=product.city_id
LEFT JOIN `user` ON user.id=product.user_id
WHERE (
((`product`.`id` in (
                    select product_id from product_to_city
                    join city on (city.id = product_to_city.city_id)
                    where city.id = 1 or city.parent_id = 1
                ))) AND

( (`product`.`zombie` = 0)) AND (`product`.`publish` = 1 AND (`product`.`delete` = 0 OR `product`.`delete` is null)))
GROUP BY `product`.`id`
HAVING COUNT(1) > 1
ORDER BY `product`.`timestamp_update` DESC

таблица продукт
http://dl3.joxi.net/drive/2017/02/06/0006/1977/436153/53/e9265d632e.jpg

таблица city
http://dl3.joxi.net/drive/2017/02/06/0006/1977/436153/53/cbeda3a622.jpg

таблица category
http://dl4.joxi.net/drive/2017/02/06/0006/1977/436153/53/8ef88e272e.jpg

таблица product_to_category
http://dl3.joxi.net/drive/2017/02/06/0006/1977/436153/53/463b0c464d.jpg

таблица user
http://dl4.joxi.net/drive/2017/02/06/0006/1977/436153/53/8c253b5dc2.jpg

таблица product_to_city
http://dl4.joxi.net/drive/2017/02/06/0006/1977/436153/53/5267f060b2.jpg

explain
http://dl4.joxi.net/drive/2017/02/06/0006/1977/436153/53/64bcd0fea9.jpg

Неактивен

 

#18 06.02.2017 14:35:05

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: выборка, группировка

пока так лучше стало, если есть предложения слушаю )

Код:

SELECT `product`.`id`, `product`.`name`, `product`.`condition`, `product`.`city_id`,  `product`.`price`,`user`.`name`  AS `user_name`, `city`.`name` AS `city_name`
FROM `product_to_category` `p2c`
JOIN `product` `product` ON product.id = p2c.product_id

JOIN product_to_city ptc ON product.id = ptc.product_id 
JOIN city c ON c.id = ptc.city_id

LEFT JOIN `city` `city` ON city.id=product.city_id
LEFT JOIN `user` ON user.id=product.user_id
WHERE (
((`product`.`id` in (
                    select product_id from product_to_city
                    join city on (city.id = product_to_city.city_id)
                    where city.id = 1 or city.parent_id = 1
                ))) AND

( (`product`.`zombie` = 0)) AND (`product`.`publish` = 1 AND `product`.`archive` = 0 AND (`product`.`delete` = 0 OR `product`.`delete` is null)))
GROUP BY `product`.`id`
HAVING COUNT(1) > 1
ORDER BY `product`.`timestamp_update` DESC

Отредактированно x-miller-x (06.02.2017 14:35:35)

Неактивен

 

#19 13.02.2017 11:17:08

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: выборка, группировка

Этот запрос занимает где то 20 секунд когда нагрузки нет, во время нагрузки еще больше, можете подсказать как оптимизировать? Структуру приводил ранее http://sqlinfo.ru/forum/viewtopic.php?pid=44509#p44760

SELECT p.city_id, p2c.category_id, count( * ) AS count
FROM product p
JOIN product_to_category p2c ON p.id = p2c.product_id
WHERE p.publish =1
GROUP BY p.city_id, p2c.category_id
 

Неактивен

 

Board footer

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