Книга «Оконные функции в T-SQL»

Опубликовано

Опубликовано по ссылке:

Глава 1. Работа с окнами в SQL

Оконные функции (window function) способны помочь в решении огромного спектра задач посредством более простого, интуитивного и эффективного подхода к расчетам в рамках наборов данных. Оконные функции представляют собой вычисления, применяемые к набору строк, определенному при помощи инструкции OVER. Главным образом такие функции используются для проведения аналитики данных, включая вычисление накопительных или нарастающих итогов (running totals), скользящих средних (moving average), а также определение пропусков (gap), островов (island), интервалов (interval) и других показателей в ваших данных. Оконные функции базируются на продвинутой концепции, выраженной в стандарте SQL ISO/IEC и называемой работой с окнами (windowing). Идея, лежащая в основе этой концепции, позволяет выполнять вычисления применительно к наборам строк (окнам) и возвращать одиночные значения.

С момента появления оконных функций в SQL Server 2005 эта концепция получила существенное развитие в следующих версиях SQL Server и SQL Azure Database. Вскоре мы посмотрим внимательнее на достигнутый за это время прогресс. В актуальных на данный момент версиях продуктов все еще не реализована часть стандартного функционала этой технологии, но за последние годы были сделаны большие шаги в этом направлении. В данной книге мы будем рассматривать как функционал, реализованный в SQL Server, так и возможности, до сих пор не получившие развития в этой среде. Впервые упоминая о тех или иных средствах, я буду отдельно говорить об их поддержке в SQL Server.

С момента появления оконных функций в SQL Server я стал все чаще применять их при решении самых разнообразных задач. Я до сих пор некоторые свои давние решения, опирающиеся на традиционные конструкции языка SQL, переписываю под поддержку оконных функций. И результат в плане эффективности и простоты чаще всего оправдывает приложенные усилия. Сейчас дошло до того, что практически в каждом моем решении так или иначе присутствуют оконные функции. Кроме того, в наши дни стандарт SQL и системы управления базами данных (СУБД), а с ними и вся индустрия данных в целом двигаются в сторону аналитических решений, и оконные функции являются одним из важнейших строительных элементов на этом пути. Упор на средства оперативной обработки транзакций (online transaction processing – OLTP), который делался в 90-е, там и остался. Лично мне видится развитие технологии SQL в ближайшем будущем именно в сторону более эффективного использования оконных функций, а значит, время, потраченное на их изучение, не пройдет для вас даром.

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

  • истоки оконных функций;
  • беглый взгляд на решения, использующие оконные функции;
  • описание основных элементов, входящих в состав оконных функций;
  • обзор инструкций, поддерживающих использование оконных функций;
  • разбор стандартного решения с повторным использованием определения окна.

Эволюция оконных функций

Как я уже говорил, с момента своего появления в SQL Server оконные функции проделали уже огромный путь, а в будущем их ждет еще большее развитие. На рис. 1.1 показаны все основные вехи эволюции оконных функций в ​​SQL Server, включая возможности, которые, как мы все надеемся, будут реализованы в ближайшем будущем.

 

@Рис. 1.1. Процесс эволюции оконных функций в SQL Server

 

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

  • в версии SQL Server 2005 появилась поддержка агрегатных оконных функций, но без возможности задавать границы окна, а также были реализованы ранжирующие функции (ROW_NUMBER, RANK, DENSE_RANK и NTILE);
  • в версиях SQL Server 2008 и 2008 R2 оконные функции затронуты не были;
  • в SQL Server 2012 появилась поддержка агрегатных оконных функций с возможностью задавать границы окна, функций смещения (LAG, LEAD, FIRST_VALUE и LAST_VALUE), а также статистических или аналитических функций (PERCENT_RANK, CUME_DIST, PERCENTILE_CONT и PERCENTILE_DISC);
  • в версии SQL Server 2014 оконные функции остались без изменений;
  • в SQL Server 2016 оконные функции были оптимизированы за счет появления нового оператора пакетной обработки Window Aggregate. Однако, чтобы воспользоваться всеми его преимуществами, хотя бы для одной из участвующих в запросе таблиц должен был быть создан индекс columnstore;
  • в версии в SQL Server 2017 появилась поддержка первой функции для работы с упорядоченными наборами – STRING_AGG, позволяющей конкатенировать строки;
  • в SQL Server 2019 был реализован пакетный режим для данных rowstore, что позволило оставить в прошлом требование наличия индекса columnstore для участвующих в запросе таблиц;
  • несколько важных возможностей, связанных с оконными функциями, на данный момент еще не реализованы в SQL Server. Сюда можно отнести создание границ окна с помощью инструкции RANGE с указанием параметра INTERVAL, технологию распознавания шаблонов в строках, вложенные оконные функции, опции RESPECT | IGNORE NULLS, инструкцию WINDOW, служащую для повторного использования определения окна, прочие функции для работы с упорядоченными наборами и многое другое.

Основы оконных функций

