Оконные функции.
Открыл для себя такую интересную вещь, как оконные функции. Они есть точно и в MS SQL Server, и в PostgreSQL. Про остальные СУБД не скажу. В-общем, суть оконных функций заключаются что они начинаются работать после того, как выполнен основный запрос над данными, который он вернул. В-общем виде их синтаксис: некоторая функция OVER окно. Окно - это некий набор строк, над которым действует функция. Если набор строк не указан, то функция действует над всей выборкой. Допустим, у нас есть таблица температур. И нас интересует выборка, где температура ниже определённого значения, например, 14. Делаем запрос select id, value from "temperature" where value < 14 и получаем результат:
Теперь мы хотим пронумеровать результат выборки. С оконными функциями это получиться довольно просто. Надо добавить функцию row_number() в запрос и сделать его таким - select row_number() OVER () AS num, value from "temperature" where value < 14. Вот что получится на выходе:
А теперь допустим нужно узнать разницу между предыдущим и текущим значением температуры. Сделать это тоже довольно просто, используя функцию lag(). С её помощью запрос преобразуем к виду
select value, value - lag(value) OVER () as change from "temperature" where value < 14
и получаем результат:
Как видите, оконные функции - очень мощный инструмент для анализа данных, если знать, как им пользоваться.
Теперь мы хотим пронумеровать результат выборки. С оконными функциями это получиться довольно просто. Надо добавить функцию row_number() в запрос и сделать его таким - select row_number() OVER () AS num, value from "temperature" where value < 14. Вот что получится на выходе:
А теперь допустим нужно узнать разницу между предыдущим и текущим значением температуры. Сделать это тоже довольно просто, используя функцию lag(). С её помощью запрос преобразуем к виду
select value, value - lag(value) OVER () as change from "temperature" where value < 14
и получаем результат:
Как видите, оконные функции - очень мощный инструмент для анализа данных, если знать, как им пользоваться.