Первичные ключи и группы By - краткое исследование SQL

Изучение тонкостей взаимодействия между первичными ключами SQL и операторами `Group By`

Сегодня знакомый задал мне интересный вопрос по SQL, но не в типичном для запроса смысле; его вопрос больше касался понимания базовой структуры SQL. Вот контекст:

1. Проблема

Это упражнение взято с этой страницы упражнений PostgreSQL и имеет следующую схему:

Вопрос SQL, который задает веб-сайт, не важен, но опубликованное решение таково:

SELECT  facs.name AS name,
 facs.initialoutlay/((sum(
 CASE
  WHEN memid = 0 
  THEN slots * facs.guestcost
 ELSE slots * membercost
 END)/3) - facs.monthlymaintenance) AS months  
FROM cd.bookings bks  
INNER JOIN cd.facilities facs   
 ON bks.facid = facs.facid  
GROUP BY facs.facid 
ORDER BY name;

Мой друг попытался переключить строку GROUP BY facs.facid на GROUP BY facs.name, что нарушило запрос с сообщением:

column "facs.initialoutlay" must appear in the GROUP BY clause or be used in an aggregate function

Мой друг задал вопрос:

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

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

2. Подход

Во-первых, давайте проверим очевидное: действительно ли эти столбцы уникальны и используются ли в нашем запросе несколько комбинаций столбцов (name, facid, initialoutlay, monthlymaintenance)? Чтобы проверить это, мы смотрим на различные комбинации этих столбцов в таблице facilities .

SELECT DISTINCT facs.name as name, facs.facid, facs.initialoutlay, facs.monthlymaintenance
    FROM cd.bookings bks
    INNER JOIN cd.facilities facs
        ON bks.facid = facs.facid
ORDER BY facid;
Output:
╔═════════════════╦═══════╦═══════════════╦════════════════════╗
║      name       ║ facid ║ initialoutlay ║ monthlymaintenance ║
╠═════════════════╬═══════╬═══════════════╬════════════════════╣
║ Tennis Court 1  ║     0 ║         10000 ║                200 ║
║ Tennis Court 2  ║     1 ║          8000 ║                200 ║
║ Badminton Court ║     2 ║          4000 ║                 50 ║
║ Table Tennis    ║     3 ║           320 ║                 10 ║
║ Massage Room 1  ║     4 ║          4000 ║               3000 ║
║ Massage Room 2  ║     5 ║          4000 ║               3000 ║
║ Squash Court    ║     6 ║          5000 ║                 80 ║
║ Snooker Table   ║     7 ║           450 ║                 15 ║
║ Pool Table      ║     8 ║           400 ║                 15 ║
╚═════════════════╩═══════╩═══════════════╩════════════════════╝

Name и facid уникальны для каждой строки и от 1 до 1, и каждая пара имеет только одно значение initialoutlay и monthlymaintenance. Интуитивно понятно, что группировка по одному из этих двух столбцов должна быть функционально эквивалентна группировке по другому. Так почему же для этого запроса не работает группировка по name?

3. Первичный ключ

Как вы можете догадаться, если вы знакомы с SQL, это проблема первичного ключа. Для тех, кто не знает, первичный ключ - это значение, однозначно идентифицирующее каждую строку таблицы, и оно никогда не может быть NULL для строки. Но как найти назначенный первичный ключ таблицы?

Быстрый поиск в Google дает нам следующий код из PostgreSQL wiki. Выполнение этого в разделе запроса веб-сайта дает следующее:

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'cd.facilities'::regclass
AND    i.indisprimary;
Output:
╔═════════╦═══════════╗
║ attname ║ data_type ║
╠═════════╬═══════════╣
║ facid   ║ integer   ║
╚═════════╩═══════════╝

Итак, facid - это первичный ключ таблицы facilities! Теперь мы подтвердили вероятную причину, но по какой причине группировка по первичному ключу позволяет добавлять столбцы без агрегатной функции, как мы это делаем с facs.initialoutlay и facs.monthlymaintenance?

