Да, теперь SQL может это сделать.

Прогнозирование временных рядов - это задача, которую я обычно выполняю с помощью Python. Возможно, вы привыкли к другим языкам, таким как R или Julia, но держу пари, что вам никогда не приходило в голову для такого типа задач. В таком случае - продолжайте читать - вы будете удивлены, сколько всего можно сделать с помощью только SQL.

Я ранее писал о выполнении задач классификации с помощью SQL, поэтому обязательно посмотрите его, если это вам интересно:



Машинное обучение с SQL - это проще, чем вы думаете
Что делать, если Python не подходит. Код включен. todatascience.com



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

Вот почему выполнение процесса обучения в базе данных может быть полезным, если аппаратные ресурсы ограничены. Python почти всегда потребляет больше ресурсов, чем база данных.

Мы снова воспользуемся Oracle Cloud. Это бесплатно, поэтому зарегистрируйтесь и создайте экземпляр базы данных OLTP (версия 19c, имеет хранилище 0,2 ТБ). После этого загрузите облачный кошелек и установите соединение с помощью SQL Developer или любого другого инструмента.

Это займет у вас как минимум 10 минут, но это довольно простая вещь, поэтому я не буду тратить на это время.

Потрясающие! Продолжим загрузку данных.

Загрузка данных

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

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

CREATE TABLE airline_passengers(
    air_period DATE,
    air_passengers INTEGER
);

Теперь мы можем загрузить набор данных с помощью функции Импорт данных:

Когда появится модальное окно, просто укажите путь к загруженному CSV и несколько раз нажмите Далее. Выбирайте столбцы по своему усмотрению, а формат даты - YYYY-MM.

После этого наш набор данных готов к использованию:

Потрясающие! Теперь мы можем продолжить обучение и прогнозирование модели.

Модельное обучение

В нашем наборе данных 144 строки. Мы не будем проводить обучение модели полностью. Мы оставим последние 12 строк для оценки.

Чтобы начать обучение, нам нужно создать VIEW, который указывает на данные обучения. Вот как:

CREATE OR REPLACE VIEW src_passengers AS
    SELECT * FROM airline_passengers 
    WHERE air_period < TO_DATE('1960-01-01', 'YYYY-MM-DD');

Представление src_passengers теперь содержит первые 132 строки - именно то, что нам нужно.

Затем мы объявим короткий PL/SQL фрагмент, который обрабатывает обучение модели:

DECLARE
    v_setlst DBMS_DATA_MINING.SETTING_LIST;
BEGIN
    v_setlst(DBMS_DATA_MINING.ALGO_NAME) := DBMS_DATA_MINING.ALGO_EXPONENTIAL_SMOOTHING;
    v_setlst(DBMS_DATA_MINING.EXSM_INTERVAL) := DBMS_DATA_MINING.EXSM_INTERVAL_MONTH;
    v_setlst(DBMS_DATA_MINING.EXSM_PREDICTION_STEP) := '12';
    v_setlst(DBMS_DATA_MINING.EXSM_MODEL) := DBMS_DATA_MINING.EXSM_HW;
    v_setlst(DBMS_DATA_MINING.EXSM_SEASONALITY) := '12';
 
    DBMS_DATA_MINING.CREATE_MODEL2(
        model_name => 'AIRLINE_PSG_FORECAST',
        mining_function => 'TIME_SERIES',
        data_query => 'SELECT * FROM src_passengers',
        set_list => v_setlst,
        case_id_column_name => 'air_period',
        target_column_name => 'air_passengers'
    );
END;
/

Давайте разберем фрагмент, чтобы его было легче понять:

  • DBMS_DATA_MINING.ALGO_NAME - тип алгоритмов прогнозирования временных рядов, экспоненциальное сглаживание - единственный доступный в настоящее время
  • DBMS_DATA_MINING.EXSM_INTERVAL - указывает интервал набора данных. Наши данные хранятся с интервалом в месяц, поэтому значение EXSM_INTERVAL_MONTH
  • DBMS_DATA_MINING.PREDICTION_STEP - сколько прогнозов сделать. 12 (один год) - это хорошо
  • DBMS_DATA_MINING.EXSM_MODEL - по сути, комбинация гиперпараметров для модели экспоненциального сглаживания. Я решил использовать тройное экспоненциальное сглаживание или Холт-Винтерс. Вот полный список доступных алгоритмов.
  • DBMS_DATA_MINING.EXSM_SEASONALITY - указывает, сколько длится один сезон

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

  • model_name - произвольно, назовите модель как хотите
  • mining_function - установлен на TIME_SERIES, понятно почему
  • data_query - как модель может получить данные обучения
  • set_list - список настроек, объявленных ранее, сообщает Oracle, как на самом деле обучать модель
  • case_id_column_name - имя столбца, содержащего значения даты
  • target_column_name - имя столбца, содержащего числовые значения (что мы пытаемся предсказать)

Вот и все! Если вы это понимаете, вы знаете, как обучать модели временных рядов с помощью SQL.

Теперь вы можете запустить фрагмент PL/SQL. На это уйдет пара секунд. После этого вы можете переходить к следующему разделу.

Оценка модели

Посмотрим, насколько хороша наша модель. Я подготовил для этого следующее заявление:

SELECT
    a.case_id AS time_period,
    b.air_passengers AS actual,
    ROUND(a.prediction, 2) AS predicted,
    ROUND((b.air_passengers - a.prediction), 2) AS difference,
    ROUND((ABS(b.air_passengers - a.prediction) / b.air_passengers) * 100, 2) AS pct_error
FROM 
    dm$p0airline_psg_forecast a, 
    airline_passengers b
WHERE 
    a.case_id = b.air_period
AND a.case_id >= TO_DATE('1960-01-01', 'YYYY-MM-DD');

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

Потрясающие! Наша модель не так уж плоха для проделанной работы. Давайте подведем итоги в следующем разделе.

Прежде чем ты уйдешь

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

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

Спасибо за прочтение.

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

Первоначально опубликовано на https://www.betterdatascience.com 22 сентября 2020 г.