Oracle DataMiner ML в сравнении с Python sklearn

В этой статье хочу посмотреть на ML опцию, встроенную в Oracle 12.

• Подготовка данных
• Регрессия
• Классификация
• Визуальный дизайнер Sql Developer

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

Для начала, чтобы все работало, надо установить американскую локаль. Иначае часть алгоритмов будут просто валиться с exception.

ALTER SESSION SET 
    NLS_LANGUAGE= 'AMERICAN' 
    NLS_TERRITORY= 'AMERICA' 
    NLS_CURRENCY= '$' 
    NLS_ISO_CURRENCY= 'AMERICA' 
    NLS_NUMERIC_CHARACTERS= '.,' 
    NLS_CALENDAR= 'GREGORIAN' 
    NLS_DATE_FORMAT= 'DD-MON-RR' 
    NLS_DATE_LANGUAGE= 'AMERICAN' 
    NLS_SORT= 'BINARY'
;

Будем предсказывать timeseries данные с количеством продаж в разрезе категории и дня:

select catid, calday, qnt from pos_sales_agg where rownum <= 5;
/*
180101 20191205 55.454
180802 20191205 36.728
150101 20191205 60.668
180101 20200204 18.421
180101 20190824 23.906
*/

Обогатим наши данными фичами:
* week_day - номер дня в неделей
* qnt_week_1, qnt_week_2, qnt_week_3, qnt_week_4 - количество продаж 1-4 недель назад в этот день
* week_num - номер недели для разделения данных на тестовые и тренировочные
* case_id_column - уникальный идентификатор строки в рамках одной недели
* qnt - колонка с кол-вом, которую будет предсказывать в регрессии
* qnt_cat - колонка с категорией кол-ва, которую будем предсказывать в классификации

create or replace view POS_SALES_V as
select week_day || '_' || catid as case_id_column, week_day, week_num, to_number(catid) as catid, 
qnt, qnt_week_1, qnt_week_2, qnt_week_3, qnt_week_4,
case when qnt <5 then '<5' 
    when qnt >= 5 and qnt < 15 then '[5-15)'
    when qnt >= 15 and qnt < 40  then '[15-40)'
    when qnt >= 40 and qnt < 100  then '[40-100)'
    else '>100' 
end as qnt_cat 
from (
    select calday, 
    to_date(calday,'yyyymmdd') - trunc(to_date(calday,'yyyymmdd'),'IW') + 1 as week_day, 
    to_number(to_char(to_date(calday,'yyyymmdd'),'IW')) as week_num,
    catid, qnt, 
    LAG(qnt, 7) OVER(PARTITION BY catid ORDER BY calday) as qnt_week_1,
    LAG(qnt, 14) OVER(PARTITION BY catid ORDER BY calday) as qnt_week_2,
    LAG(qnt, 21) OVER(PARTITION BY catid ORDER BY calday) as qnt_week_3,
    LAG(qnt, 28) OVER(PARTITION BY catid ORDER BY calday) as qnt_week_4
    from pos_sales_agg
)
;

Внешний вид данных:

select CASE_ID_COLUMN, WEEK_DAY, WEEK_NUM, CATID, QNT, QNT_CAT, QNT_WEEK_1, QNT_WEEK_2, QNT_WEEK_3, QNT_WEEK_4 
from POS_SALES_V WHERE week_num = 48 and rownum <= 5;
/*
1_150101 1 48 150101 45.162 [40-100) 89.976 14.356 50.688 68.708
2_150101 2 48 150101 77.27 [40-100) 96.846 117.396 45.468 36.544
3_150101 3 48 150101 65.781 [40-100) 45.754 110.03 12.22 66.73
4_150101 4 48 150101 92.617 [40-100) 3.486 87.812 51.307 49.686
5_150101 5 48 150101 103.159 >100         0.188 77.142 91.822 42.245
*/

Сформируем тренировочный набор данных на основании 48 недели и тестовый на 49 недели.
Из тестового набора исключим колонку с количеством (QNT) и вынесем ее в отдельную таблицу валидации.

Oracle drop table pos_sales_train purge; create table pos_sales_train as select CASE_ID_COLUMN, WEEK_DAY, CATID, QNT, QNT_WEEK_1, QNT_WEEK_2, QNT_WEEK_3, QNT_WEEK_4 from POS_SALES_V WHERE week_num = 48 ; drop table pos_sales_test purge; create table pos_sales_test as select CASE_ID_COLUMN, WEEK_DAY, CATID, /*QNT,*/ QNT_WEEK_1, QNT_WEEK_2, QNT_WEEK_3, QNT_WEEK_4 from POS_SALES_V WHERE week_num = 49 ; drop table pos_sales_val purge; create table pos_sales_val as select case_id_column, qnt from POS_SALES_V WHERE week_num = 49 ;

