投稿
  1. 首页
  2. 工具

所有的sql聚合函数的应用例题 谢谢

人体内脏图片 • 2023-4-27 • 工具 • 阅读 49

所有的sql聚合函数的应用例题 谢谢,第1张

WHERE

type

=

'business'

下面是结果集：

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

-----------

6,28125

30788

(1

row(s)

affected)

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

MAX

求最大值

例题：简单的就不写了

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

BINARY_CHECKSUM

在表中任一行上计算的

BINARY_CHECKSUM()

返回相同的值，只要随后没有修改行。

BINARY_CHECKSUM()

将为大多数（但不是全部）行更改返回不同的值，并可用于检测大多数行修

改。

例题：本例使用

BINARY_CHECKSUM

来检测

Northwind

数据库的

Products

表中一行的改变。

/Get

the

checksum

value

before

the

values

in

the

specific

rows

(#13-15)

are

changed/

USE

Northwind

GO

CREATE

TABLE

TableBC

(ProductID

int,

bchecksum

int)

INSERT

INTO

TableBC

SELECT

ProductID,

BINARY_CHECKSUM()

FROM

Products

/TableBC

contains

a

column

of

77

checksum

values

corresponding

to

each

row

in

the

Products

table/

--A

large

company

bought

products

13-15

--The

new

company

modified

the

products

names

and

unit

prices

--Change

the

values

of

ProductsName

and

UnitPrice

for

rows

13,

14,

and

15

of

the

Products

table/

UPDATE

Products

SET

ProductName='Oishi

Konbu',

UnitPrice=5

WHERE

ProductName='Konbu'

UPDATE

Products

SET

ProductName='Oishi

Tofu',

UnitPrice=20

WHERE

ProductName='Tofu'

UPDATE

Products

SET

ProductName='Oishi

Genen

Shouyu',

UnitPrice=12

WHERE

ProductName='Genen

Shouyu'

--Determine

the

rows

that

have

changed

SELECT

ProductID

FROM

TableBC

WHERE

EXISTS

(

SELECT

ProductID

FROM

Products

WHERE

ProductsProductID

=

TableBCProductID

AND

BINARY_CHECKSUM()

<>

TableBCbchecksum)

下面是结果集：

ProductID

13

14

15

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

having 你可以认为是聚合函数的 where 部分，

使用聚合函数后，比如 avg（age） ，这是是不允许用where限定 avg（age）的，

而是用having avg（age）>21。

有聚合函数后，关于聚合函数的条件就用having。

最后一段的sql的目的是 选出年龄大于平均年龄的 学生的信息 ，但是是错误语句,少了group by。

MIN() 函数

MIN() 函数返回指定列的最小值。

SQL MIN() 语法

演示数据库

在本教程中，我们将使用 shulanxt 样本数据库。

下面是选自“Websites”表的数据：

SQL MIN() 实例

下面的 SQL 语句从“Websites”表的“alexa”列获取最小值：

-from shulanxt

计算数据库的和的函数是sum；计算数据的平均值的函数是avg。

工具：oracle 10g

步骤：

1、数据库中有emp表如下：

2、按照deptno求每个deptno中sal的和，可用如下语句：

select deptno,sum(sal) from emp group by deptno;

3、查询结果：

4、按照deptno求每个deptno中sal的平均值，可用如下语句：

select deptno,avg(sal) from emp group by deptno;

5、查询结果：

我也是用oracle数据库的，在实际开发中用到开窗函数和分析函数的机会还是很少的，用聚合函数的时候非常多，请LZ多关注聚合函数，下面是我上各大网站收集的，希望对楼主有所帮助。

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是

对于每个组返回多行，而聚合函数对于每个组只返回一行。

下面通过几个例子来说明其应用。

1：统计某商店的营业额。

date sale

1 20

2 15

3 14

4 18

5 30

规则：按天统计：每天都统计前面几天的总额

得到的结果：

DATE SALE SUM

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

1 20 20 --1天

2 15 35 --1天＋2天

3 14 49 --1天＋2天＋3天

4 18 67

5 30 97

2:统计各班成绩第一名的同学信息

NAME CLASS S

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

fda 1 80

ffd 1 78

dss 1 95

cfe 2 74

gds 2 92

gf 3 99

ddd 3 99

adf 3 45

asdf 3 55

3dd 3 78

通过：

--

select from

(

select name,class,s,rank()over(partition by class order by s desc) mm from t2

)

where mm=1

--

得到结果：

NAME CLASS S MM

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

dss 1 95 1

gds 2 92 1

gf 3 99 1

ddd 3 99 1

注意：

1在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果

2rank()和dense_rank()的区别是：

--rank()是跳跃排序，有两个第二名时接下来就是第四名

--dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

3分类统计 (并显示信息)

A B C

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

m a 2

n a 3

m a 2

n b 2

n b 1

x b 3

x b 2

x b 4

h b 3

select a,c,sum(c)over(partition by a) from t2

得到结果：

A B C SUM(C)OVER(PARTITIONBYA)

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

h b 3 3

m a 2 4

m a 2 4

n a 3 6

n b 2 6

n b 1 6

x b 3 9

x b 2 9

x b 4 9

如果用sum，group by 则只能得到

A SUM(C)

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

h 3

m 4

n 6

x 9

无法得到B列值

＝＝＝＝＝

select from test

数据:

A B C

1 1 1

1 2 2

1 3 3

2 2 5

3 4 6

---将B栏位值相同的对应的C 栏位值加总

select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum

from test

A B C C_SUM

1 1 1 1

1 2 2 7

2 2 5 7

1 3 3 3

3 4 6 6

---如果不需要已某个栏位的值分割,那就要用 null

eg: 就是将C的栏位值summary 放在每行后面

select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum

from test

A B C C_SUM

1 1 1 17

1 2 2 17

1 3 3 17

2 2 5 17

3 4 6 17

求个人工资占部门工资的百分比

SQL> select from salary;

NAME DEPT SAL

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

a 10 2000

b 10 3000

c 10 5000

d 20 4000

SQL> select name,dept,sal,sal100/sum(sal) over(partition by dept) percent from salary;

NAME DEPT SAL PERCENT

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

a 10 2000 20

b 10 3000 30

c 10 5000 50

d 20 4000 100

二：开窗函数

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：

1：

over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数

over（partition by deptno）按照部门分区

2：

over（order by salary range between 5 preceding and 5 following）

每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5

例如:对于以下列

aa

1

2

2

2

3

4

5

6

7

9

sum(aa)over（order by aa range between 2 preceding and 2 following）

得出的结果是

AA SUM

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

1 10

2 14

2 14

2 14

3 18

4 18

5 22

6 18

7 22

9 9

就是说，对于aa=5的一行，sum为 5-1<=aa<=5+2 的和

对于aa=2来说，sum=1+2+2+2+3+4=14 ；

又如 对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9 ；

3：其它：

over（order by salary rows between 2 preceding and 4 following）

每行对应的数据窗口是之前2行，之后4行

4：下面三条语句等效：

over（order by salary rows between unbounded preceding and unbounded following）

每行对应的数据窗口是从第一行到最后一行，等效：

over（order by salary range between unbounded preceding and unbounded following）

等效

over(partition by null)

other（

数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

一、 over函数

over函数指定了分析函数工作的数据窗口的大小，这个数据窗口大小可能会随着行的变化而变化，例如：

over(order by salary)按照salary排序进行累计，order by是个默认的开窗函数

over(partition by deptno) 按照部门分区

over(order by salary range between 50 preceding and 150 following)每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150的数据记录

over(order by salary rows between 50 perceding and 150 following)前50行，后150行

over（order by salary rows between unbounded preceding and unbounded following）所有行

over（order by salary range between unbounded preceding and unbounded following）所有行

二、 sum函数

功能描述：该函数计算组中表达式的累积和。

SAMPLE：下例计算同一经理下员工的薪水累积值

SELECT manager_id, last_name, salary,

SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary

RANGE UNBOUNDED PRECEDING) l_csum

