如何修改SQL查询实现Oracle APEX测试健康页7天状态按日统计

修改后的查询代码 #

select  
    to_char(TRUNC(EXE.ACTUAL_END_DATE), 'DD-MM') AS DAY,
    sum(case when STATES.NAME = 'Pass' then 1 else 0 end) AS PASS,
    sum(case when STATES.NAME = 'Fail' then 1 else 0 end) AS FAIL, 
    sum(case when STATES.NAME = 'Undefined' then 1 else 0 end) AS Undefined
from TEST_CASE TEST
INNER JOIN TEST_CASE_TEST_RUN TESTRUNCASE 
    on TEST.TEST_CASE_ID = TESTRUNCASE.TEST_CASE_ID
INNER JOIN TEST_RUN RUN 
    on TESTRUNCASE.TEST_RUN_ID = RUN.TEST_RUN_ID
INNER JOIN EXECUTION EXE 
    on TESTRUNCASE.TEST_CASE_IN_TEST_RUN_ID = EXE.TEST_CASE_IN_TEST_RUN_ID 
LEFT JOIN TEST_STEP_STATE STATES  
    on STATES.TEST_STEP_RESULT_STATE_ID = EXE.TEST_STEP_RESULT_STATE_ID
where TRUNC(actual_Start_date) >= TRUNC(sysdate) - 7
GROUP BY TRUNC(EXE.ACTUAL_END_DATE)
ORDER BY TRUNC(EXE.ACTUAL_END_DATE);

关键修改说明 #

• 添加日期分组字段：在SELECT列表中加入to_char(TRUNC(EXE.ACTUAL_END_DATE), 'DD-MM') AS DAY，用TRUNC()截断日期到自然日（忽略时分秒），确保同一天的所有测试记录被归为一组，再用to_char()格式化为你需要的DD-MM样式。
• 添加GROUP BY子句：必须按TRUNC(EXE.ACTUAL_END_DATE)分组（不能直接用格式化后的DAY，避免不同月份的同日期被错误合并），这样才能得到每日的统计结果。
• 优化WHERE条件：用TRUNC(actual_Start_date) >= TRUNC(sysdate) -7替换原条件，确保筛选的是完整的最近7天数据，避免时间部分导致的遗漏或错误。
• 添加ORDER BY：按截断后的日期排序，保证结果按时间顺序展示，符合趋势图的需求。

补充：处理无测试数据的日期 #

如果希望趋势图中即使某天没有测试记录也显示该日期（对应PASS/FAIL/Undefined都为0），可以先生成连续的7天日期序列，再左连接你的统计查询：

WITH date_range AS (
    SELECT TRUNC(sysdate) - LEVEL + 1 AS day_date
    FROM dual
    CONNECT BY LEVEL <=7
)
select  
    to_char(dr.day_date, 'DD-MM') AS DAY,
    NVL(t.PASS, 0) AS PASS,
    NVL(t.FAIL, 0) AS FAIL, 
    NVL(t.Undefined, 0) AS Undefined
from date_range dr
LEFT JOIN (
    -- 嵌套之前的统计逻辑
    select  
        TRUNC(EXE.ACTUAL_END_DATE) AS day_date,
        sum(case when STATES.NAME = 'Pass' then 1 else 0 end) AS PASS,
        sum(case when STATES.NAME = 'Fail' then 1 else 0 end) AS FAIL, 
        sum(case when STATES.NAME = 'Undefined' then 1 else 0 end) AS Undefined
    from TEST_CASE TEST
    INNER JOIN TEST_CASE_TEST_RUN TESTRUNCASE 
        on TEST.TEST_CASE_ID = TESTRUNCASE.TEST_CASE_ID
    INNER JOIN TEST_RUN RUN 
        on TESTRUNCASE.TEST_RUN_ID = RUN.TEST_RUN_ID
    INNER JOIN EXECUTION EXE 
        on TESTRUNCASE.TEST_CASE_IN_TEST_RUN_ID = EXE.TEST_CASE_IN_TEST_RUN_ID 
    LEFT JOIN TEST_STEP_STATE STATES  
        on STATES.TEST_STEP_RESULT_STATE_ID = EXE.TEST_STEP_RESULT_STATE_ID
    where TRUNC(actual_Start_date) >= TRUNC(sysdate) -7
    GROUP BY TRUNC(EXE.ACTUAL_END_DATE)
) t ON dr.day_date = t.day_date
ORDER BY dr.day_date;

这个查询会生成最近7天的所有日期，即使某天没有测试数据，也会返回该行并填充0，让趋势图的时间轴更完整。

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