SQL中的开窗函数详解可代替聚合函数使用_MsSql

来源:脚本之家  责任编辑:小易  

都说眼睛是心灵的窗口,谁都希望自己可以有一双美丽的大眼睛,但有时候事与愿违,偏偏给了双小眼睛,但是也不用灰心,生活中我们可以通过锻炼来让眼睛变大,让眼睛变大的方法有哪些呢,下面一起看文章中为大家介绍有关怎么能让眼睛变大的内容吧。  怎么能让眼睛变大  做眼操  1,张大眼睛,用中指,无名指,在鬓角近太阳穴部位,按住3秒钟,放开3秒钟,双手同时按摩5回。  2,用三个手指在大颚臼齿与牙龈之间的位置,从嘴角到耳下分为4个触点,每1触点以画圆圈方式轻轻推拿2回.以同样的步骤重复5回,当然另一边也要做。  3,看着镜中的自己(与脸距离30厘米并成45度角),下巴往上仰的同时,眉毛也往上吊着,静止3秒之

 在没学习开窗函数之前,我们都知道,用了分组之后,查询字段就只能是分组字段和聚合的字段,这带来了极大的不方便,有时我们查询时需要分组,又需要查询不分组的字段,每次都要又到子查询,这样显得sql语句复杂难懂,给维护代码的人带来很大的痛苦,然而开窗函数出现了,曙光也来临了。如果要想更具体了解开窗函数,请看书《程序员的SQL金典》,开窗函数在mysql不能使用。

随着电梯房越来越多,各种各样的户型也层出不穷,传统的楼梯房,一梯两户,越来越难买到,一梯多户,两梯多户的的占据了更大的市场,我们在买房时,究竟选择几梯几户的房子才算好呢?首先传统的一梯两户的楼梯房,还是更受购房者的青睐,但是因为上面几层的上下楼不方便,开发商又设计了带电梯的多层楼房,这样的一梯两户的楼房,公摊面积只是多了电梯间的,所以公摊也不是很大,但是因为电梯的价格不菲,所以房价比普通的多层楼价格要高一些。所以带电梯的一梯两户无论公摊,还是出行都是不错的,而且住居的人群不多,环境也要好一些。至于一梯一户的房子,无论是否带有电梯,公摊其实是不小的,因为这一层所有的公摊都是你自己的,当然因为住居

       开窗函数与聚合函数一样,都是对行的集合组进行聚合计算。它用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不需要使用group by语句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。定义看不懂不要紧,会用就行。

猪不会心脑血管疾病。原因如下:1,猪不吃肉,只吃素。远离大鱼大肉海鲜等。饮食符合我国传统养生文化。2,猪不喝酒,啤酒,白酒,红酒等统统不喝,控制的非常好。3,猪不熬夜,猪不玩手机,不去夜店,不看电视,不看头条。每天准时起床,准时睡觉,心脏能得到充分休息。4,大多数猪都被阉割,对异性没有想法,少了欲望,专心修炼。正好应了道家无欲无求的最高境界。5,猪不上班,没有工作压力,没有干不完的工作,不用娶媳妇儿买房子养后代,日子非常惬意,舒心,心情舒畅,自然不会心脑血管疾病。6,猪身上的肥肉完全是良好的生活习惯和心态积攒的天地之精华,非常宝贵,所以人拼命养猪,吃猪肉。7,再多的原因,只有猪知道,你去问猪吧

       举个简单例子 查询每个工资小于5000的员工信息(姓名,城市 年龄 薪水),并且显示小于5000的员工个数,尝试使用下面语句:

湖北名菜太多,我在这里只介绍几种个人比较喜欢的。清蒸武昌鱼因产于武昌梁子湖而得名。头小体高,面扁背厚,呈菱形,脂肪丰叟,肉味鲜美,汤汁清香,营养丰富,堪称淡水鱼中的珍味佳肴。1957年毛泽东主席《水调歌头.游泳》发表,诗词中“才饮长沙水,又食武昌鱼”,引起人们对武昌鱼的兴趣。随着时代的发展,烹饪武昌鱼的制作技术不断改进和提高,从传统的蒸、煮、炙三种,发展到清蒸、油焖、网衣、滑溜等多种方法,其中尤以武昌大中华酒楼的清蒸武昌鱼别具一格,常作为该店筵席大菜,其特点是:口感滑嫩,清香鲜美,驰名中外。沔阳三蒸沔阳三蒸是以水产类、禽畜类、蔬菜类为主要原料,以粉蒸为主要技法,多种蒸菜技法(清蒸、扣蒸)并用制

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) FROM T_Person WHERE FSALARY<5000

消息 8120,级别 16,状态 1,第 1 行

选择列表中的列 'T_Person.FName' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。

         可以使用子查询实现,语句:

SELECT FName, FCITY, FAGE, FSalary, ( SELECT COUNT(FName) FROM T_Person WHERE FSALARY<5000 ) PersonNum FROM T_Person WHERE FSALARY<5000

       结果:

     使用开窗函数实现,查询结果一模一样,就不粘贴了:

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) OVER() as PersonNum FROM T_Person WHERE FSALARY<5000

1.开窗函数格式:函数名(列) OVER(选项)

