在SQL中找到差异（最近的值-最近日期时间之前的值）

debugcn 发表于 Dev

皮亚

对于下图给出的表t1，如何在sql中为每个UserID和ProductID对找到PurchaseAmount Diffenrece？

差=最新值-最近日期时间之前的值

这只是较大数据的一部分，PurchaseTime包含更多的UserID，ProductID对日期时间

最终结果将类似于以下内容：

MT0

您可以使用LEAD和ROW_NUMBER分析功能：

SELECT userid,
       productid,
       purchasetime,
       DeltaPurchaseAmount
FROM   (
  SELECT userid,
         productid,
         purchasetime,
         productamount
           - LEAD( productamount, 1, 0 ) OVER (
               PARTITION BY userid, productid
               ORDER BY purchasetime DESC
             )
           AS DeltaPurchaseAmount,
         ROW_NUMBER() OVER (
           PARTITION BY userid, productid
           ORDER BY purchasetime DESC
         )
           As rn
  FROM   table_name
)
WHERE rn = 1;

其中，对于Oracle中的示例数据：

CREATE TABLE table_name ( userid, productid, purchasetime, productamount ) AS
SELECT 1001, 901, DATE '2021-01-12', 100 FROM DUAL UNION ALL
SELECT 1001, 901, DATE '2021-01-11',  75 FROM DUAL UNION ALL
SELECT 1001, 905, DATE '2021-01-12', 150 FROM DUAL UNION ALL
SELECT 1001, 905, DATE '2021-01-11', 100 FROM DUAL UNION ALL
SELECT 1001, 999, DATE '2021-01-12', 100 FROM DUAL UNION ALL
SELECT 1002, 901, DATE '2021-01-12', 220 FROM DUAL UNION ALL
SELECT 1002, 905, DATE '2021-01-12', 215 FROM DUAL UNION ALL
SELECT 1002, 905, DATE '2021-01-11', 200 FROM DUAL UNION ALL
SELECT 1002, 999, DATE '2021-01-12', 250 FROM DUAL UNION ALL
SELECT 1002, 999, DATE '2021-01-11', 100 FROM DUAL;

（为了简化起见，使用了午夜的日期，但是在非午夜的时间仍然可以使用。）

给出：

USERID | PRODUCTID | 购买时间| DELTAPURCHASEAMOUNT
-----：| --------：| ：------------------ | ------------------：
  1001 | 901 | 2021-01-12 00:00:00 | 25
  1001 | 905 | 2021-01-12 00:00:00 | 50
  1001 | 999 | 2021-01-12 00:00:00 | 100
  1002 | 901 | 2021-01-12 00:00:00 | 220
  1002 | 905 | 2021-01-12 00:00:00 | 15
  1002 | 999 | 2021-01-12 00:00:00 | 150