Перед изучением оконных функций вам будет полезно узнать, что послужило поводом для их появления. Именно об этом мы будем говорить в этом разделе. Мы опишем разницу между подходами, основанными на работе с наборами данных и курсорами/итерациями, которые применяются в запросах, и покажем, как оконные функции позволяют перебросить мостик между ними. Наконец, мы обсудим недостатки альтернативных методов и расскажем, почему применение оконных функций в подавляющем большинстве случаев будет лучшим выбором.

Описание оконных функций

Оконная функция (window function) – это функция, применяемая к набору строк. Термином окно (window) в стандарте SQL описывается контекст, в котором выполняется функция. Спецификация создаваемого окна задается в SQL при помощи инструкции OVER. Рассмотрим в качестве примера следующий простейший запрос:

 

USE TSQLV5;

SELECT orderid, orderdate, val,

RANK() OVER(ORDER BY val DESC) AS rnk

FROM Sales.OrderValues

ORDER BY rnk;

 

Примечание. О том, где скачать базу данных TSQLV5 и сопроводительные файлы, читайте во вводной главе.

 

Ниже представлен сокращенный вывод этого запроса:

 

orderid  orderdate  val       rnk

——— ———- ——— —-

10865    2019-02-02 16387.50  1

10981    2019-03-27 15810.00  2

11030    2019-04-17 12615.05  3

10889    2019-02-16 11380.00  4

10417    2018-01-16 11188.40  5

10817    2019-01-06 10952.85  6

10897    2019-02-19 10835.24  7

10479    2018-03-19 10495.60  8

10540    2018-05-19 10191.70  9

10691    2018-10-03 10164.80  10

…

 

Оконной функцией в приведенном выше примере является функция RANK. Для выполнения ранжирования нам необходимо выполнить упорядочивание. В данном случае мы применили сортировку по полю val в убывающем (descending) порядке. Эта функция вычисляет порядковый номер для строки в рамках заданного набора данных с учетом указанного порядка сортировки. При использовании сортировки по убыванию, как в нашем примере, ранг вычисляется путем добавления единицы к количеству строк в нашем наборе данных, в которых сортируемое значение больше, чем в текущей строке. Возьмите, к примеру, строку с рангом 5 из нашего запроса. Значение 5 получилось путем прибавления единицы к количеству строк, в которых в столбце val находится сумма, превышающая сумму в нашей текущей строке (11 188,40).

Характеристики набора данных, к которому относится наша строка, включая порядок упорядочивания и другие параметры, если они присутствуют, описываются при помощи ключевого слова OVER. Если в этой инструкции не указать границы окна, как в нашем примере, набором данных окна будет считаться весь результирующий набор запроса.

 

Примечание. Если быть точными, окно определяется как набор строк – или отношение (relation), – переданное на вход фазы логической обработки запроса (logical query processing), в которой представлена оконная функция. Однако такое определение на данном этапе мало что вам скажет. Так что с целью упрощения можно считать, что речь идет о результирующем наборе запроса. Более подробное описание этого процесса будет дано далее.

 

Гораздо важнее отметить, что в концептуальном смысле инструкция OVER определяет окно для функции относительно текущей строки. И это справедливо для всех строк в итоговом наборе данных. Иными словами, для каждой строки инструкция OVER определяет окно, не зависимое от окон, определенных для других строк. На самом деле это очень глубокая идея, понимание которой может прийти не сразу. Но когда вы осознаете всю полноту этого утверждения, вы сможете сказать, что постигли концепцию оконных функций, всю ее мощь и глубину. Если пока для вас это пустой звук, не отчаивайтесь. Я просто бросил семя в почву, ростка придется подождать.

Первое упоминание об оконных функциях в стандарте SQL появилось в расширенном документе к SQL:1999 в разделе «OLAP функции». С тех пор каждая версия стандарта – SQL:2003, SQL:2008, SQL:2011 и SQL:2016 – расширяла возможности оконных функций, а в последней версии поддержка этой технологии достигла небывалого уровня. Кроме того, оконные функции приобрели такой аналитический механизм, как распознавание шаблонов в строках, что говорит о намерениях рабочей группы, ответственной за стандарт SQL, продолжать развивать это направление.

 

Примечание. Вы можете купить официальные документы стандарта SQL от ISO или ANSI. К примеру, базовый документ от ISO по стандарту SQL:2016, описывающий все ключевые конструкции языка, можно приобрести по адресу https://www.iso.org/standard/63556.html.

 

Стандарт SQL поддерживает несколько типов оконных функций: агрегатные, ранжирующие, аналитические или статистические (функции распределения) и функции смещения. Но помните, что работа с окнами в SQL представляет собой целую концепцию, так что в будущих версиях стандарта мы можем увидеть и новые типы оконных функций.