2.聚合开窗函数格式:聚合函数(列) OVER(PARTITION BY 字段)

      over关键字把聚合函数当成聚合开窗函数而不是聚合函数,SQL标准允许将所有的聚合函数用做聚合开窗函数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号为空,则开窗函数会对结果集合的所有行进行聚合运算。

      PARTITION BY来定义行的分区来进行聚合运算,与group by 不同,partition by 字句创建的分区是独立于结果集的,创建的分区只是用于进行聚合运算,而且不同的开窗函数所创建的分区不互相影响,例如:查询所有人员的信息,并查询所属城市的人员数以及同年龄的人员数:

SELECT FName,FCITY, FAGE, FSalary, COUNT(FName) OVER(PARTITION BY FCITY) CityNum, COUNT(FName) OVER(PARTITION BY FAGE) AgeNum FROM T_Person ORDER by FCITY

 

查询所有人员的信息,并查询所属城市的人员数,每个城市的人按照年龄排序语句:

SELECT FName,FCITY, FAGE, FSalary, COUNT(FName) OVER(PARTITION BY FCITY ORDER BY FAGE) CityNum FROM T_Person

 3.排序开窗函数格式:排序函数() OVER(ORDER BY 字段)

  (1)主要函数有ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()

   ROW_NUMBER() 加行号,一般可以用于分页查询(现在被offset  fetch取代 ),对于没有主键列的表加行号作用很明显,删除重复数据等。

  按照薪水高低给所有人员排序,同样薪水的排名不一样,可以用row_number(),

with a as ( SELECT FName, FSalary, FCity, FAge, ROW_NUMBER() over(ORDER BY FSalary) as RowNum FROM T_Person ) SELECT * FROM a

 使用rank()将每个城市的薪水排行,值一样的同一个排名,出现两个第一名的时候,排在两个第一名后的排名将是第三名

SELECT FName, FSalary, FCity, FAge, RANK() over(PARTITION BY FCITY ORDER BY FSalary) as RankNum FROM T_Person

  使用dense_rank()将每个城市的薪水排行,值一样的同一个排名,出现两个第一名的时候,排在两个第一名后的排名将是第三名

 

ntile(数字) over(order by 字段):数字表示一组多少个数,并根据数量得出分组的数量

SELECT *,NTILE(5) OVER(ORDER BY FSalary) AS NileNum FROM T_Person

总结

到此这篇关于SQL中的开窗函数详解可代替聚合函数使用的文章就介绍到这了,更多相关SQL 开窗函数内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

扩展阅读,根据您访问的内容系统为您准备了以下内容,希望对您有帮助。

能说一下oracle中的开窗函数 聚合函数 分析函数都是什么吗?

我也是用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()只返回一个结果

2.rank()和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,sal*100/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_sales.subcompany is '分公司';

comment on column t_sales.branch is '经营部';

comment on column t_sales.region is '片区';

comment on column t_sales.customer is '客户';

comment on column t_sales.sale_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,99990.99))

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 50.00

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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,99990.99))*/

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))

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


  • 本文相关:
  • sql server 开窗函数over()的使用实例详解
  • sql server 2012 开窗函数
  • sql server如何利用开窗函数over()进行分组统计
  • mysql中聚合函数count的使用和性能优化技巧
  • mysql 中聚合函数增加条件表达式的方法
  • sql server 字符串聚合函数
  • sql server中通过扩展存储过程实现数据库的远程备份与恢复
  • 解析如何用sql语句在指定字段前面插入新的字段
  • 如何恢复数据库备份到一个已存在的正在使用的数据库上
  • sql server 公用表表达式(cte)实现递归的方法
  • 小米正式开源 sql 智能优化与改写工具 soar
  • sqlserver关于分页存储过程的优化【让数据库按我们的意思执行查询
  • sql 常用技巧整理
  • 数据库复制性能测试 推送模式性能测试
  • sql server 2008 清空删除日志文件(瞬间缩小日志到几m)
  • sqlserver 日志恢复方法(搞定drop和truncate)
  • 能说一下oracle中的开窗函数 聚合函数 分析函数都是什么吗?
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mssqlsql server 开窗函数over()的使用实例详解sql server 2012 开窗函数sql server如何利用开窗函数over()进行分组统计mysql中聚合函数count的使用和性能优化技巧mysql 中聚合函数增加条件表达式的方法sql server 字符串聚合函数sql server中通过扩展存储过程实现数据库的远程备份与恢复解析如何用sql语句在指定字段前面插入新的字段如何恢复数据库备份到一个已存在的正在使用的数据库上sql server 公用表表达式(cte)实现递归的方法小米正式开源 sql 智能优化与改写工具 soarsqlserver关于分页存储过程的优化【让数据库按我们的意思执行查询sql 常用技巧整理数据库复制性能测试 推送模式性能测试sql server 2008 清空删除日志文件(瞬间缩小日志到几m)sqlserver 日志恢复方法(搞定drop和truncate)sql server 2012 安装图解教程(附sql server数据库入门学习总结microsoft sql server 2012 数据sql重复记录查询的几种方法win7系统安装sqlserver2000的详细sqlserver中distinct的用法(不重sql server错误代码大全及解释(sql-order by 多字段排序(升序、sql将一个表中的数据插入到另一个用sql语句添加删除修改字段、一些sql server使用自定义函数以及游标sql server 创建数据库脚本create databasqlserver中获取月份的天数的方法分享sql2005开启xp_cmdshellmssql木马修复,中木马后的处理方法sql server表中添加新列并添加描述针对sqlserver大数据量插入速度慢或丢失数sql server数据库的三种恢复模式:简单恢sqlserver实现类似oracle的before触发器示select into 和 insert into select 两种
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved