sql - SQL SUM() 窗口函数

Question

我有按期限分组的数据，我正在尝试使用窗口函数获取所有数据的运行总数。问题是每条新记录都已经求和了，我已经尝试过使用 EXCLUDE TIES 但问题是它只适用于当前行。

例如：SUM(Value1) OVER（按术语行排序，无界前不包括关系）

价值1	学期	预期成绩
0.5	1	0.5
0.75	1	0.75
1	1	1
0.25	2	1.25
0.5	2	1.5
1	2	2
0.13	3	2.13
0.65	3	2.65
0.75	3	2.75

我要问的问题是，有没有办法用最新值对先前术语的每个术语进行分组，然后仅排除当前行上的关系以获得正确的“运行总数”？如果有帮助的话，最高的“价值1”将变为1。

标签： sql

Answer 1

也许是这样的：

WITH xrows AS (
        SELECT x.*
             , value1 - COALESCE(LAG(value1) OVER (PARTITION BY term ORDER BY value1), 0) AS diff
          FROM test AS x
     )
SELECT x.*
     , SUM(diff) OVER (ORDER BY term, value1) AS rsum
  FROM xrows AS x
 ORDER BY term, value1
;

只是一个派生表，没有WITH clause：

SELECT x.*
     , SUM(diff) OVER (ORDER BY term, value1) AS rsum
  FROM (SELECT x.*
             , value1 - COALESCE(LAG(value1) OVER (PARTITION BY term ORDER BY value1), 0) AS diff
          FROM test AS x
       ) AS x
 ORDER BY term, value1
;

结果：

+--------+------+----------+------+------+
| value1 | term | expected | diff | rsum |
+--------+------+----------+------+------+
|   0.50 |    1 |     0.50 | 0.50 | 0.50 |
|   0.75 |    1 |     0.75 | 0.25 | 0.75 |
|   1.00 |    1 |     1.00 | 0.25 | 1.00 |
|   0.25 |    2 |     1.25 | 0.25 | 1.25 |
|   0.50 |    2 |     1.50 | 0.25 | 1.50 |
|   1.00 |    2 |     2.00 | 0.50 | 2.00 |
|   0.13 |    3 |     2.13 | 0.13 | 2.13 |
|   0.65 |    3 |     2.65 | 0.52 | 2.65 |
|   0.75 |    3 |     2.75 | 0.10 | 2.75 |
+--------+------+----------+------+------+

设置：

CREATE TABLE test (value1 decimal(8,2), term int, expected decimal(8,2));

INSERT INTO test VALUES
  (0.5 , 1, 0.5 )
, (0.75, 1, 0.75)
, (1   , 1, 1   )
, (0.25, 2, 1.25)
, (0.5 , 2, 1.5 )
, (1   , 2, 2   )
, (0.13, 3, 2.13)
, (0.65, 3, 2.65)
, (0.75, 3, 2.75)
;