DBSCAN聚类算法-SQL实现

 

CREATE TABLE #T
(
	p VARCHAR(10),
	x DECIMAL(18,6),
	y DECIMAL(18,6),
	j VARCHAR(10),
	c BIT DEFAULT 0 
)

INSERT INTO #t(p,x,y) VALUES('p1',1,2)
INSERT INTO #t(p,x,y) VALUES('p2',2,1)
INSERT INTO #t(p,x,y) VALUES('p3',2,4)
INSERT INTO #t(p,x,y) VALUES('p4',4,3)
INSERT INTO #t(p,x,y) VALUES('p5',5,8)
INSERT INTO #t(p,x,y) VALUES('p6',6,7)
INSERT INTO #t(p,x,y) VALUES('p7',6,9)
INSERT INTO #t(p,x,y) VALUES('p8',7,9)
INSERT INTO #t(p,x,y) VALUES('p9',9,5)
INSERT INTO #t(p,x,y) VALUES('p10',1,12)
INSERT INTO #t(p,x,y) VALUES('p11',3,12)
INSERT INTO #t(p,x,y) VALUES('p12',5,12)
INSERT INTO #t(p,x,y) VALUES('p13',3,3)
 

SELECT TOP 0 * INTO #M FROM #T 


DECLARE @eps INT=3 
DECLARE @MinPts INT=3
DECLARE @cnt INT 
DECLARE @i INT =0
DECLARE @p VARCHAR(10)
DECLARE @x DECIMAL(18,6)
DECLARE @y DECIMAL(18,6)


SELECT TOP 1 @p=p,@x=x,@y=y FROM #t WHERE c=0
WHILE ISNULL(@p,'')<>''
BEGIN 
	UPDATE #t SET c=1 WHERE p=@p 
	
	INSERT INTO #M(p,x,y,c)
	SELECT p,x,y,0 FROM 
	(
		SELECT p,x,y,SQRT(POWER(@x-x,2)+POWER(@y-y,2)) AS eps 
		FROM #T
	) a WHERE  eps<=@eps

	SELECT @cnt=COUNT(1) FROM #M 
	IF @cnt>=@MinPts
	BEGIN
		UPDATE #m SET c=1 WHERE p=@p 
	 
		SELECT TOP 1 @p=p,@x=x,@y=y FROM #m WHERE c=0 
		WHILE ISNULL(@p,'')<>''
		BEGIN
			UPDATE #m SET c=1 WHERE p=@p 
			
			INSERT INTO #M(p,x,y,c)
			SELECT p,x,y,0 FROM 
			(
				SELECT p,x,y,SQRT(POWER(@x-x,2)+POWER(@y-y,2)) AS eps FROM #T a where c=0 
			) a WHERE  eps<=@eps AND NOT EXISTS(SELECT 1 FROM #M WHERE p=a.p)
			
			SET @p=NULL
			SELECT TOP 1 @p=p,@x=x,@y=y FROM #m WHERE c=0 
		END 
		SET @i=@i+1
		UPDATE a  SET c=1,j='C'+CONVERT(VARCHAR(10),@i)
		FROM #T a INNER JOIN #M b ON b.p = a.p
	END 	
	 
	TRUNCATE TABLE #M

	SET @p=NULL
	SELECT TOP 1 @p=p,@x=x,@y=y FROM #t WHERE c=0
END 

SELECT * FROM #T
 

DROP TABLE #M
DROP TABLE #T

  算法描述:https://blog.csdn.net/dsdaasaaa/article/details/94590159