清除回答草稿
&&&您需要以后才能回答，未注册用户请先。博客访问： 143119
博文数量： 26
注册时间：
分类： Linux 17:02:39
<font class="Apple-style-span" color="#）COUNT
语法：COUNT(e1)
参数：e1为一个表达式，可以是任意的数据类型
返回：返回数值型数据
作用：返回e1指定列不为空的记录总数<font class="Apple-style-span" color="#）SUM,
语法：SUM(e1)
参数：e1为类型为数值型的表达式
返回：返回数值型数据
作用：对e1指定的列进行求和计算<font class="Apple-style-span" color="#）MIN, MAX
语法：MIN(e1)、MAX(e1)
参数：e1为一个字符型、日期型或数值类型的表达式。
若e1为字符型，则根据ASCII码来判断最大值与最小值。
返回：根据e1参数的类型，返回对应类型的数据。
作用：MIN(e1)返回e1表达式指定的列中最小值；
& & &MAX(e1)返回e1表达式指定的列中最大值；<font class="Apple-style-span" color="#）AVG
语法：AVG(e1)
参数：e1为一个数值类型的表达式
返回：返回一个数值类型数据
作用：对e1表达式指定的列，求平均值。<font class="Apple-style-span" color="#）MEDIAN
语法：MEDIAN(e1)
参数：e1为一个数值或日期类型的表达式
返回：返回一个数值或日期类型的数据
作用：首先，根据e1表达式指定的列，对值进行排序；
若排序后，总记录为奇数，则返回排序队列中，位于中间的值；
若排序后，总记录为偶数，则对位于排序队列中，中间两个值进行求平均，返回这个平均值；<font class="Apple-style-span" color="#）RANK
1）用法1:RANK OVER
语法： RANK( ) &OVER ([ PARTITION BY column1 ] ORDER BY column2 [ASC|DESC])
为分析函数，为每条记录产生一个序列号，并返回。
参数： column1为列名，指定按照哪一列进行分类（分组）
& column2为列名，指定根据哪列排序，默认为升序；
& 若指定了分类子句（PARTITION BY），则对每类进行排序（每个分类单独排序）
返回：返回一个数值类型数据，作为该记录的序号！
作用：为分析函数，对记录先按column1分类，再对每个分类进行排序，并为每条记录分配一个序号（每个分类单独排序）
注意：排序字段值相同的记录，分配相同的序号。存在序号不连续的情况
实例：student表记录了学生每科的成绩，要求按学科排序，并获取每科分数前两名的记录
student表如下：
SQL> select * from s&
NAME & & & ID & & & & & & & &KEMU & & &FENSHU
---------- -------------- -------------- ----------------
Li & & & & & &0113101 & & 物理 & & & & & & & 80
Luo & & & & 0113011 & & 物理 & & & & & & & 80
Wang & & 0113077 & & 物理 & & & & & & & 70
Zhang & & 0113098 & &物理 & & & & & & & 90
Luo & & & & 0113011 & & 高数 & & & & & & & 80
Wang & & &0113077 & &高数 & & & & & & & 70
Zhang & & 0113098 & &高数 & & & & & & & 80
Li & & & & & & 0113101 & &高数 & & & & & & & 90&
8 rows selected
按学科分类，按成绩排序（降序）
SQL> select rank() over(partition by KEMU order by FENSHU desc) as sort,student.*&& & &
SORT & &NAME & & & &ID & & & & & & &KEMU & & &FENSHU
---------- ---------- ---------------- ------------ ----------&& & & &
1 & & & & & &Zhang & & &0113098 & &物理 & & & & & & & 90&& & & &
2&&& & & & &&Li & & & & & & &0113101 & &物理 & & & & & & & 80&& & & &
2&&& & & & &&Luo & & & & & 0113011 & &物理 & & & & & & & 80&& & & &
4&&& & & & &&Wang & & & 0113077 & &物理 & & & & & & & 70&& & & &
1&&& & & & &&Li & & & & & & &0113101 & &高数 & & & & & & & 90&& & & &
2&&& & & & &&Luo & & & & & 0113011 & &高数 & & & & & & & 80&& & & &
2&&& & & & &&Zhang & & &0113098 & &高数 & & & & & & & 80&& & & &
4&&& & & & &&Wang & & & 0113077 & &高数 & & & & & & & 70
由返回记录可了解，对排序列的值相同的记录，rank为其分配了相同的序号（SORT NAME列）。
并且之后的记录的序号是不连续的。
若获取每科前两名，只需对排序后的结果增加二次查询即可
select * from&
(select rank() over(partition by KEMU order by FENSHU desc) as sort_id,student.* from student) st&
where st.sort_id<=2;
2)用法2：RANK WITHIN GROUP
语法： RANK( expr1 ) WITHIN GROUP ( ORDER BY expr2 )
为聚合函数，返回一个值。
参数：expr1为1个或多个常量表达式；
&& &expr2为如下格式的表达式：
& & & & & && &expr2的格式为'expr3 [ DESC | ASC ] [ NULLS { FIRST | LAST } ]'
其中，expr1需要与expr2相匹配，
即：expr1的常量表达式的类型、数量必须与ORDER BY子句后的expr2表达式的类型、数量相同
实际是expr1需要与expr3相匹配
如：RANK(a) WITHIN GROUP (ORDER BY b ASC NULLS FIRST);
& &其中，a为常量，b需要是与相同类型的表达式
& &RANK(a,b) WITHIN GROUP (ORDER BY c DESC NULLS LAST, d DESC NULLS LAST);
& &其中，a与b都为常量；c是与a类型相同的表达式、d是与b类型相同的表达式；
返回：返回数值型数据，该值为假定记录在表中的序号。
作用：确定一条假定的记录，在表中排序后的序号。
& 如：假定一条记录（假设为r1）的expr2指定字段值为常量expr1，则将r1插入表中后，
与原表中的记录，按照ORDER BY expr2排序后，该记录r1在表中的序号为多少，返回该序号。
注释： NULLS FIRST指定，将ORDER BY指定的排序字段为空值的记录放在前边；
NULLS LAST指定，将ORDER BY指定的排序字段为空值的记录放在后边；
实例：假设一个员工的薪水为1500，求该员工的薪水在员工表中的排名为多少？
已知员工表如下：
SQL> select *
EMP_ID & & EMP_NAME & & SALARY
---------- -------------------- ---------------
10001 & & &ZhangSan & & & & & & 500
10002 & & &LiSi & & & & & & & & & & & & 1000
10003 & & &WangWu & & & & & & & 1500
10004 & & &MaLiu & & & & & & & & & & 2000
10005 & & &NiuQi & & & & & & & & & & &2500
SQL> select rank(1500) within group (order by salary) as "rank number"
rank number
-----------&& & & & &
由结果可知，薪水为1500的员工，在表中按升序排序，序号为3
<font class="Apple-style-span" color="#）FIRST、LAST
语法： agg_function（e1） KEEP (DENSE_RANK FIRST ORDER BY e2 [NULLS {FIRST|LAST}]) [OVER PARTITION BY e3 ]
agg_function（e1） KEEP (DENSE_RANK LAST &ORDER BY e2 [NULLS {FIRST|LAST}]) [OVER PARTITION BY e3 ]
参数： agg_function为一个聚合函数，可以为 MIN、MAX、SUM、AVG、COUNT、VARIANCE或STDDEV
e2指定以哪个字段为依据，进行排序；
e3指定以哪个字段为依据，进行分类（分组）；
当指定OVER PARTITION BY子句后，针对分类后的每个类单独排序；
DENSE_RANK为排序后的记录分配序号，并且序号为连续的。
NULLS {FIRST|LAST}指定排序字段e1的值若为空，则拍在序列前边（NULLS FIRST）或者后边（NULLS LAST）
DENSE_RANK后的FIRST/LAST确定选取通过DENSE_RANK排好序后的序列中，序号最小/最大的记录。序号相同时，返回多条记录
当序号相同，返回多条记录时，agg_function(e1)聚合函数继续对这多条记录的e1字段做聚合操作。
作用： 如果agg_function为min(e1)，获取排序后的FIRST或LAST的多条记录中，某字段e1的最小值
该字段不是排序关键字段e2
已知员工表有薪水字段，奖金字段。要求获取薪水最低的员工中，奖金最高的员工的记录。
已知表内容如下：
SQL> select * from emplo&
EMP_ID & & EMP_NAME & & & & & SALARY &COMMISSION
---------- ---------------------------- ------------ &------------
10001 & & &ZhangSan & & & & & & & & & &500 & & & &200
10002 & & &LiSi & & & & & & & & & & & & & & & &500 & & & &300
10003 & & &WangWu & & & & & & & & & & &500 & & & &100
10004 & & &MaLiu & & & & & & & & & & & & & 2000 & & & 500
10005 & & &NiuQi & & & & & & & & & & & & & &2500 & & & 200
10006 & & &ShangDuo & & & & & & & & & 2500 & & & 300
10007 & & &BaiQi & & & & & & & & & & & & & & 2500 & & & 400
SQL> select max(commission) keep(dense_rank first order by salary asc) as commi
COMMISSION
----------&& & &
首先，按salary排序后，获取薪水最低的记录，分别为员工1、10003三条记录。
聚合函数max(commission)对3条记录获取奖金最高的为员工10002，奖金为300。
阅读(5786) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。SQL Server汇总数据之聚合函数与分组group by
SQL Server汇总数据之聚合函数与分组group by
主要用于对数据集的的数据进行汇总统计等操作，基本是聚合函数。
聚合的基本理念：不是返回所有指定的行，而是返回一行计算得到的值（前面指定的行
的某些数据的汇总）。它们汇总了原始数据集。
1、计算总数、平均值
2、统计分析
3、解决聚合问题
4、创建动态的交叉查询
一、简单聚合
在SQL查询的逻辑流程中，聚合函数是在From子句和Where子句之后执行的，这
意味着无须使用子查询就可以在汇总（使用聚合函数）前对数据进行组装和筛选。
基本聚合（函数）
支持的数据类型
计算指定列中所有非空值的总和
计算指定列中所有非空值的平均值
numeric、string、datetime
返回指定列中最小的数字或根据排序规则返回最前面的日期或字符串
numeric、string、datetime
返回指定列中最大的数字或根据排序规则返回最前面的日期或字符串
count( [distinct] &*)
任何基于行的数据类型
计算结果集中的总行数，
count_big( [distinct] &*)
任何基于行的数据类型
与count类似，但是其返回类型是binint比count大
使用一般聚合函数时的规则：
1、由于现在SQL返回中的信息，而不是建立一个由行组成的记录集，因此查询包含
聚合函数时，每一列（列列表、表达式、或order by中的列）都必须参与聚合函数的计算。
2、聚合选项distinct的作用与Select distinct 相同，但聚合选项中的distinct消除重复的
值而不是重复的行。
注：count( distinct * )是非法的，必须指定特定的列。
count(*)计算数据集的总行数，但count(clomun名)计算在指定列中有值的总行数
由于聚合函数属于表达式，因此结果中没有列名，最好指定列名
二、在结果集中分组（使用group by ）
group by 子句将根据特定列中的值，将数据集划分成子集。将数据划分成子集后，再
对每个子集执行聚合函数，最后由聚合函数生成数据（一般是每个子集占一行。）
如果group by 子句有多列，则是根据这些列的值完全相同的行分为一组，只要group gy
指定的任何一列的值不同，都不是同一分组。
1、简单分组
如：根据Category的值的不同分组，相同的为一值，每个分组根据聚合函数，会生成
一行汇总数据
Select Category,
& count(*) as &[COUNT],
& Sum(Amount) &as [Sum]
& Avg(Amount) & as [Avg]
& Min(Amount) & as [Min]
From RawData
group by Category
这上面是采用了分组的描述信息进行了分组，所以不需要另外添加分组的描述信息。
但是一般在大型关系数据库中很少直接使用分组的描述信息作为分组依据，这就需要
额外添加分组描述信息。这就需要使用子查询和联接来实现。
三、聚合查询（5种常用的聚合问题及解决方案）
1、包含分组依据描述
下面的实例试图返回一个没有在group by中出现的列。（也称百聚合描述列）
有两种解决方案：1、在group by 子句中包容额外的列（使用联接）
2、在子查询中执行聚合函数，并在外部查询中包含额外的列（使用联接）
其中这两个方案一般都会用到联接。。
Select Category,Categoryname
sum(Amount) as [Sum]
avg(amount) as [Avg]
from RawData R&
inner join RowCategory C on R.CategoryID=C.RowCategoryID
group by &Category,C.Categoryname
order by &Category,C.Categoryname
方案2：在子查询中执行聚合函数，并在外部查询中包含额外的列
Select SQ.Category,Categoryname,SQ.[Sum],SQ.[Avg]
Select Category
sum(Amount) as [Sum]
avg(amount) as [Avg]
from RawData R&
group by &Category
inner join RowCategory C on SQ.CategoryID=C.RowCategoryID
order by SQ.Category,C.Categoryname
2、包含所有的分组依据值
Group by 分组是在where子句之后进行的。
如果查询需要返回所有分组依据列的值，但如果要显示where过滤的行，
可使用group by all 选项返回所有分组依据值。
而不管where子句如何。
select bmname 部门名称,count(bmname) 有工资的员工总数,
sum(A.basic_gz+A.jiaban_gz+A.jiangjin) 部门工资 &from gongzi A&
inner join yuangong B &on A.ygid=B.id
inner join bumen C on B.bmID=C.id
where bmname=&#39;管理部&#39;
group by all bmname
部门名称 有工资的员工总数 部门工资
管理部 & & & & &1 & & & & & & & 702
技术部 & & & & &0 & & & & & & &NULL
客户部 & & & & &0 & & & & & & &NULL
销售部 & & & & &0 & & & & & & &NULL
注：如果不加all 结果为
部门名称 有工资的员工总数 部门工资
管理部 & & & &1 & & & & & & & & &702
如果没有where 条件结果为
部门名称 有工资的员工总数 部门工资
管理部 & & & & 1 & & & & & & & &702
技术部 & & & & 2 & & & & & & & &5469
客户部 & & & & 1 & & & & & & & &1878
销售部 & & & & 1 & & & & & & & &2200
3、嵌套聚合
对聚合过的结果集，再进行聚合查询
如：根据每种类别在每年/每季度的销售情况，求每年每季度销售最好的类别的销售
情况信息。
1、先根据种类以及每年/季度进行分组，并调用相关的聚合函数
这样就得到了不同种类在每年/每季度的销售情况
2、对上一个结果集，根据每年/每季度进行分组，并计算每个分组中销售最好的。
--根据每年每季度分组，求出销售最多的一个分组。这里没有包含类别信息，如果需要
添加，可以通过再联接一个查询实现（下面会介绍到）
Select Y,Q,Max(SQ.[SUM]) as MaxSum
(--求出每年每季度不同类型的产品销售情况
select Category ,Year(SalesDate) as Y, DatePart(q,SalesDate) as Q,sum(Amount) as [SUM]
from RowData group by Category,Year(SalesDate),DatePart(q,SalesDate)&
group by Y,Q&
order by Y,Q
4、包含详细描述
一般通过子查询来实现（并使用联接），使用group by 会导致分组的不同，如果有多
列与一列分组效果是不一样的。多列必须这些列值全部相同才会成为一个分组，这样如果只
是为了添加显示依据单列分组中的某些信息，会使原来的分组发生变换，就得不到应有的信
5、筛选分组结果
SQL Select 语句的执行顺序
1、From子句使用数据源组装数据集
2、Where子句根据条件限制返回的行
3、Group By 子句组装数据子集
4、对每个分组执行聚合函数
5、having 子句筛选数据子集
6、计算表达式
7、Order By 子句对结果进行排序
SQL Server使用Having 子句来筛选分组
--根据每年每季度分组，求出销售最多的一个分组。这里没有包含类别信息，如果需要
添加，可以通过再联接一个查询实现
Select Y,Q,Max(SQ.[SUM]) as MaxSum
(--求出每年每季度不同类型的产品销售情况
select Category ,Year(SalesDate) as Y, DatePart(q,SalesDate) as Q,sum(Amount)&
from RowData group by Category,Year(SalesDate),DatePart(q,SalesDate)&
group by Y,Q&
having avg(Amount)&25
order by Y,Q}