sql server 同一列的相同数据 则给与同一个一个编号

[qq_41679032]

之前找大神处理过，但是行数少的话可以处理，关键我这数据量有好几万行数
跑了十几分钟跑不出来 TOP 100的话 5秒左右几秒出来 ,
我用 DENSE_RANK() 函数能直接给分组排序 但是还得判断一层时间差值是否大于24小时才行，刚刚用sql 不是很熟悉，小白一个，得怎样写，求助，
例如：有A、B、C 三列 ：
序号 城市 时间差值
1 北京 0
2 北京 150.1
3 北京 15.1
4 北京 60.2
5 上海 0
6 上海 22.3
7 南京 0
8 南京 5.6
9 深圳 0
10 深圳 22.1

以B列和C列为标准 首先判断B列相同 并且本次出现时间与上次时间大于24小时为有效 否则 标识为相同编号

1和2行 3和4行， 城市列均为 ‘北京’ 但是时间差值大于24小时(这个值是之前处理过得到的，上下两行日期的差值) 则标识不同 。
4、5行 城市列不同 则标识不同，以此类推，得到一下预期结果：

序号 城市 时间差值 标识ID
1 北京 0 1
2 北京 150.1 2
3 北京 15.1 2
4 北京 60.2 3
5 上海 0 4
6 上海 22.3 4
7 南京 0 5
8 南京 5.6 5
9 深圳 0 6
10 深圳 22.1 6

[shoppo0505]

看不太懂需求。

[keueng]

试试看:

IF OBJECT_ID(N'tempdb..#t') IS NOT NULL
	DROP TABLE #t
GO

CREATE TABLE #t
(
	序号		INT
	, 城市		VARCHAR(10)
	, 时间减值	FLOAT	
)

INSERT INTO #t VALUES 
(1,'北京',0),
(2,'北京',150.1),
(3,'北京',15.1),
(4,'北京',60.2),
(5,'上海',0),
(6,'上海',22.3),
(7,'南京',0),
(8,'南京',5.6),
(9,'深圳',0),
(10,'深圳',22.1);

WITH CTE
	AS
	(
		SELECT	A.*
				, B.[城市] AS 城市B
		FROM	#t AS A
				LEFT JOIN #t AS B
					ON A.序号 = B.序号 + 1
	)
	, CTEB
	AS
	(
		SELECT	*
				, CASE 
					WHEN 城市 = 城市B AND 时间减值 >= 24 THEN 1
					WHEN 城市 != 城市B THEN 1
					ELSE 0
				  END 标识ID2
		FROM	CTE 
	)

SELECT	A.序号
		, A.城市
		, A.时间减值
		, 标识ID = (
			SELECT	SUM(标识ID2) + 1
			FROM	CTEB AS B
			WHERE	B.序号 <= A.序号
		)
FROM	CTEB AS A

[keueng]

贴一下结果

[jaki-egg]

use Tempdb
go
if not object_id(N'Tempdb..#tabA') is null
    drop table #tabA
Go
Create table #tabA(id int identity(1,1),[城市] nvarchar(20),[时间差值] float)
INSERT INTO #tabA VALUES ('北京','0')
INSERT INTO #tabA VALUES ('北京','150.1')
INSERT INTO #tabA VALUES ('北京','15.1')
INSERT INTO #tabA VALUES ('北京','60.2')
INSERT INTO #tabA VALUES ('上海','0')
INSERT INTO #tabA VALUES ('上海','22.3')
INSERT INTO #tabA VALUES ('南京','0')
INSERT INTO #tabA VALUES ('南京','5.6')
INSERT INTO #tabA VALUES ('深圳','0')
INSERT INTO #tabA VALUES ('深圳','22.1')
Go

select id,城市,时间差值,标识ID=dense_rank() over (order by 城市,时间差) from 
(select a.* , 时间差 =case when abs(a.时间差值-b.时间差值)>24 then a.id else 0 end from #tabA as a left join #tabA as b on a.城市=b.城市 and a.id=b.id+1) c

不懂为啥2,3的标识是一样的你看看这样行吗？

[zjcxc]

测试 10 万记录，可以在几秒内完成

-- 10 万测试数据
SELECT TOP(10000*10)
	序号 = IDENTITY(int, 1, 1), 城市 = O.name, 时间差值=ABS(CHECKSUM(NEWID()))%47
INTO #t
FROM sys.all_objects O, sys.all_objects B
;

-- 查询( LAG 函数取上条记录值，在 >=2012 的版本中支持）
SELECT *, SUM(_r)OVER(ORDER BY 城市, 序号)
FROM(
	SELECT *,
		CASE CASE WHEN 时间差值 > 24 THEN 1 ELSE 0 END
			WHEN LAG(CASE WHEN 时间差值 > 24 THEN 1 ELSE 0 END, 1, 2)OVER(PARTITION BY 城市 ORDER BY 序号)
			THEN 0 ELSE 1
		END as _r
	FROM #t
)A
ORDER BY 城市, 序号
GO
DROP TABLE #t

[zjcxc]

好像没理解对， 似乎应该是： 时间差值 > 24 or 城市与上条不同时，序号加1 如果是的话，用下面这个

SELECT *, DENSE_RANK()OVER(ORDER BY 城市, _r)
FROM(
	SELECT *,
		SUM(CASE WHEN 时间差值 > 24 THEN 1 END) OVER(PARTITION BY 城市 ORDER BY 序号) as _r
	FROM #t
)a
ORDER BY 城市, 序号

[keueng]

引用 6 楼 zjcxc 的回复:

好像没理解对， 似乎应该是： 时间差值 > 24 or 城市与上条不同时，序号加1 如果是的话，用下面这个

SELECT *, DENSE_RANK()OVER(ORDER BY 城市, _r)
FROM(
	SELECT *,
		SUM(CASE WHEN 时间差值 > 24 THEN 1 END) OVER(PARTITION BY 城市 ORDER BY 序号) as _r
	FROM #t
)a
ORDER BY 城市, 序号

版主对开窗函数的理解真心厉害..