ПРИСОЕДИНЯЙТЕСЬ к другой таблице после GROUP BY и COUNT

Я пытаюсь понять, как правильно использовать JOIN, COUNT(*) и GROUP BY для выполнения довольно простого запроса. На самом деле я заставил его работать (см. ниже), но из того, что я прочитал, я использую дополнительный GROUP BY, которого не должен был.

(Примечание: приведенная ниже проблема не является моей реальной проблемой (которая связана с более сложными таблицами), но я попытался придумать аналогичную проблему)

У меня есть две таблицы:

Table: Person
-------------
key  name     cityKey
1    Alice    1
2    Bob      2
3    Charles  2
4    David    1

Table: City
-------------
key  name
1    Albany
2    Berkeley
3    Chico

Я хотел бы сделать запрос о людях (с некоторым предложением WHERE), который возвращает

  • количество подходящих людей в каждом городе
  • ключ от города
  • название города.

If I do

SELECT COUNT(Person.key) AS count, City.key AS cityKey, City.name AS cityName
FROM Person 
LEFT JOIN City ON Person.cityKey = City.key 
GROUP BY Person.cityKey, City.name

Я получаю желаемый результат

count   cityKey   cityName
2       1         Albany
2       2         Berkeley

Однако я прочитал, что добавление этой последней части пункта GROUP BY (City.name) только для того, чтобы заставить его работать, неправильно.

Так как же правильно это сделать? Я пытался найти ответ в Google, но чувствую, что есть что-то фундаментальное, чего я просто не понимаю.


sql count group-by left-join aggregate-functions
person Craig S    schedule 20.06.2010    source источник

Ответы (4)


arrow_upward
4
arrow_downward

Я не думаю, что в данном случае это «неправильно», потому что между названием города и ключом города есть отношение один к одному. Вы можете переписать его так, чтобы вы присоединились к подвыборке, чтобы получить количество людей в городах по ключу, снова в таблицу городов для имени, но спорно, что это было бы лучше. Думаю, это вопрос стиля и мнения.

select PC.ct, City.key, City.name
  from City
  join (select count(Person.key) ct, cityKey key from Person group by cityKey) PC
    on City.key = PC.key

если мой SQL не слишком ржавый :-)

person Pointy    schedule 20.06.2010
comment
stackoverflow.com/questions/23278277/ Справка - person TommyT; 25.04.2014
comment
как мы могли написать то же самое для SQL Server? - person Ramsk; 05.12.2018
comment
@Ramsk, этот точный запрос должен нормально работать в SQL Server (при условии, что схема такая же, конечно) - person Pointy; 05.12.2018

arrow_upward
3
arrow_downward

... Я читал, что добавление этой последней части предложения GROUP BY (City.name) только для того, чтобы заставить его работать, неправильно.

Вы неправильно поняли, вы поняли это наоборот.
Стандартный SQL требует, чтобы вы указали в GROUP BY все столбцы, упомянутые в SELECT, которые не включены в агрегатные функции. Если вам не нужны определенные столбцы в GROUP BY, оберните их агрегатными функциями. В зависимости от базы данных вы можете использовать аналитическую/оконную функцию OVER...

Тем не менее, MySQL и SQLite предоставляют «функции», с помощью которых вы можете исключить эти столбцы из группы, что приводит к бесконечным вопросам «почему этот перенос из MySQL в базу данных fill_in_the_blank ?!» Stackoverflow и множество других сайтов и форумов.

