Запросы WITH - Общие табличные выражения (CTE) Иерархические и рекурсивные запросы в SQL

Привет, Вы узнаете о том , что такое запросы with, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое запросы with, общие табличные выражения, cte, рекурсивные запросы , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.

Иерархический запрос - это тип запроса SQL, который обрабатывает данные иерархической модели. Они являются частными случаями более общих рекурсивных запросов с фиксированной точкой, которые вычисляют транзитивные замыкания.

В стандартном SQL: 1999 иерархические запросы реализуются с помощью рекурсивных общих табличных выражений (CTE). В отличие от более раннего предложения Oracle о подключении, рекурсивные CTE были спроектированы с семантикой фиксированной точки с самого начала. Рекурсивные CTE из стандарта были относительно близки к существующей реализации в IBM DB2 версии 2 . Рекурсивные CTE также поддерживаются Microsoft SQL Server (начиная с SQL Server 2008 R2) , Firebird 2.1 , PostgreSQL 8.4+ , SQLite 3.8.3+ , IBM Informix версии 11.50+, CUBRID и MySQL 8.0.1+ . Tableau и TIBCO Spotfire не поддерживают CTE, в то время как в реализации Oracle 11g Release 2 отсутствует семантика точек фиксации.

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

WITH предоставляет способ записывать дополнительные операторы для применения в больших запросах. Эти операторы, которые также называют общими табличными выражениями (Common Table Expressions, CTE), можно представить как определения временных таблиц, существующих только для одного запроса. Дополнительным оператором в предложении WITH может быть SELECT, INSERT, UPDATE или DELETE, а само предложение WITH присоединяется к основному оператору, которым также может быть SELECT, INSERT, UPDATE или DELETE.

CTE играет роль SQL представления, которое создается в рамках одного запроса и, не сохраняется как объект схемы.

Распространенное табличное выражение (CTE) можно рассматривать как временный набор результатов, определенный в области выполнения одного оператора SELECT, INSERT, UPDATE, DELETE или CREATE VIEW. CTE похож на производную таблицу в том смысле, что он не сохраняется как объект и действует только в течение запроса. Однако, в отличие от производной таблицы, CTE может иметь самоссылку и ссылаться на нее несколько раз в одном запросе.

Все аналогично использованию представления за исключением обязательных скобок, ограничивающих запрос; формально, достаточно лишь заменить CREATE VIEW на WITH. Как и для представления, в скобках после имени CTE может быть указан список столбцов, если нам потребуется включить их не все из подлежащего запроса и/или переименовать

CTE может использоваться для:

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

Использование CTE предлагает преимущества улучшенной читаемости и простоты обслуживания сложных запросов. Запрос может быть разделен на отдельные, простые, логические строительные блоки. Эти простые блоки могут затем использоваться для построения более сложных временных CTE до тех пор, пока не будет создан окончательный набор результатов.

Общее табличное выражение, или CTE, (в SQL) - это временный именованный набор результатов, полученный из простого запроса и определенный в пределах области выполнения оператора SELECT, INSERT, UPDATEили DELETE.

CTE можно рассматривать как альтернативу производным таблицам (подзапросам), представлениям и встроенным пользовательским функциям.

