如何使用条件计算sql窗口分区中的时间差

发布于 2025-01-09 12:11:14 字数 8 浏览 1 评论 0原文

continue

Problem

I have a table which lists series of events as "fetch" and click". User can perform multiple searches in 60 seconds. The tasks need to be sequenced so that user is performing a fetch and click within 60 seconds or multiple fetches within 60 seconds of each other and then clicking

Fetch -> Click (user found the answer within 60 seconds )
Fetch -> Fetch ->...Click (user didn't find the answer and kept searching until found the answer and then clicked within 60 seconds)
Fetch -> no action (user didn't perform any further action after first fetch)

What I tried in the code below is to partition first on user_id, time as hour(hh) and time as minute(mm) to see the events that occurred within 60 seconds of each other but same hour and minute

I'm getting stuck at calculating how many clicks and fetches exist in a partition. I need to produce Output table by checking the conditions that

If there is only 1 fetch and 1 click in a partition then flag as NA
If (count(fetch) >=2 and count(click) = 1) and click is within 60 seconds of first fetch by the user then flag as Clicked and calculate the time between click and first fetch
If there is only 1 fetch and no subsequent click, then flag it as Not-Clicked and calculate the time between this fetch and next fetch

Output table shows the desired data and columns.

Select a.window, a.user_id, a.search_id, a.date, a.time, a.action,

    count(case when a.action = 'click' then 1 else 0 end)  
       over(Partition BY  a.user_id, a.hh, a.mm ORDER BY  a.user_id asc, a.hh asc, a.mm asc) as total_clicks ,

    count(case when a.action = 'fetch' then 1 else 0 end) as total_fetches 
       over(Partition BY  a.user_id, a.hh, a.mm ORDER BY  a.user_id asc, a.hh asc, a.mm asc) as total_fetches 
From

(

    SELECT
        a.user_id, a.search_id, a.date, a.time, a.action, hour(a.time) as hh, minute(a.time) as mm,
        dense_rank() OVER ( ORDER BY  a.user_id asc, hour(a.time) asc, minute(a.time asc)) as window
     

    From input_table a

) as a

Input Table

window	user_id	search_id	date	time	action
1	u1	s1	1/4/22	01/04/2022 10:05:10	fetch
1	u1	s1	1/4/22	01/04/2022 10:05:17	click
2	u1	s1	1/4/22	01/04/2022 10:16:02	fetch
2	u1	s2	1/4/22	01/04/2022 10:16:32	fetch
2	u1	s2	1/4/22	01/04/2022 10:16:37	click
5	u3	s7	1/4/22	01/04/2022 04:36:00	fetch
6	u3	s8	1/4/22	01/04/2022 04:37:00	fetch
6	u3	s8	1/4/22	01/04/2022 04:37:01	fetch
6	u3	s8	1/4/22	01/04/2022 04:37:04	click

Desired Output

window	user_id	search_id	date	time	action	flag	clicked_time	left_time
1	u1	s1	1/4/22	01/04/2022 10:05:10	fetch	NA
1	u1	s1	1/4/22	01/04/2022 10:05:17	click	NA
2	u1	s1	1/4/22	01/04/2022 10:16:02	fetch	Clicked
2	u1	s2	1/4/22	01/04/2022 10:16:32	fetch	Clicked
2	u1	s2	1/4/22	01/04/2022 10:16:37	click	Clicked	35
5	u3	s7	1/4/22	01/04/2022 04:36:00	fetch	Not-Clicked		60
6	u3	s8	1/4/22	01/04/2022 04:37:00	fetch	Clicked
6	u3	s8	1/4/22	01/04/2022 04:37:01	fetch	Clicked
6	u3	s8	1/4/22	01/04/2022 04:37:04	click	Clicked	04