开窗函数的详细的介绍

开窗函数介绍使用

开窗函数格式

开窗函数格式 :  
    方式一 :
        select ... 
        聚合函数|排序函数 over(partition by 分组字段名 order by 排序字段名 asc|desc)“添加别名处”
        from 表名;
    "默认省略rows between unbounded preceding and current row 从第一行到当前行"
    方式二 :
        select ... 
        聚合函数|排序函数 over(partition by 分组字段名 order by 排序字段名 asc|desc rows|range between 起始位置 and 结束位置)“添加别名处”
         from 表名;

聚合函数

聚合开窗函数 : max : 最大值
                         min : 最小值
                         avg : 平均值
                         sum : 相加总值
                         count(1) | count(*) : 计算行数，包括null值
                         count(列名) : 计算行数，不包含null值

排序开窗函数

 排序开窗函数 : row_number  dense_rank  rank
                          row_number: 巧记: 1234   特点: 唯一且连续
                          dense_rank: 巧记: 1223   特点: 并列且连续
                          rank   : 巧记: 1224   特点: 并列不连续

开窗函数控制范围

 开窗函数控制范围: rows|range between xxx and xxx
                                         - unbounded: 无界限
                                         - x preceding:往前x行
                                         - x following:往后x行
                
                                         - unbounded preceding :表示从前面的起点  第一行
                                         - unbounded following :表示到后面的终点  最后一行   
                   
                                          - current row:当前行

rows 和 range 的区别

测试数据准备

-- 以下操作是在Oracle中进行
-- 数据准备

-- 创建表
CREATE TABLE test(
  video_id number(10),
  dt date,
  if_follow number(10)
);

-- 插入数据
INSERT INTO test VALUES (2001, to_date('2021-09-24','yyyy-mm-dd'), 1);
INSERT INTO test VALUES (2001, to_date('2021-10-03','yyyy-mm-dd'), 9);
INSERT INTO test VALUES (2001, to_date('2021-10-02','yyyy-mm-dd'), 2);
INSERT INTO test VALUES (2001, to_date('2021-10-01','yyyy-mm-dd'), 6);
INSERT INTO test VALUES (2002, to_date('2021-09-25','yyyy-mm-dd'), 1);
INSERT INTO test VALUES (2002, to_date('2021-09-25','yyyy-mm-dd'), 1);
INSERT INTO test VALUES (2002, to_date('2021-09-26','yyyy-mm-dd'), 6);
INSERT INTO test VALUES (2002, to_date('2021-09-27','yyyy-mm-dd'), 1);
INSERT INTO test VALUES (2002, to_date('2021-09-28','yyyy-mm-dd'), 1);
INSERT INTO test VALUES (2002, to_date('2021-09-29','yyyy-mm-dd'), 8);
INSERT INTO test VALUES (2002, to_date('2021-09-30','yyyy-mm-dd'), 7);
INSERT INTO test VALUES (2002, to_date('2021-10-01','yyyy-mm-dd'), 1);
INSERT INTO test VALUES (2002, to_date('2021-10-02','yyyy-mm-dd'), 9);
INSERT INTO test VALUES (2002, to_date('2021-10-03','yyyy-mm-dd'), 1);

-- 提交数据
commit;

rows 是取范围行的值

select
    video_id,
    dt,
    sum(if_follow) over (partition by video_id order by if_follow rows between unbounded preceding and current row )row_num
from test
where video_id=2002;

range 是取范围值的值 

select
    video_id,
    dt,
    sum(if_follow) over (partition by video_id order by if_follow range between unbounded preceding and current row)row_num
from test
where video_id = 2002;

进阶开窗函数

其他开窗函数:

                        ntile(x)功能: 将分组排序之后的数据分成指定的x个部分（x个桶）   
       "注意ntile规则:尽量平均分配 ，优先满足最小(编号1)的桶，彼此最多不相差1个"

                        -lag: 用于统计窗口内往上第n行值
                        -lead: 用于统计窗口内往下第n行值

                        -first_value: 取分组内排序后，截止到当前行，第一个值
                        -last_value : 取分组内排序后，截止到当前行，最后一个值

以上就是小浪本次发布的内容，如果对友友您有帮助，还麻烦您给小浪点个关注 和 赞，这是对小浪莫大的支持，蟹蟹友友们，小浪还会持续更新，分享自己在学习整个过程中遇到的问题！