目录
0 需求分析
t_order表结构
字段名 | 含义 |
oid | 订单编号 |
uid | 用户id |
otime | 订单时间(yyyy-MM-dd) |
oamount | 订单金额(元) |
所有在2018年1月下过单并且在2月没有下过单的用户,在3月份的下单情况:
目标字段名 | 含义 |
uid | 用户id |
big_order_count | 当月订单金额超过10元的订单个数 |
first_order_amount | 当月首次下单金额 |
last_order_amount | 当月末次下单金额 |
要求:用sql (hive sql) 编写 ,t_order表的扫描次数不超过2次。
1 需求实现
(1)数据准备
1003,2,2018-01-01,100
1004,2,2018-01-02,20
1005,2,2018-01-02,100
1006,4,2018-01-02,30
1007,1,2018-01-03,130
1008,2,2018-01-03,5
1009,2,2018-01-03,5
1001,5,2018-02-01,110
1002,3,2018-02-01,110
1003,3,2018-02-03,100
1004,3,2018-02-03,20
1005,3,2018-02-04,30
1006,6,2018-02-04,100
1007,6,2018-02-04,130
1001,1,2018-03-01,120
1002,2,2018-03-03,5
1003,2,2018-03-03,11
1004,3,2018-03-03,1
1005,3,2018-03-04,20
1006,4,2018-03-04,30
1007,1,2018-03-04,50
(2)建表SQL
drop table if exists dan_test.t_order
CREATE TABLE dan_test.t_order (
oid int ,
uid int ,
otime string,
oamount int
)
ROW format delimited FIELDS TERMINATED BY ",";
(3)加载数据
load data local inpath "/home/centos/dan_test/t_order.txt" into table t_order;
(4)实现
分析:
- (1)所有在2018年1月下过单并且在2月没有下过单的用户,在3月份的下单情况:
寻找 1月下过单2月没下过单的用户,如何对该条件进行筛选呢?对于这类问题,在某一月下过单,另一个月未下过单过滤判断,可以转换为次数来判断,如果某个用户一月下过单那么他的下单次数就大于0,如果某用户在二月份未下过单,那么他的下单次数则为0,因而其筛选条件便出来了,判断 1月下过单2月没下过单的用户也就是问题转换为下单次数,1月下单次数大于0二月下单次数为0的用户。
- (2)当月订单金额超过1000元的订单个数。
这个就是按照条件统计个数的问题。典型的使用sum(if XXX,1,0) 或
sum(case when XXX then 1 else 0 end)
- (3) 当月首次下单金额与当月末次下单金额
当月首次下单金额:按照用户,月份分组,时间升序排序,针对该窗口进行排序,序号为rn,当rn=1的时候对应的金额则为首次下单的金额。
当月末次下单金额:需要计算窗口内的记录数,该记录数则为排序后的最后一个位置,该位置处对应的金额数为当月末次下单金额。
具体SQL如下:
with
tmp
as (
select
oid,
uid,
otime,
oamount,
--计算rk的目的是为了取记录中的第一条
row_number()over(partition by uid,date_format( otime,'yyyy-MM') order by otime ) rk,
--计算ct的目的是为了取记录中的最后一条
count(*)over(partition by uid,date_format( otime,'yyyy-MM') ) ct
from t_order order by otime,uid
)
select
uid,
--每个用户一月份的订单数
sum(if( date_format( o.otime,'yyyy-MM')='2018-01' ,1,0)) mon1_count,
--每个用户二月份的订单数
sum(if( date_format( o.otime,'yyyy-MM')='2018-02' ,1,0)) mon2_count,
--每个用户三月份的订单数
sum(if( date_format( o.otime,'yyyy-MM')='2018-03' and o.oamount>10 ,1,0)) mon3_count ,
--当月首次下单的金额
sum(if(rk =1 and date_format( o.otime,'yyyy-MM')='2018-03',o.oamount,0)) first_amount,
--当月末次下单的金额
sum(if(rk =ct and date_format( o.otime,'yyyy-MM')='2018-03',o.oamount,0)) last_amount
from tmp o
group by uid
having mon1_count>0 and mon2_count=0
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 1 1 0 0 0 0
Reducer 2 ...... SUCCEEDED 1 1 0 0 0 0
Reducer 3 ...... SUCCEEDED 1 1 0 0 0 0
Reducer 4 ...... SUCCEEDED 1 1 0 0 0 0
Reducer 5 ...... SUCCEEDED 1 1 0 0 0 0
--------------------------------------------------------------------------------
VERTICES: 05/05 [==========================>>] 100% ELAPSED TIME: 7.54 s
--------------------------------------------------------------------------------
OK
1 1 0 2 120 50
2 5 0 1 5 11
4 1 0 1 30 30
Time taken: 8.642 seconds, Fetched: 3 row(s)
2 小结
思维点击:
- sum(if():有条件累加
- data_format:日期格式化
- row_number() over(partition by):排序。1,2,3,4.。。。
- count(*) over(partition by):分组统计记录数。记录数就是最后一条记录的序号。
- 满足条件的下单记录可以通过转换为下单次数判断,最终再过滤出来。