编写SQL查询访问pricing及另一页面且会话时长>45秒的用户详情

筛选规则回顾 #

• 单会话内访问过pricing页面，同时额外访问过至少1个其他页面
• 对应会话时长大于45秒

查询SQL #

以下为兼容大多数关系型数据库的通用实现，可根据你使用的数据库类型调整字符串/数组处理函数：

SELECT DISTINCT t.visitor_id
FROM your_table_name t
WHERE 
  -- 条件1：会话时长大于45秒
  TIMESTAMPDIFF(SECOND, t.session_start, t.session_end) > 45
  -- 条件2：会话内包含pricing页面
  AND FIND_IN_SET('pricing', REPLACE(t.page_visited, '|', ',')) > 0
  -- 条件3：会话内去重页面数≥2（即至少1个其他页面）
  AND (
    SELECT COUNT(DISTINCT page) 
    FROM STRING_SPLIT(REPLACE(t.page_visited, '|', ','), ',')
  ) >= 2;

实现说明 #

• 如果你使用的数据库不支持STRING_SPLIT函数，可替换为对应数据库的字符串拆分函数，比如PostgreSQL用STRING_TO_ARRAY+UNNEST，MySQL 8.0以下可自定义拆分函数处理
• 代码中默认page_visited为分隔符分隔的页面列表，如果你库中该字段为JSON数组格式，可替换为JSON数组解析函数判断包含关系及元素数量
• 如需返回完整用户关联的会话详情，可删除DISTINCT并调整SELECT后返回的字段即可

内容的提问来源于stack exchange，提问作者Himani Chaurasia