小学生之Oracle分析函数

小学生之Oracle分析函数

从SQL Server 二〇〇六起,SQL Server先河帮衬窗口函数 (Window
Function),以及到SQL Server
二零一一,窗口函数成效增强,近来停止帮助以下两种窗口函数:

 

解析函数是怎么?
解析函数是Oracle专门用来缓解复杂报表总计供给的成效强大的函数,它能够在多少中开展分组然后总计基于组的某种总计值,并且每一组的每一行都足以回到1个总结值。

在率先有的中,我们谈论了APPLYCTE那多个T-SQL
Enhancement。APPLY实现了Table和TVF的Join,CTE通过制造“临时的View”的主意使难题化繁为简。现在大家随后来谈谈此外多个根本的T-SQL
Enhancement Items:PIVOTRanking。 

  1. 排序函数 (Ranking Function) ;

  2. 聚合函数 (Aggregate Function) ;

  3. 分析函数 (Analytic Function) ;

  4. NEXT VALUE FOOdyssey Function, 那是给sequence专用的二个函数;

从 转

          

三、           
PIVOT Operator

 

 

分析函数和聚合函数的区别之处是何许?
一般性的聚合函数用group by分组,每一种分组重返一个计算值,而分析函数采纳partition
by分组,并且每组每行都足以回去2个总括值。

PIVOT的汉语意思是“在枢轴上旋转”,比如对于1个2维坐标,将横坐标变成纵坐标,将纵坐标变成横坐标。反映在1个Relational Table上的意趣正是:变成为列,变列为行。相信大家在开始展览报表设计的时候都境遇过类似于那般的须求:计算二〇〇二年内有些销售职员第贰季度各样月处理的订单数。在AdventureWorks
Sample Databse中,Sales
Order存款和储蓄于SaleOrderHeader这张表中,它的结果如下:

一. 排序函数(Ranking
Function)

开窗函数是在 ISO 标准中定义的。SQL Server
提供排行开窗函数和聚集开窗函数。

              

图片 1

救助文书档案里的代码示例很全。

  在开窗函数出现在此以前存在着广大用 SQL
语句很难化解的难点,很多都要因而复杂的相关子查询只怕存储进程来成功。SQL
Server 二〇〇六 引入了开窗函数,使得这几个经典的问题能够被轻松的解决。

剖析函数的款式
剖析函数带有一个开窗函数over(),包罗多少个分析子句:分组(partition by),
排序(order by), 窗口(rows) ,他们的采纳情势如下:over(partition by xxx
order by yyy rows between zzz)。
注:窗口子句在此地小编只说rows格局的窗口,range情势和滑动窗口也不提

咱俩一般情状下通过上面包车型地铁SQL完结大家提议的计算效率:

排序函数中,ROW_NUMBE汉兰达()较为常用,可用于去重、分页、分组中甄选数据,生成数字协理表等等;

  窗口是用户钦命的一组行。开窗函数计算从窗口派生的结果集中各行的值。开窗函数分别采纳于各类分区,并为每一个分区重新启航总括。

    

图片 2SELECT SalesPersonID,
图片 3SUM(CASE DATEPART(MM,OrderDate)WHEN 1 THEN 1 ELSE 0 END) AS JAN,
图片 4SUM(CASE DATEPART(MM,OrderDate)WHEN 2 THEN 1 ELSE 0 END) AS FEB,
图片 5SUM(CASE DATEPART(MM,OrderDate)WHEN 3 THEN 1 ELSE 0 END) AS MAR,
图片 6SUM(CASE DATEPART(MM,OrderDate)WHEN 4 THEN 1 ELSE 0 END) AS APR
图片 7FROM Sales.SalesOrderHeader
图片 8WHERE DATEPART(yyyy,OrderDate) = 2002
图片 9GROUP BY SalesPersonID
图片 10

排序函数在语法上须求OVESportage子句里必须含O途乐DER
BY,不然语法不经过,对于不想排序的情景能够这么变化;

  OVE福特Explorer子句用于明确在应用关联的开窗函数在此之前,行集的分区和排序。PA帕杰罗TITION BY
将结果集分为多少个分区。

剖析函数例子(在scott用户下模拟)

于是大家取得了这么的总结数据:

drop table if exists test_ranking

create table test_ranking
( 
id int not null,
name varchar(20) not null,
value int not null
) 

insert test_ranking 
select 1,'name1',1 union all 
select 1,'name2',2 union all 
select 2,'name3',2 union all 
select 3,'name4',2

select id , name, ROW_NUMBER() over (PARTITION by id ORDER BY name) as num
from test_ranking

select id , name, ROW_NUMBER() over (PARTITION by id) as num
from test_ranking
/*
Msg 4112, Level 15, State 1, Line 1
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.
*/

--ORDERY BY后面给一个和原表无关的派生列
select id , name, ROW_NUMBER() over (PARTITION by id ORDER BY GETDATE()) as num
from test_ranking

select id , name, ROW_NUMBER() over (PARTITION by id ORDER BY (select 0)) as num
from test_ranking

 

示范目标:呈现各部门职工的薪酬,并顺便呈现该有的的最高级工程师资。

图片 11

 

一 、排行开窗函数

 

