优化新用户获取SQL查询：实现30天周期自动统计

核心思路 #

借助数据库内置日期函数自动生成30天时间范围，结合NOT EXISTS子查询（或左连接）筛选无登录记录的新用户，彻底避免手动逐天编写查询的繁琐操作。

通用SQL模板（以PostgreSQL为例） #

SELECT u.user_id, u.created_at
FROM users u
WHERE 
  -- 自动筛选最近30天注册的新用户
  u.created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND u.created_at < CURRENT_DATE
  -- 排除所有有过登录记录的用户
  AND NOT EXISTS (
    SELECT 1 
    FROM user_logins l 
    WHERE l.user_id = u.user_id
  );

适配不同数据库的日期写法 #

• MySQL：将日期条件替换为

  u.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND u.created_at < CURDATE()
  

• SQL Server：将日期条件替换为

  u.created_at >= DATEADD(day, -30, GETDATE())
  AND u.created_at < GETDATE()
  

进阶优化与扩展 #

• 性能优化：给users.created_at、user_logins.user_id分别创建索引，大幅提升时间筛选和关联查询的效率。
• 按天统计：如果需要按日期分组统计每日新增的未登录用户，可添加分组逻辑：

  SELECT 
    DATE_TRUNC('day', u.created_at) AS register_date,
    COUNT(u.user_id) AS never_login_user_count
  FROM users u
  WHERE 
    u.created_at >= CURRENT_DATE - INTERVAL '30 days'
    AND u.created_at < CURRENT_DATE
    AND NOT EXISTS (
      SELECT 1 FROM user_logins l WHERE l.user_id = u.user_id
    )
  GROUP BY DATE_TRUNC('day', u.created_at)
  ORDER BY register_date;
  

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