GROUP BY и HAVING: как суммировать строки, не получая ложного ответа

1. Что GROUP BY на самом деле делает с вашими строками

Перед пояснением: в вашей таблице 82 956 строк. Вы группируете его по жанрам и получаете обратно 28 строк. Куда делись остальные 82 928 строк и можно ли их еще увидеть?

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

В этом последнем предложении и заключается вся идея. До группировки один ряд был одной игрой. После группировки по жанрам одна строка — это один жанр. Слово для этого grainи произнесение новой детали вслух, прежде чем писать что-либо еще, предотвращает большинство ошибок на этой странице.

Агрегатная функция позволяет получить значение обратно из свернутого сегмента. COUNT(*) спрашивает, сколько строк в этом ведре. AVG(PctPositive) запрашивает среднее значение этого столбца в ведре. SUM, MIN и MAX работать так же. Каждый из них принимает множество значений и возвращает одно, а это именно то, что нужно для одной выходной строки.

SELECT PrimaryGenre,
       COUNT(*) AS games,
       ROUND(AVG(PctPositive), 1) AS avg_rating
FROM games
GROUP BY PrimaryGenre;

Это возвращает 28 строк, по одной на каждый жанр. На счету Действие 35 149 игр и средний показатель положительных результатов 75,4%. Приключение имеет 17 486 и составляет в среднем 77,7%. Все 82 956 исходных строк все еще учтены, поскольку каждая игра попала ровно в одну корзину.

2. Какие столбцы можно выбирать и какое правило стоит за этим

Перед пояснением: группируете по жанрам, а также выбираете название игры. База данных либо выдаёт ошибку, либо выдает вам одно имя из 35 149. Какое имя оно должно вам дать?

Разумного ответа нет, поэтому и существует правило. После группировки каждый столбец в вашем SELECT должно быть одно из двух.

• Столбец, по которому вы сгруппировали. Это безопасно, поскольку каждая строка в сегменте имеет одно и то же значение. Все 35 149 экшн-игр относятся к жанру Действие.
• Агрегат. Это безопасно, поскольку намеренно превращает всю корзину в одно значение.

Все остальное — это столбец с 35 149 различными значениями и одним слотом для их размещения. Большинство баз данных отказываются. PostgreSQL и SQL Server выдают ошибку при присвоении имени столбцу. Настройки SQLite и старых MySQL делают нечто худшее: они выбирают значение произвольно и возвращают его без комментариев, поэтому вы получаете реально выглядящее имя, привязанное к среднему значению по жанру.

-- Errors on PostgreSQL, returns a misleading row on SQLite
SELECT PrimaryGenre, Name, AVG(PctPositive)
FROM games
GROUP BY PrimaryGenre;

Прежде чем читать дальше, скажите своими словами, почему на этот вопрос нет правильного ответа. Если вы сможете указать причину, сообщение об ошибке больше никогда вас не смутит.

Причина в том, что Name находится не в том зерне. Он описывает одну игру, а строка теперь описывает жанр. Если вы действительно хотите, чтобы в каждой группе было первое имя, это другой инструмент, и это предмет Оконные функции.

Группировка по нескольким столбцам работает таким же образом, но с более мелким сегментом. GROUP BY PrimaryGenre, ReleaseYear составляет одну строку для каждого жанра в год, и тогда оба этих столбца можно безопасно выбрать.

3. ГДЕ против ИМЕТЬ, и почему приказ вынужденный

Перед пояснением: вам нужны жанры со средним рейтингом выше 80. Попробуйте поставить это условие в WHERE и база данных отказывается. Почему это не может просто решиться?

Потому что когда WHERE работает, среднего еще не существует. Это не стилевое предпочтение. Это порядок, в котором база данных оценивает предложения, и этот порядок фиксирован.

Так WHERE COUNT(*) >= 500 запрашивает у второго шага число, которое придумывает четвертый шаг. Ошибка заключается в том, что база данных сообщает вам правду о своей последовательности.

Практическая версия короткая. Если ваше условие составляет около одной строки, оно входит в WHERE. Если речь идет о целой группе, то она входит HAVING.

SELECT PrimaryGenre,
       COUNT(*) AS games,
       ROUND(AVG(PctPositive), 1) AS avg_rating
FROM games
WHERE TotalReviews >= 500        -- about one game
GROUP BY PrimaryGenre
HAVING COUNT(*) >= 500           -- about a whole genre
ORDER BY avg_rating DESC;

Оба фильтра в одном запросе и выполняют разную работу. WHERE линия говорит, что игре нужно достаточное количество обзоров, чтобы ее собственный рейтинг мог что-то значить, в результате чего каталог упал с 82 956 игр до 10 507. HAVING линия говорит, что жанру нужно достаточно игр, чтобы его средний показатель мог что-то значить.

4. Проблема малых групп и способ ее решения

Перед пояснением: как вы думаете, какой жанр в каталоге из 82 956 игр имеет самый высокий средний рейтинг? Прежде чем прочитать ответ, сделайте предположение.