Python import pandas as pd import numpy as np qnt_data = pd.read_csv('export.csv', ';') train = qnt_data[qnt_data['WEEK_NUM'] == 48] test = qnt_data[qnt_data['WEEK_NUM'] == 49] train = train.drop(['WEEK_NUM', 'CASE_ID_COLUMN', 'QNT_CAT'], axis=1) test = test.drop(['WEEK_NUM', 'CASE_ID_COLUMN', 'QNT_CAT'], axis=1) xtr = train.drop(['QNT'], axis=1) xts = test.drop(['QNT'], axis=1) ytr = train['QNT'].values yts = test['QNT'].values

Регрессия

Список доступных алгоритмов ML можно посмотреть в документации Oracle 12.
К сожалению, в нем нет регрессии на решающих деревьях, так что воспользуется алгоритмом "Generalized Linear Model" и сравним с регрессором на решающих деревьях в Python.

Oracle Минимальная необходимая настройка - название алгоритма ML: drop table SETTINGS_GLM purge; CREATE TABLE SETTINGS_GLM ( SETTING_NAME VARCHAR2 (200), SETTING_VALUE VARCHAR2 (200) ); INSERT INTO SETTINGS_GLM (setting_name, setting_value) VALUES ('ALGO_NAME', 'ALGO_GENERALIZED_LINEAR_MODEL'); commit; После этого можно сразу проводить обучение: * mining_function = DBMS_DATA_MINING.REGRESSION - регрессия * data_table_name = POS_SALES_TRAIN - тренировочный датасет * case_id_column_name = CASE_ID_COLUMN - колонка с уникальным идентификатором для ускорения расчета (можно не указывать, если нет) * target_column_name = QNT - колонка, которую предсказываем * settings_table_name = SETTINGS_GLM - таблица с настройками алгоритма BEGIN DBMS_DATA_MINING.CREATE_MODEL( model_name => 'MD_GLM_CAT_SALES_1', mining_function => DBMS_DATA_MINING.REGRESSION, data_table_name => 'POS_SALES_TRAIN', case_id_column_name => 'CASE_ID_COLUMN', target_column_name => 'QNT', settings_table_name => 'SETTINGS_GLM'); commit; END; /

Python База для сравнения алгоритмов - предсказание = кол-ву на прошлой неделе from sklearn.metrics import mean_squared_error from sklearn.metrics import mean_squared_log_error def rmsle(ytrue, ypred): return np.sqrt(mean_squared_log_error(ytrue, ypred)) mean_squared_error(train['QNT'].values, train['QNT_WEEK_1'].values) rmsle(train['QNT'].values, train['QNT_WEEK_1'].values) Регрессия на основании Random forest from sklearn.ensemble import RandomForestRegressor mdl = RandomForestRegressor(n_estimators=1000, n_jobs=-1, random_state=0) mdl.fit(xtr, ytr) Регрессия на основании GradientBoosting Tree + перебор гиперпараметров модели через GridSearchCV #GradientBoosting from sklearn.ensemble import GradientBoostingRegressor from sklearn.model_selection import GridSearchCV parameters = {'n_estimators': [10, 15, 20, 30, 50,100, 1000], 'learning_rate': [0.005, 0.1,0.5,1], 'max_depth': [2, 3,5,7,10]} clf_rf = GradientBoostingRegressor() grid_search_cv_clf = GridSearchCV(clf_rf, parameters, cv=5, n_jobs=-1) grid_search_cv_clf.fit(xtr, ytr) best_clf = grid_search_cv_clf.best_estimator_ grid_search_cv_clf.best_params_ # === {'learning_rate': 0.1, 'max_depth': 2, 'n_estimators': 100}

Оценка качества моделей:

