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,
    case
        when sum_so_far >= 0
        and sum_so_far < 10000 then '普通会员'
        when sum_so_far >= 10000
        and sum_so_far < 30000 then '青铜会员'
        when sum_so_far >= 30000
        and sum_so_far < 50000 then '白银会员'
        when sum_so_far >= 50000
        and sum_so_far < 80000 then '黄金会员'
        when sum_so_far >= 80000
        and sum_so_far < 100000 then '白金会员'
        when sum_so_far >= 100000 then '钻石会员'
        else null
    end 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_far
        from (
                select 
                    user_id,
                    create_date,
                    sum(total_amount) total_amount
                from order_info
                group by user_id,
                    create_date
            ) t0
    ) t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33