SQL分组统计各地点用户总净资产时，如何避免用户重复计数？

通用SQL方案（全数据库兼容） #

先对访问记录按「地点+用户ID」去重，再关联用户表计算总净资产，这是所有SQL数据库都支持的通用写法：

SELECT
  v.location,
  SUM(u.net_worth) AS total_net_worth
FROM (
  -- 去重：确保同一用户在同一地点仅保留一条记录
  SELECT DISTINCT location, user_id
  FROM visit
) v
JOIN "user" u ON v.user_id = u.id
GROUP BY v.location
ORDER BY v.location;

PostgreSQL/Redshift 优化方案 #

利用窗口函数精准过滤重复用户，适合需要保留访问记录其他字段的场景：

SELECT
  location,
  SUM(net_worth) AS total_net_worth
FROM (
  SELECT
    v.location,
    u.net_worth,
    -- 给每个地点下的用户生成唯一序号，仅保留首次访问记录
    ROW_NUMBER() OVER (PARTITION BY v.location, v.user_id ORDER BY v.id) AS rn
  FROM visit v
  JOIN "user" u ON v.user_id = u.id
) t
WHERE rn = 1
GROUP BY location
ORDER BY location;

验证结果 #

两种方案都会输出符合预期的统计结果：

| location  | total_net_worth |
-------------------------------
| mcdonalds | 1100            |
| subway    | 100             |

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