大数据开窗函数

开窗函数

聚合函数开窗

一共有四种方式

1、select聚合函数 over（）

2、select 聚合函数 over（partition by 分组字段）

3、select 聚合函数 over（order by 排序字段）

4、select 聚合函数 over（partition by 分组字段 order by 排序字段）

create table test_db.website_pv_info
(
    cookieid string, --cookieid  相同则认为是同一个用户
    createtime string, --day
    pv int  --用户浏览量或点击量
) row format delimited fields terminated by ',';
select * from website_pv_info;
--常规的分组聚合
--1、求每个用户的总pv数  sum+ group by 常规普通聚合操作
select sum(pv) as sum_pv,cookieid from website_pv_info group by cookieid;
--问题：分组聚合后，数据条目少，存在数据丢失现象，在大数据开发时不希望在计算机指标时造成数据的大量丢失，所以使用开窗函数
--2、求出网站总pv数，所有用户的所有访问加起来，以及个用户的全部数据
--计算完成后数据条目数不变，且没有数据丢失，且计算了全部的访问量，聚合范围是整张数据表
select *,sum(pv) over() user_total_pv from website_pv_info;
--3、求出每个用户的总pv，分组后我们只能使用分组字段，不方便，所以我们仍然使用窗口函数
--此处我们不使用group by 而使用partition by 进行开窗范围的修正
--计算完成后数据条目数不变，数据没有丢失，切记算了各组的访问量，聚合范围是分组内部
select *,sum(pv) over(partition by cookieid) user_total_pv from website_pv_info;、

为什么over叫开窗函数呢？

因为根据over函数的参数不同，则进行聚合计算的范围不同

窗口函数数据 UDF UDTF UDAF? UDAF因为我们输入了窗口中的多个数据，而输出的数据只有一个

window_expression窗口操作

rows between

-preceding:往前

-following:往后

-current row:当前行

-unbounded:起点

-unbounded preceding：表示从前面的起点第一行

-unbounded following 表示到后面的终点 最后一行

使用格式

select 聚合函数 over(partition by 分组字段 order by 排序字段 rows between 上边界 and 下边界)

--window_expression窗口操作
--默认情况下，这种开窗函数的范围，是从当前分组的上边界到当前位置
select cookieid,createtime,pv,sum(pv)over(partition by cookieid order by createtime) from website_pv_info;
--1、使用窗口表达式，达成默认效果，开窗范围从怎组的上边界到当前行为止
select cookieid,createtime,pv,
       sum(pv)over(partition by cookieid order by createtime rows between unbounded preceding and current row )
from website_pv_info;
--2、使用开窗范围从上边界到当前行的后1行
select cookieid,createtime,pv,
       sum(pv)over(partition by cookieid order by createtime rows between unbounded preceding and 1 following)
from website_pv_info;
--3、使开窗范围从当前行之前的三行，到当前行之后的三行（前三行+当前行+后三行）
select cookieid,createtime,pv,
       sum(pv)over(partition by cookieid order by createtime rows between unbounded preceding and 3 following)
from website_pv_info;
--4、使开窗范围从当前行到末尾、
select cookieid,createtime,pv,
       sum(pv)over(partition by cookieid order by createtime rows between current row and following)
from website_pv_info;

窗口排序函数

--窗口排序函数
--1、既不分组也不排序
--rank和dense_rank是根据数据大小进行编号，没有排序则都书写为1，row_number与数据大小无关，从上到下依次编号
select cookieid,createtime,pv,
       rank()over() rn1,
       dense_rank() over () rn2,
       row_number() over () rn3 from text_db.website_pv_info;
--2、分组不排序
--rank和dense_rank进行组内编号，没有排序则都标记为1，row_number 组内编号，与大小无关，从上到下依次编号
select cookieid,createtime,pv,
       rank()over(partition by cookieid) rn1,
       dense_rank() over (partition by cookieid) rn2,
       row_number() over (partition by cookieid) rn3 from text_db.website_pv_info;
--3、分组排序
--rank按照排序字段大小进行编号，如果大小相同则使用相同编号，并跳过未使用过的编号，例如 123446
--dese_rank 按照排序字段大小进行编号，如果大小相同则使用相同编号，不跳过未使用的编号，例如123445
--row_number组内从小到大编号，与排序字段大小无关
select cookieid,createtime,pv,
       rank()over(partition by cookieid order by pv) rn1,
       dense_rank() over (partition by cookieid order by pv) rn2,
       row_number() over (partition by cookieid order by pv) rn3 from text_db.website_pv_info;
--ntile 是按照分组排序后的数据，将每一个分组中的数据拆分为n份，每份数据尽量相同
select cookieid,createtime,pv,
       rank()over(partition by cookieid order by pv) rn1 from text_db.website_pv_info;

其他开窗函数

--其他开窗函数
create table test_db.website_url_info
    (
    cookieid string,
    createtime string,
    url string
    )row format delimited fields terminated by ',';
select * from website_url_info;
--1、lag获取当前行向上n行的数据内容
--log（获取字段的名称，向上的行数，默认值）
select *,lag(createtime,1) over(partition by cookieid order by createtime) from website_url_info;
--向上的行数不能写负数
--2、lead  获取当前行n行的数据内容
--lead（获取字段名称，向下的行数，默认值）
select *,lead(createtime,1) over(partition by cookieid order by createtime) from website_url_info;
--3、first_value 获取当前开窗范围内的第一个值
select *,first_value(createtime) over(partition by cookieid rows between 1 following and 3 following) from website_url_info;
--4、last_value获取当前开创范围内的最后一个值
select *,last_value(createtime) over(partition by cookieid order by createtime) from website_url_info;