写出HQL 求所有用户和活跃用户的总数及平均年龄(活跃用户指连续两天都有访问记录的用户)
千锋教育
select sum(user_total_count),
sum(user_total_avg_age),
sum(twice_count),
sum(twice_count_avg_age)
from (
select 0 `user_total_count`,
0 `user_total_avg_age`,
count(*) `ct`,
cast(sum(age)/count(*) as decimal(10,2)) `twice_count_avg_age`
from (
select user_id,
min(age) `age`
from (
select user_id,
min(age) `age`
from (
select user_id,
age,
date_sub(dt,rank) `flag`
from (
select dt,
user_id,
min(age) `age`,
rank() over(partition by user_id order by dt) `rank`
from user_age
group by dt,user_id
) t1
) t2
group by user_id,flag
having count(*) >= 2
) t3
group by user_id
) t4
union all
select count(*) `user_count`,
cast((sum(age)/count(*)) as decimal(10,1)),
0 `twice_count`,
0 `twice_count_avg_age`
from (
select user_id,
min(age) `age`
from user_age
group by user_id
) t5
) t6;