SQL 统计 某段时间内的活跃帐号数
0
悬赏园豆：50
[已关闭问题]
关闭于 2011-04-26 13:40
表描述 某天 某账号 登录次数
date(datetime) accountid(varchar(20)) logincount(int)
表是截面数据，某该帐号没有登录，则也会有数据，只是logincount=0
求sql 某天的活跃账号数(即最近七天登录次数大于等于3次的账号数)
求某段时间内的活跃帐号数.谢谢
所有回答(3)
0
/*create table bw24417
(
[date] datetime,
accountid varchar(20),
logincount int
)
insert into bw24417 values (GETDATE(),'1',1);
insert into bw24417 values (GETDATE(),'3',1);
insert into bw24417 values (GETDATE(),'1',1);
insert into bw24417 values (GETDATE(),'1',1);
insert into bw24417 values (GETDATE(),'2',1);
insert into bw24417 values (GETDATE(),'1',1);
insert into bw24417 values (GETDATE(),'2',1);
insert into bw24417 values (GETDATE(),'4',1);
insert into bw24417 values (GETDATE(),'3',1);
insert into bw24417 values (GETDATE(),'4',1);
insert into bw24417 values (GETDATE(),'3',1);
*/
select * from bw24417;
select accountid,count(logincount) AS huoyue from bw24417 where DATEDIFF(day,[date],GETDATE())<7 group by accountid having count(logincount)>2;
结果：
date accountid logincount
----------------------- -------------------- -----------
2011-04-25 11:30:20.167 1 1
2011-04-25 11:30:20.190 3 1
2011-04-25 11:30:20.190 1 1
2011-04-25 11:30:20.190 1 1
2011-04-25 11:30:20.200 2 1
2011-04-25 11:30:20.200 1 1
2011-04-25 11:30:20.200 2 1
2011-04-25 11:30:20.200 4 1
2011-04-25 11:30:20.200 3 1
2011-04-25 11:30:20.200 4 1
2011-04-25 11:30:20.200 3 1
(11 row(s) affected)
accountid huoyue
-------------------- -----------
1 4
3 3
(2 row(s) affected)
SQL语句中的数字7改为你想要的就OK了啊
可能是我没有表达明白,我说的最近七天,不是与当前日期相对,不是getdate()-7,而是登录日期-7.譬如,我要计算2011-04-25那天的活跃用户数,则是 date between @date-6 and @date .
你用这个：date between @date-6 and @date替换DATEDIFF(day,[date],GETDATE())<7就好了啊，如果写成存储过程就把@date作为一个参数传进来，否则就拼接一个SQL语句就好了啊
呀,你应该在自己的机子上运行下看看结果,需要另外一个表,来表示时间区间,否则是做不出来的.
某段时间，这个时间是业务上的问题，至于这个时间区间是放在另一张表中还是从配置文件中读取都没关系不，只要按照上面的语句稍加修改就可以用了，我不明白你到底指的是什么意思
这是我最后的结果 不过仍然感谢你的回答
--获取活跃用户的数帐号
DECLARE @StartDate DATETIME ,
@EndDate DATETIME
SET @StartDate = '2010-01-04'
SET @EndDate = '2010-01-10' ;
WITH temp
AS ( SELECT A.Date ,
B.UserID ,
iswwt ,
IsRegular
FROM ( SELECT *
FROM dbo.wcfn_GetTimeSpanTable(@StartDate,
@EndDate)
) A ,--时间区间
( SELECT UserID ,
Date ,
IsWWT ,
IsRegular
FROM Factloginlog
WHERE Date BETWEEN @StartDate - 6 AND @EndDate
AND loginPAD + loginMobile > 0
) B--区间内的登录记录
WHERE B.Date BETWEEN A.Date - 6 AND A.Date
GROUP BY A.Date ,
B.UserID ,
b.IsWWT ,
b.IsRegular
HAVING COUNT(B.Date) > 2
)
SELECT date ,
SUM(CASE WHEN ( iswwt = 0
AND IsRegular = 1
AND NOT ( UserID LIKE 'SJZC%' )
AND UserID <> '____anonymous'
AND UserID <> 'null'
) THEN 1
ELSE 0
END) AS [正式机构活跃数] ,
SUM(CASE WHEN ( ( iswwt <> 0
OR iswwt IS NULL
)
AND IsRegular = 1
AND NOT ( UserID LIKE 'SJZC%' )
AND UserID <> '_
--获取活跃用户的数帐号
DECLARE @StartDate DATETIME ,
@EndDate DATETIME
SET @StartDate = '2010-01-04'
SET @EndDate = '2010-01-10' ;
WITH temp
AS ( SELECT A.Date ,
B.UserID ,
iswwt ,
IsRegular
FROM ( SELECT *
FROM dbo.wcfn_GetTimeSpanTable(@StartDate,
@EndDate)
) A ,--时间区间
( SELECT UserID ,
Date ,
IsWWT ,
IsRegular
FROM Factloginlog
WHERE Date BETWEEN @StartDate - 6 AND @EndDate
AND loginPAD + loginMobile > 0
) B--区间内的登录记录
WHERE B.Date BETWEEN A.Date - 6 AND A.Date
GROUP BY A.Date ,
B.UserID ,
b.IsWWT ,
b.IsRegular
HAVING COUNT(B.Date) > 2
)
SELECT date ,
SUM(CASE WHEN ( iswwt = 0
AND IsRegular = 1
AND NOT ( UserID LIKE 'SJZC%' )
AND UserID <> '____anonymous'
AND UserID <> 'null'
) THEN 1
ELSE 0
END) AS [正式机构活跃数] ,
SUM(CASE WHEN ( ( iswwt <> 0
OR iswwt IS NULL
)
AND IsRegular = 1
AND NOT ( UserID LIKE 'SJZC%' )
AND UserID <> '_
0
select
convert(varchar(6),date,112),
accountid From [table] Group By convert(varchar(6),date,112) Having lodincount>2
推荐一篇博客
http://www.cnblogs.com/mapeng-11/archive/2011/03/09/1978417.html
我觉得没有仔细看题目 不过仍感谢
0
declare @days int,@count int,@date datetime
set @days=7
set @count=3
set @date=getdate()
select accountid from [loginInfoTable]
where datediff(dd,@date,[date])<@days
group by accountid
having sum(logincount)>=@count
