统计活跃用户

客户提出要登录系统的活跃用户，定义如下：
an active definition can be one that at least download 5 times in a week.

构思如下：
1.首先要循环每个7天的时间段
2.找出时间段内登陆超过5次的
3.循环插入临时表
4.在临时表里group by 统计用户

--创建临时表开始
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Temp_ActivtUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Temp_ActivtUser]
GO

CREATE TABLE [dbo].[Temp_ActivtUser] (
 [userid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [logon] [int] NULL ,
 [beginDate] [datetime] NULL
) ON [PRIMARY]
--创建临时表结束

DECLARE @today datetime
DECLARE @i datetime
SET @today = getdate()
--开始日期
SET @i='2009-08-01'

WHILE @i<=@today  
BEGIN 
 
 print @i
 DECLARE UserCursor CURSOR DYNAMIC
   FOR
        --一周内所有登陆等于或超过五次的用户
        select userid,count(*) as aa from TB_LOGINRECORD 
        where logonDate between @i and DATEADD(day,6,@i)
        Group by userid having count(*)>4
  
       OPEN UserCursor
       DECLARE @userid nvarchar(20)
       DECLARE @userNum int
 
 FETCH NEXT FROM UserCursor INTO @userid,@userNum
 WHILE @@FETCH_STATUS = 0
 BEGIN
  --循环体
  insert into Temp_ActivtUser values(@userid,@userNum,@i)
  FETCH NEXT FROM UserCursor INTO @userid,@userNum
 END 
 --关闭游标
 CLOSE UserCursor 
 --释放游标
 DEALLOCATE UserCursor 

 SET @i = DATEADD(day,1,@i)
 
 
END

 

转载于:https://www.cnblogs.com/haoch_00/archive/2009/09/07/1561996.html