Это бухгалтерский учет, на 79,5% положительный. Впереди приключения, впереди РПГ, впереди все. Запрос правильный, данные реальные, а ответ ничего не стоит, потому что во всем каталоге одиннадцать бухгалтерских игр.

Вот рейтинг без пола.

SELECT PrimaryGenre,
       COUNT(*) AS games,
       ROUND(AVG(PctPositive), 1) AS avg_rating
FROM games
GROUP BY PrimaryGenre
ORDER BY avg_rating DESC;

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

Одна строка исправляет это.

SELECT PrimaryGenre,
       COUNT(*) AS games,
       ROUND(AVG(PctPositive), 1) AS avg_rating
FROM games
GROUP BY PrimaryGenre
HAVING COUNT(*) >= 500
ORDER BY avg_rating DESC;

На этом этаже были удалены 20 из 28 жанров и создан первый рейтинг, на который можно было опереться. Спред также рухнул с вершины 79,5 до 77,7, что является честной картиной: жанры гораздо более похожи, чем предполагалось в первой таблице.

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

Теперь представьте себе последний отправленный вами сводный запрос. Если вы добавили HAVING COUNT(*) >= 30 к этому, изменится ли верхняя часть результата? Это основа данного руководства, и для большинства реальных отчетов ответ — да.

5. Полное до и после

Та же таблица, тот же вопрос: какие жанры игроки оценивают выше всего?

До

SELECT PrimaryGenre, AVG(PctPositive) AS avg_rating
FROM games
GROUP BY PrimaryGenre
ORDER BY avg_rating DESC;

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

после

-- ============================================================
-- STEP 3: Which genres do players actually rate highest?
-- WHY: A rating is only meaningful if enough people gave one,
--      and a genre average is only meaningful if the genre has
--      enough games. Both floors are here for that reason.
-- COVERAGE: 10,507 of 82,956 games have 500+ reviews. Eight of
--      28 genres clear the 500-game floor. The other 20 are
--      reported separately, not silently dropped.
-- ============================================================
--SELECT the genre, how many games are behind the number,
--   and their average positive rating:
--   COUNT(*)          = games in this genre that passed WHERE
--   AVG(PctPositive)  = mean positive rating across them
--FROM the full game catalogue
--WHERE the game itself has at least 500 reviews
--GROUP BY genre, so each genre collapses to one row
--HAVING at least 500 such games, so the average is stable
--ORDER BY average rating, highest first
SELECT PrimaryGenre,
       COUNT(*) AS games,
       ROUND(AVG(PctPositive), 1) AS avg_rating
FROM games
WHERE TotalReviews >= 500
GROUP BY PrimaryGenre
HAVING COUNT(*) >= 500
ORDER BY avg_rating DESC;

Размер группы теперь отображается в виде видимого столбца, поэтому читатель может самостоятельно оценить среднее значение. Оба этажа указаны не без причины. В строке покрытия указано, что было исключено. Формат комментариев тот же, что и у Как комментировать SQL, чтобы он обучал.

6. Крайние случаи, которые нарушают сводные запросы в реальных данных

Перед объяснением: жанр, о существовании которого вы знаете, полностью отсутствует в вашем сгруппированном результате. Не ноль. Отсутствующий. Что бы вы проверили в первую очередь?

Шесть вещей, каждая из которых стоит кому-то дня.

A WHERE фильтр очистил группу. Это ответ на предварительный вопрос. Группировка может создавать сегмент только для сохранившихся строк. WHERE. Если каждая игра определенного жанра имеет менее 500 обзоров, в этом жанре нет строк, которые можно было бы свернуть, и вообще нет выходной строки. Группа, которая должна читать ноль, вместо этого невидима, что является другой и более вводящей в заблуждение вещью.

COUNT(*) и COUNT(column) не одинаковы. COUNT(*) считает строки. COUNT(column) подсчитывает строки, где этого столбца нет NULL. В столбце с пропущенными значениями возвращаются разные числа, и только одно из них отвечает на ваш вопрос. СЧЕТ в SQL проходит всю семью.

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

Агрегаты пропускают NULL, и это перемещает ваше среднее значение. AVG делится на количество ненулевых значений, а не на количество строк. Таким образом, среднее значение по столбцу, пустому на 30 %, представляет собой среднее значение для 70 % ответивших, а это совсем другое утверждение.

Вы не можете использовать SELECT Псевдоним в HAVING повсюду. HAVING avg_rating > 80 работает в MySQL и SQLite и не работает в PostgreSQL и SQL Server, потому что SELECT бежит за HAVING. Повторяю полностью AVG(PctPositive) > 80 работает везде.

Группировка после объединения учитывает дубликаты. Если соединение умножило ваши строки, COUNT(*) теперь считает пары, а не вещи. COUNT(DISTINCT id) обычно это то, что вы имели в виду. Это наиболее распространенная неправильная цифра в реальной отчетности. SQL-СОЕДИНЕНИЯ объясняет, почему это происходит.