Как посчитать все на свете одним sql запросом

Как посчитать всё на свете одним SQL-запросом. Оконные функции PostgreSQL

Я с удивлением обнаружил, что многие разработчики, даже давно использующие postgresql, не понимают оконные функции, считая их какой-то особой магией для избранных. Ну или в лучшем случае «копипастят» со StackOverflow выражения типа «row_number() OVER ()», не вдаваясь в детали. А ведь оконные функции — полезнейший функционал PostgreSQL.
Попробую по-простому объяснить, как можно их использовать.

Для начала хочу сразу пояснить, что оконные функции не изменяют выборку, а только добавляют некоторую дополнительную информацию о ней. Т.е. для простоты понимания можно считать, что postgres сначала выполняет весь запрос (кроме сортировки и limit), а потом только просчитывает оконные выражения.

Синтаксис примерно такой:

Окно — это некоторое выражение, описывающее набор строк, которые будет обрабатывать функция и порядок этой обработки.
Причем окно может быть просто задано пустыми скобками (), т.е. окном являются все строки результата запроса.

Например, в этом селекте к обычным полям id, header и score просто добавится нумерация строк.

В оконное выражение можно добавить ORDER BY, тогда можно изменить порядок обработки.

Обратите внимание, что я добавил еще и в конце всего запоса ORDER BY id, при этом рейтинг посчитан все равно верно. Т.е. посгрес просто отсортировал результат вместе с результатом работы оконной функции, один order ничуть не мешает другому.

Дальше — больше. В оконное выражение можно добавить слово PARTITION BY [expression],
например row_number() OVER (PARTITION BY section), тогда подсчет будет идти в каждой группе отдельно:

Если не указывать партицию, то партицией является весь запрос.

Тут сразу надо немного сказать о функциях, которые можно использовать, так как есть очень важный нюанс.
В качестве функции можно использовать, так сказать, истинные оконные функции из мануала — это row_number(), rank(), lead() и т.д., а можно использовать функции-агрегаты, такие как: sum(), count() и т.д. Так вот, это важно, агрегатные функции работают слегка по-другому: если не задан ORDER BY в окне, идет подсчет по всей партиции один раз, и результат пишется во все строки (одинаков для всех строк партиции). Если же ORDER BY задан, то подсчет в каждой строке идет от начала партиции до этой строки.

Давайте посмотрим это на примере. Например, у нас есть некая (сферическая в вакууме) таблица пополнений балансов.

и мы хотим узнать заодно, как менялся остаток на балансе при этом:

Т.е. для каждой строки идет подсчет в отдельном фрейме. В данном случае фрейм — это набор строк от начала до текущей строки (если было бы PARTITION BY, то от начала партиции).

Если же мы для агрегатной фунции sum не будем использовать ORDER BY в окне, тогда мы просто посчитаем общую сумму и покажем её во всех строках. Т.е. фреймом для каждой из строк будет весь набор строк
от начала до конца партиции.

Вот такая особенность агрегатных функций, если их использовать как оконные. На мой взгляд, это довольно-таки странный, интуитивно неочевидный момент SQL-стандарта.

Оконные функции можно использовать сразу по несколько штук, они друг другу ничуть не мешают, чтобы вы там в них не написали.

Если у вас много одинаковых выражений после OVER, то можно дать им имя и вынести отдельно с ключевым словом WINDOW, чтобы избежать дублирования кода. Вот пример из мануала:

Здесь w после слова OVER идет без уже скобок.

Результат работы оконной функции невозможно отфильтровать в запросе с помощью WHERE, потому что оконные фунции выполняются после всей фильтрации и группировки, т.е. с тем, что получилось. Поэтому чтобы выбрать, например, топ 5 новостей в каждой группе, надо использовать подзапрос:

Еще пример для закрепления. Помимо row_number() есть несколько других функций. Например lag, которая ищет строку перед последней строкой фрейма. К примеру мы можем найти насколько очков новость отстает от предыдущей в рейтинге:

Прошу в коментариях накидать примеров, где особенно удобно применять оконные фунции. А также, какие с ними могут возникнуть проблемы, если таковые имеются.

Подписывайтесь на подкаст о разработке "Цинковый прод", где мы обсуждаем базы данных, языки программирования и всё на свете!

Агрегатные функции SQL — SUM, MIN, MAX, AVG, COUNT

Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.

Функция SQL SUM

Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:

После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

Сначала работать будем с базой данных фирмы — Company1. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке .

Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Для получения суммы размеров всех заработных плат используем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Этот запрос вернёт значение 287664,63.

А теперь упражнение для самостоятельного решения. В упражнениях уже начинаем усложнять задания, приближая их к тем, что встречаются на практике.