Oracle Настройки модели фиксируются на момент запуска DBMS_DATA_MINING.CREATE_MODEL, по этому нет возможности в автоматическом режиме перебрать гиперпараметры для выбора лучших настроек --заголовк модели: алгоритм, функция, дата создания и т.д. SELECT * FROM ALL_MINING_MODELS WHERE MODEL_NAME = 'MD_GLM_CAT_SALES_1'; /* DM MD_GLM_CAT_SALES_1 REGRESSION GENERALIZED_LINEAR_MODEL 26-MAR-20 3 0.0793 */ --Все итоговые настройки модели SELECT * FROM ALL_MINING_MODEL_SETTINGS WHERE MODEL_NAME = 'MD_GLM_CAT_SALES_1'; /* ALGO_NAME ALGO_GENERALIZED_LINEAR_MODEL PREP_AUTO OFF GLMS_RIDGE_REGRESSION GLMS_RIDGE_REG_DISABLE GLMS_CONF_LEVEL .95 GLMS_FTR_SELECTION GLMS_FTR_SELECTION_DISABLE ODMS_MISSING_VALUE_TREATMENT ODMS_MISSING_VALUE_MEAN_MODE */ --приоритет фич SELECT * FROM TABLE (DBMS_DATA_MINING.GET_MODEL_DETAILS_GLM ('MD_GLM_CAT_SALES_1')); /* CATID 0.000112648586765367 QNT_WEEK_1 0.089835779725901302 QNT_WEEK_2 0.50652402837812205 QNT_WEEK_3 0.197770451982554 QNT_WEEK_4 0.177517140328708 WEEK_DAY 0.794269308757236 */ Выполнение запроса для предсказания количества на тестовом наборе данных + сверка на валидационной таблице SELECT t.week_day, t.catid, ROUND( PREDICTION (MD_GLM_CAT_SALES_1 USING *), 3) MODEL_PREDICT_RESPONSE, v.qnt FROM POS_SALES_TEST t join POS_SALES_VAL v on v.CASE_ID_COLUMN = t.CASE_ID_COLUMN where rownum <= 5; /* 1 150101 63.172 46.456 2 150101 89.783 78.864 3 150101 56.306 180.711 4 150101 40.646 60.668 5 150101 45.799 58.541 */ Суммарно модель показывает квадратичную ошибку (mse) и квадрат-логарифмическую ошибку (msle) на уровне Random Forest в sklearn select SUM( POWER( ln(1+qnt)-ln(1+MODEL_PREDICT_RESPONSE), 2) )/COUNT(*) as msle, SUM( POWER(qnt - MODEL_PREDICT_RESPONSE,2)) /COUNT(*) as mse from ( SELECT t.CASE_ID_COLUMN, t.week_day, t.catid, PREDICTION (MD_GLM_CAT_SALES_1 USING *) MODEL_PREDICT_RESPONSE, PREDICTION_DETAILS (MD_GLM_CAT_SALES_1 USING *) DETAILS, v.qnt FROM POS_SALES_TEST t join POS_SALES_VAL v on v.CASE_ID_COLUMN = t.CASE_ID_COLUMN ) vv ; /* msle = 0.58238 mse = 1606.6146 */ Также через "PREDICTION_DETAILS" можно узнать для каждой строки ее приоритеты и коэффициенты фич: <Details algorithm="Generalized Linear Model"> <Attribute name="QNT_WEEK_1" actualValue="103.159" weight=".172" rank="1"/> <Attribute name="DIFF_WEEK_2" actualValue="58.353" weight=".155" rank="2"/> <Attribute name="QNT_WEEK_4" actualValue="91.822" weight=".144" rank="3"/> <Attribute name="QNT_WEEK_3" actualValue="77.142" weight=".096" rank="4"/> <Attribute name="WEEK_DAY" actualValue="5" weight="0" rank="5"/> </Details>

Python: Удивительно, но базовый алгоритм предсказания, когда кол-во = кол-ву на прошлой неделе, показывает наименьшую ошибку mean_squared_error. Это, скорей всего, говорит о малом кол-во информативных фич в тренировочном наборе print( mean_squared_error(train['QNT'].values, train['QNT_WEEK_1'].values) ) #1397.572380302139 print( rmsle(train['QNT'].values, train['QNT_WEEK_1'].values) ) #0.8120582052565185 Показатели ошибок для Random Forest print( mean_squared_error(yts, mdl.predict(xts)) ) ##1661.4045307175795 print( rmsle(yts, mdl.predict(xts)) ) ### 0.6373131723019242 Ошибки для GradientBoosting Tree + перебор гиперпараметров модели через GridSearchCV ps = grid_search_cv_clf.predict(xts) ps [ ps < 0 ] = 0 print( mean_squared_error(yts, ps) ) #1712.295220721582 print( rmsle(yts, ps ) ) #0.6574064004041114 Приоритет фич при GradientBoosting pd.DataFrame({'features': xtr.columns, 'feature_importances': best_clf.feature_importances_}).sort_values('feature_importances', ascending=False) # feature_importances features #3 0.415996 QNT_WEEK_2 #4 0.323615 QNT_WEEK_3 #5 0.121125 QNT_WEEK_4 #2 0.110190 QNT_WEEK_1 #1 0.026966 CATID #0 0.002108 WEEK_DAY

