开窗函数的详细的介绍

最新推荐文章于 2025-05-20 20:35:05 发布

Mr.小浪

最新推荐文章于 2025-05-20 20:35:05 发布

阅读量2k

点赞数 44

CC 4.0 BY-SA版权

文章标签： oracle mysql 大数据 hive

本文链接：https://blog.youkuaiyun.com/Mr_xiaolang/article/details/137591172

开窗函数介绍使用

开窗函数格式

开窗函数格式 :
   方式一 :
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 : 取分组内排序后，截止到当前行，最后一个值