hive sql系列（四）——所有用户中在今年10月份第一次购买商品的金额

需求：

请用sql写出所有用户中在今年10月份第一次购买商品的金额，
表order字段:
(购买用户：userid，金额：money，购买时间：paymenttime(格式：2017-10-01)，订单id：orderid

建表语句

create table `order`(
  userid string,
    money int,
    paymenttime string,
    orderid string
)
ROW format delimited FIELDS TERMINATED BY "\t"
;

数据

insert overwrite table `order` values
('001',200,'2021-09-01','121'),
('002',300,'2021-09-01','122'),
('001',100,'2021-10-01','123'),
('001',200,'2021-10-02','124'),
('002',500,'2021-10-01','125'),
('001',100,'2021-11-01','126');

实现

select
    t2.*
from
    (select
        t1.userid,
        t1.money,
        t1.paymenttime,
        t1.orderid,
        rank() over(partition by userid order by paymenttime) rank
    from
        (select
            userid,
            money,
            paymenttime,
            orderid
        from
            `order`
        where
            date_format(paymenttime,"yyyy-MM")=date_format(current_date(),"yyyy-10")
        )t1
    )t2
where rank=1
;

结果

 
#结果
Total MapReduce CPU Time Spent: 4 seconds 970 msec
OK
t2.userid  t2.money  t2.paymenttime  t2.orderid  t2.rank
001  100  2021-10-01  123  1
002  500  2021-10-01  125  1
Time taken: 14.693 seconds, Fetched: 2 row(s)

分析

1、date_format只能支持日期格式，比如2021-03-30
2、date_format(current_date(),"yyyy-10")是用yyyy取出年份，10是给的默认值，其他玩法见扩展部分
3、所有用户10月份第一次购买，从需求来看，是对每个用户分组，对购买时间排序，所以这里需要对用户开窗，因为是第一次购买，所以排序是是正序
4、这里从数据来看，购买时间是日期，不是时间，也就是说用户可能在同一天购买多次，如果是这样，则要求购买时间是时间类型，精确到秒
5、这里的数据的唯一性是通过时间约束的，所以不用去重
6、这里是一个正确的写法，先过滤数据，减少数据集——预处理、预聚合的会让数据处理的阶段更清晰，在一个子查询中实现多种和阶段处理，需要衡量效率来决定，后面把这部分拿出来做个对比分析
7、这里order是关键字，所以用`号

扩展

date_format扩展玩法
hive (default)> select date_format("2021-10-10","1000-10");
OK
_c0
1000-10
Time taken: 0.033 seconds, Fetched: 1 row(s)
hive (default)> select date_format("2021-10-10","1000-MM");
OK
_c0
1000-10
Time taken: 0.028 seconds, Fetched: 1 row(s)
hive (default)> select date_format("2021-10-10","1000-MM");

希望大家可以关注下公众号，会定期分享自己从业经历、技术积累及踩坑经验，支持一下，鞠躬感谢~

关注公众号回复：“资料全集”