Введение.
Стандарт SQL поддерживает четыре оконные функции, которые служат для ранжирования. Это ROW_NUMBER, NTILE, RANK и DENSE_RANK. Функции ранжирования появились в MS SQL начиная с Server 2005.
ROW_NUMBER – функция вычисляет последовательные номера строк, начиная с 1, в соответствии с заданным упорядочением окна.
Под окном (секцией) понимается заданная группировка строк.
Синтаксис
ROW_NUMBER () OVER ([ PARTITION BY value_expression , ... [ n ] ] ORDER BY [value_expression] )
value_expression определяет столбец, по которому секционируется результирующий набор. Если параметр PARTITION BY не указан, функция обрабатывает все строки результирующего набора запроса как одну группу.
ORDER BY определяет последовательность, в которой строкам назначаются уникальные номера с помощью функции ROW_NUMBER в пределах указанной секции (окна). Оно должно указываться обязательно.
До Server 2005 в качестве упрощённого аналога функции ROW_NUMBER () использовался запрос
SELECT orderid, val,
(SELECT COUNT(*) FROM Sales.OrderValues) AS O2
WHERE O2.orderid <= O1.orderid) AS rownum
FROM Sales.OrderValues AS O1;
В этом решении используется агрегат COUNT во вложенном запросе для определения, у скольких строк значение упорядочения (в нашем случае orderid) меньше или равно текущему.
Недостатком запроса является низкая скорость выполнения, поскольку в каждой строке запроса выполняется дополнительный запрос.
1с не поддерживает подзапросы в разделе SELECT.
В СКД 1с реализован аналог функции ROW_NUMBER ().
ВЫЧИСЛИТЬВЫРАЖЕНИЕ(НомерПоПорядкуВГруппировке(), [Поле группировки], , , , [Поле сортировки])
Создание ранжирующего запроса.
Поскольку 1с не поддерживает подзапросы в разделе SELECT, перенесем подзапрос в строке запроса
(SELECT COUNT(* FROM Sales.OrderValues) AS O2
в раздел объединения таблиц. Для этого создадим тестовый запрос с набором данных:
ВЫБРАТЬ
148 КАК id,
410 КАК val,
"Dgon" КАК name
ПОМЕСТИТЬ ВременнаяТаблица
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
130,
370,
"Smit"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
150,
170,
"Dgon"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
151,
405,
"Dgon"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
153,
770,
"Smit"
;
//Ранжирующий запрос//
ВЫБРАТЬ
ВременнаяТаблица.id КАК id,
ВременнаяТаблица.val КАК val,
КОЛИЧЕСТВО(ВременнаяТаблица.id) КАК rownum,
СУММА(1) КАК rownum1
ИЗ
ВременнаяТаблица КАК ВременнаяТаблица
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица1
ПО ВременнаяТаблица.id >= ВременнаяТаблица1.id
СГРУППИРОВАТЬ ПО
ВременнаяТаблица.id,
ВременнаяТаблица.val
Результат:
Таким образом последний запрос нашего теста, фактически является ранжирующей функцией ROW_NUMBER.
Отметим, что колонки с выражениями
КОЛИЧЕСТВО(ВременнаяТаблица.id) КАК rownum,
СУММА(1) КАК rownum1,
являются эквивалентными.
Проведем ранжирвание по полю name, в порядке следвания id для этого измеим последний запрос
ВЫБРАТЬ
ВременнаяТаблица.id КАК id,
ВременнаяТаблица.val КАК val,
ВременнаяТаблица.name КАК name,
КОЛИЧЕСТВО(ВременнаяТаблица.val) КАК rownum,
СУММА(1) КАК rownum1
ИЗ
ВременнаяТаблица КАК ВременнаяТаблица
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица1
ПО ВременнаяТаблица.id >= ВременнаяТаблица1.id
И ВременнаяТаблица.name = ВременнаяТаблица1.name
СГРУППИРОВАТЬ ПО
ВременнаяТаблица.id,
ВременнаяТаблица.name,
ВременнаяТаблица.val
УПОРЯДОЧИТЬ ПО
name,
rownum1
Результат
Ранжирвание по полю name в порядке слдования val
ВЫБРАТЬ
ВременнаяТаблица.id КАК id,
ВременнаяТаблица.val КАК val,
ВременнаяТаблица.name КАК name,
КОЛИЧЕСТВО(ВременнаяТаблица.val) КАК rownum,
СУММА(1) КАК rownum1
ИЗ
ВременнаяТаблица КАК ВременнаяТаблица
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ВременнаяТаблица1
ПО ВременнаяТаблица.val >= ВременнаяТаблица1.val
И ВременнаяТаблица.name = ВременнаяТаблица1.name
СГРУППИРОВАТЬ ПО
ВременнаяТаблица.id,
ВременнаяТаблица.name,
ВременнаяТаблица.val
УПОРЯДОЧИТЬ ПО
name,
rownum1
Результат
Итоги
В первом запросе окно не задано и нумеруется весь набор записей, что стветствует функции с параметрами
ROW_NUMBER () OVER (ORDER BY id)
Параметр функции ORDER BY id сответствует соединеию
ВременнаяТаблица.id >= ВременнаяТаблица1.id
Во вотром и третьем запросе окно задано это поле name, отличаются запросы только параметром порядка:
ROW_NUMBER (ВременнаяТаблица.name = ВременнаяТаблица1.name) OVER ( PARTITION BY name ORDER BY id)
ВременнаяТаблица.id >= ВременнаяТаблица1.id и
ROW_NUMBER (ВременнаяТаблица.name = ВременнаяТаблица1.name) OVER ( PARTITION BY name ORDER BY val)
ВременнаяТаблица.val>= ВременнаяТаблица1.val
Соответственно, за параметр окна PARTITION BY в запросах отвечает сединение
ВременнаяТаблица.name = ВременнаяТаблица1.name
Создание функций смещения LAG и LEAD.
Начиная с Server 2012 поддерживаются функции смещения LAG, LEAD, FIRST_VALUE и LAST_VALUE.
Функции LAG и LEAD позволяют возвращать выражение значения из строки в секции окна, которая находится на заданном смещении перед (LAG) или после (LEAD) текущей строки. Смещение по умолчанию — «1», оно применяется, если смещение не указать.
Например строка в запросе -
LAG(val) OVER(PARTITION BY id ORDER BY id) AS prevvalр,
возвращает значение val из предыдущей строки.
LAG(val, [prev]) – возвращает значение val перед строкой со смещением = prev.
В СКД 1с есть аналог функций LAG и LEAD:
ВычислитьВыражение("val", , ,"Предыдущая","Предыдущая" )
ВычислитьВыражение(“val”, , ,“Следующая”,“Следующая”)
Для создания функций смещения, изменим наш тест следующим образом. Поместим последний запрос во временную таблицу – «ВременнаяТаблица1» и создадим еще один запрос:
ВЫБРАТЬ
ВременнаяТаблица1.id КАК id,
ВременнаяТаблица1.rownum КАК rownum,
ВременнаяТаблица1.val КАК val,
ВременнаяТаблица11.val КАК prevvalр
ИЗ
ВременнаяТаблица1 КАК ВременнаяТаблица1
ЛЕВОЕ СОЕДИНЕНИЕ ВременнаяТаблица1 КАК ВременнаяТаблица11
ПО ВременнаяТаблица1.rownum = ВременнаяТаблица11.rownum + 1
УПОРЯДОЧИТЬ ПО
Rownum
Результат запроса:
Таким образом создан аналог функции LAG, которая возвращает значения из предыдущей строки.
Изменяя в выражении соединения переменную [смещение], можно менять строки из которых берутся значения.
ПО ВременнаяТаблица1.rownum = ВременнаяТаблица11.rownum + [смещение],
Если изменить условие соединения на
ПО ВременнаяТаблица1.rownum = ВременнаяТаблица11.rownum - 1,
то получим аналог функции LEAD..
Примеры применения.
Отчет показывает:
- Среднее количество проданного товара в день за интервалы, действия цен номенклатуры по заданному виду цен, за выбранный период дат;
- Изменение цены номенклатуры (в %), по интервалам действия цен;
- Отклонение расчетной цены продажи в документах реализации (действие скидок и наценок) от цены установленной в РегистрСведений.ЦеныНоменклатуры;
- Ошибки установки цены в документах реализации.
Отчет позволяет анализировать средние продажи за период действия цен в зависимости от их изменения, в заданном интервале дат. Тестировался для конфигурации "Управление торговлей 11" (релизы 11.2-11.5), Комплексная автоматизация 2.5.16.101.
- Отчет (ссылка) «Реестр показаний по приборам учета за период с расчетом среднего потребления ресурса».
Отчет выводит реестр показаний по приборам учета абонентов за выбранный период, с настраиваемыми группировками, и расчетом среднедневного и среднемесячного потребления ресурса за период.
Конфигурация "Расчеты с населением за газ" (Релиз 1.1.2.2).
- Анализ дебиторской задолженности по дням – вычисляем тренд и абсолютное увеличение или уменьшение задолженности относительно прошлого периода
- Анализ курсов валют и оценка прибыли или убытков от курсовой разницы – нужно получить дельту между текущим и предыдущим курсом и умножить на остаток денежных средств в валюте.
- Получение скорости и ускорения передвижения объекта.
Имеем данные изменения положения объекта во времени.
Вычисляем скорость объекта как,
Скорость =
КоординатаВточке i - КоординатаВточке i-1 / времяВточке i - времяВточке i-1
Вычисляем ускорение.
Ускорение =
Скорость в точке i - Скорость в точке i-1 / время в точке i - время в точке i-1
Фактически реализуются формулы:
v = dS/dt
a = dV/dt
Вам может быть интересно.
Загрузка данных контрагентов из России, Беларуси и Казахстана из веб-сервисов.
Мониторинг потребления ресурсов базами кластеров сервера
Заметки по SQL: Срез последних - аналог запроса
