I have a database in which there are multiple columns. I am trying to find distinct web pages visited provided the time stamp on these pages are not the same. For example:
我有一个数据库,其中有多列。我试图找到访问过的不同网页,前提是这些页面上的时间戳不一样。例如:
Sno User Page Timestamp
1 A google 18.00
2 A yahoo 18.00
3 A bing 19.00
4 A facebook 20.00
5 A insta 21.00
6 A twitter 21.00
7 A bing 22.00
the result should be:
结果应该是:
User Count
A 4
Google and yahoo has same-time stamp so it should count as 1 page visited and not 2, similarly insta and twitter has same time stamp so should also count as 1. Also, since bing is coming again in the end, it should not count that as the user has already visited that earlier. Any help would be greatly appreciated.
谷歌和雅虎有相同的时间戳,所以它应该算作1页访问而不是2,类似的insta和twitter有相同的时间戳,所以也应该算作1.另外,由于bing最后会再次出现,它不应该算在内因为用户之前已经访问过。任何帮助将不胜感激。
3 个解决方案
#1
0
Here's one option using row_number to get the first instance to each page visited by user, and then you can use count with distinct to get the total you need, but I'm not understanding why you want to return sno = 1:
这里有一个选项,使用row_number来获取用户访问的每个页面的第一个实例,然后你可以使用带有distinct的count来获得你需要的总数,但是我不明白你为什么要返回sno = 1:
select user, count(distinct timestamp)
from (
select *, row_number() over (partition by user, page order by timestamp) rn
from yourtable
) t
where rn = 1
group by user
If you need sno = 1, you could use min(sno), but again, not sure why.
如果你需要sno = 1,你可以使用min(sno),但是不知道为什么。
#2
0
You can do it with a simple CTE. Assuming the data is:
你可以用一个简单的CTE来做到这一点。假设数据是:
create table t1 (
sno int,
usr varchar(10),
page varchar(10),
ts int
);
insert into t1 (sno, usr, page, ts) values (1, 'A', 'google', 18);
insert into t1 (sno, usr, page, ts) values (2, 'A', 'yahoo', 18);
insert into t1 (sno, usr, page, ts) values (3, 'A', 'bing', 19);
insert into t1 (sno, usr, page, ts) values (4, 'A', 'facebook', 20);
insert into t1 (sno, usr, page, ts) values (5, 'A', 'insta', 21);
insert into t1 (sno, usr, page, ts) values (6, 'A', 'twitter', 21);
insert into t1 (sno, usr, page, ts) values (7, 'A', 'bing', 22);
The query could be:
查询可以是:
with g (p, c) as (
select max(page), count(*)
from t1
group by ts
)
select count(distinct p) as my_count from g;
Result:
my_count
--------
4
Or... you can also do:
或者......你也可以这样做:
select count(distinct p) from (
select max(page) as p, count(*) as c
from t1
group by ts
) x;
with the same result.
结果相同。
#3
0
you can use rank function
你可以使用等级功能
select User_, count(distinct timestamp) as count
from (
select *, rank() over (partition by User_, page order by timestamp) grp
from t
) t1
where grp = 1
group by User_
http://sqlfiddle.com/#!18/31979/6
User_ count
A 4
#1
0
Here's one option using row_number to get the first instance to each page visited by user, and then you can use count with distinct to get the total you need, but I'm not understanding why you want to return sno = 1:
这里有一个选项,使用row_number来获取用户访问的每个页面的第一个实例,然后你可以使用带有distinct的count来获得你需要的总数,但是我不明白你为什么要返回sno = 1:
select user, count(distinct timestamp)
from (
select *, row_number() over (partition by user, page order by timestamp) rn
from yourtable
) t
where rn = 1
group by user
If you need sno = 1, you could use min(sno), but again, not sure why.
如果你需要sno = 1,你可以使用min(sno),但是不知道为什么。
#2
0
You can do it with a simple CTE. Assuming the data is:
你可以用一个简单的CTE来做到这一点。假设数据是:
create table t1 (
sno int,
usr varchar(10),
page varchar(10),
ts int
);
insert into t1 (sno, usr, page, ts) values (1, 'A', 'google', 18);
insert into t1 (sno, usr, page, ts) values (2, 'A', 'yahoo', 18);
insert into t1 (sno, usr, page, ts) values (3, 'A', 'bing', 19);
insert into t1 (sno, usr, page, ts) values (4, 'A', 'facebook', 20);
insert into t1 (sno, usr, page, ts) values (5, 'A', 'insta', 21);
insert into t1 (sno, usr, page, ts) values (6, 'A', 'twitter', 21);
insert into t1 (sno, usr, page, ts) values (7, 'A', 'bing', 22);
The query could be:
查询可以是:
with g (p, c) as (
select max(page), count(*)
from t1
group by ts
)
select count(distinct p) as my_count from g;
Result:
my_count
--------
4
Or... you can also do:
或者......你也可以这样做:
select count(distinct p) from (
select max(page) as p, count(*) as c
from t1
group by ts
) x;
with the same result.
结果相同。
#3
0
you can use rank function
你可以使用等级功能
select User_, count(distinct timestamp) as count
from (
select *, rank() over (partition by User_, page order by timestamp) grp
from t
) t1
where grp = 1
group by User_
http://sqlfiddle.com/#!18/31979/6
User_ count
A 4