FROM employees

WHERE manager_id in (101,103,108);

三、 应用实例

1， 测试环境设置

设有销售表t_sales (subcompany,branch,region,customer,sale_qty); 存储客户的销售明细，记录如下所示。

Subcompany Branch Region Customer Sale_qty

北京分公司 北京经营部 片区1 客户1 1

北京分公司 北京经营部 片区1 客户1 1

北京分公司 北京经营部 片区1 客户2 1

北京分公司 北京经营部 片区1 客户2 1

北京分公司 北京经营部 片区2 客户1 1

北京分公司 北京经营部 片区2 客户1 1

北京分公司 北京经营部 片区2 客户2 1

北京分公司 北京经营部 片区2 客户2 1

北京分公司 其他经营部 片区1 客户1 1

北京分公司 其他经营部 片区1 客户1 1

北京分公司 其他经营部 片区1 客户2 1

北京分公司 其他经营部 片区1 客户2 1

北京分公司 其他经营部 片区2 客户1 1

北京分公司 其他经营部 片区2 客户1 1

北京分公司 其他经营部 片区2 客户2 1

北京分公司 其他经营部 片区2 客户2 1

create table t_sales(

subcompany varchar2(40),

branch varchar2(40),

region varchar2(40),

customer varchar2(40),

sale_qty numeric(18,4)

);

