Заметки по SQL: Создание ранжирующей функции ROW_NUMBER и функций смещения LAG и LEAD простым запросом

04.01.25

Разработка - Универсальные функции

+10 –
> > > > >
  • image-20241116053018-1.png
  • image-20241116053018-2.png
  • image-20241116053018-3.png
  • image.png
  • image.png
  • image.png
В статье рассматривается создание ранжирующей функции ROW_NUMBER с использованием простого SQL запроса, создание на его основе функций смещения LAG и LEAD и варианты применения этих функций.

Введение.

Стандарт SQL поддерживает четыре оконные функции, которые служат для ранжирования. Это ROW_NUMBER, NTILE, RANK и DENSE_RANK. Функции ранжирования появились в MS SQL начиная с Server 2005.

ROW_NUMBER – функция вычисляет последовательные номера строк, начиная  с 1, в соответствии с заданным упорядочением окна.

Под окном (секцией) понимается заданная групп

Вступайте в нашу телеграмм-группу Инфостарт

Запросы SQL

+10 –

Вы можете заказать платную адаптацию этой статьи под ваши задачи на «Бирже заказов».

  • 0% комиссии — оплата напрямую исполнителю;
  • Исполнители любого масштаба — от отдельных специалистов до команд под проект;
  • Прямой обмен контактами между заказчиком и исполнителем;
  • Безопасная сделка — при необходимости;
  • Рейтинги, кейсы и прозрачная система откликов.

См. также

Infostart Toolkit Air: Инструменты разработки в 1С:8.3 и 8.5

Инструментарий разработчика Роли и права Запросы СКД Программист Руководитель проекта 1С:Предприятие 8 Платные (руб)

Инструменты для разработчиков 1С 8.3: Infostart Toolkit. Автоматизация и ускорение разработки на управляемых формах. Легкость работы с 1С.

16500 руб.

02.09.2020    274094    1523    422    

1186
Консоль запросов ЗУП с поддержкой обращения к методам программного интерфейса в запросах

Инструментарий разработчика Запросы Программист 1С:Предприятие 8 1С:Зарплата и кадры государственного учреждения 3 1С:Зарплата и Управление Персоналом 3.x Абонемент ($m)

QueryConsole1C — расширение, включающее консоль запросов с поддержкой исполняемых представлений — аналогов виртуальных таблиц, основанных на методах программного интерфейса ЗУП. Оно позволяет выполнять запросы с учётом встроенной бизнес-логики, отлаживать алгоритмы получения данных и автоматически генерировать код на встроенном языке 1С.

1 стартмани

16.05.2025    12645    159    zup_dev    32    

86
Ловушка функции запроса АВТОНОМЕРЗАПИСИ()

Запросы Программист 1С:Предприятие 8 1C:Бухгалтерия Бесплатно (free)

Столкнулся с интересной ситуацией, которую хотел бы разобрать, ввиду её неочевидности. Речь пойдёт про использование функции запроса АВТОНОМЕРЗАПИСИ() и проблемы, которые могут возникнуть.

11.10.2024    22797    XilDen    39    

114
Загрузка табличного документа в таблицу значений. Пять строк кода, которые изменили мир

Универсальные функции Программист 1С:Предприятие 8 1C:Бухгалтерия Бесплатно (free)

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

21.05.2024    65725    dimanich70    87    

177
Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    28341    skovpin_sa    15    

107
Генерация штрихкода и QR, разбор настроек и примеры

БСП (Библиотека стандартных подсистем) Сканер штрих-кода Универсальные функции Этикетки, ценники Программист Бизнес-аналитик 1C:Бухгалтерия Бесплатно (free)

Разберем на примерах использование компоненты из БСП для генерации штрихкода и матричных кодов.

15.09.2023    52717    YA_418728146    17    

135
Валидация JSON через XDTO (включая массивы)

WEB-интеграция Универсальные функции Механизмы платформы 1С Программист 1С:Предприятие 8 1C:Бухгалтерия Бесплатно (free)

При работе с интеграциями рано или поздно придется столкнуться с получением JSON файлов. И, конечно же, жизнь заставит проверять файлы перед тем, как записывать данные в БД.

28.08.2023    30628    YA_418728146    8    

