MSSQL 基础之数据按数量分组汇总

浏览: 2806

引言

前段时间同事在项目中遇到这样一个问题:有一张表需要对数据做分组统计,这完全没有问题。但是汇总的时候要求记录数超过450的数据,要按450为一组聚合。举个例子:假设有个分组有901条数据,则需要450条一组、450条一组、1条一组,划分成3个组。最初是使用游标来完成的,但是这个存储过程的调用频率实在太高,经常会导致死锁。在强大的视游标如饿虎的反对声音下,决定做优化处理。

构造数据

因为不方便用真实数据来演示,我们先构造这样的一个表。So easy,B老师也说过,构造数据也是一种能力。

--step1、创建表
CREATE TABLE TEST
(
ID INT IDENTITY(1,1) PRIMARY KEY, --ID
Name VARCHAR(32) NOT NULL, --姓名
ClassName VARCHAR(32) NOT NULL, --班级
Credit INT --学分
)
GO
--step2、构造数据
--外层循环10次,构造10个组
DECLARE @i INT = 0
DECLARE @j INT
DECLARE @RowCount INT
WHILE(@i<10)
BEGIN
--内层循环,每组随机生成记录数
SET @j = 1
SET @RowCount = CEILING(RAND() * 100) --100以内随机
WHILE(@j <@RowCount)
BEGIN
INSERT INTO TEST
(Name , ClassName , Credit )
VALUES ( '张三'+CAST(@j AS VARCHAR) -- Name - varchar(32)
, CHAR(65+@i) -- ClassName - varchar(32)
, 10 -- Credit - int
)
SET @j = @j + 1
END

SET @i = @i + 1
END
GO
--step3、预览数据
SELECT ClassName
, COUNT(*)
FROM TEST
GROUP BY ClassName;


尝试1:游标

经过一番讨论,得出一个优化游标的解决方案:

1、先取出表中记录数超过450(我们这儿按40一组处理)的记录,并且返回总数

SELECT  ClassName
, COUNT(*)
FROM TEST
GROUP BY ClassName
HAVING COUNT(*)>40


2、循环上面的结果集,利用分页原理分组数据

DECLARE @ClassName VARCHAR(32)  --班级名称
DECLARE @MaxRow INT --最大记录数
DECLARE @PageSize DECIMAL = 40.0 --分组页数
DECLARE @PageIndex INT --当前页数

DECLARE _cursor CURSOR
FOR
SELECT ClassName
, COUNT(*) max_Row
FROM TEST
GROUP BY ClassName
HAVING COUNT(*) > 40; --过滤>40的,即需要分组
OPEN _cursor
FETCH NEXT FROM _cursor INTO @ClassName,@MaxRow
WHILE(@@FETCH_STATUS>0)
BEGIN
SELECT *,ROW_NUMBER() OVER() rn
INTO #TEMP_TEST
FROM TEST
WHERE ClassName = @ClassName --给数据编号

--利用分页原理,按页汇总数据
SET @PageIndex = 1
WHILE(@PageIndex <= CEILING(@MaxRow / @PageSize))
BEGIN
SELECT * FROM #TEMP_TEST
WHERE rn BETWEEN (@PageIndex - 1) * @PageSize + 1
AND @PageIndex * @PageSize

SET @PageIndex = @PageIndex + 1
END

DROP TABLE #TEMP_TEST

FETCH NEXT FROM _cursor INTO @ClassName,@MaxRow
END
GO

很有逻辑的一段代码,这样处理后循环次数也并不多,本身还有继续优化的空间。但是大家对于游标这个东东还是谈虎色变,强烈要求换掉。最关键的是还附了一句,这么多的DB不会想不到一种不用游标的办法吧。木有办法,为了DB人的Face,努力奋斗……

尝试2:CTE递归

去掉游标,又要保证数据能按450一组,延着上面的思路想到了CTE递归。

step1、给所有数据分组内编号

step2、主查询全部分组,递归查询取主查询中记录数大于450的和明细数据关联再分组

step3、利用自定义的深度字段,配合分页逻辑汇总数据

WITH tmp AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY ClassName ORDER BY ID) rn
FROM TEST
)
, tmp2 AS
(
SELECT ClassName,COUNT(*) row_num, 1 AS lv
FROM tmp
GROUP BY ClassName
HAVING COUNT(*)>40
UNION ALL
SELECT tmp2.ClassName,COUNT(*),CAST(lv+1 AS INT)
FROM tmp
JOIN tmp2 ON tmp2.ClassName = tmp.ClassName
GROUP BY tmp2.ClassName,tmp2.lv
)
SELECT * FROM tmp2


理想很丰满,现实多骨干,还没有走到最后一步就遇到了限制……这个怎么突破……

尝试3:数据量分组

就在这个时候羊肉泡馍来了,…………………………………………,继续…………

突然灵光一现,我这样的折腾为的是什么,不就是为了把超过450的数据再分组嘛。怎么样把超450的数据再按450分成一组呢。分组内分组,分组内分组……于是有了下面的尝试,将一组区间归成一组。举例:901条数据 1-450,451-900,901-901。除以450,1-450除以450.0结果为0.x-1之间的数,451-900除以450.0结果为1.x-2之间的数,901除以450.0结果为2.x。再配合上Ceiling天花板函数,结果为1,2,3。这不就将数据分成三组了嘛,Oh My God!!!真想骂自己一顿……

WITH tmp AS
(
SELECT *,Count(*) OVER(PARTITION BY ClassName) max_Count,
ROW_NUMBER() OVER(PARTITION BY ClassName ORDER BY ID) rn
FROM TEST
)
SELECT ClassName,MAX(max_Count),COUNT(*),CEILING(rn/40.0)
FROM tmp
GROUP BY ClassName,CEILING(rn/40.0)
ORDER BY MAX(max_Count) DESC,CEILING(rn/40.0)



完美,但是上面的写法是在分组字段是个计算列,恐怕这样会影响性能的。我们是不是可以在生成序号的时候就把序号划分成组呢。

WITH tmp AS
(
SELECT *,Count(*) OVER(PARTITION BY ClassName) max_Count,
CEILING(ROW_NUMBER() OVER(PARTITION BY ClassName ORDER BY ID)/40.0) rn
FROM TEST
)
SELECT ClassName,MAX(max_Count),COUNT(*),rn
FROM tmp
GROUP BY ClassName,rn
ORDER BY MAX(max_Count) DESC,rn



面对问题的时候,思路真的很重要。当一开始我们就复杂化这个事情的时候,我们就错了。所以只能越走越远,有时候走不下去的时候,就是回归本质的时候,共勉。

推荐 3
本文由 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

1 个评论

这白天写博客,晚上录视频的节奏,辛苦。

要回复文章请先登录注册