点击关注公众号，干货第一时间送达
凡事总能有个排名，
考试有排名，
算绩效有排名，
就连花钱消费，也能整出个排名。
有什么"高端卡"、"普卡"，
或者"高消"、"普消"，
再不行就直接来个"钻石会员"，"黑卡会员"。
五花八门，层出不穷！
这其中的蕴含的经济学原理，咱不讨论
至于，怎么去定义这些等级，
仔细琢磨，是可以用咱们正在学的SQL来完成。
算是一个新的知识点，
一起来看看。
【温馨提示：建表语句及数据导入脚本，已经放在文章末尾】
一、需求场景
题目：
从订单信息表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等级为钻石会员
订单信息表order_info：
正确结果：
二、解决方案
演示工具：Hive-3.1.3 + DataGrip2022
肯定是不难的，
需要先计算出每个用户，每天的总消费金额，
这个需要进行分组计算，
因为可能存在一个用户，一天中下单多次：
接着，计算出每个用户，
截止当天的累计金额，
这个需要用到 "sum() over()" 开窗
最后，进行金额的判断即可。
（1）按照用户user_id、下单日期create_date分组，计算每天的下单金额总和
select user_id,create_date,
sum(total_amount) as ttl
from order_info
group by user_id,create_date
结果集：
（2）按照用户user_id、下单日期create_date开窗，计算截止当天累计消费金额
select user_id,
create_date,
sum(ttl) over (partition by user_id order by create_date) as sm
from (
select user_id,create_date,
sum(total_amount) as ttl
from order_info
group by user_id,create_date
)t1
结果集：
代码里面用到的是：
sum(ttl) over(partition by user_id order by create_date)
意思就是：
按照user_id分组，组内按照create_date排序，默认是正序，
进行ttl列数值的sum求和，
这样就可以达到计算截止每个日期的金额累计求和。
（3）按照累计金额，进行VIP等级划分
select user_id,
create_date,
sm,
case when sm >= 100000 then '钻石会员'
when sm >= 80000 then '白金会员'
when sm >= 50000 then '黄金会员'
when sm >= 30000 then '白银会员'
when sm >= 10000 then '青铜会员'
when sm >= 0 then '普通会员'
end vip_level
from (
select user_id,
create_date,
sum(ttl) over (partition by user_id order by create_date) as sm
from (
select user_id,create_date,
sum(total_amount) as ttl
from order_info
group by user_id,create_date
)t1
)t2
结果集：
最后的代码用到了：case when
这类似于java里面的：swithc case
多分支判断。
语法格式：
SELECT
CASE WHEN condition1 THEN result1
WHEN condition2 THEN result2 ...
ELSE default_result END
FROM table_name
在这里，condition1和condition2是条件表达式，可以是任何布尔类型的表达式。如果第一个条件不成立，则测试第二个条件。最终，如果所有条件都不成立，则返回default_result。
三、源数据
订单表order_info：
create table order_info
(
order_id string,
user_id string,
create_date string,
total_amount decimal(16, 2)
)
row format serde 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
with serdeproperties ('field.delim' = '\t') stored as
inputformat 'org.apache.hadoop.mapred.TextInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location 'hdfs://mycluster/user/hive/warehouse/hql_test2.db/order_info'
tblproperties ('bucketing_version' = '2');
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('1', '101', '2021-09-27', 29000.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('2', '101', '2021-09-28', 70500.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('3', '101', '2021-09-29', 43300.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('4', '101', '2021-09-30', 860.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('5', '102', '2021-10-01', 46180.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('6', '102', '2021-10-01', 50000.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('7', '102', '2021-10-01', 75500.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('8', '102', '2021-10-02', 6170.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('9', '103', '2021-10-02', 18580.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('10', '103', '2021-10-02', 28000.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('11', '103', '2021-10-02', 23400.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('12', '103', '2021-10-03', 5910.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('13', '104', '2021-10-03', 13000.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('14', '104', '2021-10-03', 69500.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('15', '104', '2021-10-03', 2000.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('16', '104', '2021-10-03', 5380.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('17', '105', '2021-10-04', 6210.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('18', '105', '2021-10-04', 68000.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('19', '105', '2021-10-04', 43100.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('20', '105', '2021-10-04', 2790.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('21', '106', '2021-10-04', 9390.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('22', '106', '2021-10-05', 58000.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('23', '106', '2021-10-05', 46600.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('24', '106', '2021-10-05', 5160.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('25', '107', '2021-10-05', 55350.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('26', '107', '2021-10-05', 14500.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('27', '107', '2021-10-06', 47400.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('28', '107', '2021-10-06', 6900.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('29', '108', '2021-10-06', 56570.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('30', '108', '2021-10-06', 44500.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('31', '108', '2021-10-07', 50800.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('32', '108', '2021-10-07', 3900.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('33', '109', '2021-10-07', 41480.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('34', '109', '2021-10-07', 88000.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('35', '109', '2020-10-08', 15000.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('36', '109', '2020-10-08', 9020.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('37', '1010', '2020-10-08', 9260.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('38', '1010', '2020-10-08', 12000.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('39', '1010', '2020-10-08', 23900.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('40', '1010', '2020-10-08', 6790.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('41', '101', '2020-10-08', 300.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('42', '101', '2021-01-01', 260.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('43', '101', '2021-01-02', 280.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('44', '101', '2021-01-03', 420.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('45', '101', '2021-01-04', 240.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('46', '1011', '2021-09-26', 240.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('47', '1011', '2021-10-24', 240.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('48', '1011', '2022-09-24', 240.00);
INSERT INTO hql_test2.order_info (order_id, user_id, create_date, total_amount) VALUES ('49', '1012', '2022-09-24', 2010.00);
OK，这就是本期内容了，下期再见！
文章转载自皮皮克克，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
