«Группировать по» не группируется в том порядке, в котором мне это нужно

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

Вот мой код:

create or replace view tasters_avg_ratings1
as
select a.taster as taster, a.beer as beer, round(avg(a.rating),1) as rating 
from allratings a
group by beer, taster
;

Тем не менее, мой вывод выглядит следующим образом:

beers=# select * from tasters_avg_ratings1;
 taster |          beer          | rating 
--------+------------------------+--------
 Peter  | XXXX                   |    5.0
 Sarah  | James Squire Pilsener  |    3.0
 Raghu  | Sierra Nevada Pale Ale |    3.0
 Hector | Fosters                |    3.0
 John   | Chimay Red             |    3.0
 John   | Sierra Nevada Pale Ale |    5.0
 Geoff  | James Squire Pilsener  |    4.0
 Ramez  | Sierra Nevada Pale Ale |    4.0
 John   | 80/-                   |    4.0
 John   | Rasputin               |    4.0
 Adam   | Old                    |    4.0
 John   | Crown Lager            |    2.0
 Jeff   | Sierra Nevada Pale Ale |    4.0
 Sarah  | Burragorang Bock       |    4.0
 Sarah  | Scharer's Lager        |    3.0
 Sarah  | New                    |    2.0
 Geoff  | Redback                |    4.0
 Adam   | Victoria Bitter        |    1.0
 Sarah  | Victoria Bitter        |    1.0
 Raghu  | Rasputin               |    3.0
 Ramez  | Bigfoot Barley Wine    |    3.0
 Hector | Sierra Nevada Pale Ale |    4.0
 Sarah  | Old                    |    3.0
 Jeff   | Burragorang Bock       |    3.0
 John   | Empire                 |    3.0
 Sarah  | James Squire Amber Ale |    3.0
 Rose   | Redback                |    5.0
 Geoff  | Empire                 |    3.0
 Adam   | New                    |    1.0
 Jeff   | Rasputin               |    1.0
 Raghu  | Old Tire               |    5.0
 John   | Victoria Bitter        |    1.0
(32 rows)

Как видите, сорта пива НЕ сгруппированы вместе. В идеале, например, пиво «Victoria Bitter» должно отображаться как группа, а не отдельно.

Желаемый результат достигается с помощью «упорядочить по». Например:

create or replace view tasters_avg_ratings1
as
select a.taster as taster, a.beer as beer, round(avg(a.rating),1) as rating 
from allratings a
group by beer, taster
order by a.beer
;

ВЫВОД:

beers=# select * from tasters_avg_ratings1;
 taster |          beer          | rating 
--------+------------------------+--------
 John   | 80/-                   |    4.0
 Ramez  | Bigfoot Barley Wine    |    3.0
 Jeff   | Burragorang Bock       |    3.0
 Sarah  | Burragorang Bock       |    4.0
 John   | Chimay Red             |    3.0
 John   | Crown Lager            |    2.0
 Geoff  | Empire                 |    3.0
 John   | Empire                 |    3.0
 Hector | Fosters                |    3.0
 Sarah  | James Squire Amber Ale |    3.0
 Geoff  | James Squire Pilsener  |    4.0
 Sarah  | James Squire Pilsener  |    3.0
 Adam   | New                    |    1.0
 Sarah  | New                    |    2.0
 Adam   | Old                    |    4.0
 Sarah  | Old                    |    3.0
 Raghu  | Old Tire               |    5.0
 Jeff   | Rasputin               |    1.0
 John   | Rasputin               |    4.0
 Raghu  | Rasputin               |    3.0
 Geoff  | Redback                |    4.0
 Rose   | Redback                |    5.0
 Sarah  | Scharer's Lager        |    3.0
 Hector | Sierra Nevada Pale Ale |    4.0
 Jeff   | Sierra Nevada Pale Ale |    4.0
 John   | Sierra Nevada Pale Ale |    5.0
 Raghu  | Sierra Nevada Pale Ale |    3.0
 Ramez  | Sierra Nevada Pale Ale |    4.0
 Adam   | Victoria Bitter        |    1.0
 John   | Victoria Bitter        |    1.0
 Sarah  | Victoria Bitter        |    1.0
 Peter  | XXXX                   |    5.0
(32 rows)

Итак, хотя я знаю, что order by достигает моего результата, почему «group by» не делает то же самое? Это расстраивает, потому что я видел в Интернете множество примеров использования «группировать по» и добивался успеха, особенно в случаях, подобных моему, с неагрегированными и совокупными столбцами. Например: https://learnsql.com/blog/error-with-group-by/, совет №3.

Любая помощь будет оценена, спасибо!

Как правило, предложение ORDER BY требуется, когда необходимо применить определенную сортировку. Использование GROUP BY устанавливает GROUPING (поэтому он называется GROUP BY, а не ORDER BY), поэтому ORDER BY по-прежнему необходим.

Jonas Metzler 22.11.2022 14:48

