如何从每日个人数据表中获取目标记录日期及距今日天数？

• 对每个个人（按id+region分组），获取其最近一条非零value值的记录日期，以及该日期与当前日期的天数差；
• 若某个人的所有记录value值均为0，则获取其第一条0值记录的日期及该日期与当前日期的天数差。

示例数据（当前日期为2022-09-28） #

期望结果 #

现有尝试 #

已能识别所有记录均为0的个人：

SELECT
    id, 
    region,
    SUM(value) as value_sum,
    MIN(date) 
FROM tbl
GROUP BY
    id 
HAVING value_sum= 0;

后续尝试了联合查询，但因GROUP BY无法正确返回对应value值：

WITH CTE1 AS (
    SELECT 
        id, 
        region, 
        SUM(`value`) AS sum_values,
        MIN(date) AS min_date
    FROM tbl
    GROUP BY id, region
    HAVING sum_values = 0
),
UNION_CTE2 AS (
    SELECT 
        CTE1.id,
        CTE1.region, 
        CTE1.min_date AS `date`,
        DATEDIFF( now(), CTE1.min_date) AS `daysFromToday`
    FROM CTE1
    UNION
    SELECT 
        T.id,
        T.region,
        MAX(T.date) AS `date`,
        DATEDIFF( now(), MAX(T.date)) AS `daysFromToday`
    FROM tbl AS T
    WHERE `value` > 0 
    GROUP BY
        id, 
        region
)

SELECT * FROM UNION_CTE2;

完整SQL解决方案 #

使用窗口函数可以精准定位目标记录，同时保留对应value值：

WITH ranked_records AS (
    SELECT
        id,
        region,
        value,
        date,
        -- 标记非零记录的倒序排名（最近的非零排第1）
        ROW_NUMBER() OVER (
            PARTITION BY id, region
            ORDER BY CASE WHEN value > 0 THEN 1 ELSE 0 END DESC, date DESC
        ) AS non_zero_rank,
        -- 标记全零记录的正序排名（最早的零排第1）
        ROW_NUMBER() OVER (
            PARTITION BY id, region
            ORDER BY date ASC
        ) AS zero_rank,
        -- 统计当前分组下非零记录的总数
        SUM(CASE WHEN value > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY id, region) AS non_zero_count
    FROM tbl
)
SELECT
    id,
    region,
    value,
    date,
    DATEDIFF(CURDATE(), date) AS daysFromToday
FROM ranked_records
WHERE
    -- 非零记录存在时，取最近的那条（non_zero_rank=1）
    (non_zero_count > 0 AND non_zero_rank = 1)
    -- 非零记录不存在时，取最早的那条零记录（zero_rank=1）
    OR (non_zero_count = 0 AND zero_rank = 1);

逻辑说明 #

1. 窗口函数分组计算：按id+region分区，分别计算：
   • 非零记录的倒序排名，确保最近的非零记录排第1；
   • 全零记录的正序排名，确保最早的零记录排第1；
   • 分组内非零记录的总数，用于判断是否全为零。
2. 筛选目标记录：根据非零记录的存在情况，选择对应的目标记录，最后计算日期差。

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