PL SQL实现累计计数器日差值计算与结果转置需求问询

假设你的原始表结构如下（可根据实际业务调整）：

CREATE TABLE daily_counters (
    id NUMBER,
    record_date DATE,
    cumulative_count NUMBER
);

1. 计算每日增量值 #

使用窗口函数LAG()获取前一日的累计值，通过当日累计值与前一日累计值的差值得到当日增量；若为该ID的首条数据，直接取当日累计值作为增量。同时自动筛选最近7天（含当日）的数据：

WITH daily_increments AS (
    SELECT
        id,
        record_date,
        cumulative_count,
        -- 计算当日增量：无前置数据时默认用0兜底
        cumulative_count - LAG(cumulative_count, 1, 0) OVER (PARTITION BY id ORDER BY record_date) AS daily_increment
    FROM daily_counters
    -- 动态取最近7天，每日执行时自动更新范围
    WHERE record_date BETWEEN TRUNC(SYSDATE) - 6 AND TRUNC(SYSDATE)
)
SELECT * FROM daily_increments;

2. 转置每日增量为列 #

通过生成日期序列补全缺失日期，再用PIVOT函数将7天的增量转置为横向列，最终以当日作为参考日期输出：

WITH date_range AS (
    -- 生成最近7天的完整日期序列，避免因缺失数据导致转置后缺列
    SELECT TRUNC(SYSDATE) - LEVEL + 1 AS calc_date
    FROM DUAL
    CONNECT BY LEVEL <= 7
),
daily_increments AS (
    SELECT
        dc.id,
        dr.calc_date,
        -- 无数据的日期增量设为0，保证转置结构完整
        NVL(
            dc.cumulative_count - LAG(dc.cumulative_count, 1, 0) OVER (PARTITION BY dc.id ORDER BY dr.calc_date),
            0
        ) AS daily_increment
    FROM date_range dr
    LEFT JOIN daily_counters dc ON dr.calc_date = dc.record_date
),
pivoted_data AS (
    SELECT
        id,
        TRUNC(SYSDATE) AS reference_date, -- 当日作为统一参考日期
        -- 按日期偏移命名列，清晰区分各天增量
        "DAY_MINUS_6", "DAY_MINUS_5", "DAY_MINUS_4",
        "DAY_MINUS_3", "DAY_MINUS_2", "DAY_MINUS_1", "DAY_0"
    FROM daily_increments
    PIVOT (
        MAX(daily_increment)
        FOR calc_date IN (
            TRUNC(SYSDATE)-6 AS DAY_MINUS_6,
            TRUNC(SYSDATE)-5 AS DAY_MINUS_5,
            TRUNC(SYSDATE)-4 AS DAY_MINUS_4,
            TRUNC(SYSDATE)-3 AS DAY_MINUS_3,
            TRUNC(SYSDATE)-2 AS DAY_MINUS_2,
            TRUNC(SYSDATE)-1 AS DAY_MINUS_1,
            TRUNC(SYSDATE) AS DAY_0
        )
    )
)
SELECT * FROM pivoted_data;

关键说明 #

• date_range CTE确保最近7天的每个日期都有对应行，避免某ID某天无数据时转置结果缺失列
• 每日执行时，TRUNC(SYSDATE)会自动更新，日期范围同步调整（比如次日会覆盖11月27日至12月4日）
• 支持多ID场景，自动按ID分组生成各自的7天增量转置结果

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