我如何从第二列运行总数

Question

我有一个如下的数据集，

Lot Size    Reported QTY    Qty Balance
150          100            
150          100            
150          80             
150          80            
150          5              

Qty Balance需要按如下方式计算，

Row 1 = Lot Size - Reported Qty (row1) => 150-100 = 50
Row 2 = Reported Qty (row1) - Reported Qty(row2) => 100-100 =0
Row 3 = Reported Qty (row2) - Reported Qty(row3) => 100-80 =20
... till the last row

我的预期结果是

Lot Size    Reported QTY    Qty Balance
150          100            50
150          100            0
150          80             20
150          80             0
150          5              75

如何在查询中实现这一点?

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE lots ( Lot_Size, Reported_QTY ) AS
          SELECT 150, 100 FROM DUAL
UNION ALL SELECT 150, 100 FROM DUAL
UNION ALL SELECT 150,  80 FROM DUAL
UNION ALL SELECT 150,  80 FROM DUAL
UNION ALL SELECT 150,   5 FROM DUAL;

查询1

:

SELECT Lot_Size,
       Reported_QTY,
       COALESCE( LAG( Reported_QTY ) OVER ( ORDER BY NULL ) - Reported_QTY,
                 Lot_Size - Reported_QTY ) AS Qty_Balance
FROM   Lots

结果

:

| LOT_SIZE | REPORTED_QTY | QTY_BALANCE |
|----------|--------------|-------------|
|      150 |          100 |          50 |
|      150 |          100 |           0 |
|      150 |           80 |          20 |
|      150 |           80 |           0 |
|      150 |            5 |          75 |

Answer 2

您应该看一下LAG()解析函数。有关该函数及其接受的参数的详细信息，请参阅此处(例如，我认为当lag()函数返回null时，您将需要传入lot_size列的默认值)

一旦您确定了前一行报告的数量值，那么您可以简单地减去它。当然，您需要某种方法来标识行的顺序—您的示例数据似乎没有这样的顺序，因此数据库将无法确定哪一行是第一行，哪一行是最后一行。

。

with sample_data (lot_size, reported_qty) as (SELECT 150, 100 FROM DUAL
                                              UNION ALL SELECT 150, 100 FROM DUAL
                                              UNION ALL SELECT 150,  80 FROM DUAL
                                              UNION ALL SELECT 150,  80 FROM DUAL
                                              UNION ALL SELECT 150,   5 FROM DUAL)
select lot_size,
       reported_qty,
       lag(reported_qty, 1, lot_size) over (order by null) - reported_qty diff
from   sample_data;
  LOT_SIZE REPORTED_QTY       DIFF
---------- ------------ ----------
       150          100         50
       150          100          0
       150           80         20
       150           80          0
       150            5         75

Answer 3

正如@Boneist建议的，您需要使用LAG() OVER()解析函数。

你只需要再做一个任务来处理第一行，这将是NULL，使用CASE你可以使它工作。

测试用例

SQL> WITH data AS
  2    ( SELECT t.*, lag(reported_qty) OVER(ORDER BY NULL) rn FROM t
  3    )
  4  SELECT lot_size,
  5    reported_qty,
  6    CASE
  7      WHEN rn IS NULL
  8      THEN lot_size     - reported_qty
  9      ELSE rn - reported_qty
 10    END qty_balance
 11  FROM data;
  LOT_SIZE REPORTED_QTY QTY_BALANCE
---------- ------------ -----------
       150          100          50
       150          100           0
       150           80          20
       150           80           0
       150            5          75
SQL>