SELECT  facs.name AS name,
 facs.initialoutlay/((sum(        /* <=========== */
 CASE
  WHEN memid = 0 
  THEN slots * facs.guestcost
 ELSE slots * membercost
 END)/3) - facs.monthlymaintenance) AS months /* <=========== */
FROM cd.bookings bks  
INNER JOIN cd.facilities facs   
 ON bks.facid = facs.facid  
GROUP BY facs.facid 
ORDER BY name;
/* Shouldn't these two columns be inside of an aggregation? */

Чтобы ответить на этот вопрос, мы смотрим Справочную документацию PostgreSQL, специально для GROUP BY:

Когда присутствует GROUP BY или присутствуют какие-либо агрегатные функции, недопустимо, чтобы выражения списка SELECT ссылались на несгруппированные столбцы, за исключением агрегатных функций, или когда несгруппированный столбец функционально зависит от сгруппированных столбцов, поскольку в противном случае было бы больше, чем одно возможное значение, возвращаемое для несгруппированного столбца. Функциональная зависимость существует, если сгруппированные столбцы (или их подмножество) являются первичным ключом таблицы, содержащей несгруппированный столбец.

Как пользователь Stack Overflow Тони Л. кладет это:

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

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

Повторим: Учитывая, что мы смотрим на одну таблицу, группировка по ее первичному ключу аналогична группировке по всему, что равносильно отсутствию группировки вообще - каждый из этих подходов приведет к по одной группе в ряду. Как только вы это поймете, вы поймете суть проблемы.

Из-за этого работают такие запросы:

1. Group by everything:
SELECT *
FROM cd.facilities f
GROUP BY facid, name, membercost, guestcost, initialoutlay, monthlymaintenance
LIMIT 5
OUTPUT:

который функционально идентичен

2. Don't group by anything
SELECT * 
FROM cd.facilities f
LIMIT 5
and 
3. Group by primary key but don't aggregate
SELECT * 
FROM cd.facilities f
GROUP BY facid
LIMIT 5

Все они выводят одинаковые значения! Теперь у нас есть решение.

3. Ответ

Причина, по которой первый запрос работает, заключается просто в том, что facid является первичным ключом, а name - нет. Несмотря на то, что они обе уникальны для каждой строки, таблица facilities была создана с facid в качестве первичного ключа, поэтому она получает особую обработку при использовании в группе, как описано выше.

Вот некоторые альтернативные решения тому, что они опубликовали:

1. Group by name then aggregate everything else
SELECT facs.name as name,
 AVG(facs.initialoutlay)/((sum(case
   when memid = 0 then slots * facs.guestcost
   else slots * membercost
  end)/3) - AVG(facs.monthlymaintenance) as months
 FROM cd.bookings bks
 INNER JOIN cd.facilities facs
  ON bks.facid = facs.facid
 GROUP BY facs.name
ORDER BY name;
Why this works:
Because facs.name is unique to each row in the facilities table just as facid was, we group by facs.name then add AVG calls around previously unaggregated facilities columns.
2. Group by all facility columns used in the select statement
SELECT  facs.name as name,
 facs.initialoutlay/((sum(case
   when memid = 0 then slots * facs.guestcost
   else slots * membercost
  end)/3) - facs.monthlymaintenance) as months
 from cd.bookings bks
 INNER JOIN cd.facilities facs
  ON bks.facid = facs.facid
 GROUP BY facs.name, facs.initialoutlay, facs.guestcost,     
   facs.monthlymaintenance 
ORDER BY name;
Why this works:
This includes all the values used in the SELECT statement in the GROUP BY, which is the normal GROUP BY logic and syntax.

4. Дополнительный вопрос

На этом основной вопрос исчерпан, но если вы заинтересованы в изучении дополнительных причуд, я столкнулся со следующей проблемой при решении опубликованной проблемы. Если кто-то из вас знает, почему, не стесняйтесь пинговать меня или оставлять комментарии!

/* This works (manually lists all columns in the group by)*/
SELECT *
FROM cd.facilities f
GROUP BY facid, name, membercost, guestcost, initialoutlay, monthlymaintenance
LIMIT 5
/* This does not (selecting all columns using f.*) */
SELECT *
FROM cd.facilities f
GROUP BY f.*
LIMIT 5

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