Пример 2. Вывести сумму комиссионных, получаемых всеми сотрудниками с должностью Clerk.

Функция SQL MIN

Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.

Пример 3. База данных и таблица — те же, что и в примере 1.

Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Запрос вернёт значение 10505,90.

И вновь упражнение для самостоятельного решения. В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:

Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.

Функция SQL MAX

Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.

Пример 5. База данных и таблица — те же, что и в предыдущих примерах.

Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Запрос вернёт значение 18352,80

Пришло время упражнения для самостоятельного решения.

Пример 6. Вновь работаем с двумя таблицами — Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц).

6 SQL-запросов, о которых должен знать каждый дата-инженер

Знание продвинутого синтаксиса SQL необходимо и новичку, и опытному дата-инженеру или аналитику данных.

В связи с бурным ростом объема данных все более важным становится умение очень быстро их анализировать.

Объем данных на этом графике показан в зеттабайтах.

Есть много очень вместительных нереляционных хранилищ, которые отлично выполняют свою работу, поддерживая массовое горизонтальное масштабирование с низкими затратами. Однако они не заменяют высококачественные хранилища на основе SQL, а лишь дополняют их.

Высококачественными и очень надежными для относительно естественного моделирования данных их делают ACID-свойства SQL.

Я и сам дата-инженер, давно использую SQL и знаю, как важно быстрее писать сложные запросы. И продвинутый синтаксис SQL будет здесь очень кстати.

В примерах использованы данные таблицы bill («Счет»):

Нарастающий итог

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

Нарастающим итогом называется сумма значений во всех ячейках столбца до следующей ячейки в этом конкретном столбце.

Вот запрос на эту сумму:

А вот как будет выглядеть результат:

Обобщенные табличные выражения

Обобщенные табличные выражения используются ради большего удобства для восприятия человеком сложных запросов, требующих соединения, и подзапросов.

Фактически это временный именованный результирующий набор данных, на который можно ссылаться внутри оператора SELECT , INSERT , UPDATE или DELETE .

Рассмотрим простой запрос:

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

Упорядочение данных

Дата-инженерам и аналитикам данных очень часто приходится упорядочивать значения по каким-либо параметрам, например зарплате, затратам и т. д. И это экономит много времени при поиске точного запроса.

В этом запросе набор данных упорядочен по столбце amount («Сумма»).

Вместо RANK() используется также DENSE_RANK() . Он аналогичен, но не пропускает следующее по порядку значение, если у двух строк одинаковое значение.

Добавление подытогов

Наличие промежуточного итога (подытога) помогает оценить данные в контексте общего итога.

Это расширенная версия оператора GROUP BY : здесь есть возможность добавления к данным промежуточных и общих итогов.

Примечание: это запрос в MySQL. Для других синтаксис свертки может отличаться.

Здесь в запросе строка со значениями null и для типа, и для идентификатора — это итог. Есть также подытоги со значениями null только в столбце идентификатора: это 4-я и предпоследняя строки.

Временные функции

Временные функции позволяют легко менять данные без использования огромных операторов case .

В следующем примере временная функция применяется для преобразования типа в род. Это можно было сделать с помощью встроенного в запрос оператора case , но тогда было бы неудобно читать.

Дисперсия и среднеквадратическое отклонение

Для получения этих значений есть специальные агрегатные функции: VARIANCE , VAR_POP и VAR_SAMP . Они группируют данные и используются для определения дисперсии, дисперсии группы и дисперсии выборки набора данных по отдельности.

VAR_POP — дисперсия совокупности;
VAR_SAMP — дисперсия выборки;
STDDEV_SAMP — среднеквадратическое отклонение для выборки;
STDDEV_POP — среднеквадратическое отклонение для совокупности.

Это были основные SQL-команды, которые я постоянно использовал, работая дата-инженером, и которые пришлись очень кстати при решении многих бизнес-задач.

Stats подтверждает, что экосистема инструментов SQL, которая включает в себя все: от Excel и Tableau до SparkSQL — используется в более чем 60 % организаций. Это настоящий подвиг для SQL, особенно учитывая его возраст.

Как одним запросом получить среднее и максимальные значения по группам и всей таблице?

Есть стандартная схема HR с таблицей employees, помогите составить в ней запрос:

вычислите одним запросом среднюю и максимальную заработные платы по отделам и всей организации.

Вот так делаю двумя запросами:

А как получить тотже результат одним запросом?

Самое простое, вот так (на db<>fiddle):

Из вопроса непонятно, что делать с внештатными сотрудниками, оставил их в outsource .

Дарья, вы правильно составили запросы! Используйте оператор UNION для того чтобы объединить результаты:

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.