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

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

日期 用户 年龄
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
2019-02-17,test_4,35
2019-02-18,test_4,35
2019-02-22,test_4,35
2019-02-23,test_4,35

建表

create table users(day_time string,user_id string,age int)
row format delimited
fields terminated by ',' 
lines terminated by '\n'
stored as textfile;

导入数据

load data local inpath '/opt/data/user.txt' into table users;

注意：这里都默认每个人的年龄是不变的，不考虑一个人的年龄增长的情况；

用到的知识点有hive的 over（）开窗函数、row_number（）函数、lead（）函数

思路一

1. 求所有用户的总数和平均年龄

1)先求出每个用户的年龄

select user_id,avg(age) from users group by user_id;

user_id c1
test_1  23.0
test_2  19.0
test_3  39.0
test_4  35.0

2) 再求所有用户的总数和平均年龄

select 
	count(a.user_id) `用户数`,
	avg(a.av) `所有用户的平均年龄`
from(
	select user_id,avg(age) av from users group by user_id
) a;

用户数  所有用户的平均年龄
4       29.0

2.求活跃用户总数和平均年龄（活跃用户指连续两天都有访问记录的用户）

1) 因为一个用户每天可能登陆多次，计算活跃用户数时每天登陆一次就算今日活跃过了，所以要对每日登陆的用户去重

select distinct day_time,user_id,age from users;

day_time        user_id age
2019-02-11      test_1  23
2019-02-11      test_2  19
2019-02-11      test_3  39
2019-02-12      test_2  19
2019-02-13      test_1  23
2019-02-15      test_2  19
2019-02-16      test_2  19
2019-02-17      test_4  35
2019-02-18      test_4  35
2019-02-22      test_4  35
2019-02-23      test_4  35

2) 这里开始到了活跃用户的计算逻辑了：

先用 row_number() over(partiton by _ order by _) 对用户进行分组，对登陆日期进行排序，排序完就会多出来一列(排序的列)；

select 
	t1.user_id,t1.day_time,
	row_number() over(partition by t1.user_id