SQL - merging two separate sql queries

Question

I have a table that stores page images in a web application, keeping

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 

I want to run a sql query that will show me the most common page where users end browsing.

So my initial thinking is that in my (java) application, I can run a request that selects different http_session_id values ​​from the table, and then for each individual http_session_id, it will launch another request that will get a page with the last 'page_hit_timestamp and summarize the total for all of these pages. (For the example data above, I would have a score of 2 for info.html and a number of 1 for faq.html.)

But, what I would like to know is: is there a way to combine these two queries into a single sql statement - or do I need to go the way of the stored procedure for this?

I looked at using the connection, but I can’t figure out if it is applicable in this scenario.

PS - I know that I could use similar Google Analytics in my application to provide this information to me, but a) it is a mobile web application that is not very suitable for analytics tools on the shelves, and b) I'm just interested to know whether it can be done in SQL.

Answer 1

This should do what you want:

 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 

This will return a string for each combination of http_session_id and page_name, and this string will contain:

• http_session_id
• page_name
• number of attempts (http_session_id + page_name) in the table
• last timestamp (MAX) for combination

Answer 3

Can you provide your two queries, I could easily turn them into a JOIN, or perhaps a subquery, depending on your needs.

Answer 4

The list below lists the most recently viewed pages,

  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 a count, then the request 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;