如何用 Oracle analytic function 计算计费周期最大值？

2026-10-24数据阿明

结论

可以用 Oracle analytic function 实现，分为两步：

累计统计当前行之前出现的 BILLING_DAY='Y'，生成计费周期编号。
用 MAX(DAILY_USAGE) OVER (PARTITION BY period_id) 计算每个周期的最大用量，再通过 CASE 将对应记录的 PERIOD_MAX 标记为 'Y'。

由于计费日是当前周期的最后一天，累计窗口必须在当前行的前一行结束。否则，当前计费日会被提前划入下一个周期。

查询示例

保留原始数据查询，并在此基础上增加计费周期编号：

WITH source_data AS (
    SELECT read_time,
           billing_day,
           daily_usage
    FROM (
        -- 原查询中生成 READ_TIME、BILLING_DAY、DAILY_USAGE 的部分
        SELECT x.seq
                 + TRUNC(SYSDATE - 20)
                 + DBMS_RANDOM.VALUE / 8
                 + 0.3 AS read_time,
               CASE
                   WHEN x.seq / 7 = TRUNC(x.seq / 7) THEN 'Y'
               END AS billing_day,
               ROUND(DBMS_RANDOM.VALUE * 100, 2) AS daily_usage,
               x.seq
        FROM (
            SELECT LEVEL AS seq
            FROM dual
            CONNECT BY LEVEL <= 20
        ) x
    )
),
period_data AS (
    SELECT read_time,
           billing_day,
           daily_usage,
           SUM(daily_usage) OVER (
               ORDER BY read_time
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) + 1000 AS meter_reading,
           COUNT(
               CASE WHEN billing_day = 'Y' THEN 1 END
           ) OVER (
               ORDER BY read_time
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
           ) AS period_id
    FROM source_data
)
SELECT read_time,
       billing_day,
       meter_reading,
       daily_usage,
       CASE
           WHEN daily_usage = MAX(daily_usage) OVER (
               PARTITION BY period_id
           )
           THEN 'Y'
       END AS period_max
FROM period_data
ORDER BY read_time;

为什么要使用 `1 PRECEDING`

下面的窗口用于生成计费周期编号：

COUNT(
    CASE WHEN billing_day = 'Y' THEN 1 END
) OVER (
    ORDER BY read_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
)

它只统计当前记录之前出现的计费日数量。这样划分后：

第一条记录至第一个 BILLING_DAY='Y' 属于周期 0。
第一个计费日之后的记录属于周期 1。
第二个计费日之后的记录属于周期 2。
标记为 Y 的计费日仍属于它所结束的周期。

如果把窗口终点写成 CURRENT ROW，计费日当天就会被计入下一个周期，不符合“计费日是周期结束日”的定义。

得到 period_id 后，可以直接计算每个周期的最大值：

MAX(daily_usage) OVER (PARTITION BY period_id)

最后比较当前行的 DAILY_USAGE 与该最大值是否相等即可。

实际数据中的注意事项

排序必须稳定

如果多条记录的 READ_TIME 可能相同，仅使用以下排序条件：

ORDER BY read_time

无法保证周期边界的计算顺序稳定。此时需要加入唯一字段，例如抄表记录 ID：

ORDER BY read_time, reading_id

累计电表读数与周期编号必须使用完全相同的排序条件。

最大值并列时会标记多行

当前写法会将周期内所有并列最大值都标记为 Y。这符合“当该行等于周期最大值时进行标记”的要求。

如果每个周期只能标记一条记录，可以用 ROW_NUMBER() 明确处理并列值：

WITH ranked_data AS (
    SELECT p.*,
           ROW_NUMBER() OVER (
               PARTITION BY period_id
               ORDER BY daily_usage DESC, read_time
           ) AS usage_rank
    FROM period_data p
)
SELECT read_time,
       billing_day,
       meter_reading,
       daily_usage,
       CASE WHEN usage_rank = 1 THEN 'Y' END AS period_max
FROM ranked_data
ORDER BY read_time;

最大值并列时，这段查询会选择时间最早的一条记录。

最后一个未结束周期

即使最后一批记录还没有出现 BILLING_DAY='Y'，查询也会将它们视为一个进行中的周期，并标记该周期现有记录中的最大值。

如果业务规定只有已结束的计费周期才能出现 PERIOD_MAX='Y'，还需额外判断该周期之后是否存在计费日。

`NULL` 的处理

MAX() 会忽略 NULL。如果某个周期内的 DAILY_USAGE 全部为 NULL，该周期不会有任何记录被标记为 Y。