Question

我有一个表格,把网页上打上,储存起来。

unique_row_id  http_session_id  page_name   page_hit_timestamp
----------------------------------------------------------------
0              123456789        index.html  2010-01-20 15:00:00
1              123456789        info.html   2010-01-20 15:00:05
2              123456789        faq.html    2010-01-20 15:00:15
3              987654321        index.html  2010-01-20 16:00:00
4              987654321        faq.html    2010-01-20 16:00:05
5              987654321        info.html   2010-01-20 16:00:15
6              111111111        index.html  2010-01-20 16:01:00
7              111111111        faq.html    2010-01-20 16:01:05
8              111111111        info.html   2010-01-20 16:01:15

我想提出一个小 que,显示用户最终浏览的最普通页。

So my initial thinking is that in my (java) app, I can run a query that will select the distinct http_session_id values from the table, and then for each distinct http_session_id, run another query that gets the page with the latest page_hit_timestamp, and sum a total for of all these pages. (For the sample data above, I d have a count of 2 for info.html and a count of 1 for faq.html.)

但是,我想知道的是,这究竟是怎样的:把这两个问题合并为单一的小话——还是我是否必须走到储存的程序路线。

我先看一下加入的情况,但如果在这种情况下适用的话,我可以指出。

PS——我知道,我可以像谷歌分析公司一样,为我提供这一信息,但是为了提供这种信息),这是一个流动的网络,对离大陆架分析工具来说并不大,而且b) 我很想知道,这是否能够在Kall进行。

Answer 1

这应当做到:

select 1.page_name, count(*) as ExitPageCount
from WebLog l
inner join (
    select http_session_id, max(page_hit_timestamp)
    from WebLog
    group by session
) lm on l.http_session_id = lm.http_session_id and l.page_hit_timestamp = lm.page_hit_timestamp
group by 1.page_name

Answer 2

SELECT http_session_id, page_name, COUNT(page_name), MAX(page_hit_timestamp)
    FROM table
    GROUP BY http_session_id, page_name

这将使每个网站的浏览量回来,网址是:session_id和page_name组合,浏览量将包括:

http_session_id
page_name
the count of how many times the (http_session_id+page_name) combination occurs in the table
the latest (MAX) timestamp for the combination

Answer 3

你可以提出两点问询,我可以方便地把问答问问问答问问问问答问问问问问问问答问问问问问答问问问问问问问问问问问问问问问问,或视你的需要而定,看看看看看看看看看看看看看看看看看看看。

Answer 4

下表列出了最后访问页:

。

select http_session_id,page_name,page_hit_timestamp from 
(select row_number() over( partition by t.http_session_id order by t.page_hit_timestamp desc) rn,t.* from weblog t
) where rn=1;

if you want count, then the query below may help

select page_name,count(*) from (select 
row_number() over( partition by t.http_session_id order by t.page_hit_timestamp desc) rn,t.* from weblog t
) where rn=1
group by page_name;

友情链接