175
Печать непроведенных документов для УТ, КА, ERP. Настройка печати по пользователям, документам и печатным формам

Пакетная печать Печатные формы Адаптация типовых решений Универсальные функции 1С:Предприятие 8 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Россия Абонемент ($m)

Расширение для программ 1С:Управление торговлей, 1С:Комплексная автоматизация, 1С:ERP, которое позволяет распечатывать печатные формы для непроведенных документов. Можно настроить, каким пользователям, какие конкретные формы документов разрешено печатать без проведения документа.

2 стартмани

22.08.2023    9809    120    progmaster    23    

6
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. PerlAmutor 162 17.11.24 19:20 Сейчас в теме
1с не поддерживает подзапросы в разделе SELECT.

На самом деле поддерживает, но лишь для проверки результата подзапроса на ЛОЖЬ или ИСТИНА.
+ – Ответить
2. starik-2005 3301 18.11.24 15:07 Сейчас в теме
Прикольно. Не совсем ясно, зачем в запросах пустые строки между строками - выглядит ушибом глаза.
paulwist; triviumfan; +2 – Ответить
3. paulwist 18.11.24 15:18 Сейчас в теме
Таким образом последний запрос нашего теста, фактически является ранжирующей функцией ROW_NUMBER.


Нуу, предложенное решение сильно ограничено в функционале 


with cte as
(
sel ect  148  id, 440  val
uni on all
select  130, 770
-- Просто добавим одну строку с id = 131, val = 770 из "первой строки"
uni on all
select  131, 770
--
uni on all
sel ect  150, 170
union all
sel ect  151, 270
)
sel ect
                cte.id  id,
                cte.val val,
                count(cte.id) rownum,
                sum(1) rownum1,
				row_number() over (partition by cte.val order by cte.id, cte.val) [Настоящий Row_Number]
fr om
                cte cte
inner join      cte cte1
                on cte.id >= cte1.id
group by
                cte.id,
                cte.val
order by
                cte.id,
                cte.val
Показать


Итог, "сильно" кривой по сравнению с row_number от вендора :)
Прикрепленные файлы:
+ – Ответить
4. triviumfan 102 21.11.24 14:58 Сейчас в теме
(3)
Итог, "сильно" кривой по сравнению с row_number от вендора :)

У него ведь секции по id, а не val.
+ – Ответить
5. paulwist 21.11.24 15:59 Сейчас в теме
(4)
У него ведь секции по id, а не val.


У ТСа нет секций, вернее она одна на все данные, если 

row_number() over (partition by cte.ID order by cte.id, cte.val)


тогда row_number для всех уникальных ID будет равным 1 (единице) 

with cte as
(
sel ect  148  id, 440  val
uni on all
select  130, 770
/*-- Просто добавим одну строку с id = 131, val = 770 из "первой строки"
uni on all
select  130, 771
--*/
uni on all
sel ect  150, 170
union all
sel ect  151, 270
)
sel ect
                cte.id  id,
                cte.val val,
                count(cte.id) rownum,
                sum(1) rownum1,
				row_number() over (partition by cte.id order by cte.id, cte.val) [Настоящий Row_Number]
fr om
                cte cte
inner join      cte cte1
                on cte.id >= cte1.id
group by
                cte.id,
                cte.val
order by
                cte.id,
                cte.val
Показать
triviumfan; +1 – Ответить
6. triviumfan 102 25.11.24 23:41 Сейчас в теме
(5) Только добрался до ssms)
Получается, что у него 1 секция типа: 
row_number() over (order by cte.id, cte.val) [Настоящий Row_Number]
+ – Ответить
7. paulwist 26.11.24 08:32 Сейчас в теме
(6)
Получается, что у него 1 секция типа:


Ну да, одно "окно".

Предложенный ТСом метод хорош если надо пронумеровать результат, хотя как правило в БД делают табличку-счетчик c одним Identity полем и заполняют его скажем до 1 млн (кому сколько надо), но это не row_number() однозначно. :)
+ – Ответить
8. IVC_goal 246 04.12.24 10:52 Сейчас в теме
(6) Для вас Добавил раздел Выводы

(5)
triviumfan; +1 – Ответить
Для отправки сообщения требуется регистрация/авторизация