sql 日志统计-日、周、月活跃数

近日网站需求：统计日志表的 日、周、月活跃数。最终研究了出来了，分享给大家看下。  如果有更好的sql语句也可以评论下方。

--日活跃量
select CONVERT(varchar(100), cr.AddTime, 23)as addtimt,COUNT(distinct UserId )as ucount  from log.ClickRecord cr  with(nolock)
where 1=1 --and  AddTime>'2017-04-07'
group by CONVERT(varchar(100), cr.AddTime, 23)   order by addtimt;
--周活跃量 
 select (ltrim (datepart(YYYY,AddTime)) +'-'+ LTRIM( datename(WEEK,addtime))) as Indexs, COUNT(distinct UserId) as ucounts from log.ClickRecord 
 where year(addtime) = '2017'  
 group by (ltrim (datepart(YYYY,AddTime)) +'-'+ LTRIM( datename(WEEK,addtime)))  order by  Indexs ;
--注释:(ltrim (datepart(YYYY,AddTime)) +'-'+ LTRIM( datename(WEEK,addtime))) 为了拼接成 2017-11  周

--月活跃量
 select  datename(MONTH,addtime) +'月'as Indexs, COUNT(distinct UserId) as ucounts from log.ClickRecord 
 where addtime > '2017-4-1'  
 group by datename(MONTH,addtime) order by datename(MONTH, addtime)  ;

       

 

转载于:https://www.cnblogs.com/longm/p/6867628.html