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

题目

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

日期        用户   年龄
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

SQL

分析：活跃用户的计算通过四步骤计算，
第一步，对用户活跃日期进行排名；
第二步，计算活跃日期及排名之间的差值；
第三步，按照用户及差值进行分组，统计差值个数，选择差值相同个数大于等于N的数据
第四步，group by去重，即为连续N天以上活跃用户数。
计算完活跃用户数就好做了，之后将所有用户和活跃用户进行union all即可。
1）建表

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

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

select
	dt,
	user_id,
	min(age) age,
	rank() over(partition by user_id
order by
	dt) rk
from
	user_age
group by
	dt,
	user_id;

最后将整体作为t1。

一定要对dt和user_id进行分组，因为这两个字段重复，不然结果如下：

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

select
   user_id,
   age,
   date_sub(dt,rk) flag
from
	t1;

最后将整体作为t2。

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

select
	user_id,
	min(age) age
from
	t2
group by
	user_id,
	flag
HAVING
	count(*) > 1;

最后将整体作为t3。

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

select
	user_id,
	min(age) age
from
	t3
group by
	user_id;

最后将整体作为t4。

6）计算活跃用户（两天连续有访问）的人数以及平均年龄

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

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

select
   user_id,
   min(age) age 
from
   user_age 
group by 
   user_id;

最后将整体作为t5。

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

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

9）将第5步以及第7步两个数据集进行union all操作

select
   0 user_total_count,
   0 user_total_avg_age,
   count(*) twice_count,
   cast(sum(age)/count(*) as decimal(10,2)) twice_count_avg_age
from
   t4
UNION ALL 
select
   count(*) user_total_count,
   cast((sum(age)/count(*)) as decimal(10,1)),
   0 twice_count,
   0 twice_count_avg_age
from
	t5;

最后将整体作为t6。

10）求和并拼接为最终SQL
完整SQL

with
t1 as (select
	dt,
	user_id,
	min(age) age,
	rank() over(partition by user_id
order by
	dt) rk
from
	user_age
group by
	dt,
	user_id),
t2 as (select
   user_id,
   age,
   date_sub(dt,rk) flag
from
	t1),
t3 as (select
	user_id,
	min(age) age
from
	t2
group by
	user_id,
	flag
HAVING
	count(*) > 1),
t4 as (select
	user_id,
	min(age) age
from
	t3
group by
	user_id),	
t5 as (select
   user_id,
   min(age) age 
from
   user_age 
group by 
   user_id),
t6 as (select
   0 user_total_count,
   0 user_total_avg_age,
   count(*) twice_count,
   cast(sum(age)/count(*) as decimal(10,2)) twice_count_avg_age
from
   t4
UNION ALL 
select
   count(*) user_total_count,
   cast((sum(age)/count(*)) as decimal(10,2)),
   0 twice_count,
   0 twice_count_avg_age
from
	t5)
---------------
select 
    sum(user_total_count),
    sum(user_total_avg_age),
    sum(twice_count),
    sum(twice_count_avg_age)
from t6;

说明：复杂SQL分步写，用上面的写法更方便。