Simulative

🔥 Задача с собеседования в e-commerce или про рекурсию в SQL

Мы столкнулись с тем, что на одном из технических интервью в e-commerce просили написать запрос, который выведет иерархию продукта. Хочется верить, что каждый из читающих сразу понял, что от него требуется. Но будем объективны, многие зависнут, мол, только с агрегациями разобрались, а это что еще за зверь.

💡 Давайте разбираться. Типичный пример иерархичной структуры - таблица сотрудников, где сами сотрудники также являются чьими-то менеджерами. То есть каждый сотрудник подотчетен другому, второй - третьему и так до тех пор, пока не останется тот, который никому не отчитывается. Это будет сотрудник верхнего уровня, и его manager_id будет равен 0 или NULL.

Теперь предположим, что нам нужно найти всех людей, которыми прямо или косвенно управляет сотрудник с employee_id = 2. Как мы можем сделать это в одном запросе? Можно было бы использовать JOIN, но мы не знаем сколько раз его придется написать, поэтому JOIN нам тут не помощник. Но мы знаем, что поиск должен начаться с employee_id = 2 и с каждым разом искать подчиненных глубже и глубже, пока не зайдет в тупик. Звучит как рекурсия!

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

WITH RECURSIVE subordinates_of_2 AS (
    SELECT employee_id,
           manager_id,
           full_name
    FROM employees WHERE employee_id = 2
    UNION ALL
    SELECT e.employee_id,
           e.manager_id,
           e.full_name
    FROM employees e 
    JOIN subordinates_of_2 s2 ON e.manager_id = s2.employee_id
)
SELECT * FROM subordinates_of_2 WHERE employee_id <> 2;

Часть WITH ... называется общим табличным выражением (CTE), за ней следует ключевое слово RECURSIVE, которое сообщает PostgreSQL, что следующее выражение должно быть выполнено рекурсивно. Что это значит?

Чтобы выполнить запрос рекурсивно, мы должны сообщить ему три вещи:

- Базовое условие - это первая часть запроса, в которой мы выбираем employee_id = 2.
- Ключевое слово UNION - сообщает PostgreSQL продолжать выполнять объединение для всех промежуточных строк, сгенерированных в результате рекурсии.
- Рекурсивная часть самого запроса - в нашем случае это условие, которое сопоставляет manager_id с employee_id, сгенерированными в предыдущих вызовах.

🤯 Голова кипит? У нас тоже! На первый взгляд довольно сложно понять, но посмотрим пошагово как это работает. Запрос начинается с создания списка сотрудников с employee_id = 2, затем продолжает объединять данные до тех пор, пока не останется ни одного сотрудника, с непустым manager_id. Поскольку конечный результат также включает менеджера верхнего уровня, мы отфильтровали его, используя WHERE employee_id <> 2.

С точки зрения синтаксиса, это все, что нужно для рекурсивного запроса. И да, мы признаем, что от этого голова кругом. Но есть целый пласт задач, которые можно решить только с помощью рекурсивных запросов и никак иначе. Поэтому владеть этим инструментом - крайне желательно, если вы решаете (или планируете) серьезные рабочие задачи.

👨🏻‍🎓 А чтобы глубже разобраться в SQL, приходите в наш Симулятор SQL

#sql #interview_problems

👍18🔥7❤1

2.35K viewsedited 16:08