Выборка Из Производной Таблицы (Derived Table)

COMMENTSОператоры процедурного SQL (PSQL)

Выборка из производной таблицы (derived table)

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

Синтаксис

<derived table> ::=
  (<select-query>)
  [[AS] derived-table-alias]
  [(<derived-column-aliases>)]

<derived-column-aliases> := column-alias [, column-alias ...]

<lateral-derived-table> ::= LATERAL <derived-table>

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

Производная таблица в запросе ниже выводит список имён таблиц в базе данных и количество столбцов в них.Запрос к производной таблице выводит количество полей, и количество таблиц с таким количеством полей.

SELECT
  FIELDCOUNT,
  COUNT(RELATION) AS NUM_TABLES
FROM (SELECT
        R.RDB$RELATION_NAME RELATION,
        COUNT(*) AS FIELDCOUNT
      FROM RDB$RELATIONS R
        JOIN RDB$RELATION_FIELDS RF
          ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
      GROUP BY RELATION)
GROUP BY FIELDCOUNT

Тривиальный пример, демонстрирующий использование псевдонима производной таблицы и списка псевдонимов столбцов (оба опциональные):

SELECT
  DBINFO.DESCR, DBINFO.DEF_CHARSET
FROM (SELECT *
      FROM RDB$DATABASE) DBINFO (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)

Note

Примечания:

Производные таблицы могут быть вложенными;
Производные таблицы могут быть объединениями и использоваться в объединениях. Они могут содержать агрегатные функции, подзапросы и соединения, и сами по себе могут быть использованы в агрегатных функциях, подзапросах и соединениях. Они также могут быть хранимыми процедурами или запросами из них. Они могут иметь предложения WHERE, ORDER BY и GROUP BY, указания FIRST, SKIP или ROWS и т.д.;
Каждый столбец в производной таблице должен иметь имя. Если этого нет по своей природе (например, потому что это — константа), то надо в обычном порядке присвоить псевдоним или добавить список псевдонимов столбцов в спецификации производной таблицы;
Список псевдонимов столбцов опциональный, но если он присутствует, то должен быть полным (т.е. он должен содержать псевдоним для каждого столбца производной таблицы);
Оптимизатор может обрабатывать производные таблицы очень эффективно. Однако если производная таблица включена во внутреннее соединение и содержит подзапрос, то никакой порядок соединения не может быть использован оптимизатором;
Ключевое слово LATERAL позволяет производной таблице ссылаться на поля из ранее перечисленных таблиц в текущем <table reference list>.Подробнее смотрите в разделе Соединение с LATERAL производными таблицами.

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

Предположим, что у нас есть таблица COEFFS, содержащая коэффициенты для ряда квадратных уравнений, которые мы собираемся решить.Она может быть определена примерно так:

CREATE TABLE coeffs (
  a DOUBLE PRECISION NOT NULL,
  b DOUBLE PRECISION NOT NULL,
  c DOUBLE PRECISION NOT NULL,
  CONSTRAINT chk_a_not_zero CHECK (a <> 0)
)

В зависимости от значений коэффициентов a, b и c, каждое уравнение может иметь ноль, одно или два решения.Мы можем найти эти решения с помощью одноуровневого запроса к таблице COEFFS, однако код такого запроса будет громоздким, а некоторые значения (такие, как дискриминанты) будут вычисляться несколько раз в каждой строке.

Если использовать производную таблицу, то запрос можно сделать гораздо более элегантным:

SELECT
  IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) AS sol_1,
  IIF (D > 0, (-b + sqrt(D)) / denom, NULL) AS sol_2
FROM
  (SELECT b, b*b - 4*a*c, 2*a FROM coeffs) (b, D, denom)

Если мы захотим показывать коэффициенты рядом с решениями уравнений, то мы можем модифицировать запрос следующим образом:

SELECT
  a, b, c,
  IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) sol_1,
  IIF (D > 0, (-b + sqrt(D)) / denom, NULL) sol_2
FROM
  (SELECT a, b, c, b*b - 4*a*c AS D, 2*a AS denom
   FROM coeffs)

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

Note

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

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE) T;

результатом этого запроса будет

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              C1214CD3-423C-406D-B5BD-95BF432ED3E3
C3                              EB176C10-F754-4689-8B84-64B666381154

Для материализации результата функции GEN_UUID вы можете воспользоваться следующим способом:

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE
      UNION ALL
      SELECT NULL FROM RDB$DATABASE WHERE 1=0) T;

результатом этого запроса будет

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C3                              80AAECED-65CD-4C2F-90AB-5D548C3C7279

или завернуть функцию GEN_UUID в подзапрос

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT
          (SELECT GEN_UUID() FROM RDB$DATABASE) AS X
      FROM RDB$DATABASE) T;

Эта особенность текущей реализации и она может быть изменена в следующих версиях сервера.

Латеральные производные таблицы

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

Example 1. Запросы с латеральными производными таблицами

select dt.population, dt.city_name, c.country_name
from (select distinct country_name from cities) AS c,
LATERAL (select first 1 city_name, population
         from cities
         where cities.country_name = c.country_name
         order by population desc) AS dt;

select salespeople.name,
       max_sale.amount,
       customer_of_max_sale.customer_name
from salespeople,
LATERAL ( select max(amount) as amount from all_sales
          where all_sales.salesperson_id = salespeople.id
         ) as max_sale,
LATERAL ( select customer_name from all_sales
          where all_sales.salesperson_id = salespeople.id
            and all_sales.amount = max_sale.amount
        ) as customer_of_max_sale;