我正在使用下表跟踪模块中的页面视图:
id | page_id | user_id | session_id | created每个页面对于某个模块都是唯一的; 因此,第14页仅存在于模块2中。
我正在尝试编写一个查询,告诉我何时开始查看会话(GROUP BY session_id)特定模块,但我的订购时遇到问题。 基本上,对于该分组会话,我想返回最早的“已创建”(查看会话的开始时间)和最新的page_id(它们在模块中获得的距离)。
完成此任务的最佳方法是什么?
I am tracking page views within modules using the following table:
id | page_id | user_id | session_id | createdEvery page is unique to a certain module; so page 14 only exists in module 2, for instance.
I am trying to write a query that tells me when a viewing session began (GROUP BY session_id) for a particular module, but I am having trouble with my ordering. Basically, for that grouped session, I want to return the earliest 'created' (the starting time of the viewing session) and the latest page_id (how far they got within the module).
What is the best way to accomplish this?
最满意答案
我想你需要在select中使用子查询:
SELECT a.session_id, MIN(a.created) as start_time, b.page_id AS last_page, b.end_time FROM table a INNER JOIN (SELECT b.session_id, MAX(b.created) as end_time, MAX(b.page_id) FROM table b GROUP BY b.session_id) ON a.session_id = b.session_id GROUP BY a.session_idI think you'll need to utilise sub-queries in the select:
SELECT a.session_id, MIN(a.created) as start_time, b.page_id AS last_page, b.end_time FROM table a INNER JOIN (SELECT b.session_id, MAX(b.created) as end_time, MAX(b.page_id) FROM table b GROUP BY b.session_id) ON a.session_id = b.session_id GROUP BY a.session_id更多推荐
发布评论