общие табличные выражения поддерживаются Teradata, DB2, [[Firebird], Microsoft SQL Server, Oracle (с рекурсией начиная с версии 11g 11g), PostgreSQL (начиная с 8.4), MariaDB (начиная с 10.2), MySQL (начиная с 8.0), SQLite (начиная с 3.8.3), HyperSQL и H2 (экспериментальные) . Oracle называет CTE «факторингом подзапроса».

Синтаксис для рекурсивного CTE следующий:

WITH [RECURSIVE] with_query [, ...]
SELECT...

где синтаксис with_query:

query_name [ (column_name [,...]) ] AS (SELECT ...)

Рекурсивные CTE (или «рекурсивный факторинг подзапросов»[10] в жаргоне Oracle) могут использоваться для обхода отношений (в виде графиков или деревьев), хотя синтаксис гораздо более сложен, поскольку не создаются автоматические псевдостолбцы (как LEVEL ниже); если они желательны, они должны быть созданы в коде. Об этом говорит сайт https://intellect.icu . См. Документацию MSDN или документацию IBM[11] для учебных примеров.

Ключевое словоRECURSIVE обычно не требуется после WITH в системах, отличных от PostgreSQL.[12]

В SQL: 1999 рекурсивный (CTE) запрос может появляться везде, где разрешен запрос. Например, можно назвать результат, используя CREATE [RECURSIVE] VIEW . Используя CTE внутри INSERT INTO, можно заполнить таблицу данными, сгенерированными из рекурсивного запроса; случайная генерация данных возможна с использованием этой техники без использования процедурных утверждений.[13]

Некоторые базы данных, такие как PostgreSQL, поддерживают более короткий формат CREATE RECURSIVE VIEW, который внутренне преобразуется в кодирование WITH RECURSIVE. [14]

Примером рекурсивного запроса, вычисляющего факториал чисел от 0 до 9, является следующий:

WITH RECURSIVE temp (n, fact) AS 
(SELECT 0, 1 -- Initial Subquery
  UNION ALL 
 SELECT n+1, (n+1)*fact FROM temp -- Recursive Subquery 
        WHERE n < 9)
SELECT * FROM temp;

Альтернативный синтаксис - нестандартная конструкция CONNECT BY; он был введен Oracle в 1980-х годах. До Oracle 10g эта конструкция была полезна только для обхода ациклических графов, поскольку возвращала ошибку при обнаружении любых циклов; в версии 10g Oracle представила функцию NOCYCLE (и ключевое слово), благодаря чему обход работает и при наличии циклов.[15]

CONNECT BY поддерживается EnterpriseDB, базой данных Oracle,[16] CUBRID,[17] IBM Informix и DB2, хотя только если он включен как режим совместимости. Синтаксис выглядит следующим образом:

 SELECT select_list
 FROM table_expression
 [ WHERE ... ]
 [ START WITH start_expression ]
 CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
 [ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ...
 [ GROUP BY ... ]
 [ HAVING ... ]
 ...

Например,

 SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr "manager"
 FROM emp START WITH mgr IS NULL
 CONNECT BY PRIOR empno = mgr;

Вывод вышеприведенного запроса будет выглядеть следующим образом:

 level |  employee   | empno | manager
-------+-------------+-------+---------
     1 | KING        |  7839 |
     2 |   JONES     |  7566 |    7839
     3 |     SCOTT   |  7788 |    7566
     4 |       ADAMS |  7876 |    7788
     3 |     FORD    |  7902 |    7566
     4 |       SMITH |  7369 |    7902
     2 |   BLAKE     |  7698 |    7839
     3 |     ALLEN   |  7499 |    7698
     3 |     WARD    |  7521 |    7698
     3 |     MARTIN  |  7654 |    7698
     3 |     TURNER  |  7844 |    7698
     3 |     JAMES   |  7900 |    7698
     2 |   CLARK     |  7782 |    7839
     3 |     MILLER  |  7934 |    7782
(14 rows)

• LEVEL
• CONNECT_BY_ISLEAF
• CONNECT_BY_ISCYCLE
• CONNECT_BY_ROOT

В следующем примере возвращается фамилия каждого сотрудника в отделе 10, каждого менеджера выше этого сотрудника в иерархии, количества уровней между менеджером и сотрудником и пути между ними:

   SELECT ename "Employee", CONNECT_BY_ROOT ename "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(ename, '/') "Path"
   FROM emp
   WHERE LEVEL > 1 and deptno = 10
   CONNECT BY PRIOR empno = mgr
   ORDER BY "Employee", "Manager", "Pathlen", "Path";

• SYS_CONNECT_BY_PATH

Рекурсивные CTE

Рекурсивные общие табличные выражения (CTE) были реализацией стандарта SQL: 1999 для иерархических запросов. Первые реализации Рекурсивных CTE начали появляться в 2007 году. Рекурсивные CTE из стандарта были относительно близки к существующей реализации в IBM DB2 версии 2. Рекурсивные CTE в конечном итоге поддерживались Microsoft SQL Server (начиная с SQL Server 2008 R2), Firebird 2.1, PostgreSQL 8.4+, SQLite 3.8.3+, Oracle 11g Release 2 и IBM Informix версии 11.50+.

Без выражений Common-table или выражения connected-by все еще возможно выполнять иерархические запросы с помощью пользовательских рекурсивных функций, но они, как правило, приводят к очень сложному SQL.

CTE в MariaDB

В MariaDB нерекурсивный CTE в основном считается локальным представлением запроса, синтаксис которого более читабелен, чем вложенный FROM (SELECT…). CTE может ссылаться на другое и на него можно ссылаться из нескольких мест.

Таким образом, CTE аналогичны производным таблицам. Например:

SQL с производной таблицей:

SQL с CTE:

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

С помощью рекурсивных CTE вы можете добиться того, что было бы очень трудно сделать со стандартным SQL, и с более высокой скоростью выполнения. Они могут помочь решить многие типы бизнес-задач и даже упростить некоторую сложную логику SQL/приложения вплоть до простого рекурсивного вызова базы данных.

Некоторые примеры использования для рекурсивного CTE: найти пробелы в данных, создать организационные диаграммы и создать тестовые данные.

WITH RECURSIVE означает рекурсивный CTE. Ему присваивается имя, за которым следует тело (основной запрос):

Ниже приведен рекурсивный CTE, который насчитывает от 1 до 50.
  

Вышеприведенное утверждение печатает ряд чисел от 1 до 49.

CTE в MySQL

MySQL 8.0 добавляет CTE через стандартное ключевое слово WITH, почти так же, как это реализовано в конкурирующих продуктах.

Чтобы указать общие табличные выражения, используйте предложение WITH, содержащее один или несколько разделенных запятыми подпунктов. Каждый подпункт обеспечивает подзапрос, который создает набор результатов и связывает имя с подзапросом. В следующем примере определяются CTE с именами cte1 и cte2 в предложении WITH и ссылки на них в SELECT верхнего уровня, который следует за предложением WITH:

Сразу перед SELECT для операторов, которые включают оператор SELECT:

• INSERT … WITH … SELECT …
• REPLACE … WITH … SELECT …
• CREATE TABLE … WITH … SELECT …
• CREATE VIEW … WITH … SELECT …
• DECLARE CURSOR … WITH … SELECT …
• EXPLAIN … WITH … SELECT …

Рекурсивное общее табличное выражение – это выражение, имеющее подзапрос, который ссылается на собственное имя. Например:

  

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

Вау!! 😲 Ты еще не читал? Это зря!

• подзапросы SQL
• оконная функция , оконные функции , window functions ,

Исследование, описанное в статье про запросы with, подчеркивает ее значимость в современном мире. Надеюсь, что теперь ты понял что такое запросы with, общие табличные выражения, cte, рекурсивные запросы и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL