使用SQL查询user_id，会话的开始和结束时间(如果我们只知道user_id，actions_made和访问时间

Question

我的任务是打开试用集群Redshift并从csv文件上传数据。

在csv文件中，提供了有关用户ID的信息，他访问过的页面以及访问时间长达毫秒。

任务是找到用户按给定顺序完成后续操作的所有"会话"：rooms.homework-showcase，rooms.view.step.content，rooms.lesson.rev.step.content。(他不能连续这样做(。

"会话"是指操作之间经过的时间不超过一小时。

输出应包含用户 ID、会话开始时间和会话结束时间。

csv 中给出的内容示例：

57529,rooms.homework-showcase,2017-03-01T00:00:07.710000
57529,rooms.view.step.content,2017-03-01T00:00:10.275000
57529,rooms.view.step.content,2017-03-01T00:00:10.436000
168671,rooms.view.step.content,2017-03-01T00:00:12.035000
168671,rooms.view.step.content,2017-03-01T00:00:50.632000
64788,rooms.view.step.content,2017-03-01T00:01:21.460000
93698,rooms.view.step.content,2017-03-01T00:02:41.963000
205265,rooms.homework-showcase,2017-03-01T00:02:45.241000
205265,rooms.test-showcase,2017-03-01T00:02:57.854000
205265,notes,2017-03-01T00:03:01.016000

到目前为止我写的：

select *, 
case
when timepass IS NULL then ncount
when (timepass>=3600) and lag(timepass<3600) over(order by user_id, datet) 
then ncount
else Null
end as startt,
case
when (timepass<3600) and lead(timepass IS NULL) over(order by user_id, 
datet) then ncount
when (timepass<3600) and lead(timepass>=3600) over(order by user_id, datet) 
then ncount
else Null
end as endt
from
(
select *, row_number() OVER(ORDER BY user_id, datet) as ncount, 
case 
when page_name = 'rooms.homework-showcase' then 0 
when page_name = 'rooms.view.step.content' then 1
when page_name = 'rooms.lesson.rev.step.content' then 2
else 3
end as page_order
from
(
select *, EXTRACT(EPOCH from datet) - lag(EXTRACT(EPOCH from datet)) 
over(partition by user_id order by user_id, datet) as timepass from
(
SELECT *, to_timestamp(date_time, 'YYYY-MM-DD HH:MI:SS:US') as datet from 
testtbl
order by date_time asc
) as t1
group by user_id, page_name, date_time, datet
order by user_id, datet asc 
) as t3
) as t4
;

我的输出结果

我可以弄清楚会话的开始和结束(除了我不知道如何将最后一行作为会话的结束(。一个用户可以有多个会话。

如何查询正确的会话并从中获取会话user_id、开始和结束？你能给我一些想法吗？我没有足够的SQL知识来了解编写正确查询的方式。

附言我已经附上了输出应该是什么样子。这显示了特定用户和他的一个会话，属于给定标准。预期的输出是最后一张图片。并且应该列出所有"好"会话。

csv 文件位于此链接下 https://drive.google.com/open?id=0B288Ep6mrn6fWlQ0a3doV0hjQjA

DB 小提琴 https://www.db-fiddle.com/f/bz92LHiTW5RrFBUkaH2KtR/0#&togetherjs=m6SnA9xLSS

Answer 1

我已经通过自连接使用以下代码为您提供的示例数据运行。 下面是代码：

选择 user_id，session_begin，最大 (session_end( 作为session_end

(选择 user_id，session_begin，大小写，当session_end为 null 时session_begin否则session_end结尾为 session_end

(选择a.user_id为 user_id，a.datevisited，a.date_time为 session_begin，a.session_hour，当 (b.date_time>a.date_time 和 a.session_hour>=b.date_time b.date_time(时，否则 null 结尾为 session_end 从

(从页面中选择user_id，trunc(date_time(作为访问日期，date_time，DateAdd(小时，1，date_time(session_hour ( a

左联接 (从页面中选择user_id，trunc(date_time(作为访问日期，date_time，DateAdd(小时，1，date_time(session_hour ( b

on a.user_id=b.user_id 和 a.datevisited=b.datevisited

分组依据 1,2,3,4,5(

分组 1,2,3(决赛

按 1,2 分组;

在你最后测试它。有关代码中使用的各种函数，您可以参考 Redshift 文档：https://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html https://docs.aws.amazon.com/redshift/latest/dg/r_DATEDIFF_function.html