Oracle 分区查询-限制结果

我有一种情况，我试图确定发票行的可征税性。例如，如果发票行数量为6，则详细行不应超过6。

问题是，如果其中一个详细行导致累计数量超过发票数量，那么查询结果应该显示发票数量和累计数量之间的增量差异。

create table tt (
  ln  int,
  pl  int,
  id  int,
  qty int,
  tqty int, 
  tax varchar2(15),
  td  date
);

Insert into TT
   (LN, PL, ID, QTY, TQTY, 
    TAX, td)
 Values
   (1, 9, 23149, 6, 1, 
    'Non-Taxable', TO_DATE('1/8/2018', 'MM/DD/YYYY'));
Insert into TT
   (LN, PL, ID, QTY, TQTY, 
    TAX, td)
 Values
   (1, 9, 23149, 6, 2, 
    'Taxable', TO_DATE('11/15/2017', 'MM/DD/YYYY'));
Insert into TT
   (LN, PL, ID, QTY, TQTY, 
    TAX, td)
 Values
   (1, 9, 23149, 6, 1, 
    'Taxable', TO_DATE('10/16/2017', 'MM/DD/YYYY'));
Insert into TT
   (LN, PL, ID, QTY, TQTY, 
    TAX, td)
 Values
   (1, 9, 23149, 6, 1, 
    'Non-Taxable', TO_DATE('9/16/2017', 'MM/DD/YYYY'));
Insert into TT
   (LN, PL, ID, QTY, TQTY, 
    TAX, td)
 Values
   (1, 9, 23149, 6, 2, 
    'Taxable', TO_DATE('5/2/2017', 'MM/DD/YYYY'));
COMMIT;

SELECT *
FROM (SELECT ln,
       pl,
       id,
       qty,
       tqty,
       tax,
       td
       ,SUM(tqty) over (partition by pl ORDER BY td DESC) as CUMULATIVE_QTY 
       FROM tt
       ) 
WHERE 1=1 --cumulative_qty <= qty
ORDER BY ln, td DESC;

以下是数据的样子:

LN PL ID QTY TQTY TAX         TD     CUMULATIVE_QTY
1 9 23149 6 1 Non-Taxable  1/8/2018 1
1 9 23149 6 2 Taxable        11/15/2017 3
1 9 23149 6 1 Taxable        10/16/2017 4
1 9 23149 6 1 Non-Taxable 9/16/2017 5
1 9 23149 6 2 Taxable         5/2/2017 7

如果我取消注释 “cumulative_qty <= qty” 子句，则仅显示前4行 (因为7的累计数量超过了6的发票数量)，并且当我最终使用PIVOT时，它将显示6的数量，3个是应税的，2个是免税的。这意味着查询结果将保留1的数量 (应纳税)。

Question:

如何编写此查询，以便结果看起来像这样？

LN PL ID QTY TQTY TAX         TD     CUMULATIVE_QTY
1 9 23149 6 1 Non-Taxable  1/8/2018 1
1 9 23149 6 2 Taxable        11/15/2017 3
1 9 23149 6 1 Taxable        10/16/2017 4
1 9 23149 6 1 Non-Taxable 9/16/2017 5
1 9 23149 6 1 Taxable         5/2/2017 6

这是一种解决方法:

返回所有行，其中的累计总数为previous行小于总体数量。如果前一个总和大于总总和，则要排除当前行。

您可以使用以下窗口子句获得前一行的累计总数:

rows between unbounded preceding and 1 preceding

这将为第一行返回null (它没有以前的)。所以你需要把它映射到零。

然后，您可以将其与总数进行比较。并在最终选择中返回当前运行总量和总量中的最少数量:

select s.*, least (cumulative_qty, qty) cumulative
from (
  select ln, 
         pl, 
         id, 
         qty, 
         tqty, 
         tax, 
         td,
         sum(tqty) over (partition by pl order by td desc) as cumulative_qty,
         nvl(sum(tqty) over (partition by pl order by td desc
           rows between unbounded preceding and 1 preceding
         ), 0) as cumulative_qty_minus_1  
  from tt 
) s
where cumulative_qty_minus_1 <= qty 
order by ln, td desc;

LN   PL   ID      QTY   TQTY   TAX           TD            CUMULATIVE_QTY   CUMULATIVE_QTY_MINUS_1   CUMULATIVE   
   1    9   23149     6      1 Non-Taxable   2018 Jan 08                  1                        0            1 
   1    9   23149     6      2 Taxable       2017 Nov 15                  3                        1            3 
   1    9   23149     6      1 Taxable       2017 Oct 16                  4                        3            4 
   1    9   23149     6      1 Non-Taxable   2017 Sep 16                  5                        4            5 
   1    9   23149     6      2 Taxable       2017 May 02                  7                        5            6