=========================
SQL R
=========================
USE [DW]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
CREATE TABLE [dbo].[data_1024_kMeans](
[Driver_ID] [varchar](50) NULL,
[Distance_Feature] [varchar](50) NULL,
[Speeding_Feature] [varchar](50) NULL,
[Cluster] [int] NULL
) ON [PRIMARY]
GO
*/
-- select Distance_Feature, Speeding_Feature, cluster from data_1024_kMeans
Declare @outputData TABLE (Distance_Feature float, Speeding_Feature float, Cluster int)
INSERT INTO @outputData
EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'
trained_model <- kmeans(df[, c("Distance_Feature", "Speeding_Feature")], 4)
df$cluster <- trained_model$cluster
'
, @input_data_1 = N'SELECT Distance_Feature, Speeding_Feature from data_1024_kMeans'
, @output_data_1_name = N'df'
, @input_data_1_name = N'df';
SELECT * FROM @outputData
update TM1 set Cluster = OTP.Cluster from data_1024_kMeans TM1 inner join @outputData OTP on TM1.Distance_Feature = OTP.Distance_Feature and TM1.Speeding_Feature = OTP.Speeding_Feature
Datasource: data_1024_kMeans.xls (293,5 kB)