person OMG Ponies    schedule 20.06.2010
comment
Является ли добавление City.name к Group By лучше (то есть быстрее, переносимее и т. д.), чем использование Min(city.name), как предлагает Андомар? - person Craig S; 20.06.2010
comment
@Craig S: зависит от ваших потребностей. Но использование агрегатной функции ради того, чтобы не делать ее в GROUP BY, рискованно — потребуется дополнительное тестирование, чтобы убедиться, что все правильно. Я не вижу смысла использовать агрегат исключительно ради того, чтобы не определять его в GROUP BY. - person OMG Ponies; 20.06.2010
comment
Хорошо; Думаю, после прочтения этой статьи я забеспокоился, что просто добавляю группу, чтобы заставить ее работать, и что при этом я совершаю большую ошибку SQL. Я работаю с относительно огромной базой данных (если бы это были таблицы в моем примере, там были бы миллионы людей и сотни городов). - person Craig S; 20.06.2010

arrow_upward
2
arrow_downward

Однако я читал, что добавление этой последней части предложения GROUP BY (City.name) только для того, чтобы заставить его работать, неправильно.

Это не так. Вы должны понимать, как оптимизатор запросов видит ваш запрос. Порядок, в котором он анализируется, требует от вас «добавления последней части». Оптимизатор видит ваш запрос примерно в таком порядке:

  • необходимые таблицы объединены
  • составной набор данных фильтруется с помощью предложения WHERE
  • остальные строки разбиваются на группы предложением GROUP BY и агрегируются
  • затем они снова фильтруются с помощью предложения HAVING.
  • наконец, с помощью SELECT / ORDER BY, UPDATE или DELETE.

Дело здесь не в том, что GROUP BY должен называть все столбцы в SELECT, а на самом деле наоборот — SELECT не может включать столбцы, которых еще нет в GROUP BY.

person ahsteele    schedule 20.06.2010
comment
Лучше использовать агрегатную функцию, чем добавлять что-то в группу? - person Craig S; 20.06.2010
comment
@Craig, это зависит от ваших потребностей. Если вам не нужно выполнять функцию над данными, то нет, вы не должны помещать их в функцию и должны группировать их. Единственная причина, по которой вы получаете либо агрегатную функцию, либо ошибку предложения GROUP BY, заключается в том, что оптимизатор должен знать, что вы делаете, и он должен либо выполнять функцию в этом столбце, либо группировать по нему. Вы не должны использовать функцию только для того, чтобы избежать группировки. - person ahsteele; 20.06.2010

arrow_upward
1
arrow_downward

Ваш запрос будет работать только в MySQL, потому что вы группируете Person.cityKey, но выбираете city.key. Все другие базы данных потребуют от вас использования агрегата, такого как min(city.key), или добавления City.key к предложению group by.

Поскольку комбинация названия города и ключа города уникальна, следующие значения эквивалентны:

select    count(person.key), min(city.key), min(city.name)
...
group by  person.citykey

Or:

select    count(person.key), city.key, city.name
...
group by  person.citykey, city.key, city.name

Or:

select    count(person.key), city.key, max(city.name)
...
group by  city.key

Все строки в группе будут иметь одно и то же название города и ключ, поэтому не имеет значения, используете ли вы агрегат max или min.

P.S. Если вы хотите подсчитать только разных людей, даже если они имеют несколько строк, попробуйте:

count(DISTINCT person.key)

вместо

count(person.key)
person Andomar    schedule 20.06.2010
comment
Я думаю, что это может быть опечатка в вопросе; Я полагаю, что он намеревался включить в свою группу оба неагрегированных столбца в своем предложении SELECT. Однако я могу ошибаться. - person Pointy; 20.06.2010
comment
Для вашего второго примера у вас есть куча вещей в части group by; со страницы, на которую я ссылался в своем посте, они говорили, что это неправильно. Однако часть MIN работает; (Я думал, что это работает только для столбцов типа int.) - person Craig S; 20.06.2010
comment
@Craig S: Если я правильно прочитал сообщение, на которое вы ссылаетесь, они просто говорят, что вы должны group by в выбранных вами столбцах. Мой второй пример делает это правильно. В посте также упоминается раздражающая ошибка, которую вы получаете, если делаете это неправильно. MySQL — единственная база данных, которая приняла бы неправильный путь без досадной ошибки :) - person Andomar; 20.06.2010