数据, 术→技巧

通过SQL定义用户浏览Session

钱魏Way · · 10 次浏览

PC时代,用户问页面时,我们会先检查用户的Cookie中是否存在SessionId,如果不存在,则会通过随机数的方式生成一个SessionId存入Cookie中。如果存在,我们会更新这个Cookie的失效时间(30分钟后)。即只要用户访问的间隔在30分钟内则被认为是同一个Seesion,超过30分钟则会生成一个新的SeesionId,将浏览定义为一个新的Session。

APP时代或者小程序的时代,通常我们会把App的每次打开作为一次Seesion来记录,Cookie的概念被抛弃,但中间忽略了一个重要的问题:在你使用App或者小程序的过程,非常有可能会被其他应用程序中断,比如电话、短信、微信、推送等。当用户切屏以后Session的记录就会发生改变,这时候统计的Session数据往往是不准确的。今天要分享的是如何通过SQL的方式来定义Session。

理清思路

用户浏览日志中,我们通常能够记录到用户的身份和访问时间。在Session定义中我们首先需要识别唯一用户,并按用户的浏览时间对日志进行排序,处理完成后需要计算日志间的时间差,并将大于30分钟的浏览定位为新的Session。

逻辑转SQL

1 计算每次访问的上次访问时间

可以使用窗口函数LAG实现。具体代码如下:

SELECT *, LAG(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event
FROM tutorial.playbook_events

执行效果如下:

其中:

  • user_id:用户身份ID
  • occurred_at:当前访问时间
  • last_event:上次访问时间

2 计算访问时间差,确定是否为新的访问

判断是否是新的访问主要通过两种方式:

  • 本次访问没有上次访问时间
  • 本次访问和上次访问时间差>30分钟(30分钟还是10分钟可以根据业务场景自己定义)
SELECT *
    , CASE 
        WHEN unix_timestamp(occurred_at, 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(last_event, 'yyyy-MM-dd HH:mm:ss') >= 60 * 30
        OR last_event IS NULL THEN 1
        ELSE 0
    END AS is_new_session
FROM (
    SELECT *, LAG(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event
    FROM tutorial.playbook_events
) t1

执行效果如下:

2 定义SessionId

有了上面的数据,还缺一个SessionId,实现的方法还是通过窗口函数实现。具体代码如下:

SELECT user_id, occurred_at, SUM(is_new_session) OVER (ORDER BY user_id, occurred_at ROWS BETWEEN unbounded PRECEDING AND CURRENT ROW) AS global_session_id
    , SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY occurred_at) AS user_session_id
FROM (
    SELECT *
        , CASE 
            WHEN unix_timestamp(occurred_at, 'yyyy-MM-dd HH:mm:ss') - unix_timestamp(last_event, 'yyyy-MM-dd HH:mm:ss') >= 60 * 30
            OR last_event IS NULL THEN 1
            ELSE 0
        END AS is_new_session
    FROM (
        SELECT *, LAG(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event
        FROM tutorial.playbook_events
    ) t1
) t2

执行效果如下:

参考链接:

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注