DISTINCT ON — скрытый чит-код PostgreSQL 🔑 При работе с периодическими данными, которые, например, сохраняем каждый день в таблицу, может возникнуть ситуация: нужно сгруппировать по какому-то признаку (например, name) и получить запись с датой, когда этот признак последний раз появлялся в таблице. 🚧 Неоптимальный способ (см. скрин) 1️⃣Сначала отбираем уникальные строки по нескольким атрибутам — date и name. 2️⃣Затем полученные строки разбиваем на нумерованные группы по атрибуту name. Строки внутри группы сортируем по date. 3️⃣ В последнем запросе выбираем только ту строку, у которой номер в группе = 1. То есть это последнее по дате появление строки с name в массиве данных. Чтобы выполнить эти 3 шага, можно использовать временные таблицы (CREATE TEMP TABLE), VIEW или даже MATERIALIZED VIEW. Потому что на 2 и 3 этапе вам нужны результаты работы предыдущего запроса. ⚡️ Можно сделать в 1 запрос with portal_project_raw as ( select distinct date_trunc('day',"timestamp") as date ,name ,organization_title ,organization_inn ,row_number() over (partition by name order by date_trunc('day',"timestamp") desc) as num from portal_project pp ) select date ,name ,organization_title ,organization_inn from portal_project_raw where num = 1 Здесь немного меняется принцип работы: ➖WITH ... AS создаёт временную таблицу. ➖В ней мы объединяем шаги 1 и 2. ➖Потом уже обращаемся к этому набору и берём только строки с num = 1. Казалось бы, всё круто: мы избавились от одного шага, сократили код. НО он по-прежнему не оптимальный: ➖ используем временную таблицу, ➖применяем DISTINCT + ROW_NUMBER() OVER (...), что с большой вероятностью не позволит использовать индексы. А что можно лучше сделать? 🚀 Оптимальный вариант Перед оптимизацией создаём индекс: CREATE INDEX CONCURRENTLY idx_portal_project_distinct_on ON portal_project ( name, (date_trunc('day', "timestamp")) DESC ) INCLUDE (organization_title, organization_inn); 👉 Что тут происходит: ➕ Индекс строится по name и дате (только усечённой до дня). ➕ DESC гарантирует, что внутри каждого name последние даты будут "сверху". ➕ INCLUDE (...) позволяет сразу хранить дополнительные поля (organization_title, organization_inn) в индексе, чтобы запрос можно было обслужить только индексом, без обращения к основной таблице. Теперь конечный запрос: SELECT DISTINCT ON (name) date_trunc('day', "timestamp") AS date, name, organization_title, organization_inn FROM portal_project ORDER BY name, date_trunc('day', "timestamp") DESC; 👉 Как это работает: ➕ DISTINCT ON (name) берёт первую строку для каждого уникального name. ➕ А за счёт ORDER BY ... DESC первой всегда будет строка с максимальной датой. ➕ Благодаря созданному индексу PostgreSQL может обслужить запрос напрямую через индекс: он уже отсортирован по name и дате, плюс все нужные поля включены. Это избавляет от сортировок и лишних проходов по таблице. 💡 Что такое DISTINCT ON? ❗️DISTINCT ON (col) — расширение PostgreSQL. ❗️Возвращает первую строку для каждого значения. ❗️А какая строка попадёт — решает ORDER BY. ❗️Отличие от обычного DISTINCT: там просто убираются дубликаты без контроля. ✅ Вывод: Иногда DISTINCT ON реально ускоряет запрос и он может использовать построенные индексы. Но это не серебряная пуля — для каждой задачи свой инструмент. Сохрани, чтобы не потерять, подписчик 😉 #MyDevAdventure #DataEngineer #PostgresMagic