db2分组小计.docx
- 文档编号:23636432
- 上传时间:2023-05-19
- 格式:DOCX
- 页数:16
- 大小:204.81KB
db2分组小计.docx
《db2分组小计.docx》由会员分享,可在线阅读,更多相关《db2分组小计.docx(16页珍藏版)》请在冰豆网上搜索。
db2分组小计
【DB2】GROUPBY子句(rollup,cube,groupingsets)实例说明
将通过实例来说明groupby子句的3种样式的作用:
1.GROUPBY
语句:
selectzt,qylx_dm,sum(zczb),count(bs)fromdj_ztgroupbyzt,qylx_dm
结果:
可见,groupbyzt,qylx_dm的作用是,先对ZT进行分组,在同一ZT下,对QYLX_DM进行分组。
2.GROUPBYROLLUP
语句:
selectzt,qylx_dm,sum(zczb),count(bs)fromdj_ztgroupbyrollup(zt,qylx_dm)
结果:
可见,groupbyrollup(zt,qylx_dm)的作用是,先对ZT进行分组,输出按状态分组的结果(第2行,第3行),然后再按照ZT分组,并对分组后的ZT按照QYLX_DM进行再分组,输出结果(第4到9行);在第一行输出统计的总和(1=2+3=4+5+6+7+8+9)。
即他是将同一结果集按照不同的分组条件分别输出了2次。
3.GROUPBYCUBE
语句:
selectzt,qylx_dm,sum(zczb),count(bs)fromdj_ztgroupbycube(zt,qylx_dm)
结果:
可见,GROUPBYCUBE(ZT,QYLX_DM)的作用是:
先按照QYLX_DM分组,输出(1,2,3,4,5行);然后输出合计(6行);然后按照ZT分组,输出(7,8行);然后在按照ZT和QYLX_DM分组输出(9-14行)。
即,其实他是将同一结果集按照3种条件分别GROUP并输出了3次。
4.GROUPBYGROUPINGSETS
语句:
selectzt,qylx_dm,sum(zczb),count(bs)fromdj_ztgroupbygroupingsets(zt,qylx_dm)
结果:
可见此时其作用等同于CUBE的前2次分组。
缺失了第三次分组(即同时按照ZT和QYLX_DM分组)和合计行。
其实GROUPBYGROUPINGSETS也是可以输出合计的,只需要加一对空括号即可:
selectzt,qylx_dm,sum(zczb),count(bs)fromdj_ztgroupbygroupingsets(zt,qylx_dm,())
结果:
其中空括号的位置是没有影响的。
而且合计行必然位于第一行。
#OVER
--分组小计
select
YBHTJS_DETAIL_ID
FIXER_ID--维修工ID
casewhengrouping(YBHTJS_DETAIL_ID)=0thenmax(FIXER_CODE)elsenullend--维修工代码
casewhengrouping(YBHTJS_DETAIL_ID)=0thenmax(FIXER_NAME)
whengrouping(FIXER_ID)=0andgrouping(YBHTJS_DETAIL_ID)=1thenmax(FIXER_NAME)||'小计'
else'合计'end--维修工姓名
sum(CONTRACT_PRICE)
sum(RETAIL_PRICE)
count
(1)
fromECC_YB.T_YBHTJS_DETAIL
whereFIXER_ID<>0
groupbyrollup(FIXER_ID,YBHTJS_DETAIL_ID)
orderbyFIXER_ID,YBHTJS_DETAIL_ID
;
1.GroupBy语句简介:
GroupBy语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。
它的作用是通过一定的规则将一个数据集划分成若干个小的区域,然后针对若干个小区域进行数据处理。
P.S.这里真是体会到了一个好的命名的力量,GroupBy从字面是直接去理解是非常好理解的。
恩,以后在命名的环节一定要加把劲:
)。
话题扯远了。
2.GroupBy的使用:
上面已经给出了对GroupBy语句的理解。
基于这个理解和SQLServer2000的联机帮助,下面对GroupBy语句的各种典型使用进行依次列举说明。
2.1GroupBy[Expressions]:
这个恐怕是GroupBy语句最常见的用法了,GroupBy+[分组字段](可以有多个)。
在执行了这个操作以后,数据集将根据分组字段的值将一个数据集划分成各个不同的小组。
比如有如下数据集,其中水果名称(FruitName)和出产国家(ProductPlace)为联合主键:
FruitName
ProductPlace
Price
Apple
China
$1.1
Apple
Japan
$2.1
Apple
USA
$2.5
Orange
China
$0.8
Banana
China
$3.1
Peach
USA
$3.0
如果我们想知道每个国家有多少种水果,那么我们可以通过如下SQL语句来完成:
SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国
FROMT_TEST_FRUITINFO
GROUPBYProductPlace
这个SQL语句就是使用了GroupBy+分组字段的方式,那么这句SQL语句就可以解释成“我按照出产国家(ProductPlace)将数据集进行分组,然后分别按照各个组来统计各自的记录数量。
”很好理解对吧。
这里值得注意的是结果集中有两个返回字段,一个是ProductPlace(出产国),一个是水果种类。
如果我们这里水果种类不是用Count(*),而是类似如下写法的话:
SELECTFruitName,ProductPlaceFROMT_TEST_FRUITINFOGROUPBYProductPlace
那么SQL在执行此语句的时候会报如下的类似错误:
选择列表中的列'T_TEST_FRUITINFO.FruitName'无效,因为该列没有包含在聚合函数或GROUPBY子句中。
这就是我们需要注意的一点,如果在返回集字段中,这些字段要么就要包含在GroupBy语句的后面,作为分组的依据;要么就要被包含在聚合函数中。
我们可以将GroupBy操作想象成如下的一个过程,首先系统根据SELECT语句得到一个结果集,如最开始的那个水果、出产国家、单价的一个详细表。
然后根据分组字段,将具有相同分组字段的记录归并成了一条记录。
这个时候剩下的那些不存在于GroupBy语句后面作为分组依据的字段就有可能出现多个值,但是目前一种分组情况只有一条记录,一个数据格是无法放入多个数值的,所以这里就需要通过一定的处理将这些多值的列转化成单值,然后将其放在对应的数据格中,那么完成这个步骤的就是聚合函数。
这就是为什么这些函数叫聚合函数(aggregatefunctions)了。
2.2GroupByAll[expressions]:
GroupByAll+分组字段,这个和前面提到的GroupBy[Expressions]的形式多了一个关键字ALL。
这个关键字只有在使用了where语句的,且where条件筛选掉了一些组的情况才可以看出效果。
在SQLServer2000的联机帮助中,对于GroupByAll是这样进行描述的:
如果使用ALL关键字,那么查询结果将包括由GROUPBY子句产生的所有组,即使某些组没有符合搜索条件的行。
没有ALL关键字,包含GROUPBY子句的SELECT语句将不显示没有符合条件的行的组。
其中有这么一句话“如果使用ALL关键字,那么查询结果将包含由GroupBy子句产生的所有组...没有ALL关键字,那么不显示不符合条件的行组。
”这句话听起来好像挺耳熟的,对了,好像和LEFTJOIN和RIGHTJOIN有点像。
其实这里是类比LEFTJOIN来进行理解的。
还是基于如下这样一个数据集:
FruitName
ProductPlace
Price
Apple
China
$1.1
Apple
Japan
$2.1
Apple
USA
$2.5
Orange
China
$0.8
Banana
China
$3.1
Peach
USA
$3.0
首先我们不使用带ALL关键字的GroupBy语句:
SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国
FROMT_TEST_FRUITINFO
WHERE(ProductPlace<>'Japan')
GROUPBYProductPlace
那么在最后结果中由于Japan不符合where语句,所以分组结果中将不会出现Japan。
现在我们加入ALL关键字:
SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国
FROMT_TEST_FRUITINFO
WHERE(ProductPlace<>'Japan')
GROUPBYALLProductPlace
重新运行后,我们可以看到Japan的分组,但是对应的“水果种类”不会进行真正的统计,聚合函数会根据返回值的类型用默认值0或者NULL来代替聚合函数的返回值。
2.3GROUPBY[Expressions]WITHCUBE|ROLLUP:
首先需要说明的是GroupByAll语句是不能和CUBE和ROLLUP关键字一起使用的。
首先先说说CUBE关键字,以下是SQLServer2000联机帮助中的说明:
指定在结果集内不仅包含由GROUPBY提供的正常行,还包含汇总行。
在结果集内返回每个可能的组和子组组合的GROUPBY汇总行。
GROUPBY汇总行在结果中显示为NULL,但可用来表示所有值。
使用GROUPING函数确定结果集内的空值是否是GROUPBY汇总值。
结果集内的汇总行数取决于GROUPBY子句内包含的列数。
GROUPBY子句中的每个操作数(列)绑定在分组NULL下,并且分组适用于所有其它操作数(列)。
由于CUBE返回每个可能的组和子组组合,因此不论指定分组列时所使用的是什么顺序,行数都相同。
我们通常的GroupBy语句是按照其后所跟的所有字段进行分组,而如果加入了CUBE关键字以后,那么系统将根据所有字段进行分组的基础上,还会通过对所有这些分组字段所有可能存在的组合形成的分组条件进行分组计算。
由于上面举的例子过于简单,这里就再适合了,现在我们的数据集将换一个场景,一个表中包含人员的基本信息:
员工所在的部门编号(C_EMPLINFO_DEPTID)、员工性别(C_EMPLINFO_SEX)、员工姓名(C_EMPLINFO_NAME)等。
那么我现在想知道每个部门各个性别的人数,那么我们可以通过如下语句得到:
SELECTC_EMPLINFO_DEPTID,C_EMPLINFO_SEX,COUNT(*)ASC_EMPLINFO_TOTALSTAFFNUM
FROMT_PERSONNEL_EMPLINFO
GROUPBYC_EMPLINFO_DEPTID,C_EMPLINFO_SEX
但是如果我现在希望知道:
1.所有部门有多少人(这里相当于就不进行分组了,因为这里已经对员工的部门和性别没有做任何限制了,但是这的确也是一种分组条件的组合方式);
2.每种性别有多人(这里实际上是仅仅根据性别(C_EMPLINFO_SEX)进行分组);
3.每个部门有多少人(这里仅仅是根据部门(C_EMPLINFO_DEPTID)进行分组);那么我们就可以使用ROLLUP语句了。
SELECTC_EMPLINFO_DEPTID,C_EMPLINFO_SEX,COUNT(*)ASC_EMPLINFO_TOTALSTAFFNUM
FROMT_PERSONNEL_EMPLINFO
GROUPBYC_EMPLINFO_DEPTID,C_EMPLINFO_SEXWITHCUBE
那么这里你可以看到结果集中多出了很多行,而且结果集中的某一个字段或者多个字段、甚至全部的字段都为NULL,请仔细看一下你就会发现实际上这些记录就是完成了上面我所列举的所有统计数据的展现。
使用过SQLServer2005或者RDLC的朋友们一定对于矩阵的小计和分组功能有印象吧,是不是都可以通过这个得到答案。
我想RDLC中对于分组和小计的计算就是通过GroupBy的CUBE和ROLLUP关键字来实现的。
(个人意见,未证实)
CUBE关键字还有一个极为相似的兄弟ROLLUP,同样我们先从这英文入手,ROLLUP是“向上卷”的意思,如果说CUBE的组合是绝对自由的,那么ROLLUP的组合就需要有点约束了。
我们先来看看SQLServer2000的联机中对ROLLUP关键字的定义:
指定在结果集内不仅包含由GROUPBY提供的正常行,还包含汇总行。
按层次结构顺序,从组内的最低级别到最高级别汇总组。
组的层次结构取决于指定分组列时所使用的顺序。
更改分组列的顺序会影响在结果集内生成的行数。
那么这个顺序是什么呢?
对了就是GroupBy后面字段的顺序,排在靠近GroupBy的分组字段的级别高,然后是依次递减。
如:
GroupByColumn1,Column2,Column3。
那么分组级别从高到低的顺序是:
Column1>Column2>Column3。
还是看我们前面的例子,SQL语句中我们仅仅将CUBE关键字替换成ROLLUP关键字,如:
SELECTC_EMPLINFO_DEPTID,C_EMPLINFO_SEX,COUNT(*)ASC_EMPLINFO_TOTALSTAFFNUM
FROMT_PERSONNEL_EMPLINFO
GROUPBYC_EMPLINFO_DEPTID,C_EMPLINFO_SEXWITHROLLUP
和CUBE相比,返回的数据行数减少了不少。
:
),仔细看一下,除了正常的GroupBy语句后,数据中还包含了:
1.部门员工数;(向上卷了一次,这次先去掉了员工性别的分组限制)
2.所有部门员工数;(向上又卷了依次,这次去掉了员工所在部门的分组限制)。
在现实的应用中,对于报表的一些统计功能是很有帮助的。
这里还有一个问题需要补充说明一下,如果我们使用ROLLUP或者CUBE关键字,那么将产生一些小计的行,这些行中被剔除在分组因素之外的字段将会被设置为NULL,那么还存在一种情况,比如在作为分组依据的列表中存在可空的行,那么NULL也会被作为一个分组表示出来,所以这里我们就不能仅仅通过NULL来判断是不是小计记录了。
下面的例子展示了这里说得到的情况。
还是我们前面提到的水果例子,现在我们在每种商品后面增加一个“折扣列”(Discount),用于显示对应商品的折扣,这个数值是可空的,也就是可以通过NULL来表示没有对应的折扣信息。
数据集如下所示:
FruitName
ProductPlace
Price
Discount
Apple
China
$1.1
0.8
Apple
Japan
$2.1
0.9
Apple
USA
$2.5
1.0
Orange
China
$0.8
NULL
Banana
China
$3.1
NULL
Peach
USA
$3.0
NULL
现在我们要统计“各种折扣对应有多少种商品,并总计商品的总数。
”,那么我们可以通过如下的SQL语句来完成:
SELECTCOUNT(*)ASProductCount,Discount
FROMT_TEST_FRUITINFO
GROUPBYDiscountWITHROLLUP
好了,运行一下,你会发现数据都正常出来了,按照如上的数据集,结果如下所示:
ProductCount
Discount
3
NULL
1
0.8
1
0.9
1
1.0
6
NULL
好了,各种折扣的商品数量都出来了,但是在显示“没有折扣商品”和“商品小计”的时候判断上确存在问题,因为存在两条Discount为Null的记录。
是哪一条呢?
通过分析数据我们知道第一条数据(3,Null)应该对应没有折扣商品的数量,而(6,Null)应该对应所有商品的数量。
需要判断这两个具有不同意义的Null就需要引入一个聚合函数Grouping。
现在我们把语句修改一下,在返回值中使用Grouping函数增加一列返回值,SQL语句如下:
SELECTCOUNT(*)ASProductCount,Discount,GROUPING(Discount)ASExpr1
FROMT_TEST_FRUITINFO
GROUPBYDiscountWITHROLLUP
这个时候,我们再看看运行的结果:
ProductCount
Discount
Expr1
3
NULL
0
1
0.8
0
1
0.9
0
1
1.0
0
6
NULL
1
对于根据指定字段Grouping中包含的字段进行小计的记录,这里会标记为1,我们就可以通过这个标记值将小计记录从判断那些由于ROLLUP或者CUBE关键字产生的行。
Grouping(column_name)可以带一个参数,Grouping就会去判断对应的字段值的NULL是否是由ROLLUP或者CUBE产生的特殊NULL值,如果是那么就在由Grouping聚合函数产生的新列中将值设置为1。
注意Grouping只会检查Column_name对应的NULL来决定是否将值设置为1,而不是完全由此列是否是由ROLLUP或者CUBE关键字自动添加来决定的。
2.2GroupBy和Having,Where,Orderby语句的执行顺序:
最后要说明一下的GroupBy,Having,Where,Orderby几个语句的执行顺序。
一个SQL语句往往会产生多个临时视图,那么这些关键字的执行顺序就非常重要了,因为你必须了解这个关键字是在对应视图形成前的字段进行操作还是对形成的临时视图进行操作,这个问题在使用了别名的视图尤其重要。
以上列举的关键字是按照如下顺序进行执行的:
Where,GroupBy,Having,Orderby。
首先where将最原始记录中不满足条件的记录删除(所以应该在where语句中尽量的将不符合条件的记录筛选掉,这样可以减少分组的次数),然后通过GroupBy关键字后面指定的分组条件将筛选得到的视图进行分组,接着系统根据Having关键字后面指定的筛选条件,将分组视图后不满足条件的记录筛选掉,然后按照OrderBy语句对视图进行排序,这样最终的结果就产生了。
在这四个关键字中,只有在OrderBy语句中才可以使用最终视图的列名,如:
SELECTFruitName,ProductPlace,Price,IDASIDE,Discount
FROMT_TEST_FRUITINFO
WHERE(ProductPlace=N'china')
ORDERBYIDE
这里只有在ORDERBY语句中才可以使用IDE,其他条件语句中如果需要引用列名则只能使用ID,而不能使用IDE。
使用SQL2005递归查询结合Row_Number()实现完全SQL端树排序
在实际应用中,我们经常需要用到树型结构功能,数据库结构一般如下
即用一个ParentID来标识该节点从属关系。
为了最终生成一棵树,一般做法是把记录选出来,然后在程序里递归重新排好序后再呈现出来,但是如果有大量数据,就带来了性能开销问题。
那么能不能直接在数据库利用SQL语句排好树后再输出呢?
SQL2005有个递归查询功能也就是WITH..AS语句。
对上面这样的表格使用递归查询,可以查询得到某树支下(包括根)的所有节点记录。
类似语句如下:
with RelClass
as
(
select * from CMS_Site_Class where ClassID = 1
union all
select csc.* from CMS_Site_Class as csc inner join RelClass as rc on csc.ClassID_Parent = rc.ClassID )
SELECT * from RelClass
将得到ClassID为1的根节点下的所有记录:
但是这个记录集显然没有经过树排序,这时还需要程序里进一步处理才能输出到客户端。
在这里我介绍一种WITH结合Row_Number()实现SQL端排序的方法。
先来看看最终的代码:
Code
-- =============================================
-- Author:
-- Create date:
<2009-2-5>
-- Description:
<将指定Int数据左填0到指定宽度>
-- =============================================
CREATE FUNCTION dbo.Lpad
(
@i int,@len int
)
RETURNS nvarchar(max)
AS
BEGIN
RETURN cast (replicate('0', @len - len(@i) ) + convert(nvarchar,@i) as nvarchar(max))
END
-- =============================================
-- Author:
-- Create date:
<2009-2-5>
-- Description:
<生成已排序的树>
-- =============================================
Create PROCEDURE [dbo].[pCMS_Site_Class__GetList]
@ClassID int
AS
BEGIN
with RelClass
as
(
select *,0 as Level,cast('0' as nvarchar(max)) as treepath from CMS_Site_Class where ClassID = @ClassID
union all
select csc.*,rc.[Level] + 1,rc.treepath + dbo.Lpad(Row_Number() over (order by csc.OrderID desc),8) as treepath from CMS_Site_Class as csc inner join RelClass as rc on csc.ClassID_Parent = rc.ClassID )
SEL
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- db2 分组 小计