SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 18.10.2017 10:36:33

vasilyvp
Участник
Зарегистрирован: 18.10.2017
Сообщений: 6

Проблема при сортировке при использовании агрегирующих функций

Добрый день.
Описание задачи:
Есть две таблицы catalogs и products связанные через id_catalog. Хочу рассчитать и вывести среднюю цену продуктов в каждом каталоге.
Запрос:
mysql> SELECT catalogs.id_catalog, AVG(products.price) AS price FROM products JOIN catalogs USING(id_catalog)
    -> GROUP BY id_catalog
    -> ORDER BY price;
+------------+-------------+
| id_catalog | price       |
+------------+-------------+
|          5 | 1342.420000 |
|          4 | 2604.318000 |
|          2 | 2653.736667 |
|          1 | 3299.052222 |
|          3 | 3641.210000 |
+------------+-------------+
отрабатывает как и ожидается
Меняем catalogs.id_catalog на catalogs.name и сортировка перестает работать

mysql> SELECT catalogs.name, AVG(products.price) AS price FROM products JOIN catalogs USING(id_catalog)
    -> GROUP BY id_catalog
    -> ORDER BY price;
+--------------------+-------------+
| name               | price       |
+--------------------+-------------+
| Процессоры         | 3299.052222 |
| Материнские платы  | 2653.736667 |
| Видеоадаптеры      | 3641.210000 |
| Жёсткие диски      | 2604.318000 |
| Оперативная память | 1342.420000 |
+--------------------+-------------+

Если поменять функцию AVG() на COUNT(), то сортировка будет работать:
mysql> SELECT catalogs.name, COUNT(products.price) AS count FROM products JOIN catalogs USING(id_catalog)
    -> GROUP BY id_catalog
    -> ORDER BY count;
+--------------------+-------+
| name               | count |
+--------------------+-------+
| Видеоадаптеры      |     4 |
| Жёсткие диски      |     5 |
| Материнские платы  |     6 |
| Оперативная память |     6 |
| Процессоры         |     9 |
+--------------------+-------+

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

Отредактированно vasilyvp (18.10.2017 10:38:40)

Неактивен

 

#2 18.10.2017 11:32:55

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

Re: Проблема при сортировке при использовании агрегирующих функций

name уникально?

SELECT catalogs.name, AVG(products.price) AS price FROM products JOIN catalogs USING(id_catalog)
GROUP BY id_catalog
ORDER BY 2;

Неактивен

 

#3 18.10.2017 12:20:01

vasilyvp
Участник
Зарегистрирован: 18.10.2017
Сообщений: 6

Re: Проблема при сортировке при использовании агрегирующих функций

поле name в catalogs не помечено как уникальное, но записи фактически уникальные.
поле name есть и в products
ORDER BY 2 не работает
ORDER BY 1 - работает

Неактивен

 

#4 18.10.2017 12:25:11

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

Re: Проблема при сортировке при использовании агрегирующих функций

сделайте пример в виде create table, insert into чтобы можно было воспроизвести проблему

Неактивен

 

#5 18.10.2017 13:04:53

vasilyvp
Участник
Зарегистрирован: 18.10.2017
Сообщений: 6

Re: Проблема при сортировке при использовании агрегирующих функций

CREATE TABLE `tests`.`catalogs` (
    `id_catalog` INT NOT NULL AUTO_INCREMENT ,
    `name` TINYTEXT NOT NULL ,
    PRIMARY KEY (`id_catalog`)
) ENGINE = MyISAM CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
   
CREATE TABLE `tests`.`products` (
    `id_product` INT NOT NULL AUTO_INCREMENT ,
    `name` TINYTEXT NOT NULL ,
    `price` DECIMAL(7) NOT NULL ,
    `id_catalog` INT NOT NULL ,
    PRIMARY KEY (`id_product`)
) ENGINE = MyISAM CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
   
INSERT INTO `catalogs`(`id_catalog`, `name`) VALUES
    (NULL, 'Процессоры'),
    (NULL, 'Материнские платы'),
    (NULL, 'Видеоадаптеры'),
    (NULL, 'Жесткие диски');
   
