Oracle 对最近的记录求和，直到定义值，然后忽略其余值

嗨，尝试这样的事情，其中修改了 WHERE 子句

-- 未经测试

SELECT
   ID,
   WHEN,
   VALUE,
   AVG_COL,
   SUM(VALUE) OVER (PARTITION BY ID ORDER BY WHEN) AS VALUE, --must equal 30
   AVG(AVG_COL) OVER (PARTITION BY ID) AVG
FROM
    TABLE_ONE
WHERE
   ID IN (SELECT ID FROM ( (SELECT ID, sum(VALUE) sum_val FROM 
           TABLE_ONE GROUP BY ID) WHERE SUM_VAL = 30);

试试这个

select id,
SUM(VALUE) OVER (PARTITION BY ID ORDER BY WHEN RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS VALUE,
AVG(AVG_COL) OVER (PARTITION BY ID) AVG
from table_one
where VALUE <= 30
order by when desc
fetch first 1 rows only;

您可以使用窗口函数计算运行总和，然后在外部查询中进行筛选和限制。

假设一个表，如下所示：(id, dt, val, . . .)

select *
from (
    select t.*,
        sum(val) over(partition by id order by dt) sum_val
    from mytable t
) t
where sum_val >= 30
order by row_number() over(partition by id order by dt desc)
fetch first row with ties

笔记：

• 这将一次处理多个 ID
• 对于每个 ID，这将带来第一行，其中值的运行总和至少达到 30（如果有）（行按降序日期处理）

您需要的是运行值的总和，并决定要按什么顺序应用该运行总和。
示例数据

WITH
    tbl AS
        (
            Select 101 "ID", 2016 "YR",  6 "VAL", 84.5 "AVG_COL" From Dual Union All
            Select 101 "ID", 2015 "YR",  3 "VAL",   76 "AVG_COL" From Dual Union All
            Select 101 "ID", 2014 "YR",  3 "VAL",   87 "AVG_COL" From Dual Union All
            Select 101 "ID", 2013 "YR", 15 "VAL", 85.8 "AVG_COL" From Dual Union All
            Select 101 "ID", 2012 "YR",  6 "VAL",   92 "AVG_COL" From Dual Union All
            Select 101 "ID", 2011 "YR",  3 "VAL",   81 "AVG_COL" From Dual Union All
            Select 101 "ID", 2010 "YR",  3 "VAL", 82.3 "AVG_COL" From Dual 
        ),

创建 CTE（我将其命名为 grid）来准备数据 - 在本例中，我使用了按年份降序排列：

  grid AS
    (
        Select
          ID, YR, 
          VAL, 
          Sum(VAL) OVER(Partition By ID Order By YR DESC Rows Between Unbounded Preceding And Current Row) "RUNNING_SUM",
          CASE WHEN Sum(VAL) OVER(Partition By ID Order By YR DESC Rows Between Unbounded Preceding And Current Row) >= 30
               THEN Sum(1) OVER(Partition By ID Order By YR DESC Rows Between Unbounded Preceding And Current Row)  
          END "IS_OVER_RN",
          AVG_COL,
          Round(AVG(AVG_COL) OVER(Partition By ID Order By YR DESC Rows Between Unbounded Preceding And Current Row), 2) "RUNNING_AVG"
        From
          tbl
    )

此 cte 的结果如下：

        ID         YR        VAL RUNNING_SUM IS_OVER_RN    AVG_COL RUNNING_AVG
---------- ---------- ---------- ----------- ---------- ---------- -----------
       101       2016          6           6                  84.5        84.5 
       101       2015          3           9                    76       80.25 
       101       2014          3          12                    87        82.5 
       101       2013         15          27                  85.8       83.33 
       101       2012          6          33          5         92       85.06 
       101       2011          3          36          6         81       84.38 
       101       2010          3          39          7       82.3       84.09

现在，您可以使用下面的代码获得结果

-- Main SQL 
SELECT
    ID, RUNNING_SUM, RUNNING_AVG
FROM
    grid g
WHERE IS_OVER_RN = (Select Min(IS_OVER_RN) From grid Where ID = g.ID)

结果：

-- with YEARS in DESCENDING order
        ID RUNNING_SUM RUNNING_AVG
---------- ----------- -----------
       101          33       85.06 

如果在网格 cte ASCENDING 中的分析函数中进行排序，则相同的主 SQL 将产生：

-- with YEARS in ASCENDING order
        ID RUNNING_SUM RUNNING_AVG
---------- ----------- -----------
       101          30        82.5