Статьи

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

Ноябрь 6, 2017

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

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

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

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 значений, поскольку у нас может быть несколько действующих лиц с одинаковыми именами.

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

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)
);

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

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 предложении.

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

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

35
Share: