如何在数据中也存在增量的情况下只选择值中的增量

Question

我有一个仓库，可以退货。库存减少(订单(，增加(供应(。我只有股票价值，没有其他东西。假设我有按库存数量升序排列的数据：

库存

时间
00:11	7144<---电流
00:10	7280
00:09	7416<---注意增加，意味着新的供应到达
00:08	2259
00:07	22333
00:06	2538
00:05	2999
00:04	3074
00:03	3104<---启动

您可以将两个"相邻的"；行，并检查前一行的stock是否大于后一行的stock，以查找您的订单。然后把这些股票的差额加起来。

假设列time是唯一的，您可以使用以下查询：

SELECT SUM(t1.stock - t2.stock) AS sum_of_orders
FROM my_table t1
INNER JOIN my_table t2 ON t2.time > t1.time
AND NOT EXISTS(
SELECT 1
FROM my_table t3 
WHERE t3.time > t1.time AND t3.time < t2.time)
WHERE t1.stock > t2.stock;

这是一种间隙和孤岛问题。您可以识别"；"岛"；当库存增加时。然后在每个岛屿内，你想要最大值和最小值。你可以使用来总结岛屿

select min(stock), max(stock)
from (select t.*,
sum(case when prev_stock > stock then 0 else 1 end) over (order by time) as grp
from (select t.*,
lag(stock) over (order by time) as prev_stock
from t
) t
) t
group by grp;

然后再做一个总结，得出你想要的总数：

select sum(max_stock - min_stock)
from (select select min(stock) as min_stock, max(stock) as max_stock
from (select t.*,
sum(case when prev_stock > stock then 0 else 1 end) over (order by time) as grp
from (select t.*,
lag(stock) over (order by time) as prev_stock
from t
) t
) t
group by grp
) t;

这里有一个db<gt；不停摆弄