Hive 【Hive（七）窗口函数练习】 |
您所在的位置：网站首页 › hive统计函数 › Hive 【Hive（七）窗口函数练习】 |
Hive 【Hive（七）窗口函数练习】
|
窗口函数案例
数据准备
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;运行结果：
这里使用了 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运行结果：
这里用到一个新的函数 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;运行结果：
这里使用了 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;运行结果：
这里使用 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;运行结果：
|
【本文地址】
今日新闻 |
推荐新闻 |