如何编写SQL按日统计新增与流失用户？动态日期匹配疑问

首先确认你的user_first_last视图（注意user是SQL关键字，建议用反引号包裹避免语法错误）：

CREATE VIEW user_first_last AS
SELECT 
    `user` AS user_id,
    MIN(date) AS first_date_seen,
    MAX(date) AS last_date_seen
FROM days_users
GROUP BY `user`;

1. 统计所有日期的新增/流失用户 #

如果需要生成每日的新增、流失用户报表，可通过以下查询实现，无需硬编码日期：

SELECT 
    d.date,
    -- 新增用户：首次出现日期等于当日的用户数量
    COUNT(DISTINCT CASE WHEN u.first_date_seen = d.date THEN u.user_id END) AS new_users,
    -- 流失用户：末次出现日期等于当日的用户数量（即当日活跃后不再出现）
    COUNT(DISTINCT CASE WHEN u.last_date_seen = d.date THEN u.user_id END) AS lost_users
FROM (
    -- 先提取表中所有存在的日期
    SELECT DISTINCT date FROM days_users
) d
LEFT JOIN user_first_last u ON 1=1
GROUP BY d.date
ORDER BY d.date;

针对你的示例数据，执行后会返回：

2. 统计单个动态日期的新增/流失用户 #

如果需要查询指定日期的统计数据，可将目标日期作为参数传入，避免重复修改SQL。以下是不同数据库的实现方式：

PostgreSQL #

-- 用$1作为参数占位符，执行时传入目标日期
SELECT 
    $1 AS date,
    (SELECT COUNT(DISTINCT user_id) FROM user_first_last WHERE first_date_seen = $1) AS new_users,
    (SELECT COUNT(DISTINCT user_id) FROM user_first_last WHERE last_date_seen = $1) AS lost_users;

MySQL #

-- 用?作为参数占位符，或直接替换为变量
SELECT 
    ? AS date,
    (SELECT COUNT(DISTINCT user_id) FROM user_first_last WHERE first_date_seen = ?) AS new_users,
    (SELECT COUNT(DISTINCT user_id) FROM user_first_last WHERE last_date_seen = ?) AS lost_users;

临时查询单日期（测试用） #

如果只是临时查询某一天，可将目标日期定义为子查询变量，只需修改一次：

SELECT 
    target_date AS date,
    (SELECT COUNT(DISTINCT user_id) FROM user_first_last WHERE first_date_seen = target_date) AS new_users,
    (SELECT COUNT(DISTINCT user_id) FROM user_first_last WHERE last_date_seen = target_date) AS lost_users
FROM (SELECT '2023-01-01' AS target_date) t;

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