Hive 刷题——查询用户的累计消费金额及VIP等级

需求描述

从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额，以及每个用户在其每个下单日期的VIP等级。
用户vip等级根据累积消费金额计算，计算规则如下：
设累积消费总额为X，
若0=<X<10000,则vip等级为普通会员
若10000<=X<30000,则vip等级为青铜会员
若30000<=X<50000,则vip等级为白银会员
若50000<=X<80000,则vip为黄金会员
若80000<=X<100000,则vip等级为白金会员
若X>=100000,则vip等级为钻石会员

期望结果如下：

user_id <string> (用户id)	create_date <string> (下单日期)	sum_so_far <decimal(16,2)> (截至每个下单日期的累计下单金额)	vip_level <string> (每个下单日期的VIP等级)
101	2021-09-27	29000.00	青铜会员
101	2021-09-28	99500.00	白金会员
101	2021-09-29	142800.00	钻石会员
101	2021-09-30	143660.00	钻石会员
102	2021-10-01	171680.00	钻石会员
102	2021-10-02	177850.00	钻石会员
103	2021-10-02	69980.00	黄金会员
103	2021-10-03	75890.00	黄金会员
104	2021-10-03	89880.00	白金会员
105	2021-10-04	120100.00	钻石会员
106	2021-10-04	9390.00	普通会员
106	2021-10-05	119150.00	钻石会员
107	2021-10-05	69850.00	黄金会员
107	2021-10-06	124150.00	钻石会员
108	2021-10-06	101070.00	钻石会员
108	2021-10-07	155770.00	钻石会员
109	2020-10-08	24020.00	青铜会员
109	2021-10-07	153500.00	钻石会员
1010	2020-10-08	51950.00	黄金会员

需要用到的表：

订单信息表：order_info

order_id (订单id)	user_id (用户id)	create_date (下单日期)	total_amount (订单金额)
1	101	2021-09-30	29000.00
10	103	2020-10-02	28000.00

解题思路

这个提主要考察的是 开窗和多路分支判断的使用，需要注意一点：一个用户当天可能存在多个订单，所以在判断之前先对金额进行一次汇总

select user_id,
                   create_date,
                   sum(total_amount) total_amount
            from order_info
            group by user_id, create_date

在这个基础上进行操作即可；

参考SQL

select user_id,
       create_date,
       total_amount                                    sua_so_far,
       case
           when total_amount >= 0 and total_amount < 10000 then '普通会员'
           when total_amount >= 10000 and total_amount < 30000 then '青铜会员'
           when total_amount >= 30000 and total_amount < 50000 then '白银会员'
           when total_amount >= 50000 and total_amount < 80000 then '黄金会员'
           when total_amount >= 80000 and total_amount < 100000 then '白金会员'
           when total_amount >= 100000 then '钻石会员' end vip_level
from (select user_id,
             create_date,
             sum(total_amount) over (partition by user_id order by create_date ) total_amount
      from (select user_id,
                   create_date,
                   sum(total_amount) total_amount
            from order_info
            group by user_id, create_date) t) t
order by user_id, create_date;