web | Java

写出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;