因而数据在原表的布局和大家最终获得的结果实行比较,我们发现仿佛“旋转”了90度,原来的OrderDate是储存在每行的基于Order的贰天品质(行),今后我们要把Order
Date依据分化月份总结,那样行变成了列。

二. 聚合函数 (Aggregate
Function)

1. 语法

--显示各部门员工的工资,并附带显示该部分的最高工资。
SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
            --unbounded:不受控制的,无限的
            --preceding:在...之前
            --following:在...之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

像这么的供给,大家都能够能够经过PIVOT这么些操作符来贯彻,上边便是依照PIVOT的SQL:

SQL Server 200第55中学,窗口聚合函数仅支持PA陆风X8TITION
BY,也正是说仅能对分组的数目总体做聚合运算;

Ranking Window Functions

< OVER_CLAUSE > :: =

   OVER ( [ PARTITION BY value_expression , … [ n ] ]

          <ORDER BY_Clause> )

 

图片 12SELECT SalesPersonID, [1] AS JAN,[2] AS FEB, [3] AS MAR, [4] AS APR
图片 13FROM 
图片 14(
图片 15    SELECT SalesPersonID, DATEPART(MM,OrderDate) AS MON
图片 16    FROM Sales.SalesOrderHeader
图片 17    WHERE DATEPART(yyyy,OrderDate) = 2002
图片 18) S
图片 19
图片 20PIVOT 
图片 21(
图片 22    COUNT(MON) FOR MON IN ([1],[2],[3],[4])
图片 23)
图片 24AS P
图片 25

SQL Server 二〇一三始发,窗口聚合函数帮衬OXC90DER
BY,以及ROWS/RAGNE选项,原本须求子查询来落到实处的须求,如: 移动平均
(moving averages), 总括聚合 (cumulative aggregates), 累计求和 (running
totals) 等,变得越来越方便人民群众;

 

运作结果:

在地点的例证中,同过上边包车型地铁SELECT语句筛选出来的是为通过PIVOT的数码。

 

瞩目:O汉兰达DE兰德ENCORE BY 子句内定对相应 FROM
子句生成的行集进行分区所依照的列。value_expression 只好引用通过 FROM
子句可用的列。value_expression
不可能引用选拔列表中的表达式或小名。value_expression
能够是列表达式、标量子查询、标量函数或用户定义的变量。

图片 26

图片 27SELECT SalesPersonID, DATEPART(MM,OrderDate) AS MON
图片 28    FROM Sales.SalesOrderHeader
图片 29    WHERE DATEPART(yyyy,OrderDate) = 2002
图片 30

代码示例1:总括/小计/累计求和

 

演示指标:遵照deptno分组,然后计算每组值的总数

透过下边包车型大巴PIVOT(COUNT(MON)是大家供给总计的数额,FO大切诺基MON IN ([1],[2],[3],[4]是总结的限量)就成了我们最后输出的布局了。
 

drop table if exists test_aggregate;

create table test_aggregate
(
event_id      varchar(100),
rk            int,
price         int
)

insert into test_aggregate
values
('a',1,10),
('a',2,10),
('a',3,50),
('b',1,10),
('b',2,20),
('b',3,30)


--1. 没有窗口函数时,用子查询
select a.event_id, 
       a.rk,  --build ranking column if needed
       a.price, 
     (select sum(price) from test_aggregate b where b.event_id = a.event_id and b.rk <= a.rk) as totalprice 
  from test_aggregate a


--2. 从SQL Server 2012起,用窗口函数
--2.1 
--没有PARTITION BY, 没有ORDER BY,为全部总计;
--只有PARTITION BY, 没有ORDER BY,为分组小计;
--只有ORDER BY,没有PARTITION BY,为全部累计求和(RANGE选项,见2.2)
select *,
     sum(price) over() as TotalPrice,
     sum(price) over(partition by event_id) as SubTotalPrice,
       sum(price) over(order by rk) as RunningTotalPrice
  from test_aggregate a

--2.2 注意ORDER BY列的选择,可能会带来不同结果
select *,
     sum(price) over(partition by event_id order by rk) as totalprice 
  from test_aggregate a
/*
event_id    rk    price    totalprice
a    1    10    10
a    2    10    20
a    3    50    70
b    1    10    10
b    2    20    30
b    3    30    60
*/

select *,
     sum(price) over(partition by event_id order by price) as totalprice 
  from test_aggregate a
/*
event_id    rk    price    totalprice
a    1    10    20
a    2    10    20
a    3    50    70
b    1    10    10
b    2    20    30
b    3    30    60
*/

--因为ORDER BY还有个子选项ROWS/RANGE,不指定的情况下默认为RANGE UNBOUNDED PRECEDING AND CURRENT ROW 
--RANGE按照ORDER BY中的列值,将相同的值的行均视为当前同一行
select  *,sum(price) over(partition by event_id order by price) as totalprice from test_aggregate a
select  *,sum(price) over(partition by event_id order by price range between unbounded preceding and current row) as totalprice from test_aggregate a

--如果ORDER BY中的列值有重复值,手动改用ROWS选项即可实现逐行累计求和
select  *,sum(price) over(partition by event_id order by price rows between unbounded preceding and current row) as totalprice from test_aggregate a

2. 示例

 

图片 31PIVOT 
图片 32(
图片 33    COUNT(MON) FOR MON IN ([1],[2],[3],[4])
图片 34)
图片 35

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图