通过SQL定义用户浏览Session

标签: 数据 术→技巧 SQL | 发表时间:2021-05-26 19:42 | 作者:钱魏Way
出处:https://www.biaodianfu.com

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) 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

执行效果如下:

参考链接:

相关 [sql 定义 用户] 推荐:

通过SQL定义用户浏览Session

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

Oracle PL/SQL 非预定义异常、自定义异常处理、RAISE_APPLICATION_ERROR

- - CSDN博客互联网推荐文章
Oracle有三种类型的异常错误:. 1. 预定义(Predefined)异常. ORACLE预定义的异常情况大约有24个. 对这种异常情况的处理,无需在程序中定义,由ORACLE自动将其引发. 2. 非预定义(Predefined)异常. 即其他标准的ORACLE错误. 对这种异常情况的处理,需要用户在程序中定义,然后由ORACLE自动将其引发.

PL/SQL动态SQL(原创)

- - ITeye博客
使用动态SQL是在编写PL/SQL过程时经常使用的方法之一. 很多情况下,比如根据业务的需要,如果输入不同查询条件,则生成不同的执行SQL查询语句,对于这种情况需要使用动态SQL来完成. 再比如,对于分页的情况,对于不同的表,必定存在不同的字段,因此使用静态SQL则只能针对某几个特定的表来形成分页.

Derby SQL 分页

- - ITeye博客
    之前在网上看到有人问 Derby SQL 分页实现的问题,网上有人给出这样的解决方案,SQL 如下:. 其实,这样的分页查询,性能不理想,我试过在 300W 数据量中采用这种分页方式,需要 20~30秒之久;其实 Derby 10.6 以上版本有更好的分页支持,直接给出 SQL 实现如下:.

SQL Server--索引

- - CSDN博客推荐文章
         1,概念:  数据库索引是对数据表中一个或多个列的值进行排序的结构,就像一本书的目录一样,索引提供了在行中快速查询特定行的能力..             2.1优点:  1,大大加快搜索数据的速度,这是引入索引的主要原因..                             2,创建唯一性索引,保证数据库表中每一行数据的唯一性..

MySql动态SQL

- - SQL - 编程语言 - ITeye博客
13.7. 用于预处理语句的SQL语法. MySQL 5.1对服务器一方的预制语句提供支持. 如果您使用合适的客户端编程界面,则这种支持可以发挥在MySQL 4.1中实施的高效客户端/服务器二进制协议的优势. 候选界面包括MySQL C API客户端库(用于C程序)、MySQL Connector/J(用于Java程序)和MySQL Connector/NET.

sql优化

- - 数据库 - ITeye博客
是对数据库(数据)进行操作的惟一途径;. 消耗了70%~90%的数据库资源;独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低;. 可以有不同的写法;易学,难精通. 固定的SQL书写习惯,相同的查询尽量保持相同,存储过程的效率较高. 应该编写与其格式一致的语句,包括字母的大小写、标点符号、换行的位置等都要一致.

birt动态SQL

- - ITeye博客
birt动态SQL实现有三种方式:拼接SQL、绑定变量和让应用程序拼接,birt得到返回结果集方式. 在数据集中写SQL,如下:. 选中数据集,点script方式,在beforeOpen事件中写如下SQL:. 然后就可以了,当然,也可以不写第一步,直接所有的SQL都在beforeOpen中拼接. 但是,拼接SQL方式不仅复杂容易错,还会导致SQL注入风险.

SQL Server 面试

- - SQL - 编程语言 - ITeye博客
在SQL语言中,一个SELECT…FROM…WHERE语句称为一个查询块,将一个查询块嵌套在另一个查询块的WHERE子句中的查询称为子查询. 子查询分为嵌套子查询和相关子查询两种. 嵌套子查询的求解方法是由里向外处理,即每个子查询在其上一级查询处理之前求解,子查询的结果作为其父查询的查询条件. 子查询只执行一次,且可以单独执行;.

MongoDB sql操作

- - 数据库 - ITeye博客
1.  基本查询:. 下面的示例等同于SQL语句的where name = "stephen" and age = 35.      --返回指定的文档键值对. 下面的示例将只是返回name和age键值对.      --指定不返回的文档键值对. 下面的示例将返回除name之外的所有键值对.