Три способа рассчитать накопленную сумму в SQL
Расчет накопленной (или кумулятивной, что то же самое) суммы SQL — это очень распространенный запрос, который часто используют в анализе финансов, динамики прибыли и прочих показателей компании. В сегодняшней статье вы узнаете, что такое накопленная сумма и как можно написать SQL-запрос для ее вычисления.
Если вы вдруг являетесь начинающим пользователем SQL, то давайте, как в школьной задаче, поймем, что нам дано и что нам необходимо найти. Накопленная сумма — это совокупная сумма предыдущих чисел в столбце. Давайте посмотрим на пример ниже, чтобы точно знать, какой результат мы ожидаем увидеть в итоге. Итак, существует таблица leftjoin.daily_sales_sample, в которой есть всего два столбца date и revenue. По столбцу revenue нам нужно рассчитать накопленную сумму и записать результат в отдельный столбец.
Что у нас есть?
| Date | Revenue |
| 10.11.2021 | 1200 |
| 11.11.2021 | 1600 |
| 12.11.2021 | 800 |
| 13.11.2021 | 3000 |
Что мы хотим найти?
| Date | Revenue | Cumulative Revenue |
| 10.11.2021 | 1200 | 1200 ↓ |
| 11.11.2021 | 1600 | 2800↓ |
| 12.11.2021 | 800 | 3600 ↓ |
| 13.11.2021 | 3000 | 6600 |
На графике две этих переменных выглядят следующим образом: 
Итак, без лишних слов, давайте приступать к решению задачи.
Способ 1 — Идеальный — Используем оконные функции
Итак, если в базе данных можно пользоваться оконными функциями, то жизнь хороша и прекрасна. С их помощью можно написать простой запрос, который будет суммировать значения из столбца revenue по мере увеличения даты и сразу вернет нам таблицу с кумулятивной суммой в столбце, который мы назвали total.
Способ 2 — Хитрый — Решение без оконных функций
Вполне возможно, что вам понадобится решить такую задачу без использования оконных функций. К примеру, если вы используете MySQL (до 8 версии) или любую другую БД, в которой оконных функций нет. Тогда решение задачи чуть усложняется. Однако, вы ведь знаете, что нет ничего невозможного?
Чтобы провернуть все то же самое без оконных функций, нужно использовать INNER JOIN для присоединения таблицы к себе самой. Так, к каждой строке таблицы мы присоединяем строки, которые соответствуют всем предыдущим датам до текущей даты включительно. В нашем примере, для 10 ноября — 10 ноября, для 11 ноября — 10 и 11 ноября и так далее. Промежуточный запрос будет выглядеть вот так:
А его результат:
| Date 1 | Revenue 1 | Date 2 | Revenue 2 |
| 10.11.2021 | 1200 | 10.11.2021 | 1200 |
| 11.11.2021 | 1600 | 10.11.2021 | 1200 |
| 11.11.2021 | 1600 | 11.11.2021 | 1600 |
| 12.11.2021 | 800 | 10.11.2021 | 1200 |
| 12.11.2021 | 800 | 11.11.2021 | 1600 |
| 12.11.2021 | 800 | 12.11.2021 | 800 |
| 13.11.2021 | 300 | 10.11.2021 | 1200 |
| 13.11.2021 | 300 | 11.11.2021 | 1600 |
| 13.11.2021 | 300 | 12.11.2021 | 800 |
| 13.11.2021 | 300 | 13.11.2021 | 300 |
А затем, нужно просуммировать прибыли, группируя их по каждой дате. Если собрать все в единый запрос, то он будет выглядеть вот так:
Способ 3 — Специфический — Решение с помощью массивов в ClickHouse
Если вы используете Clickhouse, то в этой системе есть специальная функция, которая может помочь рассчитать кумулятивную сумму. Для начала, нам нужно преобразовать все столбцы таблицы в массивы и рассчитать показатель «Moving Sum» для столбца revenue.
Спасибо Дмитрию Титову из Altinity за комментарий про сортировку в подзапросе
Так, мы получим три массива значений:
| dates | revs | total |
| [’10.11.2021’,’11.11.2021’,’12.11.2021’,’13.11.2021’] | [1200, 1600, 800, 300] | [1200, 2800, 3600, 3900] |
Но три массива, которые записаны в ячейки — это не то, что мы хотим получить, хотя значения этих массивов уже абсолютно соответствуют искомому результату. Теперь массивы нужно привести обратно к табличному виду с помощью функции ARRAY JOIN.
Бонус — Оконные функции в Clickhouse
Если вам не хочется иметь дело с массивами, что иногда и правда бывает затратно по времени, то есть еще один вариант решения задачи. Можно использовать оконные функции, например функцию runningAccumulate(), которая суммирует значения всех ячеек с первой до текущей.
Если вы столкнетесь с необходимостью рассчитать кумулятивную сумму в SQL, то теперь вы сможете решить эту задачу, в какой бы системе управления баз данных ни была организована работа 🙂
MySQL Как быстро посчитать все строки в таблице
Существует несколько способов получить количество строк в таблице MySQL. Некоторые продукты управления базами данных предоставляют статистику базы данных, например, размеры таблиц, но это можно сделать и с помощью прямого SQL.
Считать кол-во строк в PHP почти всегда непроизводительно.
- information_schema.TABLES
- SHOW TABLE STATUS
- SQL_CALC_FOUND_ROWS
- Функция COUNT()
- Подсчет всех строк в таблице
- Подсчет не-NULL строк — разница между COUNT() и COUNT(*)
- Подсчет только NULL строк
- Подсчет уникальных значений
- Условные подсчеты с использованием выражений
information_schema.TABLES
Это способ позволяет получить данные о колличестве строк во всей таблице (без учета WHERE). Такие данные храняться в служебной таблице information_schema.TABLES :
Этот метод очень быстрый, но не точный, поскольку данные в information_schema о количестве строк и фактическое количество строк в таблицах не всегда синхронизированы. Чтобы этого избежать, необходимо выполнить оператор ANALYZE TABLE перед запросом к таблице information_schema.
SHOW TABLE STATUS
Мы также можем получить данные таблицы следующим запросом:
В колонке Rows находится кол-во строк в таблице.
SQL_CALC_FOUND_ROWS
Этот способ не самый быстрый, но есть случай, когда COUNT(*) не подходит, например когда результаты группируются, можно столкнуться с проблемой, что COUNT на самом деле не считает все строки.
Решением является SQL_CALC_FOUND_ROWS — этот маркер нужно добавить сразу после SELECT :
После запроса кол-во всех строк нужно получить еще одним запросом — он должен быть сделан сразу после запроса на выборку данных:
Функция COUNT()
При COUNT(*) учитываются индексы столбцов, поэтому результат будет хорошим по производительности. Также, стоит отметить, что таблица MySQL с движком MyISAM хранит количество строк как впомогательные данные к таблице и он не считает все строки каждый раз при запросе если это возможно.
Если COUNT(*) работает медленно, нужно добавить в начало запроса маркер EXPLAIN и проверить, какие индексы используются, и где их следует добавить.
Функция COUNT() считает количество строк в таблице. Она подходит для подсчета как всех строк таблицы, так и строк подходящих под условие WHERE. Есть несколько сигнатур:
COUNT(*) — получит всё не-NULL и NULL строки (одинаковые значения считаются по отдельности).
COUNT(expr) — возвращает набор результатов, не содержащих NULL строк.
COUNT(DISTINCT expr) — возвращает количество уникальных строк, не содержащих значений NULL.
В каждом случае COUNT() возвращает BIGINT — количество совпавших строк. Или ноль, если не было найдено ни одной.
Подсчет всех строк в таблице
Чтобы подсчитать все строки в таблице, независимо от того, содержат они значения NULL или нет, используйте COUNT(*). Такая запись в основном возвращает количество строк в наборе результатов, возвращенных оператором SELECT.
Вызов COUNT(*) без WHERE или дополнительных столбцов, будет работать очень быстро в таблицах MyISAM, поскольку количество строк хранится в столбце table_rows в таблице tables базы данных information_schema .
Для транзакционных систем хранения данных, таких как InnoDB, хранение точного количества строк проблематично, поскольку InnoDB не ведет внутренний подсчет строк в таблице. Если бы он велся, то одновременные транзакции могли бы «видеть» разное количество строк в одно и то же время. Следовательно, операторы SELECT COUNT() подсчитывают только строки, видимые текущей транзакции. Это означает, что выполнение запроса с COUNT() при большой нагрузке может привести к неточным числам.
Подсчет не-NULL строк — разница между COUNT() и COUNT(*)
Если в COUNT() ничего не передавать, то выполняется версия функции COUNT(expr) , но без параметров. Вызов COUNT() таким образом возвращает только строки, где нет NULL. Например, у нас есть таблица code_values:
Запрос COUNT() вернет 2, хотя в таблице 4 строки:
Обратите внимание, что эта версия функции COUNT() используется редко, потому что NULL-строки не должны быть проблемой в базах данных. Они могут создать проблемы только в том случае, если таблица не имеет первичного ключа. В большинстве случаев COUNT(*) будет работать просто отлично.
COUNT(expr) может принимать правильные выражения. Еще один запрос, который одинаково извлекает NULL и не NULL строки:
Подсчет только NULL строк
Версия функции COUNT(expr) также принимает имена отдельных столбцов, в результате чего COUNT(имя_столбца) возвращает количество записей, в которых имя_столбца не является NULL. Следовательно, следующий запрос SELECT получит количество строк, в которых столбец description содержит не NULL-значения:
Подсчет уникальных значений
Функция COUNT(DISTINCT) возвращает количество строк с уникальными не-NULL значениями. Следовательно, включение ключевого слова DISTINCT исключает дубликаты строк из подсчета. Синтаксис функции следующий:
Как и в обычной функции COUNT(), параметры expr выше могут быть любыми выражениями, включая конкретные столбцы, все столбцы (*), возвращаемые значения функций или выражения, такие как операторы IF/CASE.
Пример. Допустим, у нас есть следующая таблица клиентов:
Вызов COUNT(*) вернет количество всех строк (4), а COUNT DISTINCT по last_name каждую строку где дублирующие last_name будут считаться за одну строку, так что в итоге мы получим 3:
Условные подсчеты с использованием выражений
Параметры функции COUNT() не ограничиваются именами столбцов; возвращаемые значения функций и выражения, такие как операторы IF/CASE, также будут работать.
Вот таблица, содержащая номера телефонов и пол нескольких пользователей:
Предположим, что мы хотим создать запрос, который сообщает нам, сколько разных женщин и мужчин содержится в таблице. Человек идентифицируется по номеру телефона («tel»). Один и тот же номер может встречаться несколько раз, но пол этого номера должен учитываться только один раз.
Вот один из вариантов использования отдельного COUNT DISTINCT для каждого столбца:
Оконные функции SQL
Нет более обманчивого раздела SQL, чем «оконные функции». Когда слышишь эти слова, думаешь «наверно, просто придумали какие-то дополнительные функции».
Если вкратце — оконные функции помогают делать классные аналитические отчеты без участия «экселя». Хотите посчитать процент продаж по месяцам от общих продаж за год? Оконные функции. Разделить маркетинговые каналы на эффективные и неэффективные? Оконные функции. Выбрать топ-10 клиентов по каждому сегменту? Тоже они.
Я прочитал несколько десятков статей «для начинающих», которые объясняли, что такое оконные функции. Все они страдали от одной из двух проблем:
- Все понятно, но описан 1% возможностей «окошек».
- Написано так сложно, что если бы я уже не знал предмет обсуждения — ничего бы не понял.
Если не разобраться в запросах с «окошками», выглядят они как-то так
Хочу исправить ситуацию и написать понятное и наглядное введение в оконные функции SQL. Понятное, потому что я умею доходчиво писать о сложных темах. Наглядное — потому что подготовил несколько десятков картинок и гифок, которые помогут понять базовые принципы «окошек».
- Зачем нужны оконные функции (эта статья).
Я рекомендую не просто читать статьи, а сразу проходить курс. В нем много практических задачек, а только с ними абстрактные знания превратятся в навыки.
Итак, зачем нужны оконные функции? Проще всего объяснять на конкретных примерах. Будем работать с игрушечной таблицей сотрудников, вот такой:
Рассмотрим некоторые задачи, которые удобно решать с помощью «окошек» в SQL. Как именно их решать — разберемся в следующей части. Пока просто оценим возможности.
1. Ранжирование
Ранжирование — это всевозможные рейтинги, начиная от призеров чемпионата мира по плаванию и заканчивая Forbes 500.
На примере нашей таблички сотрудников:
Общий рейтинг зарплат
Составим рейтинг сотрудников по размеру заработной платы:
Видно, что у некоторых коллег одинаковая зарплата (Леонид и Марина, Вероника и Григорий) — поэтому они получили один и тот же ранг.
Рейтинг зарплат по департаментам
Тот же рейтинг, только не для всей компании, а по каждому департаменту в отдельности:
Группы по зарплате
Разобьем сотрудников на три группы в зависимости от размера зарплаты:
- высокооплачиваемые,
- средние,
- низкооплачиваемые.
Самые «дорогие» коллеги
Найдем самых высокооплачиваемых людей по каждому департаменту:
Что ж, этим зарплату больше не повышать! (или наоборот, повысить? ��)
2. Сравнение со смещением
Сравнение со смещением — это когда мы смотрим, в чем разница между соседними значениями. Например, сравниваем страны, которые занимают 5 и 6 место в мировом рейтинге ВВП — сильно ли отличаются? А если сравнить 1 и 6 место?
Сюда же попадают задачи, в которых мы сравниваем значение из набора с границами набора. Например, есть 100 лучших теннисисток мира. Мария Саккари занимает в рейтинге 20 место. Как ее показатели соотносятся с Эшли Бартли, которая занимает 1 место? А с Лин Чжоу, которая занимает 100 место?
На примере нашей таблички сотрудников:
Разница по зарплате с предыдущим
Упорядочим сотрудников по возрастанию зарплаты и проверим, велик ли разрыв между соседями:
Столбец diff показывает, на сколько процентов зарплата сотрудника отличается от предыдущего коллеги. Видно, что больших разрывов нет. Самые крупные — между Дарьей и Борисом (10%) и Мариной и Иваном (13%).
Диапазон зарплат в департаменте
Посмотрим, как зарплата сотрудника соотносится с минимальной и максимальной зарплатой в его департаменте:
Для каждого сотрудника столбец low показывает минимальную зарплату родного департамента, а столбец high — максимальную. Видно, что разброс значений в HR и продажах невелик, а у айтишников — значительный.
3. Агрегация
Агрегация — это когда мы считаем суммарные или средние показатели. Например, среднюю зарплату по каждому региону или количество золотых медалей у каждой страны в зачете Олимпийских игр.
На примере нашей таблички сотрудников:
Сравнение с фондом оплаты труда
У каждого департамента есть фонд оплаты труда — денежная сумма, которая ежемесячно уходит на выплату зарплат сотрудникам. Посмотрим, какой процент от этого фонда составляет зарплата каждого сотрудника:
Столбец fund показывает фонд оплаты труда отдела, а perc — долю зарплаты сотрудника от этого фонда. Видно, что в HR и продажах все более-менее ровно, а у айтишников есть заметный разброс зарплат.
Сравнение со средней зарплатой
Интересно, велик ли разброс зарплат в департаментах. Проверим: посчитаем отклонение зарплаты каждого сотрудника от средней по департаменту:
Результат подтверждает предыдущие наблюдения: у айтишников зарплаты колеблются от -16% до +20% от среднего, а у остальных департаментов отклонение в пределах 5%.
4. Скользящие агрегаты
Скользящие агрегаты — это те же сумма и среднее. Только рассчитывают их не по всем элементам набора, а более хитрым способом.
Поясню на примере. Здесь возьмем другую табличку — с доходами и расходами одного из сотрудников (пусть это будет Марина) за 9 месяцев 2020 года:
Скользящее среднее по расходам
Судя по данным, доходы у Марины растут: 94К ₽ в январе → 104К ₽ в сентябре. А вот растут ли расходы? Сходу сложно сказать, месяц на месяц не приходится. Чтобы сгладить эти скачки, используют «скользящее среднее» — для каждого месяца рассчитывают средний расход с учетом предыдущего и следующего месяца. Например:
- скользящее среднее за февраль = (январь + февраль + март) / 3;
- за март = (февраль + март + апрель) / 3;
- за апрель = (март + апрель + май) / 3;
- и так далее.
Рассчитаем скользящее среднее по всем месяцам:
Теперь хорошо видно, что расходы стабильно растут.
Прибыль нарастающим итогом
Мы выяснили, что растут и доходы, и расходы. А как они соотносятся друг с другом? Хочется понять, находится ли человек «в плюсе» или «в минусе» с учетом всех заработанных и потраченных денег.
Причем важно понимать не на конец года, а на каждый месяц. Потому что если по итогам года у Марины все ОК, а в июне ушла в минус — это потенциальная проблема (у компаний такую ситуацию называют «кассовым разрывом»).
Поэтому посчитаем доходы и расходы по месяцам нарастающим итогом (кумулятивно):
- кумулятивный доход за январь = январь;
- за февраль = январь + февраль;
- за март = январь + февраль + март;
- за апрель = январь + февраль + март + апрель;
- и так далее.
Теперь видно, что дела у Марины идут неплохо. В некоторых месяцах расходы превышают доходы, но благодаря накопленной «денежной подушке» кассового разрыва не происходит.
Резюме
Вот задачи, которые непринужденно решаются с помощью оконных функций в SQL:
- Ранжирование (всевозможные рейтинги).
- Сравнение со смещением (соседние элементы и границы).
- Агрегация (сумма и среднее).
- Скользящие агрегаты (сумма и среднее в динамике).
Конечно, это не исчерпывающий список. Но, надеюсь, теперь понятно, как пригодятся оконные функции в аналитике данных. В следующей части разберемся, что такое «окно», как работает оконная функция, и научимся ранжировать записи.
Подписывайтесь на канал, чтобы не пропустить новые заметки ��
Как посчитать все одним запросом
Можно бесконечно долго «воротить нос» от использования SQL для Data Preparation, отдавая лавры змеиному языку, но нельзя не признавать факт, что чаще мы используем и еще долго будем использовать SQL для работы с данными, в том числе и очень объемными.
Более того, считаем, что на текущий момент SQL окажется под рукой сотрудника с большей вероятностью, чем Python, и поможет быстро решить аналитическую задачку с приоритетом «-1».
Предложение OVER помогает «открыть окно», т.е. определить строки, с которым будет работать та или иная функция.
Предложение partion BY не является обязательным, но дополняет OVER и показывает, как именно мы разделяем строки, к которым будет применена функция.
ORDER BY определит порядок обработки строк.
В одном select может быть больше одного OVER, эта прекрасная особенность упростит выполнение аналитической задачи в дальнейшем.
Итак, оконные функции делятся на:
- Агрегатные функции
- Ранжирующие функции
- Функции смещения
- Аналитические функции
Собственно, те же, что и обычные, только встроенные в конструкцию с OVER
SUM/ AVG / COUNT/ MIN/ MAX
Для наглядности работы данных функций воспользуемся базовым набором данных (T)
Найти максимальную задолженность в каждом банке.
Для чего тут оконные функции? Можно же просто написать:
В данном контексте, действительно, применение оконных функций нецелесообразно, но, когда речь заходит о задаче:
Собрать дэшборд, в котором содержится информация о максимальной задолженности в каждом банке, а также средний размер процентной ставки в каждом банке в зависимости от сегмента, плюс еще количество договоров всего всем банкам (в голове рисуются множественные джойны из подзапросов и как-то сразу тяжело на душе). Однако, как я говорил выше, в одном select можно использовать много OVER, а также еще один прекрасный факт: набор строк в окне, связывается с текущей строкой, а не с группой агрегированных. Таким образом:
На примере AVG(procent_RATE) OVER (partition BY TB, segment) подробнее:
- Мы применяем AVG – агрегатную функцию по подсчету среднего значения к столбцу procent_RATE.
- Затем предложением OVER определяем, что будем работать с некоторым набором строк. По умолчанию, если указать OVER() с пустыми строками, то этот набор строк равен всей таблице.
- Предложением partition BY выделяем разделы в наборе строк по заданному условию, в нашем случае, в разбивке на Территориальные банки и Сегмент.
- В итоге, к каждой строке базовой таблицы применится функция по подсчету среднего из набора строк, разбитых на разделы (по Территориальным Банкам и Сегменту).
Другой тип оконных функций, надо признать, мой любимый и был использован для решения многих задач. Функции ранжирования для каждой строки в разделе возвращают значение рангов или рейтингов. Все ведь любят рейтинги, правда…?
Базовый набор данных: банки, отделы и количество ревизий.
Сами ранжирующие функции:
ROW_number – нумерует строки в результирующем наборе.
RANK -присваивает ранг для каждой строки, если найдутся одинаковые значения, то следующий ранг присваивается с пропуском.
DENSE_RANK -присваивает ранг для каждой строки, если найдутся одинаковые значения, то следующий ранг присваивается без пропуска.
NTILE – помогает разделить результирующий набор на группы.
Для понимания написанного, проранжируем таблицу по убыванию количества ревизий:
ROW_number – пронумеровал столбцы в порядке убывания количества ревизий.
RANK – проранжировал отделы во всех банках в порядке убывания количества ревизий, но как только встретились одинаковые значения (количество ревизий 95), функция присвоила им ранг 4, а следующее значение получило ранг 6.
DENSE_RANK – аналогично RANK, но как только встретились одинаковые значения, следующее значение получило ранг 5.
NTILE – функция помогла разбить таблицу на 3 группы (указал в аргументе). Так как в таблице 18 значений, в каждую группу попало по 6.
Найти второй отдел во всех банках по количеству ревизий.
Можно, конечно, воспользоваться чем-то вроде:
Но если речь идет не про второй отдел, а про трети? .. уже сложнее. Действительно, никто не списывает со счетов offset, но в этой статье говорится об оконных функциях, так почему бы не написать так:
Как и во всех других типах функций, здесь можно выделять разделы с помощью partitionby. Например, найти отдел в каждом банке, с меньшим количеством проведенных ревизий, для этого разделяем на секции по территориальным банкам, сортируем по возрастанию:
Оконные функции смещения помогут нам, когда необходимо обратиться к строке в наборе данных из окна, относительно текущей строки с некоторым смещением. Проще говоря, узнать, какое значение (событие/ дата) идет после/до текущей строки. Похоже на отличную штуку в предобработке лога данных.
Как посчитать всё на свете одним SQL-запросом. Оконные функции PostgreSQL 17.10.2015 00:03
Я с удивлением обнаружил, что многие разработчики, даже давно использующие 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, которая ищет строку перед последней строкой фрейма. К примеру мы можем найти насколько очков новость отстает от предыдущей в рейтинге:
Прошу в коментариях накидать примеров, где особенно удобно применять оконные фунции. А также, какие с ними могут возникнуть проблемы, если таковые имеются.
MySQL Как быстро посчитать все строки в таблице
Существует несколько способов получить количество строк в таблице MySQL. Некоторые продукты управления базами данных предоставляют статистику базы данных, например, размеры таблиц, но это можно сделать и с помощью прямого SQL.
Считать кол-во строк в PHP почти всегда непроизводительно.
- information_schema.TABLES
- SHOW TABLE STATUS
- SQL_CALC_FOUND_ROWS
- Функция COUNT()
- Подсчет всех строк в таблице
- Подсчет не-NULL строк — разница между COUNT() и COUNT(*)
- Подсчет только NULL строк
- Подсчет уникальных значений
- Условные подсчеты с использованием выражений
information_schema.TABLES
Это способ позволяет получить данные о колличестве строк во всей таблице (без учета WHERE). Такие данные храняться в служебной таблице information_schema.TABLES :
Этот метод очень быстрый, но не точный, поскольку данные в information_schema о количестве строк и фактическое количество строк в таблицах не всегда синхронизированы. Чтобы этого избежать, необходимо выполнить оператор ANALYZE TABLE перед запросом к таблице information_schema.
SHOW TABLE STATUS
Мы также можем получить данные таблицы следующим запросом:
В колонке Rows находится кол-во строк в таблице.
SQL_CALC_FOUND_ROWS
Этот способ не самый быстрый, но есть случай, когда COUNT(*) не подходит, например когда результаты группируются, можно столкнуться с проблемой, что COUNT на самом деле не считает все строки.
Решением является SQL_CALC_FOUND_ROWS — этот маркер нужно добавить сразу после SELECT :
После запроса кол-во всех строк нужно получить еще одним запросом — он должен быть сделан сразу после запроса на выборку данных:
Функция COUNT()
При COUNT(*) учитываются индексы столбцов, поэтому результат будет хорошим по производительности. Также, стоит отметить, что таблица MySQL с движком MyISAM хранит количество строк как впомогательные данные к таблице и он не считает все строки каждый раз при запросе если это возможно.
Если COUNT(*) работает медленно, нужно добавить в начало запроса маркер EXPLAIN и проверить, какие индексы используются, и где их следует добавить.
Функция COUNT() считает количество строк в таблице. Она подходит для подсчета как всех строк таблицы, так и строк подходящих под условие WHERE. Есть несколько сигнатур:
COUNT(*) — получит всё не-NULL и NULL строки (одинаковые значения считаются по отдельности).
COUNT(expr) — возвращает набор результатов, не содержащих NULL строк.
COUNT(DISTINCT expr) — возвращает количество уникальных строк, не содержащих значений NULL.
В каждом случае COUNT() возвращает BIGINT — количество совпавших строк. Или ноль, если не было найдено ни одной.
Подсчет всех строк в таблице
Чтобы подсчитать все строки в таблице, независимо от того, содержат они значения NULL или нет, используйте COUNT(*). Такая запись в основном возвращает количество строк в наборе результатов, возвращенных оператором SELECT.
Вызов COUNT(*) без WHERE или дополнительных столбцов, будет работать очень быстро в таблицах MyISAM, поскольку количество строк хранится в столбце table_rows в таблице tables базы данных information_schema .
Для транзакционных систем хранения данных, таких как InnoDB, хранение точного количества строк проблематично, поскольку InnoDB не ведет внутренний подсчет строк в таблице. Если бы он велся, то одновременные транзакции могли бы «видеть» разное количество строк в одно и то же время. Следовательно, операторы SELECT COUNT() подсчитывают только строки, видимые текущей транзакции. Это означает, что выполнение запроса с COUNT() при большой нагрузке может привести к неточным числам.
Подсчет не-NULL строк — разница между COUNT() и COUNT(*)
Если в COUNT() ничего не передавать, то выполняется версия функции COUNT(expr) , но без параметров. Вызов COUNT() таким образом возвращает только строки, где нет NULL. Например, у нас есть таблица code_values:
Запрос COUNT() вернет 2, хотя в таблице 4 строки:
Обратите внимание, что эта версия функции COUNT() используется редко, потому что NULL-строки не должны быть проблемой в базах данных. Они могут создать проблемы только в том случае, если таблица не имеет первичного ключа. В большинстве случаев COUNT(*) будет работать просто отлично.
COUNT(expr) может принимать правильные выражения. Еще один запрос, который одинаково извлекает NULL и не NULL строки:
Подсчет только NULL строк
Версия функции COUNT(expr) также принимает имена отдельных столбцов, в результате чего COUNT(имя_столбца) возвращает количество записей, в которых имя_столбца не является NULL. Следовательно, следующий запрос SELECT получит количество строк, в которых столбец description содержит не NULL-значения:
Подсчет уникальных значений
Функция COUNT(DISTINCT) возвращает количество строк с уникальными не-NULL значениями. Следовательно, включение ключевого слова DISTINCT исключает дубликаты строк из подсчета. Синтаксис функции следующий:
Как и в обычной функции COUNT(), параметры expr выше могут быть любыми выражениями, включая конкретные столбцы, все столбцы (*), возвращаемые значения функций или выражения, такие как операторы IF/CASE.
Пример. Допустим, у нас есть следующая таблица клиентов:
Вызов COUNT(*) вернет количество всех строк (4), а COUNT DISTINCT по last_name каждую строку где дублирующие last_name будут считаться за одну строку, так что в итоге мы получим 3:
Условные подсчеты с использованием выражений
Параметры функции COUNT() не ограничиваются именами столбцов; возвращаемые значения функций и выражения, такие как операторы IF/CASE, также будут работать.
Вот таблица, содержащая номера телефонов и пол нескольких пользователей:
Предположим, что мы хотим создать запрос, который сообщает нам, сколько разных женщин и мужчин содержится в таблице. Человек идентифицируется по номеру телефона («tel»). Один и тот же номер может встречаться несколько раз, но пол этого номера должен учитываться только один раз.
Вот один из вариантов использования отдельного COUNT DISTINCT для каждого столбца:
Оконные функции – то, что должен знать каждый T-SQL программист. Часть 2.
Во второй части статьи мы поговорим о самих функциях, которые применяются для формирования значения. Оконная функция вычисляет значение по набору данных, связанных с текущей строкой, то есть данные из одной группы, если используется Partition by. Обычные агрегатные функции для вычисления по группам требуют группировки строк, при этом теряется нужная уникальная информация из выборки. Поэтому приходится вместо одного запроса использовать 2, чтобы иметь все нужные данные и сумму по группам. Оконные агрегатные функции позволяют в одном запросе добиться того же результата.
Напомню, окно – это набор строк, по которым производится вычисление функции. Инструкция OVER разбивает весь набор строк на отдельные группы – окна согласно заданному условию.
Поговорим о типах оконных функций. Выделяют три группы по назначению:
- Агрегатные функции: SUM(), MAX(), MIN(), AVG(). COUNT(). Эти функции возвращают значение, полученное путем арифметических вычислений;
- Функции ранжирования: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(). Позволяют получить порядковые номера записей в окне;
- Функции смещения: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(). Возвращают значение из другой строки окна.
Для демонстрации работы функций я буду использовать простую таблицу:
Агрегатные функции
SUM()
Функция SUM() работает также как и обычная агрегатная функция – суммирует все значения заданного столбца в наборе данных. Однако, благодаря инструкции OVER() мы разбиваем набор данных на окна. Суммирование производится внутри окон согласно порядку, заданному в предложении ORDER BY. Давайте посмотрим на простой пример — сумма по трем группам.
Для удобства окна выделены разным цветом. Все значения в окне имеют одинаковую сумму – сумму всех Amount в окне.
Давайте добавим еще один столбец в выборку и изменим инструкцию OVER:
Как видите, теперь каждое окно разделено на группы благодаря полю GroupId. Каждая группа теперь имеет свою сумму.
А теперь, сделаем нарастающий итог внутри каждого окна:
Поле GroupId нам уже не нужно, поэтому мы убрали его из выборки. Теперь для каждой строки в окне рассчитывается свой итог, который представляет собой сумму текущего значения Amount и всех предыдущих.
AVG()
Эта функция рассчитывает среднее значение. Ее можно применять с предложениями Partition by и Order by.
Каждая строка в окне имеет среднее значение Amount, которое рассчитывается по формуле: сумма всех Amount / на количество строк.
Поведение этой функции похоже на SUM().
MIN()
Из названия функции понятно, что она возвращает минимальное значение в окне.
Как вы видите, в столбце Min, выводится минимальное значение Amount в окне.
MAX()
Функция MAX работает аналогично MIN, только выдает максимальное значение поля в окне:
Все предельно понятно. В первой группе максимальный Amount – 200, во второй 300, а в третьей – 200.
COUNT()
Эта функция возвращает количество строк в окне.
Усложним запрос, добавим поле GroupId.
В этом случае интереснее. Давайте рассмотрим первое окно. Для первой и второй строки количество записей составило 2. Но для третьей строки значение уже равно 3. У нас получилось накопление количества по группам наподобие накопительной суммы.
Если же мы все-таки хотим количество в каждой группе, то GroupId нужно добавить в предложение Partition by.
Функции ранжирования
RANK()/DENSE_RANK()
Функция RANK() возвращает порядковый номер текущей строки в окне. Однако, есть особенность. Если в предложении Order By попадется несколько равнозначных для правила строки, то все они будут считаться текущей строкой. Таким образом функцию RANK() нужно использовать для ранжирования, а не нумерации строк. Хотя, если правильно задать Order by, то можно нумеровать и физические строки. Например:
А вот случай с одинаковыми строками в контексте Order by:
Интересно, что третья строка в первом окне имеет ранг 3, хотя предыдущие две строки отнесены к первому рангу. Не самая понятная логика. В этом случае лучше использовать DENSE_RANK().
Вот теперь все, как и должно быть. DENSE_RANK() не пропускает ранги если предыдущий ранг содержит несколько строк.
Функции RANK() и DENSE_RANK() не требуют указания поля в скобках.
ROW_NUMBER()
Функция ROW_NUMBER () отображает номер текущей строки в окне. Как и предыдущие две функции, ROW_NUMBER () не требует указания поля в круглых скобках.
В запросе мы использовали Partition by для разделения набора данных на группы. Здесь все понятно и не должно вызвать вопросов.
Если вы не используете Partition by, то получите сквозную нумерацию по всему набору данных:
Фактически отсутствие предложения Partition by говорит от том, что весь набор данных является окном.
NTILE()
Функция NTILE() позволяет определить к какой группе относится текущая строка. Количество групп задается в скобках, а предложение ORDER BY определяет, какой столбец используется для определения группы.
К примеруЭто означает, что, если у вас есть 100 строк, и вы хотите создать 4 квартили на основе указанного поля значений, вы можете сделать это легко и посмотреть, сколько строк попадает в каждый квартиль.
Давайте посмотрим пример. В приведенном ниже запросе мы указали, что хотим создать четыре квартили на основе суммы заказа. Затем мы хотим увидеть, сколько заказов попадает в каждый квартиль.
NTILE создает группы на основе следующей формулы:
Количество строк в каждой группе = количество строк в наборе / количество указанных групп
Вот наш пример: в запросе указано всего 10 строк и 4 плитки, поэтому количество строк в каждой плите будет 2,5 (10/4). Поскольку число строк должно быть целым числом, а не десятичным. SQL engine назначит 3 строки для первых двух групп и 2 строки для оставшихся двух групп.