请求协助新增OpenTicketsLookingBackwards列：统计历史未关闭工单数量

需求分析 #

为每个工单新增OpenTicketsLookingBackwards列，统计当前工单的Open日期时刻，所有历史（含当前）工单中ClosedTicketDate大于该Open日期的工单总数。核心逻辑是仅回溯当前及之前创建的工单，不包含后续新建工单。

原表数据 #

实现SQL（通用版本） #

假设表名为tickets，使用关联子查询实现需求：

SELECT
    t1.TicketNumber,
    t1.`OpenTicketDate YYYY_MM` AS OpenTicketDate,
    t1.`ClosedTicketDate YYYY_MM` AS ClosedTicketDate,
    (
        SELECT COUNT(*)
        FROM tickets t2
        WHERE
            -- 仅筛选当前及之前创建的工单（回溯历史）
            t2.`OpenTicketDate YYYY_MM` <= t1.`OpenTicketDate YYYY_MM`
            -- 筛选关闭时间晚于当前工单Open时间的工单
            AND t2.`ClosedTicketDate YYYY_MM` > t1.`OpenTicketDate YYYY_MM`
    ) AS OpenTicketsLookingBackwards
FROM tickets t1
ORDER BY t1.TicketNumber;

严谨日期处理版本（适配更多场景） #

如果担心字符串格式的日期比较出现异常，可以将日期转换为标准日期类型后再比较（以MySQL为例）：

SELECT
    t1.TicketNumber,
    t1.`OpenTicketDate YYYY_MM` AS OpenTicketDate,
    t1.`ClosedTicketDate YYYY_MM` AS ClosedTicketDate,
    (
        SELECT COUNT(*)
        FROM tickets t2
        WHERE
            STR_TO_DATE(CONCAT(t2.`OpenTicketDate YYYY_MM`, '-01'), '%Y-%m-%d') 
            <= STR_TO_DATE(CONCAT(t1.`OpenTicketDate YYYY_MM`, '-01'), '%Y-%m-%d')
            AND STR_TO_DATE(CONCAT(t2.`ClosedTicketDate YYYY_MM`, '-01'), '%Y-%m-%d') 
            > STR_TO_DATE(CONCAT(t1.`OpenTicketDate YYYY_MM`, '-01'), '%Y-%m-%d')
    ) AS OpenTicketsLookingBackwards
FROM tickets t1
ORDER BY t1.TicketNumber;

结果验证 #

执行上述查询后，将得到预期结果：

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