1 create table test_five_active(
2     active_time string COMMENT ‘活跃日期‘,
3     user_id string COMMENT ‘用户id‘,
4     age int COMMENT ‘用户年龄‘
5 )
6 row format delimited fields terminated by ‘\t‘;

 1 insert into table test_five_active values (‘11‘,‘test_1‘,11);
 2 insert into table test_five_active values (‘11‘,‘test_2‘,22);
 3 insert into table test_five_active values (‘11‘,‘test_3‘,33);
 4 insert into table test_five_active values (‘11‘,‘test_4‘,44);
 5 
 6 insert into table test_five_active values (‘12‘,‘test_3‘,33);
 7 insert into table test_five_active values (‘12‘,‘test_5‘,55);
 8 insert into table test_five_active values (‘12‘,‘test_6‘,66);
 9 
10 insert into table test_five_active values (‘13‘,‘test_4‘,44);
11 insert into table test_five_active values (‘13‘,‘test_5‘,55);
12 insert into table test_five_active values (‘13‘,‘test_7‘,77);

 1 -- 所有用户的总数及平均年龄
 2 select
 3     count(*) sum_user,
 4     avg(age) avg_age
 5 from
 6 (
 7     select 
 8         user_id,
 9         avg(age) age
10     from test_five_active
11     group by user_id
12 ) t1;
13 
14 -- 活跃人数的总数及平均年龄
15 select  -- 最外一层算出活跃用户的个数以及平均年龄
16     count(*),
17     avg(d.age)
18 from
19 (
20     select -- 最后还需要以user_id分组,去重(防止某个用户在11,12号连续活跃,然后在14,15号又连续活跃,导致diff求出不一致,所以此用户会出现两次)
21         c.user_id,
22         c.age
23     from
24     (
25         select -- 以用户和差值diff分组,看分组下的数据的个数是否大于等于2(连续两天登录),取出活跃用户的数据
26             b.user_id,
27             b.age,
28             b.diff,
29             count(*) flag
30         from
31         (
32             select  -- 用活跃日期减去排名,求出差值,看差值是否相等,相等差值的数据肯定是连续活跃的数据
33                 a.active_time,
34                 a.user_id,
35                 a.age,
36                 a.rank_time,
37                 a.active_time-a.rank_time diff 
38             from
39             (
40                 select  -- 以用户和活跃日期分组(去重,防止某个用户在同一天活跃多次),求出每个用户的活跃日期排名
41                     active_time,
42                     user_id,
43                     age,
44                     rank() over(partition by user_id order by active_time) rank_time
45 
46                 from test_five_active
47                 group by active_time,user_id,age   
48             ) a
49         ) b
50         group by b.user_id,b.age,b.diff
51         having count(*) >=2
52     ) c
53     group by c.user_id,c.age
54 ) d;