Оконные функции и CTE: когда они ускоряют запрос, а когда прячут проблему?

Оконные функции считают агрегат «вдоль» набора строк, не схлопывая его: нумерация внутри группы (row_number), доступ к соседней строке (lag, lead), скользящие суммы. Их сила в том, что задачи вида «последний заказ каждого пользователя» или «доля строки в сумме по группе» решаются одним проходом, тогда как наивный вариант с коррелированным подзапросом делает проход на каждую строку. CTE (WITH) улучшает читаемость, но в PostgreSQL до версии 12 он был барьером оптимизации: содержимое материализовалось, и условия снаружи не проталкивались внутрь. С версии 12 непрерывно используемые CTE могут встраиваться, а поведение управляется явно через MATERIALIZED и NOT MATERIALIZED. Рекурсивный CTE (WITH RECURSIVE) — отдельный инструмент для деревьев и графов, и его почти всегда нужно ограничивать по глубине, иначе цикл в данных даст бесконечный запрос.