SQL Server汇总数据之聚合函数与分组

最新推荐文章于 2024-02-16 20:50:27 发布

捏捏小脸蛋

最新推荐文章于 2024-02-16 20:50:27 发布

阅读量6.4k

点赞数

分类专栏：转载收集文章标签： sql 数据库连接分组 group by

转载同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

收集

2 篇文章 0 订阅

订阅专栏

主要用于对数据集的的数据进行汇总统计等操作，基本是聚合函数。

聚合的基本理念：不是返回所有指定的行，而是返回一行计算得到的值（前面指定的行

的某些数据的汇总）。它们汇总了原始数据集。

1、计算总数、平均值

2、统计分析

3、解决聚合问题

4、创建动态的交叉查询

一、简单聚合

在SQL查询的逻辑流程中，聚合函数是在From子句和Where子句之后执行的，这

意味着无须使用子查询就可以在汇总（使用聚合函数）前对数据进行组装和筛选。

基本聚合（函数）

聚合函数

支持的数据类型

描述

sum( )

Numeric

计算指定列中所有非空值的总和

avg()

numeric

计算指定列中所有非空值的平均值

min()

numeric、string、datetime

返回指定列中最小的数字或根据排序规则返回最前面的日期或字符串

max()

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的值的不同分组，相同的为一值，每个分组根据聚合函数，会生成

一行汇总数据

<span style="font-family:SimSun;font-size:14px;">Select Category,

count(*) as [COUNT],

Sum(Amount) as [Sum]

Avg(Amount) as [Avg]

Min(Amount) as [Min]

From RawData

group by Category</span>

这上面是采用了分组的描述信息进行了分组，所以不需要另外添加分组的描述信息。

但是一般在大型关系数据库中很少直接使用分组的描述信息作为分组依据，这就需要

额外添加分组描述信息。这就需要使用子查询和联接来实现。

三、聚合查询（5种常用的聚合问题及解决方案）

1、包含分组依据描述

下面的实例试图返回一个没有在group by中出现的列。（也称百聚合描述列）

有两种解决方案：1、在group by 子句中包容额外的列（使用联接）

2、在子查询中执行聚合函数，并在外部查询中包含额外的列（使用联接）

其中这两个方案一般都会用到联接。。

方案1：

<span style="font-family:SimSun;font-size:14px;">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</span>

方案2：在子查询中执行聚合函数，并在外部查询中包含额外的列

<span style="font-family:SimSun;font-size:14px;">Select SQ.Category,Categoryname,SQ.[Sum],SQ.[Avg]

from 

(

Select Category

sum(Amount) as [Sum]

avg(amount) as [Avg]

from RawData R 

group by Category

) as SQ

inner join RowCategory C on SQ.CategoryID=C.RowCategoryID

order by SQ.Category,C.Categoryname</span>

2、包含所有的分组依据值

Group by 分组是在where子句之后进行的。

如果查询需要返回所有分组依据列的值，但如果要显示where过滤的行，

可使用group by all 选项返回所有分组依据值。

而不管where子句如何。

如：

<span style="font-family:SimSun;font-size:14px;">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='管理部'

group by all bmname</span>

结果：

部门名称有工资的员工总数部门工资

管理部 1 702

技术部 0 NULL

客户部 0 NULL

销售部 0 NULL

注：如果不加all 结果为

部门名称有工资的员工总数部门工资

管理部 1 702

如果没有where 条件结果为

部门名称有工资的员工总数部门工资

管理部 1 702

技术部 2 5469

客户部 1 1878

销售部 1 2200

3、嵌套聚合

对聚合过的结果集，再进行聚合查询

如：根据每种类别在每年/每季度的销售情况，求每年每季度销售最好的类别的销售

情况信息。

1、先根据种类以及每年/季度进行分组，并调用相关的聚合函数

这样就得到了不同种类在每年/每季度的销售情况

2、对上一个结果集，根据每年/每季度进行分组，并计算每个分组中销售最好的。

SQL语句：

--根据每年每季度分组，求出销售最多的一个分组。这里没有包含类别信息，如果需要

添加，可以通过再联接一个查询实现（下面会介绍到）

<span style="font-family:SimSun;font-size:14px;">Select Y,Q,Max(SQ.[SUM]) as MaxSum

from

(--求出每年每季度不同类型的产品销售情况

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) 

) as SQ

group by Y,Q 

order by Y,Q</span>

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

from

