在PostgreSQL中获取新用户与回访用户列表并实现去重

你的查询出现重复数据，核心原因是同一个用户本周内多次登录时，每条登录记录对应不同的doc_id，最终分组后仍会保留多条记录。要实现每个用户仅显示一条记录且保留doc_id，可以用窗口函数ROW_NUMBER()筛选每个用户的单条登录记录（比如本周首次或末次登录的记录）。

修改后的查询语句 #

WITH first_visit AS (
    SELECT v.user_name,
           min(v.login_date) AS first_login_date
    FROM db.user v
    GROUP BY v.user_name
), visitors_list AS (
    SELECT u.doc_id,
           u.user_name,
           fv.first_login_date,
           CASE
               WHEN fv.first_login_date > (CURRENT_DATE - '7 days'::interval) THEN 'New user'::text
               ELSE 'Returning User'::text
           END AS user_status,
           -- 按用户分组，给每条登录记录编号，这里取本周最后一次登录的记录
           ROW_NUMBER() OVER (PARTITION BY u.user_name ORDER BY u.login_date DESC) AS rn
    FROM db.user u
    JOIN first_visit fv ON u.user_name = fv.user_name
    WHERE u.login_date < CURRENT_DATE 
      AND u.login_date > (CURRENT_DATE - 7) 
      AND u.action = 'Login'
)
SELECT doc_id, user_name, user_status, first_login_date
FROM visitors_list
WHERE rn = 1; -- 只保留每个用户的第一条记录

关键说明 #

• ROW_NUMBER() OVER (PARTITION BY u.user_name ORDER BY u.login_date DESC)：按user_name分组，给每个用户的本周登录记录按登录时间倒序编号，编号为1的就是该用户本周最后一次登录的记录。
• 如果想保留用户本周第一次登录的记录，把ORDER BY u.login_date DESC改成ORDER BY u.login_date ASC即可。
• 原查询里的::text强制转换可去掉，字段类型匹配时无需额外转换，能简化语句。

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