主页 / dba / 问题 / 104596
Accepted
jpmc26
Asked: 2015-06-20 10:05:18 +0800 CST

带重置值的累积和

  • 772

考虑下表：

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL 
---+----------+-----------+-----------+-----
1  | 1        | 1         | (null)    | 3   
2  | 1        | 2         | (null)    | 2   
3  | 1        | 3         | (null)    | 1   
4  | 1        | 4         | 4         | 2   
5  | 1        | 5         | (null)    | 1   
6  | 2        | 1         | (null)    | 4   
7  | 2        | 2         | 2         | 3   
8  | 2        | 3         | (null)    | 4   
9  | 2        | 4         | (null)    | 2   
10 | 2        | 5         | (null)    | 2   
11 | 2        | 6         | (null)    | 4   
12 | 2        | 7         | 14        | 2   
13 | 2        | 8         | (null)    | 2

对于每一行，我需要计算VAL所有先前行的累积总和（按 排序ORDER_VAL和分组GROUP_ID），但每次NULL RESET_VAL遇到非时，我需要使用该值作为总和。接下来的行也需要建立在RESET_VAL而不是使用实际总和之上。请注意，每个组可以有多个重置值。

这是我对上表的预期结果：

ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL | CUMSUM
---+----------+-----------+-----------+-----+-------
1  | 1        | 1         | (null)    | 3   | 0
2  | 1        | 2         | (null)    | 2   | 3
3  | 1        | 3         | (null)    | 1   | 5
4  | 1        | 4         | 4         | 2   | 4
5  | 1        | 5         | (null)    | 1   | 6
6  | 2        | 1         | (null)    | 4   | 0
7  | 2        | 2         | 2         | 3   | 2
8  | 2        | 3         | (null)    | 4   | 5
9  | 2        | 4         | (null)    | 2   | 9
10 | 2        | 5         | (null)    | 2   | 11
11 | 2        | 6         | (null)    | 4   | 13
12 | 2        | 7         | 14        | 2   | 14
13 | 2        | 8         | (null)    | 2   | 16

如果不是重置值，我可以使用窗口查询：

