Здравствуйте, уважаемые подписчики и гости канала!
Продолжаю цикл статей по одной из моих любимых баз данных - PostgreSQL. Сори, за картинки, но дзен пока не позволяет нормально форматировать код. В самом конце статьи будут запросы для копипасты не картинкой.
Работаю я с этой БД уже достаточно давно — лет 8 наверно. Переходили с MySQL по заданию от моего руководителя, который почитал статейку и поверил в PG.
Тогда, да и долго еще после мы использовали БД как обычно, без особых изысков, разве, что секция RETURNING нам очень зашла — это особенная радость после mysql во всяком случае тех годов.
Опа! Я записал видос по этой теме - го смотреть
Подборка видео всех видео по PG - https://dzen.ru/suite/37b67ffa-176d-493a-b1a8-4762f79e3753
В общем ближе к сути — кто не знает, в PostgreSQL как и в любых нормальных БД, есть поддержка оконных функций. Речь не о OS Windows если что. Оконные функции — это такой особый вид функций, расширяющих аналитическую часть СУБД. Они не уменьшают и не увеличивают количество строк, как GROUP BY или JOIN, а добавляют действительно новую функциональность, например:
- Посчитать сумму с накоплением (нарастающий итог)
- Вывести список сотрудников с колонкой — процент отклонения ЗП сотрудника от средней в отделе. При этом в таблице нужны все сотрудники всех отделов
- Посчитать общее кол-во записей без учета limit, offset
- Получить номер строки выборки. А если показалось легко, то усложните до — получить номер строки сотрудника, среди его отдела с обратной сортировкой по имени. И все в одной таблице
К слову сказать, для Google BigQuery (которую я тоже сильно люблю) так же применимы знания, полученные вами в этой статье, разве что синтаксис может незначительно отличаться.
Самое важное, что надо понимать, что пишется это примерно так:
ФУНКЦИЯ(ВАШЕ_ПОЛЕ) OVER(ТУТ МНОГО НАСТРОЕК ИМЕННО ОКОННЫХ ФУНКЦИЙ) as ИМЯ_НОВОГО_ПОЛЯ.
Оконные функции имеют свои дополнительные функции, а так же могут использоваться вместе с функциями агрегации. Вот и все, дальше примеры.
Кстати, у меня есть отличная статья про использование json в Postgres, почитай, ведь json не мешает использовать оконные функции, возможно ты найдешь там полезное для себя.
Это оптимальнее подзапросов (как правило)
В SQL всегда можно разными путями решить задачу и этот не исключение - в mysql до относительно недавнего времени тоже не было поддержи оконных функций и жили же люди. Жили, да, но неудобно раз поддержку добавили.
В общем, вы можете наколхозить четырехэтажных запросов, но зачем, если можно этого не делать и написать элегантный код. При этом он не является просто синтаксическим сахаром - это нормальная полноценная функциональность.
Тут считаем процентное отклонение зарплаты сотрудника относительно его отдела/компании (organizationId):
Я с трудом могу представить более выразительный вариант это сделать. Напишите в комментах, если знаете. Однако — это же вершина айсберга знаний про window functions в sql. Опять же повторю, что все нормальные БД это поддерживают, так как это стандарт SQL.
Читайте на русском от ребят из Postgres Pro - ссылка.
И у них же про список функций - ссылка.
Вот простейший пример как получить данные с лимитом и сразу же общее количество строк в выборке без учета LIMIT / OFFSET. При это Postgres гарантирует, что сделает все это за один проход по данным.
Нарастающий итог в SQL (сумма с накоплением)
А это тоже самое, но в рамках organizationId:
Согласитесь впечатляет?
Есть только один нюанс «rows between unbounded preceding and current row» - вещь в данном запросе обязательная, так как говорит брать строку до текущей, но это такой местный PG хардкод на уровне их ядра. Про супер современные версии PG не проверял, но вроде это всё, что он пока из sql стандарта поддерживает, но даже это просто супер.
Получить разницу с минимальной зарплатой в отделе
И все за один проход по таблице!
Резюме
Читая пост, возможно вы подумали, что все то же самое можно получить на обычных группировках, join-ах и позапросах. Обычно так и есть, но мысля так, вы ограничиваете свой инструментарий, а опять повторюсь - это SQL стандарт, а не какая-то нить поделка неизвестного художника, а значит знание полезное и для вас и для вашего проекта.
Примеры запросов
Я не нашел более красивого варианта вывода запросов для копипасты. Напишите в комментах, если будут идеи как это лучше делать на Дзене.
SELECT name, salary, "organizationId",
ROUND(salary * 100 / first_value(salary) over(PARTITION BY "organizationId" ORDER BY salary ASC) - 100, 2) as perc_diff_with_small_salary
FROM employee
SELECT "name", salary, "organizationId",
SUM(salary) over(PARTITION BY "organizationId" rows between unbounded preceding and current row) as total
FROM employee
А на этом всё, спасибо за внимание!
Подписывайтесь на канал, ставьте лайки, оставляйте комментарии - это помогает продвижению в Дзене.
Кроме этого:
Подписывайтесь в Telegram: https://t.me/lets_goto_it
#postgres #postgresql #sql #window functions #совет профи #dba #базы данных #database #программирование #разработка сайтов
