4.查询用户的累计消费金额及VIP等级

思路分析：
（1）按照user_id及create_date 分组求消费金额total_amount
（2）开窗计算同user_id下的累计销售金额sum(total_amount) over(partition by user_id order by create_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_so_far
（3）根据sum_so_far的值写case when语句判断会员等级
注：关键点在于：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
这段代码，它的含义是开窗范围是从起始行到当前行，一般用于从起始行到当前行的累计值计算场景

​

代码实现：

select user_id,create_date,sum_so_far,casewhen sum_so_far >= 0and sum_so_far < 10000 then '普通会员'when sum_so_far >= 10000and sum_so_far < 30000 then '青铜会员'when sum_so_far >= 30000and sum_so_far < 50000 then '白银会员'when sum_so_far >= 50000and sum_so_far < 80000 then '黄金会员'when sum_so_far >= 80000and sum_so_far < 100000 then '白金会员'when sum_so_far >= 100000 then '钻石会员'else nullend as vip_level
from (SELECT user_id,create_date,sum(total_amount) over( partition by user_id order by create_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_so_farfrom (select user_id,create_date,sum(total_amount) total_amountfrom order_infogroup by user_id,create_date) t0) t1;