Классификация

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

Oracle Подготовим такие же датасеты как в регрессии, но будем предсказывать не кол-во QNT, а 1 из 5 классов QNT_CAT drop table pos_sales_cat_train purge; create table pos_sales_cat_train as select CASE_ID_COLUMN, WEEK_DAY, CATID, QNT_CAT, QNT_WEEK_1, QNT_WEEK_2, QNT_WEEK_3, QNT_WEEK_4 from POS_SALES_V WHERE week_num = 48 ; drop table pos_sales_cat_test purge; create table pos_sales_cat_test as select CASE_ID_COLUMN, WEEK_DAY, CATID, /*QNT_CAT,*/ QNT_WEEK_1, QNT_WEEK_2, QNT_WEEK_3, QNT_WEEK_4 from POS_SALES_V WHERE week_num = 49 ; drop table pos_sales_cat_val purge; create table pos_sales_cat_val as select case_id_column, QNT_CAT from POS_SALES_V WHERE week_num = 49 ;

Python train = qnt_data[qnt_data['WEEK_NUM'] == 48] test = qnt_data[qnt_data['WEEK_NUM'] == 49] train = train.drop(['WEEK_NUM', 'CASE_ID_COLUMN', 'QNT'], axis=1) test = test.drop(['WEEK_NUM', 'CASE_ID_COLUMN', 'QNT'], axis=1) xtr = train.drop(['QNT_CAT'], axis=1) xts = test.drop(['QNT_CAT'], axis=1) ytr = train['QNT_CAT'].values yts = test['QNT_CAT'].values

Внешний вид данных.
5 классов в данных распределены примерно поровну

select * from pos_sales_cat_train where rownum <=5;
/*
1_150101 1 150101 [40-100) 89.976 14.356 50.688 68.708
2_150101 2 150101 [40-100) 96.846 117.396 45.468 36.544
3_150101 3 150101 [40-100) 45.754 110.03 12.22 66.73
4_150101 4 150101 [40-100) 3.486 87.812 51.307 49.686
5_150101 5 150101 >100         0.188 77.142 91.822 42.245
*/

select QNT_CAT, ROUND(COUNT(*)/SUM(COUNT(*)) OVER()*100,2) as prc 
from pos_sales_cat_train
group by QNT_CAT
order by QNT_CAT
;
/*
<5     18.72
>100     15.51
[15-40)     25.4
[40-100)    17.65
[5-15)     22.73
*/

Произведем обучение

Oracle Для Oracle укажем только название алгоритма - решающие деревья drop table SETTINGS_CLF purge; CREATE TABLE SETTINGS_CLF ( SETTING_NAME VARCHAR2 (200), SETTING_VALUE VARCHAR2 (200) ); INSERT INTO SETTINGS_CLF (setting_name, setting_value) VALUES ('ALGO_NAME', 'ALGO_DECISION_TREE'); commit; Настройки модели аналогичны регрессии BEGIN DBMS_DATA_MINING.CREATE_MODEL( model_name => 'MD_CLSF_CAT_SALES_1', mining_function => DBMS_DATA_MINING.CLASSIFICATION, data_table_name => 'pos_sales_cat_train', case_id_column_name => 'CASE_ID_COLUMN', target_column_name => 'QNT_CAT', settings_table_name => 'SETTINGS_CLF'); commit; END; /

Python В python будет Random Forest и градиентный бустинг на решающих деревьях с перебором гиперпараметров #random forest from sklearn.ensemble import RandomForestClassifier mdl = RandomForestClassifier(n_estimators=1000, n_jobs=-1, random_state=0) mdl.fit(xtr, ytr) #GradientBoosting from sklearn.ensemble import GradientBoostingClassifier from sklearn.model_selection import GridSearchCV parameters = {'n_estimators': [10, 15, 20, 30, 50,100, 1000], 'learning_rate': [0.005, 0.1,0.5,1], 'max_depth': [2, 3,5,7,10]} clf_rf = GradientBoostingClassifier() grid_search_cv_clf = GridSearchCV(clf_rf, parameters, cv=5, n_jobs=-1) grid_search_cv_clf.fit(xtr, ytr) best_clf = grid_search_cv_clf.best_estimator_ grid_search_cv_clf.best_params_ # {'learning_rate': 0.1, 'max_depth': 2, 'n_estimators': 15}

