如何将每行的SUM()结果汇总到另一列

Question

我有这张表格

 | ID_prim    | ID (FKey)   | Date         | Moved Items  |
 |:-----------|:------------|-------------:|:------------:|
 | 1003       | 12_1        |    nov 2013  |    2         |
 | 1003       | 12_2        |    okt 2013  |    3         |
 | 1003       | 12_3        |    dec 2014  |    5         |
 | 1003       | 12_4        |    feb 2015  |    10        |
 | 1003       | 12_5        |    apr 2012  |    1         |
 | 1003       | 12_11       |    jan 2011  |    5         |

我想按以下方式查询同一张表：

1. 按照日期降序排序
2. 对每行的“Moved Item”求和
3. 如果总和达到我的目标金额，则停止查询
4. 我的目标金额从最大的“Summed Total” (26) 开始，并减去我想要的金额 (16)

就像这样

| ID_prim    | ID (FKey)   | Date         | Moved Items  | Summed Total |
|:-----------|:------------|-------------:|:------------:|:------------:|
| 1003       | 12_4        |    feb 2015  |     10       |     26
| 1003       | 12_3        |    dec 2014  |     5        |     16
| 1003       | 12_3        |    nov 2013  |     2        |     11 <
| 1003       | 12_4        |    okt 2013  |     3        |     9 
| 1003       | 12_5        |    apr 2012  |     1        |     6
| 1003       | 12_11       |    jan 2011  |     5        |     5

当我达到"Summed Total"(26)减去16的值10时，我希望停止查询。因此，请展示从10开始的所有内容，我只想在数据库中获取这些数值。

| ID_prim    | ID (FKey)   | Date         | Moved Items  | Summed Total |
|:-----------|:------------|-------------:|:------------:|:------------:|
| 1003       | 12_4        |    feb 2015  |     10       |     26
| 1003       | 12_3        |    dec 2014  |     5        |     16
| 1003       | 12_3        |    nov 2013  |     2        |     11

我所拥有的是以下内容

  SELECT
  T1.ID_prim, T1.ID as ID (FKey), T1.Moved_Items as Moved Items, t1.Date, SUM(T2.MOVEMENTQTY) AS Summed Total
  FROM Table1 T1
  INNER JOIN Table1 T2 ON T2.ID <= T1.ID
    inner join table2 inout on T1.ID_prim = inout.ID_prim
    AND T2.ID_prim = inout.ID_prim
    AND T2.ID_prim = T1.ID_prim
  where t1.ID_prim = 1003
  and t2.ID_prim = 1003
  and inout.ISSOTRX = 'N'
  GROUP BY T1.ID_prim, T1.Moved Items, t1.Date
  HAVING SUM(T2.Moved Items) <= 16
  order by  t1.UPDATED desc

但是这个总和并不真正起作用。 有人可以帮我编写适用于Oracle DB的SQL语句，以打印出我想要的表格吗？

- Igoranze

---

nov 2013 不是一个日期。请使用 to_date 提供一些日期值的示例数据。 - Lalit Kumar B

@LalitKumarB 我没有写出上面的日期。在数据库中，我的日期列看起来像这样“2015-06-08 11:52:00”，它是一个日期值。但这不是主要关注点。主要关注点是总和总数以及如何迭代。 - Igoranze

总和不应该是27而不是26吗？ - Lalit Kumar B

@LalitKumarB 不应该这样。请记住，我们首先从最大值到最小值计数，然后减去所需值以显示结果，直到我们减去所有行以获得所需值。话虽如此，16 + 10 = 26。 - Igoranze

为什么不行？如果不行，那么规则是什么？2、3、6、10、1、5的总和是27而不是26。 - Lalit Kumar B

我明白你的意思，第一个表格的值是6，应该是5。已修复。 - Igoranze

Answer 1

根据问题中的评论澄清，可以使用SUM()分析函数来获取累计总数，然后根据条件进行过滤。 表格：

SQL> SELECT * FROM t;

   ID_PRIM ID    DT             MOVED
---------- ----- --------- ----------
      1003 12_1  01-NOV-13          2
      1003 12_2  01-OCT-13          3
      1003 12_3  01-DEC-14          5
      1003 12_4  01-FEB-15         10
      1003 12_5  01-APR-12          1
      1003 12_11 01-JAN-11          5

6 rows selected.

SQL>

累计总数

SQL> SELECT t.*, SUM(moved) OVER(ORDER BY dt) sm FROM t ORDER BY dt DESC;

   ID_PRIM ID    DT             MOVED         SM
---------- ----- --------- ---------- ----------
      1003 12_4  01-FEB-15         10         26
      1003 12_3  01-DEC-14          5         16
      1003 12_1  01-NOV-13          2         11
      1003 12_2  01-OCT-13          3          9
      1003 12_5  01-APR-12          1          6
      1003 12_11 01-JAN-11          5          5

6 rows selected.

SQL>

期望的输出

SQL> WITH DATA AS
  2    ( SELECT t.*, SUM(moved) OVER(ORDER BY dt) sm FROM t ORDER BY dt DESC
  3    )
  4  SELECT * FROM data WHERE sm >= 16;

   ID_PRIM ID    DT             MOVED         SM
---------- ----- --------- ---------- ----------
      1003 12_4  01-FEB-15         10         26
      1003 12_3  01-DEC-14          5         16

SQL>

请注意，nov 2013不是日期，它是一个字符串。由于您想要基于日期排序，因此必须始终使用TO_DATE将其明确转换为日期。无论如何，我使用TO_DATE创建了示例数据。 更新：OP希望在运行时从总值的最大值中减去所需值。

SQL> WITH DATA AS
  2    ( SELECT t.*, SUM(moved) OVER(ORDER BY dt) sm FROM t ORDER BY dt DESC
  3    )
  4  SELECT * FROM DATA t WHERE sm >
  5    (SELECT MAX(sm) FROM data
  6    ) - 16 ;

   ID_PRIM ID    DT             MOVED         SM
---------- ----- --------- ---------- ----------
      1003 12_4  01-FEB-15         10         26
      1003 12_3  01-DEC-14          5         16
      1003 12_1  01-NOV-13          2         11

SQL>

在更新的查询中，MAX(sm)返回26，然后根据条件WHERE sm > MAX(sm) -16过滤行，这意味着返回所有'sm'值大于26-16即10的行。您可以在运行时使用替代变量输入值16。

- Lalit Kumar B

---

接近了，但我不想从0开始计数并检查我的>=16从哪里开始。我想先像“Running total”表中那样全部计数，然后从最大值（26）减去我的期望值（16）。如果在ID_PRIM上方添加另一行，会发生什么？ ID DT MOVED SM

---

1003 12_9 02-FEB-15 10 36 1003 12_4 01-FEB-15 10 26那么我的“期望输出”也将显示最后添加的值。然后我想从MAX（36）开始减去。 - Igoranze

@Igoranze 好的，请看我的更新。在更新的查询中，MAX(sm)返回26，然后在WHERE条件中过滤行，满足sm > MAX(sm)-16的条件，这意味着返回所有'sm'值大于26-16即10的行。您可以使用替换变量在运行时输入值16。 - Lalit Kumar B