如何从上一行结果中减去？ [英] How to subtract from previous row result?

问题描述

我需要从 currentitems 列中减去第一行 lot_size +减法结果。如果没有余额，则应为0。 bal 是结果列的外观。

I need to subtract first row lot_size + substraction result from currentitems column. If no balance left then it should be 0. bal is how result column should look.

    rowno | location | lot_size | currentitems |  bal   | bal_left  
   -------+----------+----------+--------------+--------+--------
        1 | AB1210   |     1200 |         1000 |   1000 |   200
        2 | AB1220   |     1200 |         1000 |    200 |     0
        3 | AB1230   |     1200 |          500 |      0 |     0

当前方法（使用PostgreSQL 9.3.1）：

Current approach (using postgresql 9.3.1):

SELECT 
    row_number() over (ORDER BY location) as rowno, 
    location,
    currentitems,
    1200 as lot_size,
    --here should be some case or something
    COALESCE(lag(currentitems) over(ORDER BY location),currentitems) AS bal
FROM foo;

推荐答案

不确定这是否是您要的内容，但是以下是获取下一行的方法：

Not sure if that's what you're asking, but here's how you can get next row:

create table a(
  id bigserial primary key,
  val integer
);

select 
  nth_value(val,(row_nr+1)::integer) over () as next_value, 
  val, from 
      (select row_number() over () as row_nr, val from a) t;

然后您可以进行个案和减法

And then you can do your cases and subtractions

这篇关于如何从上一行结果中减去？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！