CODEWITHSUNDEEP
sqlpostgresql
Pooya Saberian
Pooya Saberian

Reputation: 1061

Select minimal rows until sum of a column greater than a value

I have a table (orders) in Postgresql, schema and data is like this:

+----+---------+------+-------+------+--------+
| id | symbol  | qty  | price | side | status |
+----+---------+------+-------+------+--------+
| 1  | BTCUSDT | 0.02 | 6500  | SELL | NEW    | 
+----+---------+------+-------+------+--------+
| 2  | BTCUSDT | 1.00 | 6550  | SELL | NEW    |
+----+---------+------+-------+------+--------+
| 3  | BTCUSDT | 0.03 | 6600  | SELL | NEW    |
+----+---------+------+-------+------+--------+

I want select rows where side is SELL and status is NEW, order by price ascending until sum(qty) is greater than 0.5, so the result should select id 1 and 2 for me

when I run this query:

SELECT * FROM (
       SELECT *, SUM(qty) OVER (ORDER BY price ASC) as total_qty FROM orders 
       WHERE symbol = 'BTCUSDT' AND side = 'SELL' AND status = 'NEW'
    ) AS o WHERE o.total_qty <= 0.5

it just return the first row (id: 1), I need a query return 1 and 2

Upvotes: 1

Views: 2797

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You are very much on the right track. The issue is that the cumulative sum includes the current row. So, your where clause filters it out when it passes 0.5. The simple solution is to subtract out the value from the current row:

SELECT *
FROM (SELECT o.*, SUM(qty) OVER (ORDER BY price ASC) as total_qty
      FROM orders o
      WHERE symbol = 'BTCUSDT' AND side = 'SELL' AND status = 'NEW'
     ) o
WHERE o.total_qty - o.qty <= 0.5;

You can also use a window frame as Impaler does, but that introduces the problem of NULL values on the first row. You can also subtract out the amount in the subquery as forpas does. In all cases, the idea is the same: you need the accumulated quantity before each row.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48865

You'll need to keep two SUMs. One for the current row and another for the previous row. Then, filtering is easy. For example:

select *
from (
  SELECT *, 
    SUM(qty) OVER (partition by side, status ORDER BY price ASC) as total_qty,
    SUM(qty) OVER (partition by side, status ORDER BY price ASC
      rows between unbounded preceding and 1 preceding) as prev_total_qty
  FROM orders 
  WHERE symbol = 'BTCUSDT' AND side = 'SELL' AND status = 'NEW'
) AS o 
WHERE coalesce(prev_total_qty, 0) <= 0.5

See running example at DB Fiddle.

Upvotes: 0

forpas
forpas

Reputation: 164204

You want all the rows until the row where the sum is equal or exceeds 0.5.
This means that if you subtract qty from the sum of the last row that you want returned the result must be less than qty.
Subtract qty from the sum and remove the equal sign from the WHERE clause:

SELECT o.id, o.symbol, o.qty, o.price, o.side, o.status 
FROM (
  SELECT *, SUM(qty) OVER (ORDER BY price ASC) - qty as total_qty 
  FROM orders 
  WHERE symbol = 'BTCUSDT' AND side = 'SELL' AND status = 'NEW'
) AS o 
WHERE o.total_qty < 0.5

See the demo.
Results:

| id  | symbol  | qty  | price | side | status |
| --- | ------- | ---- | ----- | ---- | ------ |
| 1   | BTCUSDT | 0.02 | 6500  | SELL | NEW    |
| 2   | BTCUSDT | 1.00 | 6550  | SELL | NEW    |

Upvotes: 4

Related Questions