Window function - giải quyết bài toán tìm userflow trong analytic

Bài đăng này đã không được cập nhật trong 4 năm

Bài toán userflow trong analytic.

Một vấn đề khá đơn giản là làm thế nào vẽ được đi của user trong trang web của bạn, tất cả dữ liệu mình lưu vào bảng:

'time', 
'session_id', // trong session nào 
'page_id', // page di chuyển là page gì 
'page_ord', // page thứ mấy trong session
'start_time', // thời gian bắt đầu vào page
'end_time', // thời gian ra khỏi page
'page_referer_id',
'next_page_id'

Câu hỏi đặt ra là làm thế nào để query tạo nên các step pageview như trên :#)

Giải quyết:

Tại mỗi step chúng ta tìm các page có vị trí thứ n trong session, bằng cách dùng window function, chúng ta tìm ra được với page hiện tại thì page tiếp theo là gì:

select * from (
	select sp.time,
          sp.session_id,
          page.page_url,
          sp.page_ord,
          lead(page_url) over (partition by sp.session_id order by sp.time asc) as next_page,
          lag(page_url) over (partition by sp.session_id order by sp.time asc) as prev_page,
          lead(page_ord) over (partition by sp.session_id order by sp.time asc) as next_ord

   from "session_page" as "sp"
   inner join "page" on "page"."id" = "sp"."page_id"
   and time >= '2019-04-22 00:00:00'
   order by "time" asc
   
 ) as sub where prev_page = '/2019/01/05/go-by-example-range/' and page_ord = 2;

Với mỗi page tìm ra được, chúng ta query tính toán số lượng các visit tới page tiếp theo:

-- query event flow sub 

select count(distinct(session_id)), next_page from (
	select sp.time,
          sp.session_id,
          page.page_url,
          sp.page_ord,
          lead(page_url) over (partition by sp.session_id order by sp.time asc) as next_page,
          lead(page_ord) over (partition by sp.session_id order by sp.time asc) as next_ord

   from "session_page" as "sp"
   inner join "page" on "page"."id" = "sp"."page_id"
   and time >= '2019-04-22 00:00:00'
   order by "time" asc
   
 ) as sub where page_url = '/2019/01/05/go-by-example-range/' and page_ord = 1 group by next_page;