Oracle近7天每日记录数查询及近24小时每小时统计SQL咨询

针对你的两个Oracle数据库统计问题，我来逐一给出解决方案和细节说明：

场景1：仅统计有记录的日期 #

如果你的数据表中每天都有数据，或者只需要显示存在记录的日期，可以用简单的分组查询：

SELECT 
    TRUNC(your_date_column, 'DD') AS record_date,
    COUNT(*) AS daily_count
FROM your_table
WHERE your_date_column >= TRUNC(SYSDATE - 7)  -- 从7天前的0点开始
  AND your_date_column < TRUNC(SYSDATE + 1)   -- 包含当天的所有记录（到次日0点前）
GROUP BY TRUNC(your_date_column, 'DD')
ORDER BY record_date;

场景2：强制显示所有7天（含无记录日期） #

如果需要每天都出现在结果中，哪怕当天没有记录时显示0，就需要先生成连续的日期序列，再左连接你的数据表：

WITH date_range AS (
    -- 生成过去7天的日期（包含今天），rownum从1到7对应今天到7天前
    SELECT TRUNC(SYSDATE - rownum + 1, 'DD') AS record_date
    FROM dual
    CONNECT BY rownum <= 7
)
SELECT 
    dr.record_date,
    NVL(COUNT(t.your_date_column), 0) AS daily_count
FROM date_range dr
LEFT JOIN your_table t 
    ON TRUNC(t.your_date_column, 'DD') = dr.record_date
GROUP BY dr.record_date
ORDER BY dr.record_date;

如果需要统计的是7天前到昨天（不含今天），只需把date_range中的SYSDATE - rownum +1改成SYSDATE - rownum即可。

你的思路是对的：用CTE生成连续的小时范围，再左连接实际统计数据来补全无记录小时的0值。我帮你补全并优化了SQL：

WITH date_range AS (
    -- 生成过去24小时的每个整点时间
    SELECT TRUNC(SYSDATE - (rownum/24), 'HH24') AS the_hour 
    FROM dual 
    CONNECT BY ROWNUM <= 24
),
the_data AS (
    SELECT 
        TRUNC(systemdate, 'HH24') AS log_date, 
        COUNT(*) AS num_obj 
    FROM transactionlog 
    WHERE merchantcode='merc0003'
      -- 过滤过去24小时的数据，避免全表扫描，提升性能
      AND systemdate >= TRUNC(SYSDATE - 1, 'HH24')
    GROUP BY TRUNC(systemdate, 'HH24')
)
SELECT 
    TO_CHAR(dr.the_hour, 'DD/MM/YYYY HH:MI AM') AS hour_label,
    NVL(trans_log.num_obj, 0) AS hourly_count
FROM date_range dr
LEFT OUTER JOIN the_data trans_log 
    -- 关键：用整点时间关联两个CTE
    ON dr.the_hour = trans_log.log_date
ORDER BY dr.the_hour;

几个关键优化点： #

• 在the_data中添加了时间过滤条件systemdate >= TRUNC(SYSDATE -1, 'HH24')，只查询过去24小时的数据，大幅减少数据扫描量；
• 补全了左连接的关联条件dr.the_hour = trans_log.log_date，这是你之前SQL缺失的核心部分；
• 如果不需要分钟显示，可以把TO_CHAR的格式改成'DD/MM/YYYY HH24:00'，更符合整点时间的语义；
• 如果systemdate是带时区的字段，建议用SYSTIMESTAMP替代SYSDATE，并根据业务需求做时区转换。

内容的提问来源于stack exchange，提问作者Sana.91