窗口函数案例

数据准备

1）建表语句

create table order_info
(order_id     string, --订单iduser_id      string, -- 用户iduser_name    string, -- 用户姓名order_date   string, -- 下单日期order_amount int     -- 订单金额
);

2）装载语句

insert overwrite table order_info
values ('1', '1001', '小元', '2022-01-01', '10'),('2', '1002', '小海', '2022-01-02', '15'),('3', '1001', '小元', '2022-02-03', '23'),('4', '1002', '小海', '2022-01-04', '29'),('5', '1001', '小元', '2022-01-05', '46'),('6', '1001', '小元', '2022-04-06', '42'),('7', '1002', '小海', '2022-01-07', '50'),('8', '1001', '小元', '2022-01-08', '50'),('9', '1003', '小辉', '2022-04-08', '62'),('10', '1003', '小辉', '2022-04-09', '62'),('11', '1004', '小猛', '2022-05-10', '12'),('12', '1003', '小辉', '2022-04-11', '75'),('13', '1004', '小猛', '2022-06-12', '80'),('14', '1003', '小辉', '2022-04-13', '94');

需求

1）统计每个用户截至每次下单的累积下单总额

这里使用基于行的窗口函数，起点是第一行，终点是当前行，并根据每个用户id分区，根据下单日期排序；因为需求是累计下单总额，所以窗口范围是从第一行到当前行。

-- 1）统计每个用户截至每次下单的累积下单总额
select user_id,user_name,order_id,order_date,order_amount,sum(order_amount) over(partition by user_id order by order_date rows between unbounded preceding and current row ) sum_amount
from order_info;

 运行结果：

2）统计每个用户截至每次下单的当月累积下单总额

这里使用了 substring 函数，它作为 partition by 后面的第二个字段，意味着先根据 user_id 分区之后再根据 日期的 1~7 位（也就是 年份-月份）进行分区，这样就巧妙的将每个用户不同的月份可以进行窗口函数的累计求和。

select user_id,user_name,order_id,order_date,order_amount,sum(order_amount) over(partition by user_id ,substring(order_date,1,7) order by order_date rows between unbounded preceding and current row ) sum_amount
from order_info

运行结果：

3）统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）

这里用到一个新的函数 datediff（），它是用来计算两个日期间隔的；这道题的核心在于使用 lag 函数来获取上一行的值 last_date，然后用 datediff 函数进行差值计算得到天数。

注意：lag 函数和 lead 函数是不需要声明窗口范围的。

-- 3）统计每个用户每次下单距离上次下单相隔的天数（首次下单按0天算）
select user_id,user_name,order_id,order_date,order_amount,nvl(datediff(order_date,last_date),0) diff
from (selectorder_id,user_id,user_name,order_date,order_amount,lag(order_date,1,null) over(partition by user_id order by order_date) last_datefrom order_info)t1;

运行结果： 

4）查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期

这里使用了 first_value 和 last_value 函数，同样分区规则是先根据 user_id 再根据 order_date 进行分区的，其中 last_value 需要声明窗口范围，我们取从第一行到最后一行；first_value 没有声明窗口范围，则会默认按照 基于列 的窗口函数 between unbounded preceding and current row，因为我们比较的是日期，所以即使每次移动窗口也都会取小于当前日期的值。

-- 4）查询所有下单记录以及每个用户的每个下单记录所在月份的首/末次下单日期
select user_id,user_name,order_id,order_date,order_amount,first_value(order_date,false) over (partition by user_id,substring(order_date,1,7) order by order_date) first_date,last_value(order_date,false) over (partition by user_id,substring(order_date,1,7) order by order_date rows between unbounded preceding and unbounded following) last_date
from order_info;

运行结果：

5）为每个用户的所有下单记录按照订单金额进行排名

这里使用 row_number() ，order by 是根据 order_amount 进行排序，排名的结果也是按照 order_amount 排名。 

-- 5）为每个用户的所有下单记录按照订单金额进行排名
select user_id,user_name,order_id,order_date,order_amount,row_number() over (partition by user_id order by order_amount) rank
from order_info;

运行结果：