Агрегатные оконные функции (aggregate window functions) – это все те же функции агрегирования, с которыми вы давно и хорошо знакомы (SUM, COUNT, MIN, MAX и другие), но которые привыкли применять в контексте запросов с группировками. Агрегатные функции по определению работают с наборами данных, будь то наборы, определенные запросами с группировками или спецификацией окна.

К ранжирующим функциям (ranking functions) относятся следующие: RANK, DENSE_RANK, ROW_NUMBER и NTILE. При этом первые и последние две функции относятся в стандарте к разным категориям, и позже мы выясним причину этого. Я предпочитаю для простоты относить все четыре функции к одной категории по примеру официальной документации к SQL Server.

Статистические функции (statistical functions) или функции распределения включают в себя функции PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK и CUME_DIST. Эти функции предназначены для расчета статистических показателей, таких как процентили, процентные ранги и накопительные распределения.

К функциям смещения (offset functions) относятся функции LAG, LEAD, FIRST_VALUE, LAST_VALUE и NTH_VALUE. SQL Server поддерживает первые четыре из этого списка. Функция NTH_VALUE в версии SQL Server 2019 не реализована.

 

Примечание. В главе 2 мы подробно поговорим обо всем перечисленных здесь функциях.

 

Новые идеи, устройства или инструменты, даже если они очевидно превосходят своих предшественников в простоте использования, всегда являются определенным барьером. Все новое на первый взгляд кажется сложным. Так что если оконные функции для вас в новинку и вы ищете мотивацию в их освоении, я поделюсь несколькими наблюдениями из собственного опыта:

  • оконные функции помогают в решении огромного спектра задач. Я даже не знаю, как еще лучше это акцентировать. Как я уже упоминал ранее, в настоящее время я применяю оконные функции практически во всех своих решениях. Заключительная шестая глава этой книги будет посвящена практическим примерам использования этих функций. А пока я лишь перечислю типы задач, с которыми оконные функции справляются легко и непринужденно:
    • постраничный вывод;
    • удаление дубликатов;
    • возвращение верхних n строк по группам;
    • вычисление накопительных итогов;
    • выполнение операций над интервалами, таких как объединение интервалов и вычисление максимального количества пересекающихся интервалов;
    • определение пропусков и островов;
    • вычисление процентилей;
    • вычисление моды распределения;
    • сортировка иерархий;
    • сведение данных;
    • определение новизны данных.
  • я пишу запросы на языке SQL вот уже почти три десятилетия и последние несколько лет очень активно использую оконные функции. Могу сказать, что, несмотря на время, которое потребовалось на доскональное освоение этой концепции, сейчас применение оконных функций при решении самых разнообразных задач кажется мне гораздо более эффективным и интуитивным по сравнению с традиционными методами;
  • оконные функции поддаются оптимизации, и далее в этой книге мы поговорим об этом более подробно.

Декларативный язык и оптимизация

Вас может удивить то, что в случае использования декларативного языка SQL, в котором вы логически объявляете свой запрос, а не описываете, как именно должен быть получен результат, две разновидности одного и того же запроса (к примеру, с использованием оконных функций и без) могут давать разную производительность. Почему движок SQL Server, использующий диалект языка T-SQL, не всегда способен понять, что две записи означают одно и то же, а значит, должны приводить к созданию одинаковых планов выполнения?

На то есть свои причины. Во-первых, оптимизатор SQL Server не идеален. Я не хочу показаться неблагодарным, ведь оптимизатор запросов, использующийся в SQL Server, представляет собой настоящий шедевр искусства создания программного обеспечения. Но факт в том, что даже в нем не реализованы все без исключения правила оптимизации запросов. Во-вторых, движок ограничен во времени, которое отводится на оптимизацию. Иначе могло бы произойти так, что на саму оптимизацию тратилось бы больше времени, чем можно было бы сэкономить за счет нее. Для любого запроса можно придумать бессчетное количество планов выполнения, и на проверку каждого просто не хватит времени. На основании факторов, таких как размер таблиц, участвующих в запросе, SQL Server вычисляет два значения: стоимость (cost), которая может считаться приемлемой для данного запроса, и максимальное количество времени, которое может быть затрачено на выполнение оптимизации. По достижении любого из этих значений оптимизация прекращается, и SQL Server использует лучший из найденных на этот момент времени планов выполнения запроса.

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

Здесь важно понимать, что вы должны сделать абсолютно осознанный выбор, переключаясь на использование оконных функций, поскольку это совершенно иная концепция, которую нужно использовать во благо. К работе с окнами в SQL привыкнуть бывает непросто. Но по прохождении периода адаптации вы осознаете всю легкость и интуитивность этой технологии. Вспомните, как долго вы привыкали к современным гаджетам, без которых сегодня просто не мыслите жизни. (…)

Текст неполный и представлен для ознакомления…

Купить книгу:
Ссылка: https://dmkpress.com/catalog/computer/databases/978-5-93700-139-10/

Промокоды:

Бумажная версия: промокод (22%) Ginko_SQLW_paper

Версия PDF: промокод (25%) Ginko_SQLW_PDF