大数据第50天—Mysql练习题12道之五-活跃用户的总数-杨大伟

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

日期 用户 年龄

11,test_1,23

11,test_2,19

11,test_3,39

11,test_1,23

11,test_3,39

11,test_1,23

12,test_2,19

13,test_1,23

 

create table test_five_active(
    active_time string COMMENT '活跃日期',
    user_id string COMMENT '用户id',
    age int COMMENT '用户年龄'
)
row format delimited fields terminated by '\t';

insert into table test_five_active values ('11','test_1',11);
insert into table test_five_active values ('11','test_2',22);
insert into table test_five_active values ('11','test_3',33);
insert into table test_five_active values ('11','test_4',44);

insert into table test_five_active values ('12','test_3',33);
insert into table test_five_active values ('12','test_5',55);
insert into table test_five_active values ('12','test_6',66);

insert into table test_five_active values ('13','test_4',44);
insert into table test_five_active values ('13','test_5',55);
insert into table test_five_active values ('13','test_7',77);

-- 所有用户的总数及平均年龄
select
    count(*) sum_user,
    avg(age) avg_age
from
(
    select 
        user_id,
        avg(age) age
    from test_five_active
    group by user_id
) t1;

-- 活跃人数的总数及平均年龄
select  -- 最外一层算出活跃用户的个数以及平均年龄
    count(*),
    avg(d.age)
from
(
    select -- 最后还需要以user_id分组,去重(防止某个用户在11,12号连续活跃,然后在14,15号又连续活跃,导致diff求出不一致,所以此用户会出现两次)
        c.user_id,
        c.age
    from
    (
        select -- 以用户和差值diff分组,看分组下的数据的个数是否大于等于2(连续两天登录),取出活跃用户的数据
            b.user_id,
            b.age,
            b.diff,
            count(*) flag
        from
        (
            select  -- 用活跃日期减去排名,求出差值,看差值是否相等,相等差值的数据肯定是连续活跃的数据
                a.active_time,
                a.user_id,
                a.age,
                a.rank_time,
                a.active_time-a.rank_time diff 
            from
            (
                select  -- 以用户和活跃日期分组(去重,防止某个用户在同一天活跃多次),求出每个用户的活跃日期排名
                    active_time,
                    user_id,
                    age,
                    rank() over(partition by user_id order by active_time) rank_time

                from test_five_active
                group by active_time,user_id,age   
            ) a
        ) b
        group by b.user_id,b.age,b.diff
        having count(*) >=2
    ) c
    group by c.user_id,c.age
) d;