窗口函数案例
数据准备
1）建表语句
create table order_info ( order_id string, --订单id user_id string, -- 用户id user_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 ( select order_id, user_id, user_name, order_date, order_amount, lag(order_date,1,null) over(partition by user_id order by order_date) last_date from 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;
运行结果：