comment on table t_sales is '销售表，分析函数测试';

comment on column t_salessubcompany is '分公司';

comment on column t_salesbranch is '经营部';

comment on column t_salesregion is '片区';

comment on column t_salescustomer is '客户';

comment on column t_salessale_qty is '销售数量';

2，问题提出

现在要求给出销售汇总报表，报表中需要提供的数据包括客户汇总，和客户在其上级机构中的销售比例。

Subcompany Branch Region Customer Sale_qty Rate

北京分公司 北京经营部 片区1 客户1 2 50％

北京分公司 北京经营部 片区1 客户2 2 50％

北京分公司 北京经营部 片区1 小计 4 50％

北京分公司 北京经营部 片区2 客户1 2 50％

北京分公司 北京经营部 片区2 客户2 2 50％

北京分公司 北京经营部 片区2 小计 4 50％

北京分公司 北京经营部 小计 小计 8 50％

北京分公司 北京经营部 片区1 客户1 2 50％

北京分公司 北京经营部 片区1 客户2 2 50％

北京分公司 北京经营部 片区1 小计 4 50％

北京分公司 北京经营部 片区2 客户1 2 50％

北京分公司 北京经营部 片区2 客户2 2 50％

北京分公司 北京经营部 片区2 小计 4 50％

北京分公司 北京经营部 小计 小计 8 50％

北京分公司 小计 小计 小计 16 100％

3，解决方案（方案1）

首先我们可以使用oracle对group by 的扩展功能rollup得到如下的聚合汇总结果。

select

subcompany,

branch,

region,

customer,

sum(sale_qty) sale_qty

from t_sales

group by rollup(subcompany,branch,region,customer);

Subcompany Branch Region Customer Sale_qty

北京分公司 北京经营部 片区1 客户1 2

北京分公司 北京经营部 片区1 客户2 2

北京分公司 北京经营部 片区1 4

北京分公司 北京经营部 片区2 客户1 2

北京分公司 北京经营部 片区2 客户2 2

北京分公司 北京经营部 片区2 4

北京分公司 北京经营部 8

北京分公司 其他经营部 片区1 客户1 2

北京分公司 其他经营部 片区1 客户2 2

北京分公司 其他经营部 片区1 4

北京分公司 其他经营部 片区2 客户1 2

北京分公司 其他经营部 片区2 客户2 2

北京分公司 其他经营部 片区2 4

北京分公司 其他经营部 8

北京分公司 16

16

分析上面的临时结果，我们看到：

明细到客户的汇总信息，其除数为当前的sum(sale_qty),被除数应该是到片区的小计信息。

明细到片区的汇总信息，其除数为片区的sum(sale_qty),被除数为聚合到经营部的汇总数据。

。。。

考虑到上述因素，我们可以使用oracle的开窗函数over，将数据定位到我们需要定位的记录。如下代码中，我们利用开窗函数over直接将数据定位到其上次的小计位置。

over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null)

经整理后的查询语句如下。

select subcompany,

decode(f_branch, 1,subcompany||'(С¼Æ)', branch),

decode(f_region,1,branch||'(С¼Æ)',region),

decode(f_customer,1,region||'(С¼Æ)', customer),

sale_qty,

