如何针对swabs表按日期统计截至当日仍阳性的唯一用户总数

我有一张追踪拭子检测结果的swabs表，每个用户可在不同日期（精确到秒）多次采样，结果分为'Y'（阳性）和'N'（阴性）。表结构如下：

CREATE TABLE swabs
(
    id          INTEGER    PRIMARY KEY,
    user_id     INTEGER    NOT NULL,
    date_result DATE       NOT NULL,
    positivity  CHAR(1)    NOT NULL,

    CONSTRAINT chk_swabs CHECK (positivity IN ('Y', 'N')),
    CONSTRAINT uc_swabs UNIQUE (user_id, date_result),
    CONSTRAINT fk_swabs_users FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

插入模拟数据后的表内容：

我的需求是：针对表中每个distinct日期，统计截至该日期仍呈阳性的distinct用户数量，预期结果如下：

目前我已实现一个针对指定日期的查询，可统计截至该日期仍阳性的用户总数，查询语句如下：

SELECT COUNT(positivity)
FROM swabs s
         INNER JOIN (SELECT user_id,
                            MAX(date_result) AS max_date
                     FROM swabs
                     WHERE date_result < TO_DATE('2023-03-21', 'YYYY-MM-DD')
                     GROUP BY user_id) m ON s.user_id = m.user_id AND s.date_result = m.max_date
WHERE s.date_result < TO_DATE('2023-03-21', 'YYYY-MM-DD')
  AND s.POSITIVITY = 'Y';

我希望对表中每个distinct日期执行该查询，请问此方法是否可行？

你的方法可行，但存在明显效率问题：如果表中有N个不同日期，就要执行N次几乎相同的查询，每次都要全表扫描统计用户的最新检测结果，数据量较大时性能会急剧下降。

更高效的做法是用一次查询完成所有日期的统计，核心思路是：

1. 提取所有不重复的日期（按日分组）
2. 对每个用户，找出其在每个日期之前的最新检测记录
3. 统计每个日期下最新结果为'Y'的用户数量

以下是适配需求的SQL语句（以Oracle为例，其他数据库可调整日期处理函数）：

WITH distinct_dates AS (
    -- 提取所有不重复的日期（只取日期部分）
    SELECT TRUNC(date_result) AS date_distinct
    FROM swabs
    GROUP BY TRUNC(date_result)
),
user_latest_results AS (
    -- 对每个用户，计算其在每个日期前的最新检测记录
    SELECT 
        dd.date_distinct,
        s.user_id,
        FIRST_VALUE(s.positivity) OVER (
            PARTITION BY s.user_id, dd.date_distinct 
            ORDER BY s.date_result DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS latest_positivity
    FROM distinct_dates dd
    CROSS JOIN swabs s
    WHERE s.date_result < dd.date_distinct + INTERVAL '1' DAY -- 包含当天所有时间点的记录
),
valid_user_results AS (
    -- 去重，每个用户在每个日期只保留一条最新结果
    SELECT DISTINCT date_distinct, user_id, latest_positivity
    FROM user_latest_results
)
-- 统计每个日期下阳性用户数
SELECT 
    date_distinct,
    COUNT(CASE WHEN latest_positivity = 'Y' THEN user_id END) AS total_infected
FROM valid_user_results
GROUP BY date_distinct
ORDER BY date_distinct;

逻辑说明： #

• distinct_dates：先把所有检测记录的日期截断到日，得到所有需要统计的日期。
• user_latest_results：用CROSS JOIN关联所有日期和用户检测记录，再通过FIRST_VALUE窗口函数，按用户和日期分组，取该日期前（含当天）的最新检测结果。
• valid_user_results：去重每个用户在每个日期的结果，避免重复统计。
• 最后分组统计每个日期下最新结果为'Y'的用户数量，得到预期结果。

如果你的数据库支持LATERAL JOIN（比如PostgreSQL、MySQL 8.0+），还可以用更简洁的写法：

WITH distinct_dates AS (
    SELECT TRUNC(date_result) AS date_distinct
    FROM swabs
    GROUP BY TRUNC(date_result)
)
SELECT 
    dd.date_distinct,
    COUNT(u.user_id) AS total_infected
FROM distinct_dates dd
LEFT JOIN LATERAL (
    SELECT s.user_id, s.positivity
    FROM swabs s
    WHERE s.date_result < dd.date_distinct + INTERVAL '1' DAY
    ORDER BY s.date_result DESC
    LIMIT 1
) u ON u.positivity = 'Y'
GROUP BY dd.date_distinct
ORDER BY dd.date_distinct;

这个写法对每个日期，直接关联该日期前每个用户的最新检测记录，过滤出阳性的用户后统计数量，逻辑更直观，性能也更优。

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