HiveSql面试题10--sum(if)统计问题

目录

0 需求分析

1 需求实现

2 小结

---

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)：分组统计记录数。记录数就是最后一条记录的序号。
• 满足条件的下单记录可以通过转换为下单次数判断，最终再过滤出来。