Oracle SQL问题：使用时间区间分配电表数据至时段分组的异常排查

你遇到的问题根源在于WHERE子句中对时间区间的匹配逻辑不一致：你只在和BlockEnd比较时使用了加1天后的调整区间，但和BlockStart比较时还是用的原始时分秒区间，这就导致00:00-05:00的记录无法匹配上Valley时段的起始条件。

具体问题点拆解 #

对于00:00-05:00的记录，原始时分秒区间（比如00:00:00）明显小于Valley的BlockStart（23:00:00），所以NUMTODSINTERVAL(...) > h.BlockStart这个条件根本不成立，自然不会匹配到Valley分组。

修正后的SQL代码 #

我们需要统一使用调整后的时间区间来和HourlyBlocks的BlockStart、BlockEnd做比较，同时用CTE提取重复计算的逻辑，让代码更简洁易懂：

WITH AdjustedMeterData AS (
    SELECT 
        m.MeterID,
        m.DateHour,
        m.KWH,
        -- 统一计算调整后的时间区间：00:00-05:00加1天，其余保持原样
        NUMTODSINTERVAL(m.DateHour - TRUNC(m.DateHour), 'DAY') + 
        CASE 
            WHEN NUMTODSINTERVAL(m.DateHour - TRUNC(m.DateHour), 'DAY') <= INTERVAL '0 05:00:00' DAY TO SECOND 
            THEN INTERVAL '1' DAY 
            ELSE INTERVAL '0' DAY 
        END AS AdjustedInterval
    FROM MeterData m
)
SELECT 
    am.MeterID,
    am.DateHour,
    am.KWH,
    am.AdjustedInterval AS intval,
    h.HourlyBlock
FROM AdjustedMeterData am
JOIN HourlyBlocks h 
    ON am.AdjustedInterval > h.BlockStart 
    AND am.AdjustedInterval <= h.BlockEnd
ORDER BY am.DateHour;

逻辑说明 #

1. CTEAdjustedMeterData：先把每条记录的时分秒区间做统一调整——00:00-05:00的区间加1天，变成1 00:00:00到1 05:00:00，刚好和Valley时段的BlockStart（0 23:00:00）、BlockEnd（1 05:00:00）范围匹配。
2. JOIN条件：用调整后的区间同时匹配BlockStart和BlockEnd，所有时段的匹配逻辑完全统一：
   • Rest时段（05:00-18:00）：调整后区间是0 05:00:00到0 18:00:00，完美对应Block范围
   • Peak时段（18:00-23:00）：调整后区间是0 18:00:00到0 23:00:00，匹配对应Block范围
   • Valley时段：包含当日23:00-24:00（调整后区间0 23:00:00到1 00:00:00）和次日00:00-05:00（调整后区间1 00:00:00到1 05:00:00），都能匹配Valley的Block范围

执行这段SQL后，所有记录都会正确分配到对应的时段分组，完全符合你给出的预期输出。

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