SQL按visitor_id去重保留最早datetime行及过去重问题咨询

现有SQL问题排查 #

你当前的写法核心逻辑是通顺的，出现访客数统计异常一般是以下几个常见原因：

• 多余的distinct：按visitor_id分区后row_number()生成的rank=1记录，每个visitor_id天然只有1条，不需要额外加distinct去重，极端场景下反而会引发统计偏差
• 时间字段排序错误：如果你的datetime字段是字符串格式存储，直接按字符串排序会走字典序而非实际时间先后逻辑，导致取到的不是真正最早的访问记录
• 空值visitor_id被合并：partition by visitor_id会把所有值为NULL的visitor_id归为同一组，仅保留1条最早记录，如果你的业务场景中visitor_id为空的访客属于不同主体，就会出现访客数被过度去重的问题

优化后SQL（窗口函数写法） #

WITH T AS
(SELECT *, 
  ROW_NUMBER() OVER (
    PARTITION BY visitor_id 
    -- 如果是字符串格式的时间，按你所用数据库的时间转换函数先转成时间类型再排序，以下为通用写法
    ORDER BY CAST(datetime AS DATETIME) ASC
  ) AS rn
FROM 表A)

SELECT visitor_id, datetime, channel, visit_page
FROM T
WHERE rn = 1

如果业务中需要保留visitor_id为空的不同访客，可在分区条件中加上会话唯一标识字段，比如PARTITION BY visitor_id, session_id即可。

无窗口函数替代写法 #

如果你不想使用partition语法，也可以用分组关联的方式实现：

SELECT a.visitor_id, a.datetime, a.channel, a.visit_page
FROM 表A a
INNER JOIN (
  SELECT visitor_id, MIN(CAST(datetime AS DATETIME)) AS first_visit_time
  FROM 表A
  GROUP BY visitor_id
) b 
ON a.visitor_id = b.visitor_id 
AND CAST(a.datetime AS DATETIME) = b.first_visit_time
-- 极端场景下同visitor_id存在完全相同的最早访问时间时，加分组去重
GROUP BY a.visitor_id, a.datetime, a.channel, a.visit_page

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