當SQL Server 完成機器學習後,如果能把訓練後的模型直接儲存在SQL資料表,新的交易數據馬上能就近在資料庫內參照模型作預測。這篇先紀錄將訓練後的模型儲存在SQL Server 2016資料庫內,下一篇再筆記使用模型對新數據作出預測。
氣溫升高業績熱!
假使我們認為這當中有其線性關係,當累積了一段時間的氣溫與銷售量數據後,透過簡單的機器學習(ML),我們就能用天氣預報最高氣溫來預測茶飲銷售量。
選擇機器學習種類
為了把關注點放在儲存及使用模型上,我們拿簡單線性迴歸分析(Simple Linear Regression Analysis)當範例寫筆記,迴歸分析的介紹可以參考Wiki下圖及說明。
回到正題,怎麼儲存各種訓練後的模型?
從MSDN的學習文章中又有新大陸,哥倫布可以把學習後的模型在Client端R Script進行序列化,接著使用RODBC套件(R Package)寫入SQL Server Table中。
這邊稍微調整作法,既然已經在資料庫了,我們就直接將序列化的結果透過T-SQL輸出到SQL Server 指定資料表內。
儲存模型步驟
1.建立儲存模型的SQL資料表(模型名稱、訓練時間、模型內容)
CREATE TABLE ML_Models
(
name varchar(20) not null,
mnt_dt datetime not null,
model varbinary(max) not null
)
2.建立儲存訓練資料的SQL資料表及初始訓練資料(Training Data)
資料表有2個欄位: 天氣溫度與冰紅茶銷售量的統計資料。
(未來可以不斷輸入新資料,讓模型訓練的更好!)
CREATE TABLE ML_IcedRedTeaData
(
temperature int not null,
icedTeaSales int not null
)
insert into ML_IcedRedTeaData (temperature,icedTeaSales)
values(29,77),(28,62),(34,93),(31,84),(25,59),(29,64),(32,79)
,(31,75),(24,58),(33,91),(25,51),(31,73),(26,65),(30,84)
3.建立訓練模型的預存程序
這邊假設資料直接從Table ML_IcedRedTeaData輸入到InputDataSet DataFrame。
create proc usp_lmIcedTeaSales as
execute sp_execute_external_script
@language = N'R'
, @script = N'
# (1)建立線性迴歸模型
#Y:因變數或從屬變 icedTeaSales
#X:自變數或獨立變數 temperature
trainData <-InputDataSet
colnames(trainData) <- c("temperature", "icedTeaSales")
lmObj <- lm(formula = icedTeaSales ~ temperature, data = trainData)
# (2)序列化線性迴歸模型
modelbin <- serialize(lmObj, NULL)
modelbinstr=paste(modelbin, collapse="")
OutputDataSet <- data.frame(data=readBin(modelbin,what=raw(),n=1e6))
'
, @input_data_1 = N'SELECT * FROM ML_IcedRedTeaData'
WITH RESULT SETS (([model] varbinary(max) NOT NULL));
4.呼叫步驟2的預存程序usp_lmIcedTeaSales,然後將回傳的模型儲存在SQL資料表內。
(當訓練資料不斷的更新,這個步驟就可以視情況從排程中執行,訓練出來的模型也能更貼近實際情況。)
create table #temp
(
model varbinary(max)
)
insert into #temp
exec usp_lmIcedTeaSales
insert into [dbo].ML_Models
select 'lmIcedRedTeaSales' as name,getdate() as mnt_dt,model from #temp
驗證看看,查詢儲存學習模型的資料表
select * from ML_Models
寫入成功!儲存完模型後,下一步就是使用模型,可以參考這篇:使用模型筆記in SQL Server
通常在跑模型前,可以先用圖表觀察資料:
#Y軸=icedTeaSales X軸:temperature
plot(icedTeaSales ~ temperature, main = "依據氣溫預測冰紅茶銷量", xlab = "當日最高氣溫(度)", ylab = "冰紅茶銷量(杯)", family = "STHeiti")
abline(reg = lmIcedTea$coefficients, col = "red", lwd = 2)
看起來有很高的線性關係!
參考:
在 Transact-SQL 中使用 R 程式碼 (SQL Server R Services)
Lesson 4: Build and Save the Model (Data Science End-to-End Walkthrough)
Lesson 5: Deploy and Use the Model (Data Science End-to-End Walkthrough)