SQL查询：统计每日分别通过iPhone和Web端登录的独立用户数

问题根因 #

你现有写法存在两个核心问题：

• 日期字段仅从iphone.ts提取，当某一天只有Web端的登录数据时，FULL JOIN后iPhone侧的字段全为空，最终返回的day自然为null
• JOIN逻辑同时关联user_id和ts，统计的是「同一用户同一天在两端登录的重合用户」，和你需要的「每日两端各自的登录用户数」需求不匹配，会导致计数错误，且你原来用extract('day' from ts)仅能提取当月日期序号，无法得到示例里的2021-09-10格式的完整日期。

修正后的SQL #

无需单独日期维表，先分别聚合两端的每日登录用户，再按日期全连接即可：

SELECT
  COALESCE(i.daily_user_cnt, 0) AS iphone_user,
  COALESCE(w.daily_user_cnt, 0) AS web_user,
  COALESCE(i.day, w.day) AS day
FROM 
  -- 先聚合iPhone端每日去重登录用户数
  (
    SELECT 
      COUNT(DISTINCT user_id) AS daily_user_cnt,
      DATE(ts) AS day -- 按实际使用的数据库替换为对应日期格式化函数，保证输出为yyyy-MM-dd格式
    FROM Iphone
    GROUP BY day
  ) i
FULL OUTER JOIN
  -- 再聚合Web端每日去重登录用户数
  (
    SELECT 
      COUNT(DISTINCT user_id) AS daily_user_cnt,
      DATE(ts) AS day -- 和上面对应的日期格式化规则保持一致
    FROM Web
    GROUP BY day
  ) w
ON i.day = w.day
ORDER BY day;

逻辑说明 #

• 先分别对两张表按日期聚合，得到各自每天的登录用户数，避免跨表JOIN带来的计数偏差
• 按日期做全连接，用COALESCE函数优先取iPhone侧的日期，iPhone侧为空则取Web侧的日期，保证所有有登录的日期都能正常展示
• 同样用COALESCE把没有登录数据的端的用户数置为0，完全匹配你期望的输出格式

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