Эй, Джонас, не могли бы вы уточнить, что вы подразумеваете под «группировкой» наборов «группировкой»? Означает ли это, что все сорта пива с одинаковым названием не обязательно будут помещены в последовательный блок таблицы? Как насчет примера, который я привел на веб-сайте. Спасибо

Rayyan Khan 22.11.2022 14:50

GROUP BY позволяет суммировать/подсчитывать/и т.д. значения (вы называете это агрегацией), СГРУППИРОВАННЫЕ ПО определенным столбцам. В вашем примере средний рейтинг будет строиться по дегустатору и пиву. Без GROUP BY средний рейтинг не отличался бы, а всегда рассчитывался на основе всей таблицы. GROUP BY не имеет ничего общего с сортировкой. Для сортировки требуется предложение ORDER BY. Об этом больше нечего сказать. На этот вопрос был дан ответ много лет назад: stackoverflow.com/questions/28149876/…

Jonas Metzler 22.11.2022 14:57

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

The Impaler 22.11.2022 14:58

Вы уже знаете, как добиться упорядоченности. Так сделай это. Зачем добровольно разочаровываться из-за ерунды?

jjanes 22.11.2022 17:03

@jjanes Привет, я не думаю, что это очень полезный вопрос, и звучит почти снисходительно. Как указано в моем посте, я знаю, что упорядочение достигает моего результата, но я поясняю, почему группировка этого не делает, несмотря на то, что интернет-источник показывает, что он работает таким же образом. Другие разъяснили, почему это так, в полезной и доброжелательной манере, поэтому у меня есть ответ, который мне нужен. Если вы все еще пытаетесь понять мой вопрос, я буду более чем счастлив уточнить!

Rayyan Khan 22.11.2022 23:10
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
6
54
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

GROUP BY используется только для вычисления совокупного значения (в данном случае среднего рейтинга). Это не имеет никакого отношения к порядку результатов при их отображении. Как вы упомянули, вам нужно использовать ORDER BY, чтобы получить желаемый заказ.

Имеет смысл. Получается, что группа предназначена только для агрегирования, а не для представления информации? Судя по ответам здесь, представление информации в упорядоченном виде кажется приятным побочным эффектом / бонусом функции, но не ее основным.

Rayyan Khan 22.11.2022 22:34

Группировать по следует использовать для группировки строк, имеющих одинаковое значение для указанного столбца. Полное объяснение здесь https://stackoverflow.com/a/2421441.

В вашем случае вы хотите сгруппировать пиво по совокупному рейтингу и дегустаторам, поэтому вам нужно GROUP BY a.beers и упорядочить по первому и третьему столбцу. Таким образом, вид должен быть таким:

create or replace view tasters_avg_ratings1
as
select a.taster as taster, a.beer as beer, round(avg(a.rating),1) as rating 
from allratings a
group by a.beer
order by 1,3;

1 и 3 — это порядковые позиции столбцов, которые появляются в списке выбора.

Это имеет смысл, спасибо. Но также, когда я пробую ваше представление, я получаю эту ошибку: psql: prac6_me.sql: 111: ОШИБКА: столбец «a.taster» должен отображаться в предложении GROUP BY или использоваться в агрегатной функции. LINE 3: выберите a.taster как дегустатор, a.beer как пиво, round(avg(a.ratin... Я на psql, если это поможет.

Rayyan Khan 22.11.2022 22:33

Это распространенная проблема агрегации, выбранные поля должны отображаться в предложении GROUP BY, вы можете исправить это, добавив a.taster в предложение group by, или вы должны вычислить агрегат в подзапросе, а затем соединить его с самим собой, чтобы получить дополнительный столбец. См. stackoverflow.com/a/19602031 более подробный пример.

Ali Ibrahim 23.11.2022 10:22

Стандарт SQL определяет, что оператор SELECT без ORDER BY может возвращать результирующие строки в произвольном порядке. Существует хорошее объяснение того, почему поначалу это может выглядеть иначе.

GROUP BY предназначен для группировки и объединения связанных кортежей вместе. Тривиальная реализация для группировки, конечно, сначала сортирует данные, а затем работает сверху вниз и объединяет связанные кортежи. Если ваша база данных решит использовать такую ​​реализацию, велики шансы, что вы получите упорядоченный набор результатов. Но возможны и другие реализации, и ваша база данных может перейти на использование одной из них в любое время. И если это так, вы получите те же строки, но в другом порядке.

Короче говоря, если вы хотите, чтобы ваш набор результатов был упорядоченным, используйте ORDER BY.

Что касается примера, который вы упомянули, он, возможно, немного вводит в заблуждение. Результат упорядочен по столбцам, по которым он также сгруппирован. Это возможный заказ, и, учитывая мысли раньше, вероятно, вполне вероятный. Но так как запрос без ORDER BY, это просто совпадение и не гарантируется.

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

Rayyan Khan 22.11.2022 22:36

Другие вопросы по теме