窗口函数开窗函数分析函数笔记

0.前言：

做了窗口函数的 笔记。

前排鸣谢这位大佬↓  我也算照着做的笔记

链接：https://blog.youkuaiyun.com/jerrytomcat/article/details/82790543?spm=1018.2226.3001.9630.1&extra%5Btitle%5D=oracle

全文可以复制到 sql 文本中看 方便 运行理解。我写的太 抽象 可以看 上方链接

一  什么是分析函数、

1.概念

分析函数是 ORACLE  专门用于解决复杂报表统计 需求的功能强大的函数，
它 可以再数据中进行分组然后计算 
基于组的 某种统计值，
并且每一组 的每一行 都可以返回一个统计值

2. 开窗函数  对比 聚合函数

聚合函数：一般用group by               每个分组   返回一个统计值   

开窗函数：采用partition by分组         每组 每行  都可以返回一个统计值

3. 开窗函数

开窗函数 指定了  所能影响的窗口范围
就是说： /* 这个窗口范围中都可以受到 函数影响*/

/*PS:【有些 分析函数 就是 开窗函数】*/

 4. 语法：↓↓ ↓ ↓ ↓ ↓ ↓ ↓                

       
--------------------------------------------------
function_name (<argument>,<argument>...)    -----1
over                                        ---      -----3
 <Order-by-Clause>                          -----4
 <Windowin--2
(<Partition-Clause>                   g-Clause>)                        -----5
--------------------------------------------------

1）.function_name 【函数名字】

常见分析函数:
①聚合函数
sum         ：略
min         ：略
max         ：略
avg         ：略
count       ：略
②排名函数
row_number():返回一个唯一值 ，当碰到相同数据  排名按照 记录中记录的顺序 依次递增
rank()      :返回一个唯一值 ，当碰到相同数据  所有相同数据排名一样   然后不同记录 之间  空出排名
dense_rank():返回一个唯一值 ，当碰到相同数据  所有相同数据排名一样   然后不同记录 之间  紧邻递增

2).over            :关键字，用于标识分析函数

3).Partition-Clause:分区子句，根据分区表达式的条件逻辑 将 单个结果分成N组  

  格式： partition by ....  [翻译：partition 分割]

4).Order-by-Clause :排序子句，  用于 对 分区 中的 数据进行排序

            
  格式： order by
  
    
5).Windowing-Clause:窗口子句， 用于定义function在其上    操作的集合    就是function所影响的范围

  格式： order by 字段名  range|rows  between  边界规则1  AND  边界规则2
  range: 表示 按照值的范围进行范围的定义
  rows : 表示 按照行的范围进行范围的定义
  
  边界规则取值表：
  ------------------------------------------------
  |    可取值          |            说明         |
  ------------------------------------------------
  | CURRENT   ROW      |          当前行         |
  | N  PRECEDING       |          前N行          |
  |UNBOUNDED PRECEDING |     一直到第一条记录    |
  | N   FOLLOWING      |          后N行          |
  |UNBOUNDED FOLLOWING |     一直到最后一条记录  |
  -------------------------------------------------
 

 

 二 .实例练习 聚合函数 和开窗函数
 

 1) 单一聚合函数 求出 订单表 有 多少订单

 
 select  count(orderkey)   from   orders 
 
 2) 聚合函数 和开窗函数 over（）联合使用

 
2.1）通过子查询实现

 每个 订单  订单日期是  1月3日 的 订单 ，  且  每行显示  订单日期是 1月3日的 订单个数
  select 
  orderkey ,
  (select  count(orderkey) from orders  where to_char(ORDERDATE,'YYYY-MM-DD') ='2023-12-19'  ) 
  from orders   
  where to_char(ORDERDATE,'YYYY-MM-DD') ='2023-12-19' 

2.2) 开窗函数 over() 实现

select orderkey ,status, count(*)over ()
from orders 
where to_char(ORDERDATE,'YYYY-MM-DD') ='2023-12-19'
 

2.3) 加上 partition by  分区子句

select orderkey ,
status, 
count(*)over (partition by  status)相同状态数
from orders 
where to_char(ORDERDATE,'YYYY-MM-DD') ='2023-11-15'

2.4）order by  子句 

 
2.4.0)用于对分区中数据进行排序

select  orderkey  ,  status,TOTALQTY，
count(orderkey)over (order by  status)--通过 【状态】 升序  排列 这个结果
from orders 
where to_char(ORDERDATE,'YYYY-MM-DD') ='2023-11-15'

2.4.1）row：按照行定位的

--查询 从第一行 到当前行的 商品总数【TOTALQTY】的 总和
select  
 orderkey  ,  status,TOTALQTY，
sum(TOTALQTY)over (order by  TOTALQTY rows between unbounded preceding and current row ) 到当前行商品数求和
from orders 
where to_char(ORDERDATE,'YYYY-MM-DD') ='2023-11-15'

2.4.2）range：按照范围定位的

--查询  从 第一行到 当前行的 工资 总和 
select  
 orderkey  ,  status,TOTALQTY，
sum(TOTALQTY)over (order by  TOTALQTY range  between unbounded preceding and current row ) 到当前行商品数求和
from orders 
where to_char(ORDERDATE,'YYYY-MM-DD') ='2023-11-15'

2.4.3  ROW  和  range  区别

/*案例*/
     qty      sum..over row..   range...

|     1    |       1       |        1     |
|     2    |       3       |        3     |
|     3    |       6       |        9     |
|     3    |       9       |        9     |
|     4    |       13      |        13    |
|     5    |       18      |        18    |

↑ 从上 可以发现 qty  两次为 3 时候  row  和 range  得出结果不一样

因为 
row 以 行 按来定位的  6行  1行到3行的 和 是多少 【6】
range 以 范围 来定位的     qty 第一行的值  到 第三行的值得 的  和是 多少 【9】 并列的 就相加

----------------------\

测试 用 句
select  *   from orders  where to_char(ORDERDATE,'YYYY-MM-DD') ='2023-11-15'

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

三 排名函数  和 分析函数 实例

0.名词解释

排名函数 ： 
row_number():返回一个唯一的值， 当碰到相同数据时， 排名按照记录集中记录的 顺序 依次递增 --ps: 1234567

rank()      :返回一个唯一的值， 当碰到相同数据时， 此时所有相同数据排名是一样的， 
             同时在接下来不同排名记录 会空下来排名-- ps:1222567
             
dense_rank():返回一个唯一的值， 当碰到相同数据时， 此时所有相同数据排名是一样的,
             同时会在最后一条相同记录和下一条不同记录排名之间 紧密相连--ps: 1222345

1.--案例  --照着  状态 排

select  orderkey  ,  status,TOTALQTY，
row_number()over (order by  status) rownumber,--风雨无阻排序
rank()      over (order by  status) rank,--并列让位排序
dense_rank()over (order by status)  denserank--并列 不让位排序
from orders 
where to_char(ORDERDATE,'YYYY-MM-DD') ='2023-11-15'