MySQL技术问询：计算指定用户指定月份各状态停留天数

要计算特定用户指定月份内各状态的停留天数，可以利用窗口函数LEAD()获取状态的结束时间，再结合日期函数计算重叠天数，具体步骤如下：

假设你的表名为status_log，执行以下SQL语句：

WITH status_periods AS (
    SELECT
        user_id,
        new_status AS status,
        -- 转换字符串时间为MySQL可识别的日期类型
        STR_TO_DATE(modified_on, '%d/%m/%Y %H:%i') AS start_time,
        -- 获取下一次状态变更的时间，作为当前状态的结束时间
        LEAD(STR_TO_DATE(modified_on, '%d/%m/%Y %H:%i')) OVER (
            PARTITION BY user_id 
            ORDER BY STR_TO_DATE(modified_on, '%d/%m/%Y %H:%i')
        ) AS end_time
    FROM status_log
    WHERE user_id = 488
)
SELECT
    user_id AS user,
    status,
    -- 计算6月内的有效停留天数
    DATEDIFF(
        LEAST(COALESCE(end_time, '2022-06-30 23:59:59'), '2022-06-30 23:59:59'),
        GREATEST(start_time, '2022-06-01 00:00:00')
    ) AS Days
FROM status_periods
WHERE
    -- 只保留6月内有实际停留的状态
    GREATEST(start_time, '2022-06-01 00:00:00') 
    <= LEAST(COALESCE(end_time, '2022-06-30 23:59:59'), '2022-06-30 23:59:59')
ORDER BY status;

代码说明： #

1. CTE status_periods：

   • 提取用户488的所有状态变更记录，将new_status标记为当前状态，把字符串格式的modified_on转换为日期类型作为状态的开始时间。
   • 使用LEAD()窗口函数按时间顺序，获取下一次状态变更的时间作为当前状态的结束时间；如果是最后一条无后续变更的记录，用COALESCE()默认6月30日作为结束时间。

2. 主查询：

   • GREATEST(start_time, '2022-06-01')：确保只计算6月1日及之后的状态起始节点。
   • LEAST(end_time, '2022-06-30')：确保只计算6月30日及之前的状态结束节点。
   • DATEDIFF()计算两个日期的天数差，得到状态在6月内的有效停留天数。
   • 过滤掉起始时间晚于结束时间的记录（即无有效停留的状态）。

执行后将得到与你期望一致的结果：

user    status  Days
488     5       6
488     16      2
488     2       21

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