Postgresql incremental and conditional filtering

Question

I have this table

  ID value user stock
 ---- | -------- | --------- | ---------
   1 |  10 |  mark |  Aapl
   2 |  20 |  rob |  GOOG
   3 |  30 |  mark |  Aapl
   4 |  -40 |  mark |  Aapl
   5 |  -10 |  rob |  GOOG
   6 |  25 |  mark |  GOOG
   7 |  5 |  mark |  GOOG
   8 |  45 |  mark |  Aapl

I would like to build a query (possibly without using any PGSQL function) that returns the rows shown below. It should start in order (ID ASC), summing the column "value", grouped by the user, the stock. If the temporary amount is 0, all previous lines (for this group) will be discarded.

  id value user stock
 ---- | -------- | --------- | ---------
   2 |  20 |  rob |  GOOG
   5 |  -10 |  rob |  GOOG
   6 |  25 |  mark |  GOOG
   7 |  5 |  mark |  GOOG
   8 |  45 |  mark |  Aapl

I think that you should use the OVER (PARTITION BY) function and WINDOW SELECT *, SUM(value) OVER w AS scm FROM "mytable" WINDOW w AS (PARTITION BY user,stock ORDER BY id ASC)

this returns the following table

  ID value user stock scm
 ---- | -------- | --------- | --------- | -------
   1 |  10 |  mark |  AAPL |  ten
   2 |  20 |  rob |  GOOG |  20
   3 |  30 |  mark |  AAPL |  40
   4 |  -40 |  mark |  AAPL |  0
   5 |  -10 |  rob |  GOOG |  ten
   6 |  25 |  mark |  GOOG |  25
   7 |  5 |  mark |  GOOG |  thirty
   8 |  45 |  mark |  AAPL |  45

So this should be a good starting point, because it shows that the APPL is for mark 0 (id = 4), and for this group (AAPL, mark) I have to keep all the following lines. This rule: for each group (stock, user), save all lines following the last line with scm = 0

Answer 1

SQL Fiddle

 with s as ( select *, count(scm = 0 or null) over wz from ( select *, sum(value) over w as scm from mytable window w as (partition by "user", stock order by id asc) ) s window w as (partition by "user", stock order by id asc) ) select * from s inner join ( select max(z) z, "user", stock from s group by "user", stock ) z using (z, "user", stock) where scm > 0 order by s.user, s.stock, id 

Answer 2

Something like the following, I think you want what you want. Basically it will do the following:

• Use the SQL statement that you must calculate by the total.
• Calculate the minimum identifier that should be displayed for each group (username, stock) .
• Select from the original SQL sum and filter any identifiers below the minimum ID.

 WITH sums AS ( SELECT id, value, username, stock, SUM(value) OVER w AS scm FROM "mytable" WINDOW w AS (PARTITION BY user,stock ORDER BY id ASC)), minimum_ids AS ( SELECT username, stock, MAX(id) as minimum_id FROM sums WHERE scm <= 0 GROUP BY username, stock) SELECT sums.id, sums.value, sums.username, sums.stock, sums.scm FROM sums LEFT JOIN minimum_ids ON (sums.username = minimum_ids.username AND sums.stock = minimum_ids.stock) WHERE (minimum_ids.minimum_id IS NULL OR sums.id > minimum_ids.minimum_id) ORDER BY id;