Hive QL解题流程详解三

有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）日期 用户 年龄

数据集

2019-02-11,test_1,23
2019-02-11,test_2,19
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-12,test_2,19
2019-02-13,test_1,23
2019-02-15,test_2,19
2019-02-16,test_2,19

1） 建表

create table log_info(
dt string,
user string,
age int
)
row format delimited
fields terminated by ',';   

需求：所有用户和活跃用户的总数及平均年龄

2） 按照日期以及用户进行分组，按照日期排序并给出排名

select
dt,
user,
min(age) age,
rank() over(partition by user order by dt) rk
from log_tab
group by
dt,
user;t1

3) 计算日期及排名的差值

select
user,
age,
data_sub(dt,rk) flag
from
t1;t2

4） 过滤出差值大于等于2的，即是连续两天活跃的用户

select
user,
min(age) age,
from
t2
group by
user_id,flag
having count(*)>=2;t3

5) 对数据进行去重处理（一个用户可以在两个不同的时间点连续登录），例如：a用户在1月10号1月11号以及1月20号和1月21号4天登录。

select
user,
min(age) age,
from
t3
group by
user;t4

6) 计算活跃用户的人数以及平均年龄

select
count(*) ct,
cast(sum(age)/count(*) as decimal(10,2))
from
t4;

7) 对全量数据集进行按照用户去重

select
user,
min(age) age,
from
log_tab
group by
user;t5

8) 计算所有用户的数量以及平均年龄

select
count(*) user_count,
cast((sum(age)/count(*)) as decimal(10,1))
from
t5;

9）将五步以及第七步两个数据集进行union操作

略

10) 拼接成最终的SQL

略

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

1） 建表

create table ordertable(
userid string,
money int,
paymenttime string,
orderid string)
row format delimited
fields terminated by 't';

2）查询出

需求：所有用户中在今年10月份第一次购买商品的金额

分析：
	按照用户分组
	条件是今年十月份、第一次购买
	
select
userid,
min(paymenttime) paymenttime
from
ordertable
where
date_format(paymenttime,'yyyy-MM')='2017-10'
group by
userid;t1


select
t1.userid,
t1.paymenttime,
od.money,
from
t1
join
ordertable od
on
t1.userid = od.userid
and
t1.paymenttime=od.paymenttime;

3） SQL

所有用户中在今年10月份第一次购买商品的金额
select
t1.userid,
ti.paymenttime,
od.money
from
(select
userid,
min(paymenttime) paymenttime
from
ordertable
where
date_format(paymenttime,'yyyy-MM')='2017-10'
group by
userid)t1
join
ordertable od
on
t1.userid=od.userid
and
t1.paymenttime=od.paymenttime;