減去視窗內的數字

假設我有一張桌子：

WITH MY_TABLE AS (SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 1 RN, 3500 PRODUCED, 3500 WPRODUCED FROM DUAL 
                  UNION ALL
                  SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 2 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL 
                  UNION ALL
                  SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 3 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL)
SELECT * FROM MY_TABLE

我想根據 TO_PRODUCE 減去 PRODUCED 并將其余部分留在最后一個 RN (RowNumber) 中，例如

1000

1000

1500

我寫了以下查詢，但它只有在 RN 不高于 3 時才有效。在去年的記錄中，我發現它高達 32，但可能更高。

SELECT ORDER_NUM, TO_PRODUCE, PRODUCT_ID, RN, PRODUCED, SUBTRACTING, 
CASE WHEN RN = 1 AND (PRODUCED <= TO_PRODUCE OR LEAD(RN) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) IS NULL) THEN PRODUCED
     WHEN RN = 1 AND PRODUCED > TO_PRODUCE THEN TO_PRODUCE
     WHEN LEAD(SUBTRACTING) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) IS NULL AND SUBTRACTING > 0 THEN LAG(SUBTRACTING) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN)
     WHEN SUBTRACTING >= 0 AND LAG(SUBTRACTING) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) > 0 THEN TO_PRODUCE
     WHEN SUBTRACTING < 0 AND LAG(SUBTRACTING) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) > 0 THEN LAG(SUBTRACTING) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) 
     ELSE 0
     END AS TRUE_PRODUCED        
FROM (  
WITH MY_TABLE AS (SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 1 RN, 3500 PRODUCED, 3500 WPRODUCED FROM DUAL 
               UNION ALL
               SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 2 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL 
               UNION ALL
               SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 3 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL)
SELECT PRODUCT_ID, ORDER_NUM, TO_PRODUCE, PRODUCED, RN,
    CASE WHEN RN = 1 AND LEAD(RN) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) IS NULL THEN PRODUCED 
        ELSE CASE WHEN RN = 1 AND PRODUCED > TO_PRODUCE AND LEAD(RN) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) > 1 THEN PRODUCED - TO_PRODUCE
                        WHEN RN = 2 THEN LAG(PRODUCED - TO_PRODUCE) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) - TO_PRODUCE
                        WHEN RN = 3 THEN LAG(PRODUCED - TO_PRODUCE, 2) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) - LAG(TO_PRODUCE) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) - TO_PRODUCE
            END 
        END AS SUBTRACTING FROM MY_TABLE ORDER BY RN
);

這個查詢給了我一個輸出：

我想讓任何 RN 都通用 SUBTRACTING。我可以繼續

WHEN RN = 4 THEN 
LAG(PRODUCED - TO_PRODUCE, 3) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN) 
- LAG(TO_PRODUCE, 2) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN)
- LAG(TO_PRODUCE) OVER (PARTITION BY ORDER_NUM, PRODUCT_ID ORDER BY ORDER_NUM, PRODUCT_ID, RN)
- TO_PRODUCE

并以類似方式繼續，但我認為有更好（更快）的方式。

uj5u.com熱心網友回復：

您可以通過更有效地使用視窗函式來做到這一點 -

WITH MY_TABLE AS (SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 1 RN, 3500 PRODUCED, 3500 WPRODUCED FROM DUAL 
                  UNION ALL
                  SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 2 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL 
                  UNION ALL
                  SELECT 'Order1' ORDER_NUM, 1000 TO_PRODUCE, 'ProductID1' PRODUCT_ID, 3 RN, 0 PRODUCED, 3500 WPRODUCED FROM DUAL)
SELECT ORDER_NUM, TO_PRODUCE, PRODUCT_ID, RN, PRODUCED,
       SUM(PRODUCED) OVER() - 
       SUM(TO_PRODUCE) OVER (ORDER BY RN) SUBTRACTING
  FROM MY_TABLE;

演示。

標籤：

上一篇：如何將新添加列中的數百萬條記錄插入到SQLServer中的現有表中？

下一篇：從幾行的單列到一條記錄中的多列，但允許在行中為NULL