Я хочу «группировать» сорта пива, чтобы они были сгруппированы вместе, а совокупный рейтинг уважения и дегустаторы (люди, которые оценивают пиво) перечислены в отдельных столбцах.
Вот мой код:
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.
Любая помощь будет оценена, спасибо!
Эй, Джонас, не могли бы вы уточнить, что вы подразумеваете под «группировкой» наборов «группировкой»? Означает ли это, что все сорта пива с одинаковым названием не обязательно будут помещены в последовательный блок таблицы? Как насчет примера, который я привел на веб-сайте. Спасибо
GROUP BY позволяет суммировать/подсчитывать/и т.д. значения (вы называете это агрегацией), СГРУППИРОВАННЫЕ ПО определенным столбцам. В вашем примере средний рейтинг будет строиться по дегустатору и пиву. Без GROUP BY средний рейтинг не отличался бы, а всегда рассчитывался на основе всей таблицы. GROUP BY не имеет ничего общего с сортировкой. Для сортировки требуется предложение ORDER BY. Об этом больше нечего сказать. На этот вопрос был дан ответ много лет назад: stackoverflow.com/questions/28149876/…
Вам нужно указать логику для сборки этих подгрупп (пиво, без пива и т. д.). Вам нужно либо создать для него новый столбец, либо определить логику для их вычисления на лету.
Вы уже знаете, как добиться упорядоченности. Так сделай это. Зачем добровольно разочаровываться из-за ерунды?
@jjanes Привет, я не думаю, что это очень полезный вопрос, и звучит почти снисходительно. Как указано в моем посте, я знаю, что упорядочение достигает моего результата, но я поясняю, почему группировка этого не делает, несмотря на то, что интернет-источник показывает, что он работает таким же образом. Другие разъяснили, почему это так, в полезной и доброжелательной манере, поэтому у меня есть ответ, который мне нужен. Если вы все еще пытаетесь понять мой вопрос, я буду более чем счастлив уточнить!
GROUP BY используется только для вычисления совокупного значения (в данном случае среднего рейтинга). Это не имеет никакого отношения к порядку результатов при их отображении. Как вы упомянули, вам нужно использовать ORDER BY, чтобы получить желаемый заказ.
Имеет смысл. Получается, что группа предназначена только для агрегирования, а не для представления информации? Судя по ответам здесь, представление информации в упорядоченном виде кажется приятным побочным эффектом / бонусом функции, но не ее основным.
Группировать по следует использовать для группировки строк, имеющих одинаковое значение для указанного столбца. Полное объяснение здесь 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, если это поможет.
Это распространенная проблема агрегации, выбранные поля должны отображаться в предложении GROUP BY, вы можете исправить это, добавив a.taster в предложение group by, или вы должны вычислить агрегат в подзапросе, а затем соединить его с самим собой, чтобы получить дополнительный столбец. См. stackoverflow.com/a/19602031 более подробный пример.
Стандарт SQL определяет, что оператор SELECT
без ORDER BY
может возвращать результирующие строки в произвольном порядке. Существует хорошее объяснение того, почему поначалу это может выглядеть иначе.
GROUP BY
предназначен для группировки и объединения связанных кортежей вместе. Тривиальная реализация для группировки, конечно, сначала сортирует данные, а затем работает сверху вниз и объединяет связанные кортежи. Если ваша база данных решит использовать такую реализацию, велики шансы, что вы получите упорядоченный набор результатов. Но возможны и другие реализации, и ваша база данных может перейти на использование одной из них в любое время. И если это так, вы получите те же строки, но в другом порядке.
Короче говоря, если вы хотите, чтобы ваш набор результатов был упорядоченным, используйте ORDER BY
.
Что касается примера, который вы упомянули, он, возможно, немного вводит в заблуждение. Результат упорядочен по столбцам, по которым он также сгруппирован. Это возможный заказ, и, учитывая мысли раньше, вероятно, вполне вероятный. Но так как запрос без ORDER BY
, это просто совпадение и не гарантируется.
Спасибо, ценю ответ. Таким образом, кажется, что группировка не гарантирует упорядочения, и это разные вещи. Группировка используется в основном для создания сводных столбцов для связанных кортежей, и это просто приятное совпадение, что иногда она представляет упорядоченные данные.
Как правило, предложение ORDER BY требуется, когда необходимо применить определенную сортировку. Использование GROUP BY устанавливает GROUPING (поэтому он называется GROUP BY, а не ORDER BY), поэтому ORDER BY по-прежнему необходим.