INSERT INTO `products`(`id_product`, `name`, `price`, `id_catalog`) VALUES
    (NULL, 'Product 1', 4300, 1),
    (NULL, 'Product 2', 5600, 1),
    (NULL, 'Product 3', 6450, 1),
    (NULL, 'Product 4', 2300, 2),
    (NULL, 'Product 5', 1105, 2),
    (NULL, 'Product 6', 3201, 2),
    (NULL, 'Product 7', 4100, 3),
    (NULL, 'Product 8', 1409, 3),
    (NULL, 'Product 9', 1550, 3),
    (NULL, 'Product 10', 2300, 4),
    (NULL, 'Product 11', 3554, 4),
    (NULL, 'Product 12', 2500, 4);

Неактивен

 

#6 18.10.2017 13:56:58

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 21.01.2007
Сообщений: 6641

Re: Проблема при сортировке при использовании агрегирующих функций

Вы хотите получить сортировку по средней цене или по названию? В примере в первом сообщении очень неудачно получилось, что там сортировано и так, и так smile

Неактивен

 

#7 18.10.2017 14:05:19

vasilyvp
Участник
Зарегистрирован: 18.10.2017
Сообщений: 6

Re: Проблема при сортировке при использовании агрегирующих функций

Сортировать хочу по средней цене
все остальные сортировки работают ожидаемо
Если сортируешь по средней цене и выводишь в первый столбец catalogs.name, то само выводится отсортированным по catalogs.id_catalog не взирая на ORDER BY sad

Неактивен

 

#8 19.10.2017 18:06:29

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 21.01.2007
Сообщений: 6641

Re: Проблема при сортировке при использовании агрегирующих функций

Попробуйте тогда проделать над теми данными, которые Вы привели? У меня сортирует правильно. Если неправильно, покажите версию MySQL smile


> SELECT catalogs.name, catalogs.id_catalog, AVG(products.price) FROM products JOIN catalogs USING(id_catalog)     GROUP BY id_catalog    ORDER BY AVG(products.price);
+-----------------------------------+------------+---------------------+
| name                              | id_catalog | AVG(products.price) |
+-----------------------------------+------------+---------------------+
| Материнские платы                 |          2 |           2202.0000 |
| Видеоадаптеры                     |          3 |           2353.0000 |
| Жесткие диски                     |          4 |           2784.6667 |
| Процессоры                        |          1 |           5450.0000 |
+-----------------------------------+------------+---------------------+
4 rows in set (0,01 sec)
 

Неактивен

 

#9 19.10.2017 19:14:58

vasilyvp
Участник
Зарегистрирован: 18.10.2017
Сообщений: 6

Re: Проблема при сортировке при использовании агрегирующих функций

mysql> SELECT catalogs.name, catalogs.id_catalog, AVG(products.price) FROM products JOIN catalogs USING(id_catalog)
    -> GROUP BY id_catalog
    -> ORDER BY AVG(products.price);
+-------------------+------------+---------------------+
| name                      | id_catalog | AVG(products.price) |
+-------------------+------------+---------------------+
| Процессоры            |              1 |           5450.0000 |
| Материнские платы |              2 |           2202.0000 |
| Видеоадаптеры       |              3 |           2353.0000 |
| Жесткие диски        |              4 |           2784.6667 |
+-------------------+------------+---------------------+
К сожалению, без изменений. Более того, HAVING тоже не работает, если его добавить и пока заметил только с функцией AVG().

Версия MySQL: 5.7.14 для Win64

Неактивен

 

#10 19.10.2017 21:15:05

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 21.01.2007
Сообщений: 6641

Re: Проблема при сортировке при использовании агрегирующих функций

Ого! Давайте начнем с того, что поставим последнюю версию (5.7.20). На ней так же будет себя вести?

Неактивен

 

#11 20.10.2017 12:47:37

vasilyvp
Участник
Зарегистрирован: 18.10.2017
Сообщений: 6

Re: Проблема при сортировке при использовании агрегирующих функций

Аллилуйя - заработало.
Видимо в версии 5.7.14 был баг.

Спасибо!

Неактивен

 

Board footer

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