我该如何从第二栏中运行总计 [英] How do i do running totals from second column

问题描述

我有一个如下数据集，

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

数量余额"需要计算如下，

The Qty Balance needs to calculated as follows,

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

如何在查询中实现这一目标?

How do I achieve this in a query?

推荐答案

SQL提琴

Oracle 11g R2架构设置:

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

结果 :

Results:

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

这篇关于我该如何从第二栏中运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！