SELECT temp.*,
       COALESCE(SUM(val) OVER (PARTITION BY group_id ORDER BY order_val ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
                0) AS cumsum
FROM temp;

上面的 SQLFiddle

我最初错误地认为我可以放在RESET_VAL的开头COALESCE，但这不起作用，因为它不会重置后续行的值。

我也尝试了这个解决方案，但它只会重置为零，而不是列中的值。将其调整为这样做证明是不平凡的，因为该值必须传播到所有后续行。

递归查询似乎很自然，但我还没有弄清楚如何做到这一点。

我可能应该提一下，我实际上必须处理的表比上面的示例要大得多（数十万到几百万行），所以请说明是否存在任何答案的性能缺陷。

oracle oracle-11g-r2

2 个回答

  1. Best Answer

    以下工作，但可能有一些更聪明的版本。查询逻辑说明：

    我们首先通过计算列的非空值来确定当前行已经完成了多少“重置” reset_val，因此我们可以将行分成子组。

    我们还使用了另一个窗口函数LAST_VALUE()，IGNORE NULLS所以我们可以找到最后一个reset_value。

    请注意，这两个窗口函数COUNT()都有LAST_VALUE()一个ORDER BY，因此是默认窗口ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。在查询中省略，使代码更清晰。

    假设val不可为空，则其他窗口函数也可以缩短，从：

           COALESCE(SUM(val) OVER 
         (PARTITION BY group_id, reset_count 
          ORDER BY order_val 
          ROWS BETWEEN UNBOUNDED PRECEDING 
                   AND 1 PRECEDING), 0)

    （也避免COALESCE()）到：

           SUM(val) OVER 
         (PARTITION BY group_id, reset_count 
          ORDER BY order_val)
       - val

    最后，在第二个 cte 中，我们使用上面找到的子组（使用PARTITION BY group_id, reset_count）来找到累积和。

    WITH x AS
  ( SELECT temp.*, 
           COUNT(reset_val) OVER 
               (PARTITION BY group_id 
                ORDER BY order_val)
             AS reset_count,
           COALESCE(LAST_VALUE(reset_val IGNORE NULLS) OVER 
               (PARTITION BY group_id 
                ORDER BY order_val), 0)
             AS reset_value
    FROM temp
  ) ,
y AS 
  ( SELECT x.*,
           COALESCE(SUM(val) OVER 
             (PARTITION BY group_id, reset_count 
              ORDER BY order_val 
              ROWS BETWEEN UNBOUNDED PRECEDING 
                       AND 1 PRECEDING), 0)            
           + reset_value AS cumsum      
    FROM x
  )
SELECT *
FROM y ;

    在SQLfiddle进行测试。

    另一种变体，基于@Chris' recursive answer。（略有改进，与 non-consecutive 一起使用order_val，避免使用 final GROUP BY）。
    也适用于组的第一行有一个reset_val：

    WITH row_nums AS
  ( SELECT id, group_id, order_val, reset_val, val, 
           ROW_NUMBER() OVER (PARTITION BY group_id
                              ORDER BY order_val)
             AS rn
    FROM temp
  ) ,
updated_temp (id, group_id, order_val, reset_val, val, rn, cumsum) AS
  ( SELECT id, group_id, order_val, reset_val, val, rn, 
           COALESCE(reset_val, 0)
    FROM row_nums
    WHERE rn = 1
  UNION ALL
    SELECT curr.id, curr.group_id, curr.order_val, curr.reset_val, curr.val, curr.rn, 
           COALESCE(curr.reset_val, prev.val + prev.cumsum) 
    FROM row_nums  curr 
      JOIN updated_temp  prev 
        ON  curr.rn-1 = prev.rn 
        AND curr.group_id = prev.group_id
  )
SELECT id, group_id, order_val, reset_val, val, cumsum
FROM updated_temp
ORDER BY group_id, order_val ;

    在SQLfiddle-2进行测试。

    另一种变体，使用旧的（专有）CONNECT BY语法进行递归查询。更紧凑，但我发现它比 CTE 版本更难写和读：

    WITH row_nums AS
  ( SELECT id, group_id, order_val, reset_val, val, 
           ROW_NUMBER() OVER (PARTITION BY group_id
                              ORDER BY order_val)
             AS rn,
           COALESCE(reset_val, 0) AS cumsum
    FROM temp
  ) 
SELECT id, group_id, order_val, reset_val, val, rn,  
       COALESCE(reset_val, PRIOR val + PRIOR cumsum, 0) AS cumsum
FROM row_nums
START WITH rn = 1 OR reset_val IS NOT NULL
CONNECT BY  rn-1 = PRIOR rn 
        AND group_id = PRIOR group_id
        AND reset_val IS NULL 
ORDER BY group_id, order_val ;

    在SQLfiddle-3测试。

    • 4

  2. 作为一项学术练习，我在 Postgres 中实现了一个解决方案。现在，我知道这是一个关于 Oracle 的问题，但它也被框定为一个 SQL 问题！:) 如果有人更熟悉 Oracle 的递归查询语法，也许他们可以找到必要的更改以使其在 Oracle 中运行。

    递归查询（在 Postgres 中）

    在这个解决方案中，我使用递归查询，WITH RECURSIVE在我的 CTE 中应用 Postgres 语法。递归查询如下：

    WITH RECURSIVE updated_temp AS (
  SELECT temp.*, 0::numeric AS CUMSUM FROM temp
  UNION
  SELECT curr.id, curr.group_id, curr.order_val, curr.reset_val, curr.val, 
  COALESCE(curr.RESET_VAL,prev.VAL + prev.CUMSUM) AS CUMSUM
  FROM temp AS curr JOIN updated_temp AS prev 
  ON curr.ORDER_VAL-1 = prev.ORDER_VAL AND curr.group_id = prev.group_id)
SELECT id, group_id, order_val, reset_val, val, max(cumsum) FROM updated_temp 
GROUP BY id, group_id, order_val, reset_val, val ORDER BY id ASC;

    这是相应的SQL Fiddle。

    当然，此查询正确行为的关键是首先使用查询的递归部分来构建累积总和，同时确保用于COALESCE替换任何RESET_VAL指示CUMSUM行的内容。最后一个SELECT只是让您过滤 的最大值，CUMSUM因为如果查询构建正确，则最大值与累积和相同。

    编辑：递归查询的 Oracle 版本

    此对 Oracle 的翻译由ypercube提供：

    WITH updated_temp (id, group_id, order_val, reset_val, val, cumsum) 
AS (
  SELECT id, group_id, order_val, reset_val, val, 0 
  FROM temp
  UNION ALL
  SELECT curr.id, curr.group_id, curr.order_val, curr.reset_val, curr.val, 
     COALESCE(curr.reset_val, prev.val + prev.cumsum) 
  FROM temp  curr 
    JOIN updated_temp  prev 
      ON  curr.order_val-1 = prev.order_val 
      AND curr.group_id = prev.group_id
)
  SELECT id, group_id, order_val, reset_val, val, max(cumsum) 
  FROM updated_temp 
  GROUP BY id, group_id, order_val, reset_val, val 
  ORDER BY id ASC;

    Oracle SQL 小提琴

    • 4

相关问题