单天SQL查询正常，30天范围查询结果异常求助

问题根源在于30天查询时，TRUNC(B.LOGINLOGOUTTIME)的关联条件范围错误：单日查询里，你要求B的登录时间在当前统计日期到截止日之间，但30天查询时误写成了整个30天范围，导致关联逻辑和单日不一致，结果自然出错。

以下是修正后的SQL，同时优化了结构以提升可读性和性能：

SELECT 
    day_date AS "TIMESTAMP",
    SUM(daily_fs) AS "fs"
FROM (
    SELECT 
        TO_CHAR(TRUNC(a.timestamp), 'MM/DD/YY') AS day_date,
        COUNT(DISTINCT a.id) AS daily_fs
    FROM (
        -- 合并所有事件表查询，减少重复代码
        SELECT id, timestamp, eventid FROM ag_se WHERE eventid = 'AS20'
        UNION ALL
        SELECT id, timestamp, eventid FROM ag_sw WHERE eventid = 'AS20'
        UNION ALL
        SELECT id, timestamp, eventid FROM ag_mw WHERE eventid = 'AS20'
        UNION ALL
        SELECT id, timestamp, eventid FROM ag_w WHERE eventid = 'AS20'
        UNION ALL
        SELECT id, timestamp, eventid FROM ag_bl WHERE eventid = 'AS20'
    ) a
    JOIN aglogin b 
        ON a.id = b.id
        AND b.agentrole IN ('FS_SW_EC', 'FS_SE_EC', 'FS_W_EC', 'FS_MW_EC')
        -- 核心修正：和单日查询逻辑对齐，B的登录时间需在A记录日期到截止日之间
        AND TRUNC(b.loginlogouttime) BETWEEN TRUNC(a.timestamp) AND TRUNC(sysdate - 1)
    WHERE 
        TRUNC(a.timestamp) BETWEEN TRUNC(sysdate - 30) AND TRUNC(sysdate - 1)
    GROUP BY 
        b.agentrole, TO_CHAR(TRUNC(a.timestamp), 'MM/DD/YY')
)
GROUP BY day_date
ORDER BY day_date;

关键修正点说明 #

• 关联逻辑对齐：将B的登录时间条件改为和单日查询一致，确保每条A记录（对应某天）只关联在该天及之后登录的B记录，避免跨日期错误关联
• 结构优化：用显式JOIN代替隐式连接，逻辑更清晰；合并UNION ALL子查询，去掉重复的WHERE条件，简化代码
• 细节优化：用COUNT(DISTINCT)替代COUNT(UNIQUE)（Oracle中功能一致，但DISTINCT更通用）；添加ORDER BY让结果按日期排序，便于查看

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