пятница, 27 марта 2020 г.

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

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

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

Для начала, чтобы все работало, надо установить американскую локаль. Иначае часть алгоритмов будут просто валиться с 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 преобразования
* Обучать модель
* Предсказывать на основании готовой модели
* Строить графики
* Оценивать качество модели и другое

Комментариев нет:

Отправить комментарий