Что делать, если Python не подходит. Код включен.

Если вы изучали науку о данных, вероятно, вы знаете, как выполнять задачи машинного обучения на таких языках, как Python, R и Julia. Но что вы можете сделать, когда скорость является ключевым фактором, оборудование ограничено или компания, в которой вы работаете, рассматривает SQL как единственный вариант для прогнозной аналитики? Машинное обучение в базе данных - вот ответ.

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

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

Мы будем использовать Oracle Machine Learning (OML) для обучения модели классификации на хорошо известном наборе данных Iris. Я выбрал его, потому что он не требует никакой подготовки - нам нужно только создать таблицу и вставить данные.

Давай сделаем это дальше.

Подготовка данных

Как уже упоминалось, нам нужно создать таблицу для хранения набора данных Iris, а затем нам нужно загрузить в нее данные. OML требует, чтобы один столбец использовался в качестве идентификатора строки (последовательности), поэтому давайте это запомним:

CREATE SEQUENCE seq_iris;
CREATE TABLE iris_data(
    iris_id      NUMBER DEFAULT seq_iris.NEXTVAL,
    sepal_length NUMBER,
    sepal_width  NUMBER,
    petal_length NUMBER,
    petal_width  NUMBER,
    species      VARCHAR2(16)
);

Потрясающие! Теперь мы можем скачать данные и загрузить их:

Когда появится модальное окно, просто укажите путь к загруженному CSV и несколько раз нажмите Далее. Разработчик SQL должен исправить ситуацию без вашей помощи.

После этого у нас есть загруженный и подготовленный набор данных:

А теперь давайте продолжим самое интересное.

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

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

Разделение на тренировку / тест

Oracle нравится, что этот шаг выполняется с двумя представлениями - одним для данных обучения и одним для данных тестирования. Мы можем легко создать их с помощью магии PL / SQL:

BEGIN
    EXECUTE IMMEDIATE 
        ‘CREATE OR REPLACE VIEW 
        iris_train_data AS 
        SELECT * FROM iris_data 
        SAMPLE (75) SEED (42)’;
    EXECUTE IMMEDIATE 
        ‘CREATE OR REPLACE VIEW 
        iris_test_data AS 
        SELECT * FROM iris_data 
        MINUS 
        SELECT * FROM iris_train_data’;
END;
/

Этот сценарий выполняет две функции:

  1. Создает представление поезда - 75% данных (SAMPLE (75)) разделены на случайное начальное число 42 (SEED (42))
  2. Создает тестовое представление - как различие всего набора данных и обучающего представления.

Наши данные хранятся в представлениях с именами iris_train_data и iris_test_data - вы догадываетесь, какое из них что содержит.

Давайте быстро проверим, сколько строк в каждой:

SELECT COUNT(*) FROM iris_train_data;
>>> 111

SELECT COUNT(*) FROM iris_test_data;
>>> 39

Мы готовы обучать модель, так что займемся этим дальше.

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

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

Мы будем использовать алгоритм Decision Tree для обучения нашей модели. Вот как:

DECLARE 
    v_setlst DBMS_DATA_MINING.SETTING_LIST;
BEGIN
    v_setlst(‘PREP_AUTO’) := ‘ON’;
    v_setlst(‘ALGO_NAME’) := ‘ALGO_DECISION_TREE’;
 
    DBMS_DATA_MINING.CREATE_MODEL2(
        ‘iris_clf_model’,
        ‘CLASSIFICATION’,
        ‘SELECT * FROM iris_train_data’,
        v_setlst,
        ‘iris_id’,
        ‘species’
    );
END;
/

Процедура CREATE_MODEL2 (любопытно, почему она не была названа CREATE_MODEL_FINAL_FINAL89) принимает множество параметров. Поясним те, которые мы ввели:

  • iris_clf_model - просто название вашей модели. Может быть что угодно
  • CLASSIFICATION - тип задачи машинного обучения, которую мы выполняем. По какой-то причине должен быть прописным
  • SELECT * FROM iris_train_data - указывает, где хранятся данные обучения
  • v_setlst - выше заявленный список настроек для нашей модели
  • iris_id - имя столбца типа последовательности (каждое значение уникально)
  • species - имя целевой переменной (что мы пытаемся предсказать)

Выполнение этого блока займет секунду или две, но как только он будет выполнен, он будет готов для оценки!

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

Давайте воспользуемся этим скриптом для оценки нашей модели:

BEGIN
    DBMS_DATA_MINING.APPLY(
        ‘iris_clf_model’, 
        ‘iris_test_data’, 
        ‘iris_id’, 
        ‘iris_apply_result’
    );
END;
/

Он применяет iris_clf_model к невидимым тестовым данным iris_test_data и сохраняет результаты оценки в iris_apply_result таблице. Вот как выглядит эта таблица:

В нем намного больше строк (39 x 3), но вы поняли суть. Это все еще не самая простая задача, поэтому давайте покажем результаты немного по-другому:

DECLARE 
     CURSOR iris_ids IS 
         SELECT DISTINCT(iris_id) iris_id 
         FROM iris_apply_result 
         ORDER BY iris_id;
     curr_y      VARCHAR2(16);
     curr_yhat   VARCHAR2(16);
     num_correct INTEGER := 0;
     num_total   INTEGER := 0;
BEGIN
    FOR r_id IN iris_ids LOOP
        BEGIN
            EXECUTE IMMEDIATE 
                ‘SELECT species FROM 
                iris_test_data 
                WHERE iris_id = ‘ || r_id.iris_id
                INTO curr_y;
            EXECUTE IMMEDIATE 
                ‘SELECT prediction 
                FROM iris_apply_result 
                WHERE iris_id = ‘ || r_id.iris_id || 
               ‘AND probability = (
                   SELECT MAX(probability) 
                       FROM iris_apply_result 
                       WHERE iris_id = ‘ || r_id.iris_id || 
                   ‘)’ INTO curr_yhat;
        END;
 
        num_total := num_total + 1;
        IF curr_y = curr_yhat THEN 
            num_correct := num_correct + 1;
        END IF; 
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE(‘Num. test cases: ‘ 
        || num_total);
    DBMS_OUTPUT.PUT_LINE(‘Num. correct : ‘ 
        || num_correct);
    DBMS_OUTPUT.PUT_LINE(‘Accuracy : ‘ 
        || ROUND((num_correct / num_total), 2));
END;
/

Да, это много, но приведенный выше сценарий не может быть проще. Давайте разберемся:

  • CURSOR - получает все отдельные iris_ids (потому что мы дублировали их в iris_apply_results таблице
  • curr_y, curr_yhat, num_correct, num_total - переменные для хранения фактических и прогнозируемых видов на каждой итерации, количества правильных классификаций и общего количества тестовых заданий.
  • Для каждого уникального iris_id мы получаем фактический вид (из iris_test_data, где идентификаторы совпадают) и прогнозируемый вид (где вероятность предсказания самая высокая в iris_apply_results таблице)
  • Затем легко проверить, идентичны ли фактические и прогнозируемые значения, что указывает на правильность классификации.
  • Переменные num_total и num_correct обновляются на каждой итерации.
  • Наконец, мы выводим данные модели на консоль.

Вот результат этого скрипта:

Потрясающие! Интерпретировать:

  • В наборе 39 кейсов.
  • Из этих 39 37 были правильно классифицированы
  • В результате достигается точность 95%.

И это почти все, что касается оценки модели.

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

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

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

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

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

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