当用用户回头率分析,一天算一次,如果一天来访了N次也算一次。第二天再来才能加一次
这是一份网站用户来访统计表
--------------------------------
id 用户 时间
a 2008-9-1
a 2008-9-1
c 2008-9-1
b 2008-9-1
c 2008-9-1
d 2008-9-1
e 2008-9-1
d 2008-9-1 a 2008-9-2
a 2008-9-2
b 2008-9-2
a 2008-9-2
a 2008-9-2
d 2008-9-2 a 2008-9-3我要查询回仿次数为1次的用户结果=3 (因为第二天。。有abd 都来访了)我要查询回访次数为2次的用户
结果=1 (国为第三天。只有a 来访了)
这是一份网站用户来访统计表
--------------------------------
id 用户 时间
a 2008-9-1
a 2008-9-1
c 2008-9-1
b 2008-9-1
c 2008-9-1
d 2008-9-1
e 2008-9-1
d 2008-9-1 a 2008-9-2
a 2008-9-2
b 2008-9-2
a 2008-9-2
a 2008-9-2
d 2008-9-2 a 2008-9-3我要查询回仿次数为1次的用户结果=3 (因为第二天。。有abd 都来访了)我要查询回访次数为2次的用户
结果=1 (国为第三天。只有a 来访了)
a 2008-9-1 1:23:23
a 2008-9-1 2:23:23
c 2008-9-1 4:23:23
b 2008-9-1 5:23:23
c 2008-9-1 6:23:23
d 2008-9-1 7:23:23
e 2008-9-1 8:23:23
d 2008-9-1 9:23:23 a 2008-9-2 1:23:23
a 2008-9-2 1:23:23
b 2008-9-2 2:23:23
a 2008-9-2 3:23:23
a 2008-9-2 4:23:23
d 2008-9-2 5:23:23 a 2008-9-3 1:23:23
if not object_id('Tempdb..#T2') is null
drop table #T2
Go
set nocount on ;
Create table #T2([用户] nvarchar(1),[时间] Datetime)
Insert #T2
select N'a','2008-9-1 1:23:23' union all
select N'a','2008-9-1 2:23:23' union all
select N'c','2008-9-1 4:23:23' union all
select N'b','2008-9-1 5:23:23' union all
select N'c','2008-9-1 6:23:23' union all
select N'd','2008-9-1 7:23:23' union all
select N'e','2008-9-1 8:23:23' union all
select N'd','2008-9-1 9:23:23' union all
select N'a','2008-9-2 1:23:23' union all
select N'a','2008-9-2 1:23:23' union all
select N'b','2008-9-2 2:23:23' union all
select N'a','2008-9-2 3:23:23' union all
select N'a','2008-9-2 4:23:23' union all
select N'd','2008-9-2 5:23:23' union all
select N'a','2008-9-3 1:23:23'
Goselect 用户,count(distinct convert(varchar(10),时间,120)) as 访问次数 from #T2 group by 用户 having count(distinct convert(varchar(10),时间,120)) =1用户 访问次数
---- -----------
c 1
e 1
-->生成测试数据
declare @tb table([用户] nvarchar(9),[时间] datetime)
Insert @tb
select N'a',N'2008-9-1 1:23:23' union all
select N'a',N'2008-9-1 2:23:23' union all
select N'c',N'2008-9-1 4:23:23' union all
select N'b',N'2008-9-1 5:23:23' union all
select N'c',N'2008-9-1 6:23:23' union all
select N'd',N'2008-9-1 7:23:23' union all
select N'e',N'2008-9-1 8:23:23' union all
select N'd',N'2008-9-1 9:23:23' union all
select N'a',N'2008-9-2 1:23:23' union all
select N'a',N'2008-9-2 1:23:23' union all
select N'b',N'2008-9-2 2:23:23' union all
select N'a',N'2008-9-2 3:23:23' union all
select N'a',N'2008-9-2 4:23:23' union all
select N'd',N'2008-9-2 5:23:23' union all
select N'a',N'2008-9-3 1:23:23'
select 用户,count(distinct convert(varchar(10),时间,120) ) as 访问次数
from @tb
group by 用户
--having count(distinct convert(varchar(10),时间,120))=1
/*
用户 访问次数
--------- -----------
a 3
b 2
c 1
d 2
e 1
*/
select 用户,count(distinct convert(varchar(10),时间,120) ) as 访问次数
from @tb
group by 用户
having count(distinct convert(varchar(10),时间,120))=1
/*
用户 访问次数
--------- -----------
c 1
e 1
*/
CAST 和 CONVERT (Transact-SQL)
http://msdn.microsoft.com/zh-cn/library/ms187928.aspx
这样对了吗
select count(1) as c from (
select 用户,count(distinct convert(varchar(10),时间,120) ) as 访问次数
from @tb
group by 用户
having count(distinct convert(varchar(10),时间,120))=1
) a