2.开窗分区,    分析函数

1.基本概念

    学习目标

    学习目标

此书及其相关资源下载方法:关注微信公众号,点击功能介绍-书籍链接下载,即可获取

SUM        :该函数计算组中表达式的累积和

开窗函数分为两个部分分别是

        -了解分析函数作用和类型

        -了解分析函数作用和类型

说明

本文主要介绍如下:

  • 9.1.1 什么是分析函数
  • 9.1.2 基本语法
  • 9.1.3 分析函数结构
  • 9.1.4 分析函数列表

MIN        :在一个组中的数据窗口中查找表达式的最小值

1.聚合,排名,偏移,分布函数 。

        -使用分析函数产生报告

        -使用分析函数产生报告

9.1.1 什么是分析函数

尽管SQL语句非常强大,但是各种情形的出现,并不能完全满足日常开发,例如:找出同一个部门的工资总和与其他信息并排显示,但只使用一张表

MAX        :在一个组中的数据窗口中查找表达式的最大值

2.开窗分区,排序,框架。

 

 

9.1.2 基本语法

AVG         :用于计算一个组和数据窗口内表达式的平均值。

下面举个例子

    分析函数

    分析函数

语法

FUNCTION_NAME (<argument>,<argument>. . . )
OVER
(<Partition-Clause><Order—by—Clause><windowing Clause>)

COUNT      :对一组内发生的事情进行累积计数

SELECT empid, ordermonth, val,
  SUM(val) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS runval
FROM Sales.EmpOrders;

        分析函数用于计算一些基于组的聚合值,它与聚合函数的区别在于,分析函数每组返回多行,聚合函数每组返回一行。

        分析函数用于计算一些基于组的聚合值,它与聚合函数的区别在于,分析函数每组返回多行,聚合函数每组返回一行。

语法说明

  • FUNCTION_NAME:用于指定分析函数名,Oracle 提供了 26 个分析函数,比如
    SUM、 COUNT、 AVG、 MIN、 MAX 及 ROW_NUMBER 等.
  • <argument>: 用于指定分析函数的参数, 每个函数可以有0~3 个参数,参数可 以
    是列名或表达式, 比如 SUM(saI+comm)这样的形式.
  • OVER:是一个关键字,用于标识分析函数,否则查询分析器不能区别比如 SUM
    是分组函数还是一个分析函数.
  • <Patition_Clause>: 分区子句是可选的分区于句,如果不存在任何分区于句, 则
    全部的结果集可看作一个单一的大区.
  • <Order_by-Clause>: 可选的排序子句, 用来根据结果集进行排序.
  • <Wind0wing Clause>: 用于定义分析函数将在其上操作的行的集合, 该子句给出了
    一个定义变化或固定的数据窗口的方法, 分析函数将对这些数据进行操作.

RANK       :根据ORDER BY子句中表达式的值,从查询返回的每一行,计算它们与其它行的相对位置

sum(val)  就是集合函数

 

 

9.1.3 分析函数结构

DENSE_RANK :根据ORDER BY子句中表达式的值,从查询返回的每一行,计算它们与其它行的相对位置

over() 就是开窗     PARTITION BY empid  就是开窗分区(分组)   ORDER BY ordermonth 开窗排序  

    一般分析函数

    一般分析函数

1.分析函数名

(1)等级函数:用于寻找前N,如ROW_NUMBER,RANK,DENSE_BANK函数

SELECT s.deptno,
       s.job,
       SUM(s.sal) AS sum_sal,
       rank() over(ORDER BY SUM(s.sal) DESC) AS 薪资等级,
       row_number() over(PARTITION BY s.deptno ORDER BY SUM(s.sal) DESC) AS 分组行号,
       SUM(SUM(s.sal)) over(PARTITION BY s.deptno ORDER BY s.job) 部门薪资运行总计
  FROM scott.emp s
 WHERE s.deptno IN (10, 20, 30)
   AND s.job IS NOT NULL
 GROUP BY s.deptno, s.job;

(2)开窗函数:SUM、 COUNT、 AVG、 MIN 和 MAX 等
(3)制表函数:与开窗函数的区别是作用于整个分区或者组,MIN, MAX,SUM,COUNT,AVG等
(4)LEG和LEAD函数:允许结果集中向前或后检索值,如果避免数据的自连接可以用到
(5)统计函数:VAR_POP,VAR_SAMP和SRDEV_POP及线性的衰减函数等,计算任何未排序的统计值

FIRST       :从DENSE_RANK返回的集合中取出排在最前面的一个值的行

 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  开窗架构

        ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)  按分区或返回记录生成唯一编号

        ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)  按分区或返回记录生成唯一编号

2.分区子句

分区子句使用 PARTlTION BY 关键宇, 用来将一个简单的结果集分为N组,分区与
组的概念比较相似′在语法上与 SQL查询的 GROUP BY子句很相似, 如下所示。
PARTITION BY expression <, expression> <, expression>

