Функция окна SQL выполняет вычисления в наборе строк таблицы, чтобы упростить анализ данных.
Много раз во время анализа данных мы сталкиваемся с проблемой создания агрегированных вычислений для группы строк. Появляются многочисленные примеры необходимости создания нескольких итоговых выходных данных для нескольких временных таблиц. После создания новых результатов они объединяются с исходным набором данных, чтобы продолжить процесс анализа данных. Наличие этой постоянной циклической ссылки на создание и добавление сводной статистики может привести к более длительному процессу, чем требуется. Использование оконных функций SQL позволяет разработчику избежать ситуации, когда создаются большие объемы данных, а память потребляется только ради небольшой выгоды.
В этой статье мы покажем некоторые начальные атрибуты, которые могут помочь решить оконные функции SQL. Кроме того, будет представлено несколько функций временных рядов, чтобы показать этапы обработки данных во времени.
Набор данных и программное обеспечение
Поскольку многие люди интересуются стоимостью недвижимости, мы рассмотрим индекс цен на жилье, который дает макроэкономическую перспективу. Мы рассмотрим Индекс цен на жилую недвижимость для Ирландской Республики, взятый с data.gov.ie¹. Обратите внимание, что для облегчения работы с набором данных была выполнена некоторая предварительная обработка. Символьная переменная Тип жилой недвижимости содержала данные по региону страны и типу недвижимости. Поэтому имело смысл разделить эту переменную на две отдельные переменные с именами Площадь и Тип_свойства в наборе данных.
Код SQL, показанный в этой статье, был создан с использованием экземпляра Microsoft SQL Server Management Studio 18.
При использовании оконных функций SQL доступно несколько различных категорий. Мы рассматриваем три общих уровня категорий; агрегатные, аналитические и ранжирующие функции. Добавление раздела обработки временных рядов показывает временные элементы, доступные для просмотра.
Агрегатные функции
Агрегирование предоставляет сводную статистику просматриваемого набора данных. Некоторые из этих основных статистических данных помогают нам понять структуру числовых переменных. Благодаря этому пониманию мы можем лучше понять историю данных. Обычно эти сводные значения создаются в рамках отдельного запроса, а затем статистика снова присоединяется к первому набору данных. Создание временной таблицы может работать со специальным анализом. Однако если мы хотим изменить диапазон оцениваемых значений, это потенциально может привести к ошибкам, поскольку потребуется обновить несколько разделов кода. Наличие более автоматизированного подхода действительно может помочь улучшить удобство использования кода.
Здесь оконные функции могут помочь обеспечить большую гибкость. В приведенном ниже коде у нас есть общий обзор оконной функции в действии. Ключевое слово over определяет предложения окна для агрегатных функций. В скобках первый вариант предназначен для использования разделения по полям. Это список полей раздела, определяющих окно или набор строк, с которыми будет работать оконная функция. Далее у нас есть порядок по параметрам, который будет сортировать строки для секционированных переменных.
Из приведенного выше кода мы показали четыре доступных статистических параметра. Функция count будет записывать количество строк в окне. При использовании трех других функций они будут перемещаться по окну по одной строке за раз.
С псевдонимом avgValue метод среднего используется для понимания того, как изменяется среднее значение по мере добавления новых строк. Включение минимального и максимального значения помогает просмотреть границы значений. Когда из строки 7 появляется новое окно, мы можем видеть, как будут сбрасываться значения совокупных переменных.
Аналитические функции
Для выполнения более сложных операций с оконными функциями можно использовать область аналитических функций. Мы увидим, что многие из этих функций помогут перемещать точки данных из разных частей окна.
Из приведенного выше кода мы рассмотрели множество различных опций, доступных с функцией задержки. По умолчанию функция запаздывания принимает первое запаздывающее значение. Для строки 2 в выходных данных ниже мы видим, что lagValue взял данные из переменной значения из первой строки. Чтобы найти данные с задержкой за три месяца до этого, в скобки функции задержки можно добавить значение параметра, равное трем.
Оставшиеся две функции подчеркивают, как может быть выполнено присвоение значения нулевым значениям, когда данные недоступны. Во-первых, использование объединения позволит просмотреть функцию и понять, будет ли получено ненулевое значение. Мы должны помнить, что порядок входных данных для функции объединения является ключевым, при доступе к первому входу, если он доступен, значение будет использоваться. Если это не так, то будет принято нулевое значение второго параметра. Мы видим, что функция запаздывания также обеспечивает эту функциональность, и включение третьего параметра нуля в строке 20 показывает, как этот метод может быть выполнен за один шаг.
Возможность принимать значения с запаздыванием поможет при сравнении временных рядов набора данных. Опять же, оконная функция помогает автоматизировать ряд этих параметров с запаздыванием вместо необходимости выполнять несколько объединений. Взяв значения с трехмесячным лагом, это помогает анализировать квартальные изменения. Включение разницы в шесть или двенадцать месяцев показало бы полугодовые или годовые движения.
Функция Lead вернет значения из будущего обратно в текущую строку. Подобно функции запаздывания, по умолчанию используется параметр 1, а также может быть назначено заполнение нулевого значения. Использование функции first_value предоставляет подробную информацию о начале окна. Это может помочь, если диапазон значений пересматривается с учетом определенной даты временного ряда. В данном примере за первое значение берется начало календарного года. Однако при использовании функции last_value требуется дополнительное предложение, чтобы гарантировать просмотр всего окна значений. Включение неограниченного диапазона в начале (предыдущий) и в конце (последующий) завершает этот процесс.
Первое и последнее значения помогут показать, как промежуточные значения перемещались в течение периода времени окна. Если для анализа данных требуется относительное сравнение с этими точками данных, расчет может быть выполнен быстро.
Функции ранжирования
Включение функций ранжирования в анализ данных ранжирует значения указанного поля. Много раз мы заинтересованы в понимании первых (N) записей определенного значения. В других случаях нижние (N) записи могут дать больше информации в зависимости от заданного вопроса.
Функция ранга предназначена для понимания значений от самого высокого до самого низкого. При этой оценке мы смотрим на верхние (N) значения. Как мы видим в коде, было добавлено ключевое слово по убыванию, чтобы гарантировать, что предложение order by правильно обрабатывает значения. Чтобы сохранить первоначальный порядок входного набора данных, мы включили предложение order by в строку 16, чтобы изменить порядок результатов после завершения оконной функции. Возможность изменить порядок результатов показывает гибкость, доступную с оконной функцией.
Для функции номера строки использование предложения partition by гарантировало сброс счетчика строк для каждого окна. Вывод для этой переменной похож на предыдущую функцию подсчета.
Если бы результаты сохранялись в порядке вывода оконной функции, временной характер данных был бы потерян. Как мы видим, строки 1 и 2 поменялись бы местами. Этот вывод был бы хорош, если бы данные не были связаны с временным рядом. Необходимо позаботиться о том, чтобы выходные результаты соответствовали ожиданиям.
Обработка временных рядов
При рассмотрении индекса цен на жилье анализ данных концентрируется на движении значений во времени. Используя знания, полученные из предыдущих разделов этой статьи, мы можем создать ряд начальных функций временных рядов. Понимание тенденций во времени может помочь лучше понять макроэкономическую среду, в которой генерируются данные.
Совокупное значение сначала генерируется для усреднения значений во временном окне. Простая скользящая средняя может помочь снизить волатильность с помощью точек данных временного ряда. Результат этой операции должен показать более плавную визуализацию данных о том, как точки данных изменились с течением времени. Чтобы создать среднее значение за три месяца, была включена опция строк, чтобы уменьшить количество строк для включения. Добавленные параметры гарантируют, что используемые точки данных находятся в диапазоне между текущей строкой и двумя предыдущими строками.
Чтобы создать процентное изменение за три месяца, мы можем использовать предыдущую функцию задержки. Происходит сравнение между текущей строкой и отложенным значением трехмесячной давности. Используя деление и вычитание из результата единицы, отрицательный вывод покажет уменьшение, а положительный вывод покажет увеличение.
При использовании логики процентного изменения нулевое значение по умолчанию было исключено. Если бы значение было включено, интерпретатор SQL вернул бы ошибку деления на ноль. В результате в начале вычислений присутствуют три нулевых значения. По мере того, как к расчетам добавляется больше данных, может начать проявляться более плавный тренд.
Заключение
В этой статье мы работали с функцией SQL Windows, чтобы показать, чего можно достичь с помощью SQL-запроса. Проведенные процессы проектирования признаков показали, как можно получить совокупную статистику для каждого окна. Раньше нам приходилось создавать эту статистику во временной таблице, а затем присоединять ее к исходной таблице. Однако мы узнали, что функция Windows предоставляет возможность избежать этого шага в потоке процесса. Для функций аналитики и ранжирования мы увидели, как можно создавать метрики временных рядов, чтобы помочь с визуализацией данных. В рамках моделей машинного обучения мы могли бы использовать эти новые функции для прогнозирования будущих значений.
Оставляйте свои комментарии и большое спасибо за прочтение!
Вы можете стать премиум-участником Medium, используя приведенную ниже ссылку, и получить доступ ко всем моим историям и тысячам других:
Вы можете связаться со мной в LinkedIn для дружеской беседы обо всем, что связано с данными. Другие истории, которыми я поделился:
[1] Набор данных взят с сайта DATA.GOV.IE https://data.gov.ie/dataset/hpm09-residential-property-price-index?package_type=dataset, авторские права на эти данные защищены https://creativecommons.org/licenses/by/4.0/