trim(to_char(round(sale_qty/

sum(sale_qty) over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null),2) 100,9999099))

from (select grouping(branch) f_branch,

grouping(region) f_region,

grouping(customer) f_customer,

subcompany,

branch,

region,

customer,

sum(sale_qty) sale_qty

from t_sales

group by subcompany, rollup(branch, region, customer))

Subcompany Branch Region Customer Sale_qty Rate

北京分公司 北京经营部 片区1 客户1 2 5000

北京分公司 北京经营部 片区1 客户2 2 5000

北京分公司 北京经营部 片区2 客户1 2 5000

北京分公司 北京经营部 片区2 客户2 2 5000

北京分公司 北京经营部 片区1 片区1(小计) 4 5000

北京分公司 北京经营部 片区2 片区2(小计) 4 5000

北京分公司 其他经营部 片区1 客户1 2 5000

北京分公司 其他经营部 片区1 客户2 2 5000

北京分公司 其他经营部 片区2 客户1 2 5000

北京分公司 其他经营部 片区2 客户2 2 5000

北京分公司 其他经营部 片区1 片区1(小计) 4 5000

北京分公司 其他经营部 片区2 片区2(小计) 4 5000

北京分公司 北京经营部 北京经营部(小计) (小计) 8 5000

北京分公司 其他经营部 其他经营部(小计) (小计) 8 5000

北京分公司 北京分公司(小计) (小计) (小计) 16 10000

北京分公司 北京经营部 片区1 客户1 2 5000

4，可能的另外一种解决方式（方案2）

select subcompany,

decode(f_branch, 1,subcompany||'(С¼Æ)', branch),

decode(f_region,1,branch||'(С¼Æ)',region),

decode(f_customer,1,region||'(С¼Æ)', customer),

sale_qty,

/ trim(to_char(round(sale_qty//

decode(f_branch+f_region+f_customer,

0,

(sum(sale_qty) over(partition by subcompany,branch,region))/2,

1,

(sum(sale_qty) over(partition by subcompany,branch))/3,

2,

(sum(sale_qty) over(partition by subcompany))/4 ,

sum(sale_qty) over()/4

)/

,2) 100,9999099))/

from (select grouping(branch) f_branch,

grouping(region) f_region,

grouping(customer) f_customer,

subcompany,

branch,

region,

customer,

sum(sale_qty) sale_qty

from t_sales

group by subcompany, rollup(branch, region, customer))

在上面的解决方式中，最大的问题在于开窗函数过大。导致每次计算涉及到的行数过多，影响到执行的速度和效率。并且需要额外的计算处理清除多余叠加进去的数值 。

）

建议你在数据库中增加一个isPass字段 每次往这个表中写数据的时候判断score>=60 如果成立则isPass=1

那么查询的时候可以

select count(score) as all, sum(isPass) as pass from a group by aname

相当于是说吧查询时要做的处理分散到每次单条数据 *** 作中

这样可以提高查询效率

可以直接用sno分组，但是select后面能用的字段只能是聚合函数(字段)或者是groupby子句中的字段。

如果在group by中不用sname，你可以这样写，但是你要看是否符合题目要求。

select min(sname)

from student s join sc

on ssno=csno

group by scsno

having count(scsno)>3

MAX()/MIN()是指SQL中的最大/最小值函数

因为聚合函数处理的是数据组，在本例中，MAX函数将整个TEACHER表看成一组

而TNAME、DNAME和TSEX的数据都没有进行任何分组，因此SELECT语句没有逻辑意义

MAX()和MIN()函数不仅可以作用于数值型数据，也可以作用于字符串或是日期时间数据类型的数据。

SQL 如下：

实例MAX()函数用于字符型数据

如下面代码：

SELECT MAX (a) AS MAXNAME

FROM     A

以上就是关于所有的sql聚合函数的应用例题 谢谢全部的内容，包括:所有的sql聚合函数的应用例题 谢谢、数据库having和聚合函数使用方法、SQL中聚合函数“MIN（列名）”的功能是等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/9350639.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
人体内脏图片 人体内脏图片 一级用户组
0 0
上一篇 2023-04-27
下一篇 2023-04-27

发表评论

登录后才能评论

评论列表（0条）

保存