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) = ''

 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)  ;