SELECT s.deptno,
      s.empno,
      s.ename,
      row_number() over(PARTITION BY s.deptno ORDER BY s.empno DESC) AS 分组行号
 FROM scott.emp s
WHERE s.deptno IN (10, 20, 30)
  AND s.job IS NOT NULL;

LAST        :从DENSE_RANK返回的集合中取出排在最后面的一个值的行

2.排名开窗函数

        RANK() OVER(PARTITION BY ... ORDER BY ...)  按分区或返回记录排序,会跳号

        RANK() OVER(PARTITION BY ... ORDER BY ...)  按分区或返回记录排序,会跳号

3.排序子句

ORDER BY expression <ASCIDESC> (NULLS FIRSTINULLS LAST>,

  • asc|desc:指定排序顺序 〈升序或降序) 。 asc 是默认值
  • nulls first|nulls last:指定若返回行包含空值, 该值应该出现在排序序列的开始还是末尾

FIRST_VALUE :返回组中数据窗口的第一个值

 SQL SERVER 支持4个排名函数 ROW_NUMBER,RANK,DENSE_RANK ,NTLE   来看看它们分别的作用

        DENSE_RANK() OVER(PARTITION BY ... ORDER BY ...)  按分区或返回记录排序,不跳号

        DENSE_RANK() OVER(PARTITION BY ... ORDER BY ...)  按分区或返回记录排序,不跳号

4.开窗子句

--rows BETWEEN unbounded preceding AND unbounded following:从首列到末尾所有的记录
SELECT deptno,
       SUM(sal) AS 部门薪资小计,
       SUM(SUM(sal)) over(ORDER BY deptno rows BETWEEN unbounded preceding AND unbounded following) AS 部门总计
  FROM scott.emp
 GROUP BY deptno

LAST_VALUE :返回组中数据窗口的最后一个值。

SELECT orderid, custid, val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum,
RANK() OVER(ORDER BY val) AS rank,
DENSE_RANK() OVER(ORDER BY val) AS dense_rank,
NTILE(10) OVER(ORDER BY val) AS ntile
FROM Sales.OrderValues
ORDER BY val;

        COUNT() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录进行计数

        COUNT() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录进行计数

9.1.4 分析函数列表

LAG        :可以访问结果集中的其它行而不用进行自连接

图片 1

        MAX() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录计算最大值 

        MAX() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录计算最大值 

1.COUNT函数

语法:COUNTH(| [DISTINCT | ALL] expr}) OVER (analytic_clause)

LEAD       :LEAD与LAG相反,LEAD可以访问组中当前行之后的行

可以看到 它们不同排序规则

        MIN() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录计算最小值  

        MIN() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录计算最小值  

2.sum函数

语法:SUM ( [ DISTINCT | ALL ] expr ) OVER ( analytic_clause )

ROW_NUMBER:返回有序组中一行的偏移量,从而可用于按特定标准排序的行号

ROW_NUMBER() 对排序字段行号进行排序  

        SUM() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录进行求和

        SUM() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录进行求和

3.AVG函数

语法:AVG([DISTINCTIALL] expr) OVER(analytic_clause)

STDDEV     :计算当前行关于组的标准偏离

RANK() 对数值进行排序 对相同数值有行号占用

        AVG() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录求出平均值

        AVG() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录求出平均值

4.MAX和MIN函数

语法:MAX (col) OVER (analytic_clause)
MIN (col) OVER (analytic_clause)

STDDEV_POP:该函数计算总体标准偏离,并返回总体变量的平方根

DENSE_RANK() 也是对数值排序 如果有相同数值 依旧会按照原先行号加

        FIRST_VALUE() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录的第一个值

        FIRST_VALUE() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录的第一个值

5.RANK和DENSE_RANK ,ROW_NUMBER函数

语法:RANK” OVER([partition_clause] order_by_clause)
DENSE_RANK () OVER([partition_clause] order_by_clause)
ROW_NUMBER () OVER([partition_clause] order_by_clause)

STDDEV_SAMP:该函数计算累积样本标准偏离,并返回总体变量的平方根

NTILE 分区排序 为每一行分配一个区号 如果分10区 会对所有数据进行分区  总数据/分区数  就是每多少数据为一区

        LAST_VALUE() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录的最后一个值

        LAST_VALUE() OVER(PARTITION BY ... ORDER BY ...) 按分区或返回记录的最后一个值

6.FIRST和LAST函数

语法:KEEP
(DENSE_RANK FIRST ORDER BY
expr [DESC|ASC]
[NULLS ( FIRST | LAST}]
[, expr [DESC IASC]
[NULLS ( FIRST | LAST}]
] . . .
)
[OVER query_partition_clause] ;

VAR_POP    :该函数返回非空集合的总体变量(忽略null)

本文由必威发布于必威-数据,转载请注明出处:2.开窗分区,    分析函数

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。