Посмотрим результаты обучения:

Oracle Итоговые настройки модели решающих деревьев в Oracle имеют схожие параметры настройки с Python. Причем это простой Random Forest. Градиентный бустинг появился в Oracle с 20 версии Также тут нет возможности перебрать гиперпараметры, они фиксируются на момент обучения SELECT * FROM ALL_MINING_MODELS WHERE MODEL_NAME = 'MD_CLSF_CAT_SALES_1'; /* DM MD_CLSF_CAT_SALES_1 CLASSIFICATION DECISION_TREE 26-MAR-20 0.9999999999999999999999999999999999999996 0.0641 */ --итоговые настройки модели SELECT * FROM ALL_MINING_MODEL_SETTINGS WHERE MODEL_NAME = 'MD_CLSF_CAT_SALES_1'; /* ALGO_NAME ALGO_DECISION_TREE PREP_AUTO OFF TREE_TERM_MINPCT_NODE .05 TREE_TERM_MINREC_SPLIT 20 TREE_IMPURITY_METRIC TREE_IMPURITY_GINI TREE_TERM_MINPCT_SPLIT .1 TREE_TERM_MAX_DEPTH 7 TREE_TERM_MINREC_NODE 10 */ Запрос для предсказания категории на основании тестовых данных: SELECT t.week_day, t.catid, PREDICTION (MD_CLSF_CAT_SALES_1 USING *) MODEL_PREDICT_RESPONSE, v.qnt_cat FROM POS_SALES_CAT_TEST t join POS_SALES_CAT_VAL v on v.CASE_ID_COLUMN = t.CASE_ID_COLUMN WHERE rownum <= 5 ; /* 1 150101 [40-100) [40-100) 2 150101 >100 [40-100) 3 150101 >100 >100 4 150101 [40-100) [40-100) 5 150101 [40-100) [40-100) */ Оценим качество модели как кол-во верных предсказаний в каждом классе и в общем. select MODEL_PREDICT_RESPONSE, good, all_rows, round(good/all_rows*100) as prc, round(sum(good) OVER()/sum(all_rows) OVER()*100) as prc_all --67% from ( select MODEL_PREDICT_RESPONSE, COUNT(case when qnt_cat = MODEL_PREDICT_RESPONSE then 1 end ) as good, COUNT(*) all_rows from ( SELECT PREDICTION (MD_CLSF_CAT_SALES_1 USING *) MODEL_PREDICT_RESPONSE, v.qnt_cat FROM POS_SALES_CAT_TEST t join POS_SALES_CAT_VAL v on v.CASE_ID_COLUMN = t.CASE_ID_COLUMN ) vv group by MODEL_PREDICT_RESPONSE ); /* [40-100) 33 78 42 49 >100 33 63 52 49 <5 58 130 45 49 [15-40) 62 107 58 49 */ В общем среднее предсказание оказалось = 49%, что чуть меньше, чем в sklearn, но и параметры запуска моделей были разные (см. grid_search_cv_clf.best_params_ и ALL_MINING_MODEL_SETTINGS) Стоит заметить, что случайное распределение дало бы 20% (100% / 5 категорий)

Python Python sklearn дает % предсказания больше 50% во всех способах: # Random Forest yts[yts == mdl.predict(xts)].shape[0] / yts.shape[0] #0.5555555555555556 #Gradient Boosting yts[yts == grid_search_cv_clf.predict(xts)].shape[0] / yts.shape[0] #0.5687830687830688 #Приоритет фич pd.DataFrame({'features': xtr.columns, 'feature_importances': best_clf.feature_importances_}).sort_values('feature_importances', ascending=False) # feature_importances features #2 0.361643 QNT_WEEK_1 #4 0.296994 QNT_WEEK_3 #3 0.154571 QNT_WEEK_2 #5 0.095998 QNT_WEEK_4 #1 0.083929 CATID #0 0.006865 WEEK_DAY

Визуальный дизайнер Sql Developer

Также все эти настройки можно сделать в удобном визуальном виде используя Oracle Sql developer через расширение DataMiner.
* Читать таблицы, просматривать их содержимое и распределение
* Делать ETL преобразования
* Обучать модель
* Предсказывать на основании готовой модели
* Строить графики
* Оценивать качество модели и другое