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

 

 

select count(*),round(avg(age),1) from (
select user_id,age from log group by user_id,age) as a

union all

select count(*), round(avg(age),1) from(

select distinct user_id,age from (
select *,lead(date,1)over(PARTITION by user_id ORDER BY date ) as rn from
(
select * from log group by date,user_id,age) as a )as b

where DATEDIFF(rn,date)=1) as c;

1.对同一用户进行去重处理

2.通过lead（），datediff()函数

3.union all 不是活跃用户