Всем привет. Это моя первая статья на Хабре, буду рад критике и комментариям.

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

Будем рассматривать задачи обучения с учителем — задачи регрессии или классификации. Решение таких задач можно разбить на много этапов, и нас будет интересовать этап создания новых предикторов. Будем предполагать, что в исходных сырых данных у нас есть, кроме всего прочего, данные по транзакциям. Детали не важны, поэтому будем считать, что у нас есть пользователи, которые во времени совершают некоторые действия, характеризующие их активность (поле Amount), а также действия, которые мы хотим предсказать (поле Target):

При этом время индексируется целыми числами. При создании такой индексации важно, чтобы индексы $$ и $$ соответствовали временным единицам, которые действительно следуют друг за другом (из дальнейшего будет ясно, почему это важно). Например, если надо индексировать дни, то каждому дню можно поставить в соответствие индекс, равный числу дней с самого первого дня среди всех рассматриваемых. Если самый первый день в табличке – 1 января 2017 года, то 2 января будет соответствовать индекс 1, 28 января – индекс 27, а 4 февраля – индекс 34. Иногда бывает целесообразно для каждого пользователя добавить записи для всех пропущенных значений индекса времени, заполняя поле amount нулями. Например, нас могут интересовать данные по потреблению некоторого продукта в течение каждого из предыдущих трех месяцев – для каждого пользователя в таблице всегда будут три записи.

Поле Target в этом примере хранит информацию о бинарном событии, которое либо произошло, либо не произошло в течение соответствующего момента времени.

Важно отметить, что на практике, если такая возможность есть, я всегда отделяю создание предикторов от моделирования. Обычно большую часть трансформаций и выкрутасов, которые можно сделать в R-е, python-е или ещё где-то, также можно сделать и в SQL-е. Поэтому я по возможности всегда создаю датасет и все предикторы в SQL-е. Преимущества заключаются в том, что, во-первых, этапы машинного обучения отделяются друг от друга и, во-вторых, все манипуляции с данными делаются сразу и только в базе данных. Более того, трансформации предикторов иногда даже легче делать, используя SQL.

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

Из формулы ясно видно, что коэффициент наклона зависит только от средних и их произведений. Следовательно, должна быть возможность его рассчитывать "на лету" в SQL-е, так как он вполне себе заточен под такие операции. Также ясно, что можно рассчитывать и изменения этого коэффициента: например, фиксируем то, что тренд рассчитывается по пяти временным значениям, дальше для каждого набора из пяти последовательных записей рассчитываем тренд. Те, кто хорошо знаком с SQL-м, уже понял, как это можно сделать — надо использовать оконные функции. Оконные функции позволяют рассчитывать скользящее среднее, а именно это нам и надо. Цель — посчитать скользящие средние как для поля Amount, так и для поля TimeID. Первое будет играть роль зависимой переменной, второе — независимой:

SELECT UserID
             , TimeID, Amount,
             , AVG(Amount) OVER(PARTITION BY UserID
                                              ORDER BY TimeID 
                                              ROWS BETWEEN 5 PRECEDING
                                              AND 1 PRECEDING)  AS [y_bar]
             , AVG(1.0*TimeID) OVER(PARTITION BY UserID
                                              ORDER BY TimeID 
                                              ROWS BETWEEN 5 PRECEDING
                                              AND 1 PRECEDING)  AS [x_bar]
             , AVG(Amount*TimeID) OVER(PARTITION BY UserID
                                              ORDER BY TimeID 
                                              ROWS BETWEEN 5 PRECEDING
                                              AND 1 PRECEDING)  AS [xy_bar]
             , AVG(1.0*TimeID*TimeID) OVER(PARTITION BY UserID
                                              ORDER BY TimeID 
                                              ROWS BETWEEN 5 PRECEDING
                                              AND 1 PRECEDING)  AS [x2_bar]
INTO #tmp_LinearTrendAmount
FROM MyTransTable

После выполнения этого select-а в нашем распоряжении будут все необходимые составляющие для расчета коэффициента наклона. Ещё одним select-м можно собрать их в нужный показатель, делая необходимые проверки (например, деление на ноль, которое может возникнуть, если у пользователя только одна запись). В указанном примере наклон рассчитывается по 5-ти записям, предшествующим данной (в коде используем ...AND 1 PRECEDING). Дело в том, что поле Target относится к моменту времени из своей строки, так что брать записи вплоть до неё было бы "заглядыванием в будущее".

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

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

Замечание 2. Теперь должно быть ясно, почему важно было корректно посчитать индексацию времени TimeID. Потому что это наша независимая переменная и она должна быть правильно рассчитана.

Замечание 3. Дьявол кроется в мелочах. Например, если брать фиксированное число значений времени, то тренды могут рассчитываться по разным временным промежуткам — для одного пользователя 5 значений времени покроет 1 неделю, для другого — 1 месяц. Разумеется, логика должна позволять такое использовать. Если же, например, для каждого пользователя мы храним потребление для каждого из предыдущих 3 месяцев, то указанная проблема пропадает.

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