Oracle工单系统基于指定日期最大状态变更记录的分组统计——当日末unknown状态工单计数实现咨询

嘿，我来帮你搞定第三个统计项的问题！这确实是个需要处理「最新状态」的典型场景，我给你拆解一下思路和具体的实现方案：

核心思路 #

要统计指定日期当日结束时处于unknown状态的工单总数，关键是要先找到每个工单在该日期结束前的最后一次状态变更记录，然后再统计其中状态为unknown的数量。具体分三步：

1. 定位每个工单的最后状态
   对于每个工单（按TICKETID分组），我们需要筛选出所有状态变更时间早于等于指定日期当日23:59:59的记录，然后取其中时间最晚的那条——这条记录的状态就是该工单在当日结束时的状态。
   这里可以用窗口函数ROW_NUMBER()来实现：按TICKETID分组，按STATUS_CHANGED降序排序，每组里排第1的就是最后一次变更记录。

2. 统计unknown状态的工单数量
   从上面得到的所有工单最后状态记录中，筛选出状态为unknown的条目，统计其数量即可。

3. 整合已有统计结果
   把这个统计结果和你已经实现的前两个统计项（当日创建工单总数、当日创建时unknown的数量）结合，用CTE（公共表表达式）或者子查询的方式整合到一个查询里。

具体SQL实现 #

下面是针对指定日期（比如2020-01-01）的完整SQL代码，你可以替换日期参数来适配不同的查询需求：

WITH last_status AS (
    -- 第一步：获取每个工单在指定日期结束前的最后状态
    SELECT TICKETID, STATUS
    FROM (
        SELECT 
            TICKETID,
            STATUS,
            ROW_NUMBER() OVER (PARTITION BY TICKETID ORDER BY STATUS_CHANGED DESC) AS rn
        FROM myTable
        -- 过滤出指定日期结束前的变更记录（精确到当日23:59:59）
        WHERE STATUS_CHANGED <= TRUNC(to_date('01.01.2020', 'DD.MM.YYYY')) + 1 - 1/86400
    )
    WHERE rn = 1 -- 取每个工单的最后一次变更记录
),
daily_created_stats AS (
    -- 你已实现的前两个统计项
    SELECT 
        ticket_created,
        COUNT(*) AS "Total Created",
        SUM(CASE WHEN status = 'unknown' THEN 1 ELSE 0 END) AS "Unknown tickets created"
    FROM myTable
    WHERE id IN (
        SELECT MIN(id) 
        FROM myTable 
        WHERE ticket_created = TRUNC(to_date('01.01.2020', 'DD.MM.YYYY')) 
        GROUP BY ticketid
    )
    GROUP BY ticket_created
)
-- 整合三个统计结果
SELECT 
    dcs.ticket_created,
    dcs."Total Created",
    dcs."Unknown tickets created",
    (SELECT COUNT(*) FROM last_status WHERE STATUS = 'unknown') AS "Total tickets in Unknown status"
FROM daily_created_stats dcs;

关键细节说明 #

• 日期处理：TRUNC(指定日期) + 1 - 1/86400是Oracle中表示指定日期当日最后一秒（23:59:59）的写法，确保我们不会漏掉当日最后一刻的状态变更。
• 窗口函数的使用：ROW_NUMBER()能精准定位每个工单的最新状态，避免因为同一工单多次变更导致的统计错误。

测试一下这个代码，针对2020-01-01会返回你预期的2，针对2020-01-02会返回1，完全符合你的需求。

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