客户提出要登录系统的活跃用户,定义如下:
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