Статьи

SQL GROUP BY и функциональные зависимости: очень полезная функция

Декабрь 28, 2018

Реляционные базы данных определяют термин «функциональная зависимость» как таковой (из Википедии):

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

В SQL функциональные зависимости появляются всякий раз, когда существует уникальное ограничение (например, ограничение первичного ключа). Давайте предположим следующее:

1
2
3
4
5
CREATE TABLE actor (
  actor_id BIGINT NOT NULL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL
);

Можно сказать, что FIRST_NAME и LAST_NAME имеют функциональную зависимость от столбца ACTOR_ID .

Ницца. Ну и что?

Это не просто математическое утверждение, которое можно применить к уникальным ограничениям. Это чрезвычайно полезно для SQL. Это означает, что для каждого значения ACTOR_ID может быть только одно (функционально зависимое) значение FIRST_NAME и LAST_NAME . С другой стороны, это не так. Для любого заданного значения FIRST_NAME и / или LAST_NAME у нас может быть несколько значений ACTOR_ID , поскольку у нас может быть несколько действующих лиц с одинаковыми именами.

Поскольку для любого заданного значения ACTOR_ID может быть только одно соответствующее значение FIRST_NAME и LAST_NAME , мы можем опустить эти столбцы в предложении GROUP BY . Предположим также:

1
2
3
4
5
6
7
8
CREATE TABLE film_actor (
  actor_id BIGINT NOT NULL,
  film_id BIGINT NOT NULL,
   
  PRIMARY KEY (actor_id, film_id),
  FOREIGN KEY (actor_id) REFERENCS actor (actor_id),
  FOREIGN KEY (film_id) REFERENCS film (film_id)
);

Теперь, если мы хотим посчитать количество фильмов на одного актера, мы можем написать:

1
2
3
4
5
6
SELECT
  actor_id, first_name, last_name, COUNT(*)
FROM actor
JOIN film_actor USING (actor_id)
GROUP BY actor_id
ORDER BY COUNT(*) DESC

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

  • Выражения столбцов, которые появляются в предложении GROUP BY
  • Выражения столбцов, которые функционально зависят от набора выражений столбцов в предложении GROUP BY
  • Агрегатные функции

К сожалению, не все это поддерживают

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

1
2
3
4
5
6
7
SELECT
  actor_id, first_name, last_name, COUNT(*)
FROM actor
JOIN film_actor USING (actor_id)
GROUP BY actor_id, first_name, last_name
--                 ^^^^^^^^^^  ^^^^^^^^^ unnecessary
ORDER BY COUNT(*) DESC

Дальнейшее чтение:

Ссылка: SQL GROUP BY и функциональные зависимости: очень полезная функция от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и AND JOOQ .
37
Share: