Comments 54
«There was SQL before window functions and SQL after window functions».
Оконные функции удобно применять для всякой аналитики, отчетов, и так далее.
А еще однажды была задача добавить unique constraint на таблицу, где уже было порядка 500к записей и из них много не соответствовало условию. Запрос на удаление лишних записей был удобно сформирован с помощью оконной фукнции. Боюсь представить, сколько времени я бы удалял это вручную.
Оконные функции удобно применять для всякой аналитики, отчетов, и так далее.
А еще однажды была задача добавить unique constraint на таблицу, где уже было порядка 500к записей и из них много не соответствовало условию. Запрос на удаление лишних записей был удобно сформирован с помощью оконной фукнции. Боюсь представить, сколько времени я бы удалял это вручную.
Ну, самый распространенный способ использования — для пагинации раз уж у нас номера строк под рукой
Вы бы еще OFFSET предложили.
ну, затупил. Я єтим в mssql пользуюсь а там только TOP (если в последних версиях конечно не добавили что то типа offset).
Может я чего не понимаю, но в MySQL есть «LIMIT from,count», которым я всегда пользовался…
если в последних версиях конечно не добавили что то типа offset
Добавили technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx
SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
А можно ли использовать результат оконной функции в HAVING? Как раз сегодня решал задачу на MySQL, нужно было сделать пагинацию по дням, имея колонку с таймстемпом записей. Очень горевал, что он не умеет оконные функции, пришлось извращаться с переменными в селектах и подзапросами, но зато потом эту переменную можно в HAVING напихать и всё отфлильтруется.
Вот такая особенность агрегатных функций, если их использовать как оконные. На мой взгляд, это довольно-таки странный, интуитивно неочевидный момент SQL-стандарта.
Не понял, чего тут не очевидного? Не указываем
order by — границами окна являются первая и последняя запись в выборке, указываем — первая и текущая запись в выборке.Кстати, ваше описание оконных функций неполно — как минимум не указаны полезнейшие предложения
range / rows, позволяющие явно указать границы окна.
> Не понял, чего тут не очевидного? Не указываем order by — границами окна являются первая и последняя запись в выборке, указываем — первая и текущая запись в выборке.
Если мы берем функцию row_number(), то она как-то все равно учитывает текущую строку, даже если не делать order by. А sum — никак не учитывает. Вот это и не очевидно, на мой взгляд.
Если мы берем функцию row_number(), то она как-то все равно учитывает текущую строку, даже если не делать order by. А sum — никак не учитывает. Вот это и не очевидно, на мой взгляд.
Об этом прямо написано в документации. sum — это агрегатная функция изначально, а row_number — оконная.
«When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame. An aggregate used with ORDER BY and the default window frame definition produces a „running sum“ type of behavior, which may or may not be what's wanted. To obtain aggregation over the whole partition, omit ORDER BY or use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other frame specifications can be used to obtain other effects.»
«When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame. An aggregate used with ORDER BY and the default window frame definition produces a „running sum“ type of behavior, which may or may not be what's wanted. To obtain aggregation over the whole partition, omit ORDER BY or use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other frame specifications can be used to obtain other effects.»
А в чём проблема? FIRST_VALUE вы куда отнесёте?
я довольно часто использовал оконную функцию, чтобы найти крайнее значение из набора.
Представим, что есть таблица со списком тарифов, для каждого тарифа есть цена и дата начала действия. Текущую цену можно будет узнать таким запросом.
Представим, что есть таблица со списком тарифов, для каждого тарифа есть цена и дата начала действия. Текущую цену можно будет узнать таким запросом.
+----+------------+--------+------------+
| id | service_id | tariff | put_into |
+----+------------+--------+------------+
| 1 | 1 | 100.0 | 2013-10-01 |
| 2 | 1 | 200.0 | 2014-12-01 |
| 3 | 1 | 150.0 | 2015-02-01 |
| 4 | 1 | 170.0 | 2016-02-15 |
| 5 | 2 | 300.0 | 2013-10-01 |
| 7 | 2 | 330.0 | 2015-02-01 |
| 8 | 2 | 315.0 | 2016-02-15 |
+----+------------+--------+------------+
select distinct first_value (t1.tariff) over (order by t1.put_into desc) as price
from tariff_price t1 where t1.service_id = 1 and t1.put_into <= CURRENT_DATE;
+-------+
| price |
+-------+
| 150.0 |
+-------+
Эмм. А зачем тут оконная функция?
И очевиднее и план запроса куда проще.
select t1.tariff from from tariff_price t1 where t1.service_id = 1 and t1.put_into <= CURRENT_DATE order by t1.put_into desc limit 1
И очевиднее и план запроса куда проще.
Ваш запрос подойдет для предложенной задачи только при условии «t1.service_id = 1».
Если же нужно найти последние цены по всем видам услуг — то не подойдет :)
Если же нужно найти последние цены по всем видам услуг — то не подойдет :)
Если бы не было этого условия, и было бы PARTITION BY, тогда да. А так — странноватый запрос.
Чудак ты автор, сам же просил:
Ну я и дал пример сферического запроса в вакуме, реальный запрос намного сложнее и масштабнее, но я его здесь приводить не буду так как не интересно.
Прошу в коментариях накидать примеров, где особенно удобно применять оконные фунции. А также, какие с ними могут возникнуть проблемы, если таковые имеются.
Ну я и дал пример сферического запроса в вакуме, реальный запрос намного сложнее и масштабнее, но я его здесь приводить не буду так как не интересно.
Та запросто:
select distinct on (t1.service_id) t1.service_id, t1.tariff from from tariff_price t1 where t1.put_into <= CURRENT_DATE order by t1.service_id, t1.put_into desc
Нужна помощь зала… как красиво вытащить последний ряд партишена?
http://sqlfiddle.com/#!15/6bba4/1
http://sqlfiddle.com/#!15/6bba4/1
Зря вы так, попробуйте лучше сюда.
последний ряд партишена, если я правильно понял задачу, можно вытащить примерно так: пронумеровать row_number() over (order by… desc) as num, потом весь запрос сделать подзапросом, и отфильтровать where num = 1
В очередной раз жалею, что выбрал MySQL для своего проекта с несколькими десятками таблиц…
Несколько десятков – это не тысячи, можно и смигрировать :-)
Держите. pgloader.io
Из плюсов — он единственный из списка wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL работает с гео полями
Что-то вот так просто никак не соображу как его собрать под Gentoo.
Прежде учтите, что в индексах пг хранится ссылка на данные по внутреннему идентификатору
Как следствие — при обновлении строки обновляется ссылка на данные в индексе
Как следствие — при обновлении строки обновляется ссылка на данные в индексе
Оконные функции я часто использую для миграции данных. Очень удобно. Это либо удаление дубликатов, либо штуки типа перенумерования записей, например так: envek.name/ru/blog/2015/04/28/sql-window-functions
Отличная статья, спасибо, очень радует, что материалы по этой действительно великолепной СУБД стали появляться всё чаще. Ещё очень бы хотелось материалов по масштабированию — советы, практики использования таких вещей, как pgPool-II, PostgresXL и т.д.
Оконные функции типа first/last_value очень удобны при работе с версионированными или хронологическими данными. Особенно функции, «заглядывающие» вперёд по запросу. В том же мускуле с помощью переменных худо-бедно можно выводить результаты в текущей строке, базирующиеся на результатах предыдущих, но вот если нужны последующие, то без подзапросов с обратной сортировкой не обойтись.
Неплохо бы ещё пару слов сказать про волшебное «UNBOUNDED FOLLOWING», и как оно влияет на выражения. Оптимизатору несложно выполнять агрегации над теми строками, которые он уже «просканировал», вероятно, поэтому по умолчанию используются границы «RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW», но если вам нужно «заглядывать вперёд» и учитывать значения будущих строк окна, то приходится использовать то самое «FOLLOWING», которое может кардинальным образом менять план и скорость выполнения запроса.
Вот простой пример расчёта среднего по умолчанию
Подробнее, например, на www.dba-oracle.com/t_advanced_sql_windowing_clause.htm
Вот простой пример расчёта среднего по умолчанию
SELECT
NAME,
SALARY,
AVG(SALARY) OVER (ORDER BY SAL) AVG
FROM
EMP;
NAME SALARY AVG
---------- ---------- ----------
SMITH 800 800
JAMES 950 875
ADAMS 1100 950
WARD 1250 1025
Подробнее, например, на www.dba-oracle.com/t_advanced_sql_windowing_clause.htm
Не упомянута вот такая возможность:
select id,
sum(viewed) FILTER (where viewed > 5) OVER (ORDER BY expectedenddate DESC) AS rating
from table1
Если добавлено предложение FILTER, агрегатной функции подаются только те входные строки, для которых условие_фильтра вычисляется как истинное; другие строки отбрасываютсяНу и доп. ссылки:PS: спецом на postgrespro.ru ссылки поставил, уж больно Олег Бартунов его хвалил в Уфе
один order ничуть не мешает другомуАвтор, я попробовал на тестовых данных, объясни, плиз, как сделать этот запрос лучше, ибо ордеры, таки мешают друг другу
подскажите, я правильно понял, что окна не бывают «скользящими»? Иными словами, нельзя вычислить скользящее среднее, типа (тут окно в 2 значения):
val avg
3 | 4.5
6 | 4.5
3 | 4
5 | 4.5
4 |
Бывают. Для этого как раз предназначены фразы range / rows оконной функции. На примере Oracle:
with data as (
select 3 val from dual union all
select 6 val from dual union all
select 3 val from dual union all
select 5 val from dual union all
select 4 val from dual
)
select n, val, avg(val) over(order by n rows between current row and 1 following) avg
from (select rownum n, val from data) t;Результат:
| N | VAL | AVG |
|---|-----|-----|
| 1 | 3 | 4.5 |
| 2 | 6 | 4.5 |
| 3 | 3 | 4 |
| 4 | 5 | 4.5 |
| 5 | 4 | 4 |Здесь колонка n искусственно введена для того, чтобы правильно упорядочить данные во фразе order by окна. Если у вас уже есть колонка сортировки, эта искусственная колонка не нужна. Фраза rows between current row and 1 following определяет границы окна: между текущей строкой и плюс одной строкой от текущей (включительно) — получаем ваш интервал в 2 строки; rows означает, что оперируем физическими строками. Значения за последней строкой считаются как бы равными null. Ещё можно использовать range — в этом случае диапазон будет рассчитываться не от порядковых номеров строк, а от значений в колонке n (той, которая в order by). В нашем случае порядковый номер и значение совпадают, так что результат был бы одним и тем же, но если бы вместо n стоял, например, реальный идентификатор строки, разница была бы существенной!
Документация по аналитическим функциям.
Здорово, спасибо. На "[sql flavour] rows between" ищется, что нужно. Например, тут для MS SQL: docs.microsoft.com/ru-RU/sql/t-sql/queries/select-over-clause-transact-sql?view=aps-pdw-2016
Некропост, но ... вот пример, где удобно использовать оконные функции: в БД есть некоторая таблица MyTable. Все строки MyTable соответствующие критерию a образуют множество строк A, а все строки соответствующие критерию b образуют множество строк В. Необходимо получить объединение множеств A и B, при этом строки выводятся в следующем порядке:
Сперва все строки соответствующие только критерию a.
Затем все строки соответствующие критерию a и критерию b.
Под конец все строки соответствующие только критерию b. Критерии a и b не определенны заранее и формируются динамически.
можете привести пример? Не оч понятно
Ок. Пусть у нас есть таблица:
create table MyTable (
id UUID not null,
value1 INT not null,
value2 INT not null,
primary key(id)
);Нужно вывести все строки которые соответствуют критерию value1 > 1000 или критерию value2 > -250 and value2 < 370, при этом строки должны быть отсортированы в следующем порядке:
Сперва все строки соответствующие только критерию value1 > 1000
Затем все строки соответствующие критерию value1 > 1000 и критерию value2 > -250 and value2 < 370
Под конец выводятся строки соответствующие только критерию value2 > -250 and value2 < 370
Sign up to leave a comment.
Как посчитать всё на свете одним SQL-запросом. Оконные функции PostgreSQL