(--求出每年每季度不同类型的产品销售情况

<span style="font-family:SimSun;font-size:14px;">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) 

) as SQ

group by Y,Q 

having avg(Amount)>25

order by Y,Q</span>

1.Group By 子句用于对结果集进行分组，并对每一组数据进行汇总计算。

语法格式：

Group By [列名] [HAVING 条件表达式]

Group By按“列名”指定的列进行分组，将该列列值相同的记录组成一组，并对每一组进行汇总计算。每一组生成一条记录。若有“HAVING 条件表达式”，则表示对生成的组进行筛选。

假如，TableX 表包含：

--------------------------------

ColumnA　　ColumnB　　ColumnC

1　　　　　　abc　　　　　 5

1　　　　　　def　　　　　　4

1　　　　　　ghi　　　　　　9

2　　　　　　jkl　　　　　　 8

2　　　　　　mno　　　　　 3

如果 ColumnA 是组合列，则结果集中最终将有两行，其中一行汇总值 1 的信息，而另一行汇总值 2 的信息。

如果 ColumnA 是组合列，要引用 ColumnB 或 ColumnC，这两列必须是能为 ColumnA 中的每个值返回单个值的聚合函数中的参数。选择列表中可以包含诸如 MAX (ColumnB)、SUM (ColumnC) 或 AVG (ColumnC) 之类的表达式:

<span style="font-family:SimSun;font-size:14px;">SELECT ColumnA, 
MAX(ColumnB) AS MaxB, 
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA
</span>

此选择语句返回两行，为 ColumnA 中的每个唯一值各返回一行：

ColumnA MaxB SumC
----------- ---- -----------
1 ghi 18
2 mno 11
(2 row(s) affected)
但是，选择列表中不能只包含 ColumnB 表达式：

<span style="font-family:SimSun;font-size:14px;">SELECT 
ColumnA, 
ColumnB, 
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA</span>

　　由于 GROUP BY 只能返回一行，该行在 ColumnA 中的值为 1，因此无法返回与 ColumnA 中的值 1 关联的 ColumnB 中的三个值（abc、def 和 ghi）。

不能对 ntext、text、image 或 bit 列使用 GROUP BY 或 HAVING，除非它们所在的函数返回的值属于其他数据类型。这样的函数包括 SUBSTRING 和 CAST。

四、范围分组查询（group by, case）

需求：查询在指定年龄段的人数

<span style="font-family:SimSun;font-size:14px;">select case when age>1 and age <26 then 1
when age>25 and age <27 then 2
else 0 end as stage ,count(*) as population from student 


group by case when age>1 and age <26 then 1
when age>25 and age <27 then 2
else 0 end</span>

结果
stage population
1 1 2
2 2 5

可通过stage的值区分该条记录是属于哪个年龄段

五、sql多表分组查询

一般group by 放在查询语句的最外边，如果外层查询列包含了非聚集函数也不在group by子查询里面就在内层查询里面使用group by

<span style="font-family:SimSun;font-size:14px;">select * from
( 
select t0=CONVERT(VARCHAR(10),[TimeStamp],120) 
from table0 S 
INNER JOIN ACDGroupMember M on 
S.LoginID=M.AgentID 
WHERE M.ACDGroupID=10016 AND S.TimeStamp BETWEEN '2012-10-2' AND '2012-11-2'
group by CONVERT(VARCHAR(10),[TimeStamp],120)
)a
left join 
(
select t1= COUNT(M.AgentID),t2=CONVERT(VARCHAR(10),[TimeStamp],120)
from table1 S 
INNER JOIN ACDGroupMember M on 
S.LoginID=M.AgentID 
WHERE M.ACDGroupID=10016 AND S.TimeStamp BETWEEN '2012-10-2' AND '2012-11-2'
group by CONVERT(VARCHAR(10),[TimeStamp],120) 
)b on t0=t2</span>

捏捏小脸蛋

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
SQL Server汇总数据之聚合函数与分组

主要用于对数据集的的数据进行汇总统计等操作，基本是聚合函数。聚合的基本理念：不是返回所有指定的行，而是返回一行计算得到的值（前面指定的行的某些数据的汇总）。它们汇总了原始数据集。1、计算总数、平均值2、统计分析3、解决聚合问题4、创建动态的交叉查询一、简单聚合在SQL查询的逻辑流程中，聚合函数是在From子句和Where子句之后执
复制链接

扫一扫