Реляционные базы данных и язык SQL. Обобщённые табличные выражения в PostgreSQL

Доброго здоровья читателям моего канала programmer's notes. Подписываемся и ставим лайки.

Обобщённые табличные выражения (CTE) в PostgreSQL, рекурсивные запросы

Сегодня рассмотрим ещё один механизм совместного использования нескольких запросов. Это обобщённые табличные выражения или Common Table Expression (CTE).

Пример обобщённое табличного выражения в PostgreSQL

Понять, что такое обобщённое табличное выражение (CTE) очень просто. Это ещё один способ объединить несколько запросов, вместе с использованием подзапросов и объединением запросов. Специальный оператор (with) позволяет объединить несколько запросов, которые могут обращаться друг к другу. Ну, а далее можно писать уже запрос, который будет обращаться к любому из запросов в CTЕ или к нескольким запросов.

Как и ранее будем обращаться к уже известной схеме данных (см. Рисунок 1).

Рисунок 1. Используемая база данных

Поставим такую задачу. Нужно получить список лучших студентов. Как их определить? Один из вариантов следующий. Нужно найти максимальную среднюю оценку. Лучшими студентами будут те, средние оценки которых отличаются от максимальной средней не более чем на некоторую дробную величину. У нас взято значение 0.2 (не более чем на 20%). Смотрим запрос ниже.

with stud as (
select id, fio from student
), av as (
select id_s, avg(ocenka) a
from ocenki
group by id_s
), mav as (
select max(av.a) as m
from av
)
select stud.fio, concat(' (', cast(stud.id as character), ')') as id
from av inner join stud on av.id_s=stud.id
where (((select m from mav)-av.a)/(select m from mav))<=0.2;

Рисунок 2. Картинка запроса выше. Во-первых, это просто красиво

Результат выполнения рисунок 3

Рисунок 3. Результат выполнения запроса из рисунка 2

Пояснение к запросу (см. рисунок 2).

• Мы видим, что каждый запрос в CTE имеет имя (stud, av, mav). В начале идёт ключевое слово with, а далее идут запросы через запятую.
• Запросы в CTE могут обращаться к таблицам, а могут друг к другу. Например запрос mac обращается к запросу av.
• Ну, а далее когда обобщённое табличное выражение готово, можно обращаться из обычного запроса, к каждому запросу в CTE.
• Запрос ниже использует все запросы в CTE.
• В запросе используются две встроенные функции PostgreSQL: concat() - объединение строк (конкатенация) и cast() - преобразование типов (преобразования числа в строку).

Честно вам скажу, я почти не использую CTE, мне нравятся подзапросы и объединения запросов. Но у всех свои вкусы. Я не увидел, что использование CTE сильно упрощает сложные запросы. Но данный механизм очень интересен.

Рекурсивные запросы на основе обобщённых табличных выражений

Оказывается есть рекурсивный вариант использования CTE. Ниже представлен вариант рекурсивного подсчёта n!

with recursive fact (n, f) as (
select 1 as n, 10 as f
union all
select n+1, f*n
from fact
where n < 10
)
select max(fact.f) from fact;

Рисунок 4. Вычисление факториала, картинка по запросу выше. Во-первых, это красиво

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

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

Рассмотрим таблицу, представленную на рисунке 5. Всего три столбца. txt - текст сообщения, id_rec ссылка на первичный ключ, т.е. тот комментарий, который комментируется.

Рисунок 5. Таблица с нутренней связью

Содержимое таблицы см. на рисунке 6.

Рисунок 6. Содержимое таблицы (рисунок 5)

Т.е. мы имеем древообразную структуру, состоящую сообщений на сообщение. Самая первая запись с id_re=0.

Естественно возникает задача вывести ветку сообщений. Это можно решить, используя рекурсивный запрос CTE. Внизу дан запрос, выводящий ветку сообщений, начиная с id=5.

with recursive fr (id, id_rec, txt, n) as (
select r.id, r.id_rec, r.txt, 0 as n
from rec r
where id=5 --начиная с данного id
union all
select r1.id, r1.id_rec, r1.txt, n+1
from rec r1 inner join fr b on b.id=r1.id_rec
)
select * from fr;

Рисунок 7. Рекурсивно получить данные из таблицы (рисунок 5). Во-первых, это красиво

Результат выполнения запроса см. на рисунке 8. n, как вы уже, наверное, поняли, в данном случае нумерует уровни (узлы) дерева.

Рисунок 8. Ветка сообщений.

Пока всё!

Хорошего программирования. Оставляйте свои комментарии, не забывайте про лайки и подписывайтесь на мой канал programmer's notes.

Ваша программа в принципе работает, но если подойти к этому обобщённо, то она никуда не годится