Подтвердить что ты не робот

MySQL. Выбор столбца не в группе.

Я пытаюсь добавить функции к существующим приложениям, и я столкнулся с представлением MySQL примерно так:

SELECT
     AVG(table_name.col1),
     AVG(table_name.col2),
     AVG(table_name.col3),
     table_name.personID,
     table_name.col4
FROM table_name
GROUP BY table_name.personID;

ОК, так что есть несколько агрегатных функций. Вы можете выбрать personID, потому что вы группируете его. Но он также выбирает столбец, который не находится в агрегатной функции и не является частью предложения GROUP BY. Как это возможно??? Он просто выбирает случайное значение, потому что значения определенно не уникальны для каждой группы?

Откуда я (сервер MSSQL), это ошибка. Может ли кто-нибудь объяснить это поведение мне и почему это разрешено в MySQL?

4b9b3361

ОТВЕТЫ

Ответ 1

Верно, что эта функция допускает некоторые неоднозначные запросы и тихо возвращает набор результатов с произвольным значением, выбранным из этого столбца. На практике это имеет значение из строки внутри группы, которая физически хранится в первую очередь.

Эти запросы не являются двусмысленными, если вы выбираете только столбцы, которые функционально зависят от столбца (ов) в критериях GROUP BY. Другими словами, если может быть только одно отдельное значение "неоднозначного" столбца на значение, определяющее группу, нет проблем. Этот запрос был бы незаконным в Microsoft SQL Server (и ANSI SQL), хотя он не может логически привести к двусмысленности:

SELECT AVG(table1.col1), table1.personID, persons.col4
FROM table1 JOIN persons ON (table1.personID = persons.id)
GROUP BY table1.personID;

Кроме того, MySQL имеет режим SQL, чтобы заставить его вести себя по стандарту: ONLY_FULL_GROUP_BY

FWIW, SQLite также допускает эти неоднозначные предложения GROUP BY, но он выбирает значение из последней строки в группе. †

† По крайней мере, в версии, которую я тестировал. Что значит быть произвольным, так это то, что MySQL или SQLite могут изменить свою реализацию в будущем и иметь какое-то другое поведение. Поэтому вы не должны полагаться на поведение, оставаясь таким, каким оно есть в настоящее время в таких неоднозначных случаях. Лучше переписать ваши запросы как детерминированные, а не двусмысленные. Вот почему MySQL 5.7 теперь включает ONLY_FULL_GROUP_BY по умолчанию.

Ответ 2

У меня должен был быть Googled еще немного... Кажется, я нашел мой ответ.

MySQL расширяет использование GROUP BY, поэтому что вы можете использовать неагрегированные столбцы или вычисления в списке SELECT которые не отображаются в группе GROUP BY пункт. Вы можете использовать эту функцию для получить лучшую производительность, избегая ненужная сортировка столбцов и группировка. Например, вам не нужно Группировать по адресу customer.name в следующий запрос

В стандартном SQL вам нужно будет добавить customer.name в предложение GROUP BY. В MySQL имя является избыточным.

Тем не менее, это просто кажется... неправильным.

Ответ 3

select * from personel where p_id IN(select
min(dbo.personel.p_id)
FROM
personel
GROUP BY dbo.personel.p_adi)

Ответ 4

Скажем, у вас есть такой запрос:

SELECT g, v 
FROM t
GROUP BY g;

В этом случае для каждого возможного значения для g mysql выбирает одно из соответствующих значений v.

Однако, какой из них выбран, зависит от некоторых обстоятельств.

Я где-то читал, что для каждой группы g первое значение v сохраняется в порядке внесения записей в таблицу t.

Это довольно уродливо, потому что записи в таблице следует рассматривать как набор, где порядок элементов не имеет значения. Это так "mysql-ish"...

Если вы хотите определить, какое значение для v нужно сохранить, вам нужно применить подзапрос для t следующим образом:

SELECT g, v 
FROM (
    SELECT * 
        FROM t 
        ORDER BY g, v DESC
) q
GROUP BY g;

Таким образом вы определяете, в каком порядке записи подзапроса обрабатываются внешним запросом, поэтому вы можете доверять тому, какое значение v оно выберет для отдельных значений g.

Однако, если вам нужны какие-то условия WHERE, будьте очень осторожны. Если вы добавите условие WHERE в подзапрос, то оно будет поддерживать поведение, оно всегда будет возвращать ожидаемое значение:

SELECT g, v 
FROM (
    SELECT * 
        FROM t 
        WHERE g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9' 
        ORDER BY g, v DESC
) q
GROUP BY g;

Это то, что вы ожидаете, подзаголовок фильтрует и заказывает таблицу. Он хранит записи, где g имеет заданное значение, а внешний запрос возвращает g и первое значение для v.

Однако, если вы добавите к внешнему запросу такое же условие WHERE, вы получите недетерминированный результат:

SELECT g, v 
FROM (
    SELECT * 
        FROM t 
        -- WHERE g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9' 
        ORDER BY g, v DESC
) q
WHERE g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9'
GROUP BY g;

Удивительно, но вы можете получить разные значения для v при выполнении одного и того же запроса снова и снова, что... странно. Ожидаемое поведение состоит в том, чтобы получить все записи в соответствующем порядке из подзапроса, отфильтровать их во внешнем запросе и затем выбрать то же, что и в предыдущем примере. Но это не так.

Он выбирает значение для v казалось бы, случайным образом. Тот же запрос возвращал разные значения для v если я выполнил больше (~ 20) раз, но распределение было неоднородным.

Если вместо добавления внешнего WHERE вы указываете условие HAVING следующим образом:

SELECT g, v 
FROM (
    SELECT * 
        FROM t1 
        -- WHERE g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9' 
        ORDER BY g, v DESC
) q
-- WHERE g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9'
GROUP BY g
HAVING g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9';

Затем вы снова получите последовательное поведение.

ЗАКЛЮЧЕНИЕ: Я бы предложил не полагаться на эту технику вообще. Если вам действительно нужно/нужно, то избегайте условий WHERE во внешнем запросе. Используйте его во внутреннем запросе, если хотите, или в предложении HAVING во внешнем запросе.

Я тестировал это с помощью этих данных:

CREATE TABLE t1 (
    v INT,
    g VARCHAR(36)
);

INSERT INTO t1 VALUES (1, '737a8783-110c-447e-b4c2-1cbb7c6b72c9');
INSERT INTO t1 VALUES (2, '737a8783-110c-447e-b4c2-1cbb7c6b72c9');

в mysql 5.6.41.

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