K最近邻算法

/*
1.存在一个样本数据集合,也称为训练样本集,并且样本集中每个数据都存在标签
2.计算待分类数据与训练集中每一个样本之间的距离
3.找出与待分类样本距离最近的k个样本
4.观测这k个样本的分类情况
5.把出现次数最多的类别作为待分类数据的类别
*/
CREATE TABLE #T
(
	name VARCHAR(100),
	ttype VARCHAR(100),
	item VARCHAR(100),
	value INT
)

INSERT INTO #T VALUES('宝贝当家','喜剧','搞笑',45)
INSERT INTO #T VALUES('宝贝当家','喜剧','拥抱',2)
INSERT INTO #T VALUES('宝贝当家','喜剧','打斗',9)
INSERT INTO #T VALUES('澳门风云','喜剧','搞笑',54)
INSERT INTO #T VALUES('澳门风云','喜剧','拥抱',9)
INSERT INTO #T VALUES('澳门风云','喜剧','打斗',11)
INSERT INTO #T VALUES('功夫熊猫','喜剧','搞笑',39)
INSERT INTO #T VALUES('功夫熊猫','喜剧','拥抱',0)
INSERT INTO #T VALUES('功夫熊猫','喜剧','打斗',31)
INSERT INTO #T VALUES('美人鱼','喜剧','搞笑',21)
INSERT INTO #T VALUES('美人鱼','喜剧','拥抱',17)
INSERT INTO #T VALUES('美人鱼','喜剧','打斗',5)
INSERT INTO #T VALUES('谍影重重','动作片','搞笑',5)
INSERT INTO #T VALUES('谍影重重','动作片','拥抱',2)
INSERT INTO #T VALUES('谍影重重','动作片','打斗',57)
INSERT INTO #T VALUES('叶问','动作片','搞笑',3)
INSERT INTO #T VALUES('叶问','动作片','拥抱',2)
INSERT INTO #T VALUES('叶问','动作片','打斗',65)
INSERT INTO #T VALUES('伦敦陷落','动作片','搞笑',2)
INSERT INTO #T VALUES('伦敦陷落','动作片','拥抱',3)
INSERT INTO #T VALUES('伦敦陷落','动作片','打斗',55)
INSERT INTO #T VALUES('我的特工爷爷','动作片','搞笑',6)
INSERT INTO #T VALUES('我的特工爷爷','动作片','拥抱',4)
INSERT INTO #T VALUES('我的特工爷爷','动作片','打斗',21)
INSERT INTO #T VALUES('奔爱','爱情片','搞笑',7)
INSERT INTO #T VALUES('奔爱','爱情片','拥抱',46)
INSERT INTO #T VALUES('奔爱','爱情片','打斗',4)
INSERT INTO #T VALUES('夜孔雀','爱情片','搞笑',9)
INSERT INTO #T VALUES('夜孔雀','爱情片','拥抱',39)
INSERT INTO #T VALUES('夜孔雀','爱情片','打斗',8)
INSERT INTO #T VALUES('代理情人','爱情片','搞笑',9)
INSERT INTO #T VALUES('代理情人','爱情片','拥抱',38)
INSERT INTO #T VALUES('代理情人','爱情片','打斗',2)
INSERT INTO #T VALUES('新步步惊心','爱情片','搞笑',8)
INSERT INTO #T VALUES('新步步惊心','爱情片','拥抱',34)
INSERT INTO #T VALUES('新步步惊心','爱情片','打斗',17)   
 
              
CREATE TABLE #C
(
	name VARCHAR(100),
	item VARCHAR(100),
	value INT
)
INSERT INTO #C VALUES('唐人街探案','搞笑',23)   
INSERT INTO #C VALUES('唐人街探案','拥抱',3)    
INSERT INTO #C VALUES('唐人街探案','打斗',17)    


DECLARE @K INT=5
 
SELECT name,ttype FROM 
(  
	SELECT *,ROW_NUMBER() OVER(PARTITION BY b.name ORDER BY c DESC) AS i  
	FROM 
	(  
		SELECT *,COUNT(1) OVER(PARTITION BY a.name,a.ttype) AS c 
		FROM 
		(          
			SELECT TOP(@K)b.name,a.ttype,SQRT(SUM(POWER(a.value-b.value,2))) AS j 
			FROM #T a INNER JOIN #C b ON  b.item = a.item
			GROUP  BY a.name,a.ttype,b.name
			ORDER BY j 
		) a 
	) b 
) c 
WHERE i=1

DROP TABLE #T
DROP TABLE #C