計算兩個時間戳之間的值差異

資料庫小提琴

CREATE TABLE logistics (
    id SERIAL PRIMARY KEY,
    time_stamp DATE,
    product VARCHAR(255),
    quantity INT
);

INSERT INTO logistics
(time_stamp, product, quantity)
VALUES 
('2020-01-14', 'Product_A', '100'),
('2020-01-14', 'Product_B', '300'),

('2020-01-15', 'Product_B', '400'),
('2020-01-15', 'Product_C', '350'),

('2020-01-16', 'Product_B', '530'),
('2020-01-16', 'Product_C', '350'),
('2020-01-16', 'Product_D', '670'),

('2020-01-17', 'Product_C', '500'),
('2020-01-17', 'Product_D', '980'),
('2020-01-17', 'Product_E', '700'),
('2020-01-17', 'Product_F', '450');

預期結果：

time_stamp  |      product    |   difference    |
------------|-----------------|-----------------|---------
2020-01-14  |     Product_A   |        100      |  
2020-01-14  |     Product_B   |        300      |  
------------|-----------------|-----------------|---------
2020-01-15  |     Product_B   |        100      |
2020-01-15  |     Product_C   |        350      |
------------|-----------------|-----------------|---------
2020-01-16  |     Product_B   |        130      |  
2020-01-16  |     Product_C   |          0      |
2020-01-16  |     Product_D   |        670      |
------------|-----------------|-----------------|--------
2020-01-17  |     Product_C   |        150      |
2020-01-17  |     Product_D   |        310      |
2020-01-17  |     Product_E   |        700      |
2020-01-17  |     Product_F   |        450      |

我想計算每個產品的differencein到。quantitytimestamptimestamp

我試過這樣的事情：

SELECT
t2.time_stamp AS time_stamp, 
t2.product AS product,
SUM(t2.difference) AS difference

FROM
 
  (SELECT
  t1.time_stamp AS time_stamp, 
  t1.product AS product,
  t1.quantity AS quantity,
  t1.quantity-LAG(t1.quantity) OVER (ORDER BY t1.time_stamp) AS difference
  FROM
 
      (SELECT
      l.time_stamp AS time_stamp,
      l.product AS product,
      SUM(l.quantity) AS quantity
      FROM logistics l
      GROUP BY 1,2
      ORDER BY 1,2) t1
  
  GROUP BY 1,2,3
  ORDER BY 1,2,3) t2
 
GROUP BY 1,2
ORDER BY 1,2;

但是，我沒有通過此查詢獲得預期的結果。
您知道我需要如何更改它才能使其正常作業嗎？

uj5u.com熱心網友回復：

使用視窗函式應該很簡單：

SELECT time_stamp,
       product,
       quantity -
       coalesce(
          lag(quantity) OVER (PARTITION BY product ORDER BY time_stamp),
          0
       ) AS difference
FROM logistics
ORDER BY time_stamp, product;

 time_stamp │  product  │ difference 
════════════╪═══════════╪════════════
 2020-01-14 │ Product_A │        100
 2020-01-14 │ Product_B │        300
 2020-01-15 │ Product_B │        100
 2020-01-15 │ Product_C │        350
 2020-01-16 │ Product_B │        130
 2020-01-16 │ Product_C │          0
 2020-01-16 │ Product_D │        670
 2020-01-17 │ Product_C │        150
 2020-01-17 │ Product_D │        310
 2020-01-17 │ Product_E │        700
 2020-01-17 │ Product_F │        450
(11 rows)

uj5u.com熱心網友回復：

這是一個帶有“lag”和“coalesce”的簡單查詢。

SELECT
  time_stamp AS time_stamp, 
  product AS product,
  Coalesce(quantity-LAG(quantity) OVER (Partition by product ORDER BY product, time_stamp), quantity) AS difference
  FROM logistics
Order by time_stamp, product

time_stamp |product     |difference
___________|____________|_____________
2020-01-14 |"Product_A" |100
2020-01-14 |"Product_B" |300
___________|____________|_____________
2020-01-15 |"Product_B" |100
2020-01-15 |"Product_C" |350
___________|____________|_____________
2020-01-16 |"Product_B" |130
2020-01-16 |"Product_C" |0
2020-01-16 |"Product_D" |670
___________|____________|_____________
2020-01-17 |"Product_C" |150
2020-01-17 |"Product_D" |310
2020-01-17 |"Product_E" |700
2020-01-17 |"Product_F" |450

標籤：sql PostgreSQL的

上一篇：帶有OR條件的SQL子查詢

下一篇：SQL查詢選擇回傳結果不在一個表中