# XGBoost和LightGBM模型转sql语句工具包
现在是大数据量的时代，我们开发的模型要应用在特别大的待预测集上，使用单机的python，需要预测2、3天，甚至更久，中途很有可能中断。因此需要通过分布式的方式来预测。这个工具包就是实现了将训练好的python模型，转换成sql语句。将生成的sql语句可以放到大数据环境中进行分布式执行预测，能比单机的python预测快好几个量级
## 思想碰撞
| 微信 | 微信公众号 |
| :---: | :----: |
| <img src="https://github.com/ZhengRyan/autotreemodel/blob/master/images/%E5%B9%B2%E9%A5%AD%E4%BA%BA.png" alt="RyanZheng.png" width="50%" border=0/> | <img src="https://github.com/ZhengRyan/autotreemodel/blob/master/images/%E9%AD%94%E9%83%BD%E6%95%B0%E6%8D%AE%E5%B9%B2%E9%A5%AD%E4%BA%BA.png" alt="魔都数据干饭人.png" width="50%" border=0/> |
| 干饭人 | 魔都数据干饭人 |
> 仓库地址：https://github.com/ZhengRyan/treemodel2sql
>
> 微信公众号文章：https://mp.weixin.qq.com/s/u8Nsp5M93WIGL2M0tU4U_g
>
> pipy包：https://pypi.org/project/treemodel2sql/
## 环境准备
可以不用单独创建虚拟环境，因为对包的依赖没有版本要求
### `treemodel2sql` 安装
pip install（pip安装）
```bash
pip install treemodel2sql # to install
pip install -U treemodel2sql # to upgrade
```
Source code install（源码安装）
```bash
python setup.py install
```
## 运行样例
###【注意：：：核验对比python模型预测出来的结果和sql语句预测出来的结果是否一致请查看教程代码】"https://github.com/ZhengRyan/treemodel2sql/tree/master/examples"路径处的tutorial_code_lightgbm_model_transform_sql.ipynb和tutorial_code_xgboost_model_transform_sql.ipynb
## xgboost model transform sql
+ 导入相关依赖
```python
import xgboost as xgb
from sklearn.datasets import make_classification
from sklearn.model_selection import train_test_split
```
+ 训练1个xgboost二分类模型
```python
X, y = make_classification(n_samples=10000,
n_features=10,
n_informative=3,
n_redundant=2,
n_repeated=0,
n_classes=2,
weights=[0.7, 0.3],
flip_y=0.1,
random_state=1024)
X_train, X_test, y_train, y_test = train_test_split(X, y, random_state=1024)
###训练模型
model = xgb.XGBClassifier(n_estimators=3)
model.fit(X_train, y_train)
#xgb.to_graphviz(model)
```
+ 使用treemodel2sql工具包将模型转换成的sql语句
```python
from treemodel2sql import XGBoost2Sql
xgb2sql = XGBoost2Sql()
sql_str = xgb2sql.transform(model)
```
+ 将sql语句保存
```python
xgb2sql.save()
```
+ 将sql语句打印出来
```python
print(sql_str)
```
```sql
select key,1 / (1 + exp(-(tree_1_score + tree_2_score + tree_3_score)+(-0.0))) as score
from (
select key,
--tree1
case when (f9<-1.64164519 or f9 is null) then
case when (f3<-4.19117069 or f3 is null) then
case when (f2<-1.31743848 or f2 is null) then
-0.150000006
else
-0.544186056
end
else
case when (f3<1.23432565 or f3 is null) then
case when (f7<-2.55682254 or f7 is null) then
-0.200000018
else
case when (f5<0.154983491 or f5 is null) then
0.544721723
else
case when (f3<0.697217584 or f3 is null) then
-0.150000006
else
0.333333373
end
end
end
else
case when (f5<-1.0218116 or f5 is null) then
case when (f0<-0.60882163 or f0 is null) then
case when (f2<0.26019755 or f2 is null) then
0.0666666701
else
-0.300000012
end
else
-0.520000041
end
else
0.333333373
end
end
end
else
case when (f9<1.60392439 or f9 is null) then
case when (f3<0.572542191 or f3 is null) then
case when (f5<0.653370142 or f5 is null) then
case when (f7<-0.765973091 or f7 is null) then
case when (f3<-0.432390809 or f3 is null) then
0.204000011
else
-0.485454559
end
else
case when (f3<-1.20459461 or f3 is null) then
-0.5104478
else
0.441509455
end
end
else
case when (f7<0.133017987 or f7 is null) then
case when (f8<0.320554674 or f8 is null) then
-0.290322572
else
0.368339777
end
else
case when (f8<-0.211985052 or f8 is null) then
0.504000008
else
-0.525648415
end
end
end
else
case when (f7<2.22314501 or f7 is null) then
case when (f8<-0.00532855326 or f8 is null) then
case when (f8<-0.204920739 or f8 is null) then
-0.533991575
else
-0.200000018
end
else
0.428571463
end
else
case when (f3<1.33772755 or f3 is null) then
case when (f0<-0.975171864 or f0 is null) then
0.163636371
else
0.51818186
end
else
-0
end
end
end
else
case when (f3<1.77943277 or f3 is null) then
case when (f7<-0.469875157 or f7 is null) then
case when (f3<-0.536645889 or f3 is null) then
case when (f9<1.89841866 or f9 is null) then
-0
else
0.333333373
end
else
case when (f4<-2.43660188 or f4 is null) then
0.150000006
else
0.551020443
end
end
else
case when (f1<-0.0788691565 or f1 is null) then
0.150000006
else
-0.375
end
end
else
case when (f4<-1.73232496 or f4 is null) then
-0.150000006
else
case when (f6<-1.6080606 or f6 is null) then
-0.150000006
else
case when (f7<-0.259483218 or f7 is null) then
-0.558620751
else
-0.300000012
end
end
end
end
end
end
as tree_1_score,
--tree2
case when (f9<-1.64164519 or f9 is null) then
case when (f3<-4.19117069 or f3 is null) then
case when (f0<0.942570388 or f0 is null) then
-0.432453066
else
-0.128291652
end
else
case when (f3<1.23432565 or f3 is null) then
case when (f7<-2.55682254 or f7 is null) then
-0.167702854
else
case when (f5<0.154983491 or f5 is null) then
case when (f1<2.19985676 or f1 is null) then
0.41752997
else
0.115944751
end
else
0.115584135
end
end
else
case when (f5<-1.0218116 or f5 is null) then
case when (f0<-0.60882163 or f0 is null) then
-0.119530827
else
-0.410788596
end
else
0.28256765
end
end
end
else
case when (f9<1.60392439 or f9 is null) then
case when (f3<0.460727394 or f3 is null) then
case when (f5<0.653370142 or f5 is null) then
case when (f7<-0.933565617 or f7 is null) then
case when (f3<-0.572475374 or f3 is null) then
0.182491601
else
-0.377898693
end
else
case when (f3<-1.20459461 or f3 is null) then
-0.392539263
else
0.352721155
end
end
else
case when (f7<0.207098693 or f7 is null) then
case when (f8<0.498489976 or f8 is null) then
-0.193351224
else
0.29298231
end
else
case when (f8<-0.117464997 or f8 is null) then
0.400667101
else
-0.402199954
end
end
end
else
case when (f7<1.98268723 or f7 is null) then
case when (f8<-0.00532855326 or f8 is null) then
case when (f7<1.36281848 or f7 is null) then
-0.408002198
else
-0.236123681
end
else
case when (f5<1.14038813 or f5 is null) then
0.404326111
else
-0.110877581
end
end
else
case when (f3<1.56952488 or f3 is null) then
case when (f5<2.14646816 or f5 is null) then
0.409404457
else
0.0696995854
end
else
-0.32059738
end
end
end
else
case when (f3<1.77943277 or f3 is null) then
case when (f7<-0.469875157 or f7 is null) then
case when (f3<-0.536645889 or f3 is null) then
case when (f9<1.89841866 or f9 is null) then
-0
else
0.28256765
end
else
0.419863999
end
else
case when (f3<0.444227457 or f3 is null) then
-0.34664312
else
0.0693304539
end
end
else
case when (f4<-1.10089087 or f4 is null) then
case when (f3<2.3550868 or f3 is null) then
0.0147894565
else
-0.331404865
end
else
-0.421277165
end
end
end
end
as tree_2_score,
--tree3
case when (f9<-1.64164519 or f9 is null) then
case when (f3<-4.19117069 or f3 is null) then
case when (f4<-1.30126143 or f4 is null) then
-0.0772174299
else
-0.374165356
end
else
case when (f3<1.23432565 or f3 is null) then
case when (f7<-2.55682254 or f7 is null) then
-0.142005175
else
case when (f5<0.154983491 or f5 is null) then
case when (f7<3.59379435 or f7 is null) then
0.352122813
else
0.132789165
end
else
0.0924336985
end
end
else
case when (f5<-1.0218116 or f5 is null) then
case when (f0<-0.60882163 or f0 is null) then
-0.0954768136
else
-0.351594836
end
else
0.245992288
end
end
end
else
case when (f9<1.60392439 or f9 is null) then
case when (f3<0.347133756 or f3 is null) then
case when (f5<0.661561131 or f5 is null) then
case when (f7<-0.933565617 or f7 is null) then
case when (f3<-0.472413659 or f3 is null) then
0.116336405
else
-0.313245147
end
else
case when (f3<-1.5402329 or f3 is null) then
-0.352897167
else
0.311400592
end
end
else
case when (f7<0.275665522 or f7 is null) then
case when (f8<0.403402805 or f8 is null) then
-0.292606086
else
0.220064178
end
else
case when (f8<-0.0442957953 or f8 is null) then
0.350784421
else
-0.336107522
end
end
end
else
case when (f7<1.77503061 or f7 is null) then
case when (f8<0.196157426 or f8 is null) then
case when (f7<1.36281848 or f7 is null) then
-0.3376683
else
-0.0711223111
end
else
case when (f7<-0.661211252 or f7 is null) then
0.434363276
else
-0.219307661
end
end
else
case when (f3<1.37940335 or f3 is null) then
case when (f6<1.34894884 or f6 is null) then
0.367155522
else
0.124757253
end
else
-0.293739736
end
end
end
else
case when (f3<1.77943277 or f3 is null) then
case when (f7<-0.469875157 or f7 is null) then
case when (f3<-0.536645889 or f3 is null) then
case when (f9<1.89841866 or f9 is null) then
-0
else
0.245992288
end
else
case when (f0<1.60565615 or f0 is null) then
0.357973605
else
0.193993196
end
end
else
case when (f9<1.89456153 or f9 is null) then
-0.276471078
else
0.111896731
end
end
else
case when (f1<1.35706067 or f1 is null) then
case when (f4<-1.10089087 or f4 is null) then
case when (f3<2.3550868 or f3 is null) then
0.0119848112
else
-0.284813672
end
else
-0.376859784
end
else
case when (f2<-0.25748384 or f2 is null) then
0.0723158419
else
-0.253415495
end
end
end
end
end
as tree_3_score
from data_table)
```
## lightgbm model transform sql
+ 导入相关依赖
```python
import lightgbm as lgb
from sklearn.datasets import make_classification
from sklearn.model_selection import train_test_split
```
+ 训练1个lightgbm二分类模型
```python
X, y = make_classification(n_samples=10000,
n_features=10,
n_informative=3,
n_redundant=2,
n_repeated=0,
n_classes=2,
weights=[0.7, 0.3],
flip_y=0.1,
random_state=1024)
X_train, X_test, y_train, y_test = train_test_split(X, y, random_state=1024)
###训练模型
model = lgb.LGBMClassifier(n_estimators=3)
model.fit(X_train, y_train)
lgb.create_tree_digraph(model._Booster, tree_index=0)
```
+ 使用treemodel2sql工具包将模型转换成的sql语句
```python
from treemodel2sql import Lgb2Sql
lgb2sql = Lgb2Sql()
sql_str = lgb2sql.transform(model)
```
+ 将sql语句保存
```python
lgb2sql.save()
```
+ 将sql语句打印出来
```python
print(sql_str)
```
```sql
select key,1 / (1 + exp(-((tree_0_score + tree_1_score + tree_2_score)+(-0.0)))) as score
from (
select key,
--tree0
case when (Column_9 is null and true==true or Column_9<=-1.6632177658322267) then
case when (Column_3 is null and true==true or Column_3<=-1.5495347315228287) then
-0.8762269835062461
else
case when (Column_3 is null and true==true or Column_3<=1.3809327191663714) then
case when (Column_7 is null and true==true or Column_7<=-0.9295802204706084) then
-0.5897045622608377
else
-0.45043755217654896
end
else
-0.8259039361137315
end
end
else
case when (Column_9 is null and true==true or Column_9<=1.6094262296522934) then
case when (Column_3 is null and true==true or Column_3<=0.5872311864187175) then
case when (Column_5 is null and true==true or Column_5<=0.6614392939376948) then
case when (Column_7 is null and true==true or Column_7<=-0.7719746626173067) then
case when (Column_3 is null and true==true or Column_3<=-0.47247338144095047) then
case when (Column_8 is null and true==true or Column_8<=1.366961884973301) then
-0.4786799717862057
else
-0.7246405208927198
end
else
case when (Column_5 is null and true==true or Column_5<=0.33583497995253037) then
-0.8576174849758683
else
case when (Column_8 is null and true==true or Column_8<=0.725660478148893) then
-0.45255653873335094
else
-0.7268525857883245
end
end
end
else
case when (Column_3 is null and true==true or Column_3<=-1.1870271596234325) then
-0.8614608311023392
else
case when (Column_3 is null and true==true or Column_3<=0.3450499438974393) then
case when (Column_9 is null and true==true or Column_9<=0.719805683840674) then
-0.452301345707117
else
case when (Column_3 is null and true==true or Column_3<=0.24246683106041103) then
-0.5283016889821918
else
-0.7127023928846948
end
end
else
case when (Column_8 is null and true==true or Column_8<=-0.41161510713991156) then
-0.4296985348121034
else
case when (Column_5 is null and true==true or Column_5<=0.10191127467897389) then
-0.8660786096722881
else
-0.7213351365659543
end
end
end
end
end
else
case when (Column_7 is null and true==true or Column_7<=0.1335285190014809) then
case when (Column_8 is null and true==true or Column_8<=0.3151386351229311) then
-0.7813601336005307
else
case when (Column_8 is null and true==true or Column_8<=0.797889562545179) then
case when (Column_7 is null and true==true or Column_7<=-0.6119464230811894) then
-0.47541454265459887
else
case when (Column_9 is null and true==true or Column_9<=1.036435633602195) then
-0.5335985526359568
else
-0.7562414479727859
end
end
else
-0.4911879020856353
end
end
else
case when (Column_8 is null and true==true or Column_8<=-0.2260123021962532) then
-0.4495750599610143
else
case when (Column_5 is null and true==true or Column_5<=1.3421825678767703) then
-0.8610329200059168
else
case when (Column_7 is null and true==true or Column_7<=0.9404699950203693) then
-0.8282483467723211
else
-0.6811365779458289
end
end
end
end
end
else
case when (Column_7 is null and true==true or Column_7<=2.2029620939306103) then
case when (Column_8 is null and true==true or Column_8<=-0.20972527484810635) then
-0.8620295284631452
else
-0.6406954940851597
end
else
-0.49244599655670496
end
end
else
case when (Column_3 is null and true==true or Column_3<=1.7659981730252496) then
case when (Column_7 is null and true==true or Column_7<=-0.709846883702808) then
-0.44913417585419935
else
-0.7039945818670766
end
else
-0.8609816276658351
end
end
end
as tree_0_score,
--tree1
case when (Column_9 is null and true==true or Column_9<=-1.6632177658322267) then
case when (Column_3 is null and true==true or Column_3<=-1.5495347315228287) then
-0.1304300334944157
else
case when (Column_3 is null and true==true or Column_3<=1.084128912605023) then
0.23707714508016445
else
-0.0285157252239888
end
end
else
case when (Column_9 is null and true==true or Column_9<=1.6094262296522934) then
case when (Column_3 is null and true==true or Column_3<=0.5872311864187175) then
case when (Column_5 is null and true==true or Column_5<=0.6614392939376948) then
case when (Column_7 is null and true==true or Column_7<=-0.9295802204706084) then
case when (Column_3 is null and true==true or Column_3<=-0.47247338144095047) then
case when (Column_9 is null and true==true or Column_9<=0.6831935640096413) then
0.19417988489655402
else
-0.02440020590879623
end
else
case when (Column_9 is null and true==true or Column_9<=1.036435633602195) then
-0.11286120415008301
else
0.08687819192210895
end
end
else
case when (Column_3 is null and true==true or Column_3<=-1.1870271596234325) then
-0.11564799847528345
else
case when (Column_9 is null and true==true or Column_9<=0.3004389492550987) then
0.2405259765006961
else
case when (Column_3 is null and true==true or Column_3<=0.2158166423973093) then
case when (Column_7 is null and true==true or Column_7<=-0.6547653704267197) then
0.11054725461728983
else
0.2269735323256574
end
else
case when (Column_5 is null and true==true or Column_5<=0.10191127467897389) then
-0.1208387319652229
else
case when (Column_1 is null and true==true or Column_1<=0.6151636033969191) then
case when (Column_8 is null and true==true or Column_8<=-0.018071874612147206) then
0.0016586271011360867
else
0.1332964744950624
end
else
-0.04300639955361431
end
end
end
end
end
end
else
case when (Column_7 is null and true==true or Column_7<=0.1335285190014809) then
case when (Column_8 is null and true==true or Column_8<=0.27824194846895633) then
-0.0613742224255702
else
case when (Column_3 is null and true==true or Column_3<=-0.37785910715343846) then
0.23646592246665976
else
case when (Column_7 is null and true==true or Column_7<=-0.3752232334694377) then
case when (Column_8 is null and true==true or Column_8<=1.2531231065059638) then
0.20734413117883457
else
0.08650249777206469
end
else
case when (Column_6 is null and true==true or Column_6<=0.1864449772206759) then
0.0014253268716679143
else
0.19695058122999737
end
end
end
end
else
case when (Column_8 is null and true==true or Column_8<=-0.2260123021962532) then
0.23847961174940435
else
case when (Column_5 is null and true==true or Column_5<=1.3421825678767703) then
-0.115222500730401
else
case when (Column_8 is null and true==true or Column_8<=0.1800136296190439) then
0.060703267253890685
else
-0.08603393258665121
end
end
end
end
end
else
case when (Column_7 is null and true==true or Column_7<=2.0654353175567177) then
case when (Column_8 is null and true==true or Column_8<=-0.20972527484810635) then
-0.11660912729818569
else
0.08556190442726179
end
else
0.19286044838453295
end
end
else
case when (Column_3 is null and true==true or Column_3<=1.7659981730252496) then
case when (Column_7 is null and true==true or Column_7<=-0.709846883702808) then
0.23881782889986833
else
0.031196179358762684
end
else
-0.11517151290097437
end
end
end
as tree_1_score,
--tree2
case when (Column_9 is null and true==true or Column_9<=-1.6632177658322267) then
case when (Column_3 is null and true==true or Column_3<=-1.5495347315228287) then
-0.1246790381676725
else
case when (Column_3 is null and true==true or Column_3<=1.3809327191663714) then
case when (Column_3 is null and true==true or Column_3<=0.7880730732430219) then
0.20644395671242038
else
0.1189732213687583
end
else
-0.0788791406092519
end
end
else
case when (Column_9 is null and true==true or Column_9<=1.6094262296522934) then
case when (Column_3 is null and true==true or Column_3<=0.6038877970616275) then
case when (Column_5 is null and true==true or Column_5<=0.6614392939376948) then
case when (Column_7 is null and true==true or Column_7<=-0.9295802204706084) then
case when (Column_3 is null and true==true or Column_3<=-0.47247338144095047) then
case when (Column_7 is null and true==true or Column_7<=-1.6012773225012003) then
case when (Column_9 is null and true==true or Column_9<=0.5166566313259823) then
0.14609631025061118
else
-0.08519567041375777
end
else
0.20775467159820815
end
else
case when (Column_9 is null and true==true or Column_9<=1.013669661144344) then
-0.10698219135389433
else
0.06871478156256401
end
end
else
case when (Column_3 is null and true==true or Column_3<=-1.3968501345470952) then
-0.11575647257046306
else
case when (Column_3 is null and true==true or Column_3<=0.3381305759653203) then
case when (Column_9 is null and true==true or Column_9<=0.6711065070274237) then
0.2055124396523362
else
case when (Column_3 is null and true==true or Column_3<=0.08416177325543671) then
0.18486979450427765
else
case when (Column_5 is null and true==true or Column_5<=0.4068544493018646) then
-0.04785536117613759
else
0.11879752654423877
end
end
end
else
case when (Column_8 is null and true==true or Column_8<=-0.5159476948660279) then
0.22110765206038635
else
case when (Column_9 is null and true==true or Column_9<=0.7486693174493504) then
-0.07426890908854177
else
0.12667195625568606
end
end
end
end
end
else
case when (Column_7 is null and true==true or Column_7<=0.1335285190014809) then
case when (Column_8 is null and true==true or Column_8<=0.24202650482335777) then
-0.07289970951054063
else
case when (Column_8 is null and true==true or Column_8<=0.797889562545179) then
case when (Column_7 is null and true==true or Column_7<=-0.6119464230811894) then
0.19832855719431475
else
0.05525269245438189
end
else
0.17963780553305242
end
end
else
case when (Column_8 is null and true==true or Column_8<=-0.25745431183810524) then
0.20767489164465192
else
case when (Column_5 is null and true==true or Column_5<=1.3421825678767703) then
-0.10914096073415368
else
case when (Column_9 is null and true==true or Column_9<=1.1239281866391186) then
0.04590138464892762
else
-0.08877584425107066
end
end
end
end
end
else
case when (Column_7 is null and true==true or Column_7<=2.2516563501714586) then
case when (Column_8 is null and true==true or Column_8<=-0.20972527484810635) then
-0.11032211105487942
else
0.06868549293845214
end
else
0.18271175459060623
end
end
else
case when (Column_3 is null and true==true or Column_3<=1.7659981730252496) then
case when (Column_7 is null and true==true or Column_7<=-0.709846883702808) then
0.2062125352630051
else
0.027771830564502133
end
else
-0.10919849076791249
end
end
end
as tree_2_score
from data_table)
```
Raw data
{
"_id": null,
"home_page": "https://github.com/ZhengRyan/treemodel2sql",
"name": "treemodel2sql",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.6",
"maintainer_email": "",
"keywords": "treemodel2sql",
"author": "RyanZheng",
"author_email": "zhengruiping000@163.com",
"download_url": "https://files.pythonhosted.org/packages/64/b6/c8016949ac0f0a58de1acd753719cffb6e738e7c2148e8b728c1f4f0b505/treemodel2sql-0.1.5.tar.gz",
"platform": null,
"description": "# XGBoost\u548cLightGBM\u6a21\u578b\u8f6csql\u8bed\u53e5\u5de5\u5177\u5305\n\u73b0\u5728\u662f\u5927\u6570\u636e\u91cf\u7684\u65f6\u4ee3\uff0c\u6211\u4eec\u5f00\u53d1\u7684\u6a21\u578b\u8981\u5e94\u7528\u5728\u7279\u522b\u5927\u7684\u5f85\u9884\u6d4b\u96c6\u4e0a\uff0c\u4f7f\u7528\u5355\u673a\u7684python\uff0c\u9700\u8981\u9884\u6d4b2\u30013\u5929\uff0c\u751a\u81f3\u66f4\u4e45\uff0c\u4e2d\u9014\u5f88\u6709\u53ef\u80fd\u4e2d\u65ad\u3002\u56e0\u6b64\u9700\u8981\u901a\u8fc7\u5206\u5e03\u5f0f\u7684\u65b9\u5f0f\u6765\u9884\u6d4b\u3002\u8fd9\u4e2a\u5de5\u5177\u5305\u5c31\u662f\u5b9e\u73b0\u4e86\u5c06\u8bad\u7ec3\u597d\u7684python\u6a21\u578b\uff0c\u8f6c\u6362\u6210sql\u8bed\u53e5\u3002\u5c06\u751f\u6210\u7684sql\u8bed\u53e5\u53ef\u4ee5\u653e\u5230\u5927\u6570\u636e\u73af\u5883\u4e2d\u8fdb\u884c\u5206\u5e03\u5f0f\u6267\u884c\u9884\u6d4b\uff0c\u80fd\u6bd4\u5355\u673a\u7684python\u9884\u6d4b\u5feb\u597d\u51e0\u4e2a\u91cf\u7ea7\n\n\n## \u601d\u60f3\u78b0\u649e\n\n| \u5fae\u4fe1 | \u5fae\u4fe1\u516c\u4f17\u53f7 |\n| :---: | :----: |\n| <img src=\"https://github.com/ZhengRyan/autotreemodel/blob/master/images/%E5%B9%B2%E9%A5%AD%E4%BA%BA.png\" alt=\"RyanZheng.png\" width=\"50%\" border=0/> | <img src=\"https://github.com/ZhengRyan/autotreemodel/blob/master/images/%E9%AD%94%E9%83%BD%E6%95%B0%E6%8D%AE%E5%B9%B2%E9%A5%AD%E4%BA%BA.png\" alt=\"\u9b54\u90fd\u6570\u636e\u5e72\u996d\u4eba.png\" width=\"50%\" border=0/> |\n| \u5e72\u996d\u4eba | \u9b54\u90fd\u6570\u636e\u5e72\u996d\u4eba |\n\n\n> \u4ed3\u5e93\u5730\u5740\uff1ahttps://github.com/ZhengRyan/treemodel2sql\n> \n> \u5fae\u4fe1\u516c\u4f17\u53f7\u6587\u7ae0\uff1ahttps://mp.weixin.qq.com/s/u8Nsp5M93WIGL2M0tU4U_g\n> \n> pipy\u5305\uff1ahttps://pypi.org/project/treemodel2sql/\n\n\n\n## \u73af\u5883\u51c6\u5907\n\u53ef\u4ee5\u4e0d\u7528\u5355\u72ec\u521b\u5efa\u865a\u62df\u73af\u5883\uff0c\u56e0\u4e3a\u5bf9\u5305\u7684\u4f9d\u8d56\u6ca1\u6709\u7248\u672c\u8981\u6c42\n\n### `treemodel2sql` \u5b89\u88c5\npip install\uff08pip\u5b89\u88c5\uff09\n\n```bash\npip install treemodel2sql # to install\npip install -U treemodel2sql # to upgrade\n```\n\nSource code install\uff08\u6e90\u7801\u5b89\u88c5\uff09\n\n```bash\npython setup.py install\n```\n\n## \u8fd0\u884c\u6837\u4f8b\n###\u3010\u6ce8\u610f\uff1a\uff1a\uff1a\u6838\u9a8c\u5bf9\u6bd4python\u6a21\u578b\u9884\u6d4b\u51fa\u6765\u7684\u7ed3\u679c\u548csql\u8bed\u53e5\u9884\u6d4b\u51fa\u6765\u7684\u7ed3\u679c\u662f\u5426\u4e00\u81f4\u8bf7\u67e5\u770b\u6559\u7a0b\u4ee3\u7801\u3011\"https://github.com/ZhengRyan/treemodel2sql/tree/master/examples\"\u8def\u5f84\u5904\u7684tutorial_code_lightgbm_model_transform_sql.ipynb\u548ctutorial_code_xgboost_model_transform_sql.ipynb\n\n## xgboost model transform sql\n+ \u5bfc\u5165\u76f8\u5173\u4f9d\u8d56\n```python\nimport xgboost as xgb\nfrom sklearn.datasets import make_classification\nfrom sklearn.model_selection import train_test_split\n```\n\n+ \u8bad\u7ec31\u4e2axgboost\u4e8c\u5206\u7c7b\u6a21\u578b\n```python\nX, y = make_classification(n_samples=10000,\n n_features=10,\n n_informative=3,\n n_redundant=2,\n n_repeated=0,\n n_classes=2,\n weights=[0.7, 0.3],\n flip_y=0.1,\n random_state=1024)\n\nX_train, X_test, y_train, y_test = train_test_split(X, y, random_state=1024)\n\n###\u8bad\u7ec3\u6a21\u578b\nmodel = xgb.XGBClassifier(n_estimators=3)\nmodel.fit(X_train, y_train)\n#xgb.to_graphviz(model)\n```\n+ \u4f7f\u7528treemodel2sql\u5de5\u5177\u5305\u5c06\u6a21\u578b\u8f6c\u6362\u6210\u7684sql\u8bed\u53e5\n```python\nfrom treemodel2sql import XGBoost2Sql\nxgb2sql = XGBoost2Sql()\nsql_str = xgb2sql.transform(model)\n```\n\n+ \u5c06sql\u8bed\u53e5\u4fdd\u5b58\n```python\nxgb2sql.save()\n```\n\n+ \u5c06sql\u8bed\u53e5\u6253\u5370\u51fa\u6765\n```python\nprint(sql_str)\n```\n\n```sql\nselect key,1 / (1 + exp(-(tree_1_score + tree_2_score + tree_3_score)+(-0.0))) as score\n from (\n select key,\n --tree1\n\t\tcase when (f9<-1.64164519 or f9 is null) then\n\t\t\tcase when (f3<-4.19117069 or f3 is null) then\n\t\t\t\tcase when (f2<-1.31743848 or f2 is null) then\n\t\t\t\t\t-0.150000006\n\t\t\t\telse\n\t\t\t\t\t-0.544186056\n\t\t\t\tend\n\t\t\telse\n\t\t\t\tcase when (f3<1.23432565 or f3 is null) then\n\t\t\t\t\tcase when (f7<-2.55682254 or f7 is null) then\n\t\t\t\t\t\t-0.200000018\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f5<0.154983491 or f5 is null) then\n\t\t\t\t\t\t\t0.544721723\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f3<0.697217584 or f3 is null) then\n\t\t\t\t\t\t\t\t-0.150000006\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.333333373\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\telse\n\t\t\t\t\tcase when (f5<-1.0218116 or f5 is null) then\n\t\t\t\t\t\tcase when (f0<-0.60882163 or f0 is null) then\n\t\t\t\t\t\t\tcase when (f2<0.26019755 or f2 is null) then\n\t\t\t\t\t\t\t\t0.0666666701\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.300000012\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t-0.520000041\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\t0.333333373\n\t\t\t\t\tend\n\t\t\t\tend\n\t\t\tend\n\t\telse\n\t\t\tcase when (f9<1.60392439 or f9 is null) then\n\t\t\t\tcase when (f3<0.572542191 or f3 is null) then\n\t\t\t\t\tcase when (f5<0.653370142 or f5 is null) then\n\t\t\t\t\t\tcase when (f7<-0.765973091 or f7 is null) then\n\t\t\t\t\t\t\tcase when (f3<-0.432390809 or f3 is null) then\n\t\t\t\t\t\t\t\t0.204000011\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.485454559\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f3<-1.20459461 or f3 is null) then\n\t\t\t\t\t\t\t\t-0.5104478\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.441509455\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f7<0.133017987 or f7 is null) then\n\t\t\t\t\t\t\tcase when (f8<0.320554674 or f8 is null) then\n\t\t\t\t\t\t\t\t-0.290322572\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.368339777\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f8<-0.211985052 or f8 is null) then\n\t\t\t\t\t\t\t\t0.504000008\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.525648415\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\telse\n\t\t\t\t\tcase when (f7<2.22314501 or f7 is null) then\n\t\t\t\t\t\tcase when (f8<-0.00532855326 or f8 is null) then\n\t\t\t\t\t\t\tcase when (f8<-0.204920739 or f8 is null) then\n\t\t\t\t\t\t\t\t-0.533991575\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.200000018\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t0.428571463\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f3<1.33772755 or f3 is null) then\n\t\t\t\t\t\t\tcase when (f0<-0.975171864 or f0 is null) then\n\t\t\t\t\t\t\t\t0.163636371\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.51818186\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t-0\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\tend\n\t\t\telse\n\t\t\t\tcase when (f3<1.77943277 or f3 is null) then\n\t\t\t\t\tcase when (f7<-0.469875157 or f7 is null) then\n\t\t\t\t\t\tcase when (f3<-0.536645889 or f3 is null) then\n\t\t\t\t\t\t\tcase when (f9<1.89841866 or f9 is null) then\n\t\t\t\t\t\t\t\t-0\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.333333373\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f4<-2.43660188 or f4 is null) then\n\t\t\t\t\t\t\t\t0.150000006\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.551020443\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f1<-0.0788691565 or f1 is null) then\n\t\t\t\t\t\t\t0.150000006\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t-0.375\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\telse\n\t\t\t\t\tcase when (f4<-1.73232496 or f4 is null) then\n\t\t\t\t\t\t-0.150000006\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f6<-1.6080606 or f6 is null) then\n\t\t\t\t\t\t\t-0.150000006\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f7<-0.259483218 or f7 is null) then\n\t\t\t\t\t\t\t\t-0.558620751\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.300000012\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\tend\n\t\t\tend\n\t\tend\n\t\tas tree_1_score,\n--tree2\n\t\tcase when (f9<-1.64164519 or f9 is null) then\n\t\t\tcase when (f3<-4.19117069 or f3 is null) then\n\t\t\t\tcase when (f0<0.942570388 or f0 is null) then\n\t\t\t\t\t-0.432453066\n\t\t\t\telse\n\t\t\t\t\t-0.128291652\n\t\t\t\tend\n\t\t\telse\n\t\t\t\tcase when (f3<1.23432565 or f3 is null) then\n\t\t\t\t\tcase when (f7<-2.55682254 or f7 is null) then\n\t\t\t\t\t\t-0.167702854\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f5<0.154983491 or f5 is null) then\n\t\t\t\t\t\t\tcase when (f1<2.19985676 or f1 is null) then\n\t\t\t\t\t\t\t\t0.41752997\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.115944751\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t0.115584135\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\telse\n\t\t\t\t\tcase when (f5<-1.0218116 or f5 is null) then\n\t\t\t\t\t\tcase when (f0<-0.60882163 or f0 is null) then\n\t\t\t\t\t\t\t-0.119530827\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t-0.410788596\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\t0.28256765\n\t\t\t\t\tend\n\t\t\t\tend\n\t\t\tend\n\t\telse\n\t\t\tcase when (f9<1.60392439 or f9 is null) then\n\t\t\t\tcase when (f3<0.460727394 or f3 is null) then\n\t\t\t\t\tcase when (f5<0.653370142 or f5 is null) then\n\t\t\t\t\t\tcase when (f7<-0.933565617 or f7 is null) then\n\t\t\t\t\t\t\tcase when (f3<-0.572475374 or f3 is null) then\n\t\t\t\t\t\t\t\t0.182491601\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.377898693\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f3<-1.20459461 or f3 is null) then\n\t\t\t\t\t\t\t\t-0.392539263\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.352721155\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f7<0.207098693 or f7 is null) then\n\t\t\t\t\t\t\tcase when (f8<0.498489976 or f8 is null) then\n\t\t\t\t\t\t\t\t-0.193351224\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.29298231\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f8<-0.117464997 or f8 is null) then\n\t\t\t\t\t\t\t\t0.400667101\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.402199954\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\telse\n\t\t\t\t\tcase when (f7<1.98268723 or f7 is null) then\n\t\t\t\t\t\tcase when (f8<-0.00532855326 or f8 is null) then\n\t\t\t\t\t\t\tcase when (f7<1.36281848 or f7 is null) then\n\t\t\t\t\t\t\t\t-0.408002198\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.236123681\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f5<1.14038813 or f5 is null) then\n\t\t\t\t\t\t\t\t0.404326111\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.110877581\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f3<1.56952488 or f3 is null) then\n\t\t\t\t\t\t\tcase when (f5<2.14646816 or f5 is null) then\n\t\t\t\t\t\t\t\t0.409404457\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.0696995854\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t-0.32059738\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\tend\n\t\t\telse\n\t\t\t\tcase when (f3<1.77943277 or f3 is null) then\n\t\t\t\t\tcase when (f7<-0.469875157 or f7 is null) then\n\t\t\t\t\t\tcase when (f3<-0.536645889 or f3 is null) then\n\t\t\t\t\t\t\tcase when (f9<1.89841866 or f9 is null) then\n\t\t\t\t\t\t\t\t-0\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.28256765\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t0.419863999\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f3<0.444227457 or f3 is null) then\n\t\t\t\t\t\t\t-0.34664312\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t0.0693304539\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\telse\n\t\t\t\t\tcase when (f4<-1.10089087 or f4 is null) then\n\t\t\t\t\t\tcase when (f3<2.3550868 or f3 is null) then\n\t\t\t\t\t\t\t0.0147894565\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t-0.331404865\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\t-0.421277165\n\t\t\t\t\tend\n\t\t\t\tend\n\t\t\tend\n\t\tend\n\t\tas tree_2_score,\n--tree3\n\t\tcase when (f9<-1.64164519 or f9 is null) then\n\t\t\tcase when (f3<-4.19117069 or f3 is null) then\n\t\t\t\tcase when (f4<-1.30126143 or f4 is null) then\n\t\t\t\t\t-0.0772174299\n\t\t\t\telse\n\t\t\t\t\t-0.374165356\n\t\t\t\tend\n\t\t\telse\n\t\t\t\tcase when (f3<1.23432565 or f3 is null) then\n\t\t\t\t\tcase when (f7<-2.55682254 or f7 is null) then\n\t\t\t\t\t\t-0.142005175\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f5<0.154983491 or f5 is null) then\n\t\t\t\t\t\t\tcase when (f7<3.59379435 or f7 is null) then\n\t\t\t\t\t\t\t\t0.352122813\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.132789165\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t0.0924336985\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\telse\n\t\t\t\t\tcase when (f5<-1.0218116 or f5 is null) then\n\t\t\t\t\t\tcase when (f0<-0.60882163 or f0 is null) then\n\t\t\t\t\t\t\t-0.0954768136\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t-0.351594836\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\t0.245992288\n\t\t\t\t\tend\n\t\t\t\tend\n\t\t\tend\n\t\telse\n\t\t\tcase when (f9<1.60392439 or f9 is null) then\n\t\t\t\tcase when (f3<0.347133756 or f3 is null) then\n\t\t\t\t\tcase when (f5<0.661561131 or f5 is null) then\n\t\t\t\t\t\tcase when (f7<-0.933565617 or f7 is null) then\n\t\t\t\t\t\t\tcase when (f3<-0.472413659 or f3 is null) then\n\t\t\t\t\t\t\t\t0.116336405\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.313245147\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f3<-1.5402329 or f3 is null) then\n\t\t\t\t\t\t\t\t-0.352897167\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.311400592\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f7<0.275665522 or f7 is null) then\n\t\t\t\t\t\t\tcase when (f8<0.403402805 or f8 is null) then\n\t\t\t\t\t\t\t\t-0.292606086\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.220064178\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f8<-0.0442957953 or f8 is null) then\n\t\t\t\t\t\t\t\t0.350784421\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.336107522\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\telse\n\t\t\t\t\tcase when (f7<1.77503061 or f7 is null) then\n\t\t\t\t\t\tcase when (f8<0.196157426 or f8 is null) then\n\t\t\t\t\t\t\tcase when (f7<1.36281848 or f7 is null) then\n\t\t\t\t\t\t\t\t-0.3376683\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.0711223111\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f7<-0.661211252 or f7 is null) then\n\t\t\t\t\t\t\t\t0.434363276\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.219307661\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f3<1.37940335 or f3 is null) then\n\t\t\t\t\t\t\tcase when (f6<1.34894884 or f6 is null) then\n\t\t\t\t\t\t\t\t0.367155522\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.124757253\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t-0.293739736\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\tend\n\t\t\telse\n\t\t\t\tcase when (f3<1.77943277 or f3 is null) then\n\t\t\t\t\tcase when (f7<-0.469875157 or f7 is null) then\n\t\t\t\t\t\tcase when (f3<-0.536645889 or f3 is null) then\n\t\t\t\t\t\t\tcase when (f9<1.89841866 or f9 is null) then\n\t\t\t\t\t\t\t\t-0\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.245992288\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\tcase when (f0<1.60565615 or f0 is null) then\n\t\t\t\t\t\t\t\t0.357973605\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t0.193993196\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f9<1.89456153 or f9 is null) then\n\t\t\t\t\t\t\t-0.276471078\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t0.111896731\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\telse\n\t\t\t\t\tcase when (f1<1.35706067 or f1 is null) then\n\t\t\t\t\t\tcase when (f4<-1.10089087 or f4 is null) then\n\t\t\t\t\t\t\tcase when (f3<2.3550868 or f3 is null) then\n\t\t\t\t\t\t\t\t0.0119848112\n\t\t\t\t\t\t\telse\n\t\t\t\t\t\t\t\t-0.284813672\n\t\t\t\t\t\t\tend\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t-0.376859784\n\t\t\t\t\t\tend\n\t\t\t\t\telse\n\t\t\t\t\t\tcase when (f2<-0.25748384 or f2 is null) then\n\t\t\t\t\t\t\t0.0723158419\n\t\t\t\t\t\telse\n\t\t\t\t\t\t\t-0.253415495\n\t\t\t\t\t\tend\n\t\t\t\t\tend\n\t\t\t\tend\n\t\t\tend\n\t\tend\n\t\tas tree_3_score\n from data_table)\n```\n\n## lightgbm model transform sql\n+ \u5bfc\u5165\u76f8\u5173\u4f9d\u8d56\n```python\nimport lightgbm as lgb\nfrom sklearn.datasets import make_classification\nfrom sklearn.model_selection import train_test_split\n```\n\n+ \u8bad\u7ec31\u4e2alightgbm\u4e8c\u5206\u7c7b\u6a21\u578b\n```python\nX, y = make_classification(n_samples=10000,\n n_features=10,\n n_informative=3,\n n_redundant=2,\n n_repeated=0,\n n_classes=2,\n weights=[0.7, 0.3],\n flip_y=0.1,\n random_state=1024)\n\nX_train, X_test, y_train, y_test = train_test_split(X, y, random_state=1024)\n\n###\u8bad\u7ec3\u6a21\u578b\nmodel = lgb.LGBMClassifier(n_estimators=3)\nmodel.fit(X_train, y_train)\nlgb.create_tree_digraph(model._Booster, tree_index=0)\n```\n+ \u4f7f\u7528treemodel2sql\u5de5\u5177\u5305\u5c06\u6a21\u578b\u8f6c\u6362\u6210\u7684sql\u8bed\u53e5\n```python\nfrom treemodel2sql import Lgb2Sql\nlgb2sql = Lgb2Sql()\nsql_str = lgb2sql.transform(model)\n```\n\n+ \u5c06sql\u8bed\u53e5\u4fdd\u5b58\n```python\nlgb2sql.save()\n```\n\n+ \u5c06sql\u8bed\u53e5\u6253\u5370\u51fa\u6765\n```python\nprint(sql_str)\n```\n\n```sql\nselect key,1 / (1 + exp(-((tree_0_score + tree_1_score + tree_2_score)+(-0.0)))) as score\n from (\n select key,\n --tree0\n case when (Column_9 is null and true==true or Column_9<=-1.6632177658322267) then\n case when (Column_3 is null and true==true or Column_3<=-1.5495347315228287) then\n -0.8762269835062461\n else\n case when (Column_3 is null and true==true or Column_3<=1.3809327191663714) then\n case when (Column_7 is null and true==true or Column_7<=-0.9295802204706084) then\n -0.5897045622608377\n else\n -0.45043755217654896\n end\n else\n -0.8259039361137315\n end\n end\n else\n case when (Column_9 is null and true==true or Column_9<=1.6094262296522934) then\n case when (Column_3 is null and true==true or Column_3<=0.5872311864187175) then\n case when (Column_5 is null and true==true or Column_5<=0.6614392939376948) then\n case when (Column_7 is null and true==true or Column_7<=-0.7719746626173067) then\n case when (Column_3 is null and true==true or Column_3<=-0.47247338144095047) then\n case when (Column_8 is null and true==true or Column_8<=1.366961884973301) then\n -0.4786799717862057\n else\n -0.7246405208927198\n end\n else\n case when (Column_5 is null and true==true or Column_5<=0.33583497995253037) then\n -0.8576174849758683\n else\n case when (Column_8 is null and true==true or Column_8<=0.725660478148893) then\n -0.45255653873335094\n else\n -0.7268525857883245\n end\n end\n end\n else\n case when (Column_3 is null and true==true or Column_3<=-1.1870271596234325) then\n -0.8614608311023392\n else\n case when (Column_3 is null and true==true or Column_3<=0.3450499438974393) then\n case when (Column_9 is null and true==true or Column_9<=0.719805683840674) then\n -0.452301345707117\n else\n case when (Column_3 is null and true==true or Column_3<=0.24246683106041103) then\n -0.5283016889821918\n else\n -0.7127023928846948\n end\n end\n else\n case when (Column_8 is null and true==true or Column_8<=-0.41161510713991156) then\n -0.4296985348121034\n else\n case when (Column_5 is null and true==true or Column_5<=0.10191127467897389) then\n -0.8660786096722881\n else\n -0.7213351365659543\n end\n end\n end\n end\n end\n else\n case when (Column_7 is null and true==true or Column_7<=0.1335285190014809) then\n case when (Column_8 is null and true==true or Column_8<=0.3151386351229311) then\n -0.7813601336005307\n else\n case when (Column_8 is null and true==true or Column_8<=0.797889562545179) then\n case when (Column_7 is null and true==true or Column_7<=-0.6119464230811894) then\n -0.47541454265459887\n else\n case when (Column_9 is null and true==true or Column_9<=1.036435633602195) then\n -0.5335985526359568\n else\n -0.7562414479727859\n end\n end\n else\n -0.4911879020856353\n end\n end\n else\n case when (Column_8 is null and true==true or Column_8<=-0.2260123021962532) then\n -0.4495750599610143\n else\n case when (Column_5 is null and true==true or Column_5<=1.3421825678767703) then\n -0.8610329200059168\n else\n case when (Column_7 is null and true==true or Column_7<=0.9404699950203693) then\n -0.8282483467723211\n else\n -0.6811365779458289\n end\n end\n end\n end\n end\n else\n case when (Column_7 is null and true==true or Column_7<=2.2029620939306103) then\n case when (Column_8 is null and true==true or Column_8<=-0.20972527484810635) then\n -0.8620295284631452\n else\n -0.6406954940851597\n end\n else\n -0.49244599655670496\n end\n end\n else\n case when (Column_3 is null and true==true or Column_3<=1.7659981730252496) then\n case when (Column_7 is null and true==true or Column_7<=-0.709846883702808) then\n -0.44913417585419935\n else\n -0.7039945818670766\n end\n else\n -0.8609816276658351\n end\n end\n end\n as tree_0_score,\n--tree1\n case when (Column_9 is null and true==true or Column_9<=-1.6632177658322267) then\n case when (Column_3 is null and true==true or Column_3<=-1.5495347315228287) then\n -0.1304300334944157\n else\n case when (Column_3 is null and true==true or Column_3<=1.084128912605023) then\n 0.23707714508016445\n else\n -0.0285157252239888\n end\n end\n else\n case when (Column_9 is null and true==true or Column_9<=1.6094262296522934) then\n case when (Column_3 is null and true==true or Column_3<=0.5872311864187175) then\n case when (Column_5 is null and true==true or Column_5<=0.6614392939376948) then\n case when (Column_7 is null and true==true or Column_7<=-0.9295802204706084) then\n case when (Column_3 is null and true==true or Column_3<=-0.47247338144095047) then\n case when (Column_9 is null and true==true or Column_9<=0.6831935640096413) then\n 0.19417988489655402\n else\n -0.02440020590879623\n end\n else\n case when (Column_9 is null and true==true or Column_9<=1.036435633602195) then\n -0.11286120415008301\n else\n 0.08687819192210895\n end\n end\n else\n case when (Column_3 is null and true==true or Column_3<=-1.1870271596234325) then\n -0.11564799847528345\n else\n case when (Column_9 is null and true==true or Column_9<=0.3004389492550987) then\n 0.2405259765006961\n else\n case when (Column_3 is null and true==true or Column_3<=0.2158166423973093) then\n case when (Column_7 is null and true==true or Column_7<=-0.6547653704267197) then\n 0.11054725461728983\n else\n 0.2269735323256574\n end\n else\n case when (Column_5 is null and true==true or Column_5<=0.10191127467897389) then\n -0.1208387319652229\n else\n case when (Column_1 is null and true==true or Column_1<=0.6151636033969191) then\n case when (Column_8 is null and true==true or Column_8<=-0.018071874612147206) then\n 0.0016586271011360867\n else\n 0.1332964744950624\n end\n else\n -0.04300639955361431\n end\n end\n end\n end\n end\n end\n else\n case when (Column_7 is null and true==true or Column_7<=0.1335285190014809) then\n case when (Column_8 is null and true==true or Column_8<=0.27824194846895633) then\n -0.0613742224255702\n else\n case when (Column_3 is null and true==true or Column_3<=-0.37785910715343846) then\n 0.23646592246665976\n else\n case when (Column_7 is null and true==true or Column_7<=-0.3752232334694377) then\n case when (Column_8 is null and true==true or Column_8<=1.2531231065059638) then\n 0.20734413117883457\n else\n 0.08650249777206469\n end\n else\n case when (Column_6 is null and true==true or Column_6<=0.1864449772206759) then\n 0.0014253268716679143\n else\n 0.19695058122999737\n end\n end\n end\n end\n else\n case when (Column_8 is null and true==true or Column_8<=-0.2260123021962532) then\n 0.23847961174940435\n else\n case when (Column_5 is null and true==true or Column_5<=1.3421825678767703) then\n -0.115222500730401\n else\n case when (Column_8 is null and true==true or Column_8<=0.1800136296190439) then\n 0.060703267253890685\n else\n -0.08603393258665121\n end\n end\n end\n end\n end\n else\n case when (Column_7 is null and true==true or Column_7<=2.0654353175567177) then\n case when (Column_8 is null and true==true or Column_8<=-0.20972527484810635) then\n -0.11660912729818569\n else\n 0.08556190442726179\n end\n else\n 0.19286044838453295\n end\n end\n else\n case when (Column_3 is null and true==true or Column_3<=1.7659981730252496) then\n case when (Column_7 is null and true==true or Column_7<=-0.709846883702808) then\n 0.23881782889986833\n else\n 0.031196179358762684\n end\n else\n -0.11517151290097437\n end\n end\n end\n as tree_1_score,\n--tree2\n case when (Column_9 is null and true==true or Column_9<=-1.6632177658322267) then\n case when (Column_3 is null and true==true or Column_3<=-1.5495347315228287) then\n -0.1246790381676725\n else\n case when (Column_3 is null and true==true or Column_3<=1.3809327191663714) then\n case when (Column_3 is null and true==true or Column_3<=0.7880730732430219) then\n 0.20644395671242038\n else\n 0.1189732213687583\n end\n else\n -0.0788791406092519\n end\n end\n else\n case when (Column_9 is null and true==true or Column_9<=1.6094262296522934) then\n case when (Column_3 is null and true==true or Column_3<=0.6038877970616275) then\n case when (Column_5 is null and true==true or Column_5<=0.6614392939376948) then\n case when (Column_7 is null and true==true or Column_7<=-0.9295802204706084) then\n case when (Column_3 is null and true==true or Column_3<=-0.47247338144095047) then\n case when (Column_7 is null and true==true or Column_7<=-1.6012773225012003) then\n case when (Column_9 is null and true==true or Column_9<=0.5166566313259823) then\n 0.14609631025061118\n else\n -0.08519567041375777\n end\n else\n 0.20775467159820815\n end\n else\n case when (Column_9 is null and true==true or Column_9<=1.013669661144344) then\n -0.10698219135389433\n else\n 0.06871478156256401\n end\n end\n else\n case when (Column_3 is null and true==true or Column_3<=-1.3968501345470952) then\n -0.11575647257046306\n else\n case when (Column_3 is null and true==true or Column_3<=0.3381305759653203) then\n case when (Column_9 is null and true==true or Column_9<=0.6711065070274237) then\n 0.2055124396523362\n else\n case when (Column_3 is null and true==true or Column_3<=0.08416177325543671) then\n 0.18486979450427765\n else\n case when (Column_5 is null and true==true or Column_5<=0.4068544493018646) then\n -0.04785536117613759\n else\n 0.11879752654423877\n end\n end\n end\n else\n case when (Column_8 is null and true==true or Column_8<=-0.5159476948660279) then\n 0.22110765206038635\n else\n case when (Column_9 is null and true==true or Column_9<=0.7486693174493504) then\n -0.07426890908854177\n else\n 0.12667195625568606\n end\n end\n end\n end\n end\n else\n case when (Column_7 is null and true==true or Column_7<=0.1335285190014809) then\n case when (Column_8 is null and true==true or Column_8<=0.24202650482335777) then\n -0.07289970951054063\n else\n case when (Column_8 is null and true==true or Column_8<=0.797889562545179) then\n case when (Column_7 is null and true==true or Column_7<=-0.6119464230811894) then\n 0.19832855719431475\n else\n 0.05525269245438189\n end\n else\n 0.17963780553305242\n end\n end\n else\n case when (Column_8 is null and true==true or Column_8<=-0.25745431183810524) then\n 0.20767489164465192\n else\n case when (Column_5 is null and true==true or Column_5<=1.3421825678767703) then\n -0.10914096073415368\n else\n case when (Column_9 is null and true==true or Column_9<=1.1239281866391186) then\n 0.04590138464892762\n else\n -0.08877584425107066\n end\n end\n end\n end\n end\n else\n case when (Column_7 is null and true==true or Column_7<=2.2516563501714586) then\n case when (Column_8 is null and true==true or Column_8<=-0.20972527484810635) then\n -0.11032211105487942\n else\n 0.06868549293845214\n end\n else\n 0.18271175459060623\n end\n end\n else\n case when (Column_3 is null and true==true or Column_3<=1.7659981730252496) then\n case when (Column_7 is null and true==true or Column_7<=-0.709846883702808) then\n 0.2062125352630051\n else\n 0.027771830564502133\n end\n else\n -0.10919849076791249\n end\n end\n end\n as tree_2_score\n from data_table)\n```\n",
"bugtrack_url": null,
"license": "MIT license",
"summary": "tree model transform to sql",
"version": "0.1.5",
"project_urls": {
"Homepage": "https://github.com/ZhengRyan/treemodel2sql"
},
"split_keywords": [
"treemodel2sql"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "d4322a40de030d00b6a3079f1861ca2565f05e0adf87921836f814e7618da11f",
"md5": "7a06264d95935318f3f73f1b7f7f968e",
"sha256": "4e43151b1a6b2d77cf5313987b5d80b96221fa2010187344e9f8f4bb07ca006c"
},
"downloads": -1,
"filename": "treemodel2sql-0.1.5-py2.py3-none-any.whl",
"has_sig": false,
"md5_digest": "7a06264d95935318f3f73f1b7f7f968e",
"packagetype": "bdist_wheel",
"python_version": "py2.py3",
"requires_python": ">=3.6",
"size": 14877,
"upload_time": "2023-08-09T14:23:57",
"upload_time_iso_8601": "2023-08-09T14:23:57.497109Z",
"url": "https://files.pythonhosted.org/packages/d4/32/2a40de030d00b6a3079f1861ca2565f05e0adf87921836f814e7618da11f/treemodel2sql-0.1.5-py2.py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "64b6c8016949ac0f0a58de1acd753719cffb6e738e7c2148e8b728c1f4f0b505",
"md5": "c3762eedd695c0d33df043d4124a6630",
"sha256": "b7e34c738311ba6a3ffb483926d628066356a2632071cbefa096082328b632ae"
},
"downloads": -1,
"filename": "treemodel2sql-0.1.5.tar.gz",
"has_sig": false,
"md5_digest": "c3762eedd695c0d33df043d4124a6630",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.6",
"size": 107127,
"upload_time": "2023-08-09T14:23:59",
"upload_time_iso_8601": "2023-08-09T14:23:59.749532Z",
"url": "https://files.pythonhosted.org/packages/64/b6/c8016949ac0f0a58de1acd753719cffb6e738e7c2148e8b728c1f4f0b505/treemodel2sql-0.1.5.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-08-09 14:23:59",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "ZhengRyan",
"github_project": "treemodel2sql",
"travis_ci": true,
"coveralls": false,
"github_actions": false,
"requirements": [],
"lcname": "treemodel2sql"
}
JSON
