Oracle SQL实现按自然日统计近30天累计值的视图需求

问题分析 #

你之前的代码用ROWS BETWEEN 29 preceding and current row是按行的数量统计，而非自然日区间。如果存在日期缺失（比如某几天无数据），这种方式会导致统计的时间范围不准确，没法满足“往前30天”的需求。

方案1：使用窗口函数的RANGE范围（Oracle 12c+支持） #

利用Oracle窗口函数的RANGE BETWEEN结合日期差值定义时间区间，直接基于自然日范围统计：

CREATE OR REPLACE VIEW daily_30d_sum AS
SELECT
    t.year_,
    t.month_,
    t.day_,
    t.dt,
    SUM(t.daily_value) OVER (
        ORDER BY t.dt
        RANGE BETWEEN INTERVAL '29' DAY PRECEDING AND CURRENT ROW
    ) AS VALUE_LAST_30
FROM (
    -- 先按天聚合VALUE，同时生成完整日期dt
    SELECT
        year_,
        month_,
        day_,
        TO_DATE(year_ || LPAD(month_, 2, '0') || LPAD(day_, 2, '0'), 'YYYYMMDD') AS dt,
        SUM(value_) AS daily_value
    FROM table1
    GROUP BY year_, month_, day_
) t;

说明： #

• RANGE BETWEEN INTERVAL '29' DAY PRECEDING AND CURRENT ROW：统计当前日期往前29天到当天的所有数据，刚好是30个自然日（包含当天）。
• 先聚合每日的VALUE总和，再基于日期区间做窗口统计，确保每个日期对应的是自然日范围内的累计值。

方案2：使用关联查询（兼容低版本Oracle） #

如果你的Oracle版本不支持窗口函数的RANGE日期区间，可以用关联查询实现：

CREATE OR REPLACE VIEW daily_30d_sum AS
SELECT
    t1.year_,
    t1.month_,
    t1.day_,
    t1.dt,
    SUM(t2.daily_value) AS VALUE_LAST_30
FROM (
    SELECT
        year_,
        month_,
        day_,
        TO_DATE(year_ || LPAD(month_, 2, '0') || LPAD(day_, 2, '0'), 'YYYYMMDD') AS dt,
        SUM(value_) AS daily_value
    FROM table1
    GROUP BY year_, month_, day_
) t1
LEFT JOIN (
    SELECT
        TO_DATE(year_ || LPAD(month_, 2, '0') || LPAD(day_, 2, '0'), 'YYYYMMDD') AS dt,
        SUM(value_) AS daily_value
    FROM table1
    GROUP BY year_, month_, day_
) t2 ON t2.dt BETWEEN t1.dt - INTERVAL '29' DAY AND t1.dt
GROUP BY t1.year_, t1.month_, t1.day_, t1.dt;

说明： #

• 主查询的t1是每日聚合数据，关联子查询t2中符合t1.dt往前29天到当天的所有日期数据，求和后得到累计值。
• 这种方式兼容性更好，适合Oracle 11g及更早版本。

额外优化建议 #

如果你的表中已经有DATE字段（题目提到表结构包含DATE字段），可以直接使用该字段，无需通过YEAR、MONTH、DAY拼接生成，更高效且避免格式错误：

-- 替换日期生成部分为表中的DATE字段
SELECT
    EXTRACT(YEAR FROM date_col) AS year_,
    EXTRACT(MONTH FROM date_col) AS month_,
    EXTRACT(DAY FROM date_col) AS day_,
    date_col AS dt,
    SUM(value_) AS daily_value
FROM table1
GROUP BY EXTRACT(YEAR FROM date_col), EXTRACT(MONTH FROM date_col), EXTRACT(DAY FROM date_col), date_col

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