SQL计算每周截至当前活动的累计时长并判断是否符合规定时长

原有代码存在以下几类错误：

• 字段引用错误：表中日期字段为date，不存在datum字段，且JOIN的子查询内部无法直接引用外层表a的字段，不符合SQL语法规范
• 未定义别名错误：JOIN关联条件里的pt.activity_id不存在，没有定义过pt这个表别名
• 统计逻辑错误：子查询按activity_id分组，只会统计单条活动的时长，无法实现周内累计求和的需求
• 字段类型错误：time字段是带hours后缀的字符串，无法直接执行SUM求和运算，需要先提取其中的数值部分

优先使用窗口函数实现，逻辑更简洁、执行性能更高，支持所有支持标准SQL窗口函数的数据库（MySQL 8.0+、PostgreSQL、Snowflake、Spark SQL等）：

SELECT 
    activity_id,
    `date`,
    yearweek,
    time,
    prescribed_time_for_week,
    -- 累计时长小于等于周规定时长则标记yes，否则no
    IFF(
        cumulative_time <= CAST(REGEXP_REPLACE(prescribed_time_for_week, '[^0-9]', '') AS UNSIGNED),
        'yes',
        'no'
    ) AS Within_prescribed_time
FROM (
    SELECT 
        *,
        -- 按周分组，按日期+活动ID排序，计算周内累计时长
        SUM(CAST(REGEXP_REPLACE(time, '[^0-9]', '') AS UNSIGNED)) OVER (
            PARTITION BY yearweek 
            ORDER BY `date`, activity_id
        ) AS cumulative_time
    FROM activities
) t

代码说明 #

• 用REGEXP_REPLACE提取time和prescribed_time_for_week字段中的纯数字部分，转为数值类型后参与计算
• 窗口函数PARTITION BY yearweek实现按周分组，ORDER BY date, activity_id保证按活动发生顺序累加时长
• 外层查询直接对比累计时长和周规定时长，输出对应的标记结果

如果你使用的数据库不支持窗口函数，可以使用以下自关联版本实现：

SELECT 
    a.activity_id,
    a.`date`,
    a.yearweek,
    a.time,
    a.prescribed_time_for_week,
    IFF(
        SUM(CAST(REGEXP_REPLACE(a2.time, '[^0-9]', '') AS UNSIGNED)) <= CAST(REGEXP_REPLACE(a.prescribed_time_for_week, '[^0-9]', '') AS UNSIGNED),
        'yes',
        'no'
    ) AS Within_prescribed_time
FROM activities a
LEFT JOIN activities a2 
    ON a.yearweek = a2.yearweek 
    AND (a2.`date` < a.`date` OR (a2.`date` = a.`date` AND a2.activity_id <= a.activity_id))
GROUP BY a.activity_id, a.`date`, a.yearweek, a.time, a.prescribed_time_for_week
ORDER BY a.activity_id

内容的提问来源于stack exchange，提问作者RómanH