CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
Matt Dice
Matt Dice

Reputation: 357

How can I fetch the results from the first and last row of each partition?

I can't figure out how to write an efficient query that merges results with the same identifier and uses information from the first and last result.

I have the following table (Only the trades with a buy and sell action):

Position action symbol executed_at price profit lot_size
1111 buy XAUUSD 2022-07-05 20:05:49 1763.20 0 0
1111 sell XAUUSD 2022-07-05 20:08:49 1764.20 500 5
1111 sell XAUUSD 2022-07-05 20:10:49 1765.20 1000 5
2222 sell XAUUSD 2022-07-05 20:05:49 1400 0 0

This must result in the following table:

Position action symbol opened_at closed_at entry_price close_price profit lot_size
1111 buy XAUUSD 2022-07-05 20:05:49 2022-07-05 20:10:49 1763.20 1765.20 1500 10

The requirements:

I've created the following query and the only thing that is not working is the last requirement (only show results with buy and sell action).

SELECT DISTINCT ON (position)
    position,
    symbol,
  action,
  first_value(executed_at) OVER w as opened_at,
  last_value(executed_at) OVER w as closed_at,
    first_value(price) OVER w as entry_price,
    last_value(price) OVER w as close_price,
  sum(lot_size) OVER w as lot_size,
    sum(profit) OVER w as profit
FROM deals
WHERE action IN('buy', 'sell')
WINDOW w AS (PARTITION BY position ORDER BY executed_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Can someone steer me in the right direction? I don't have much experience with window functions and group functions. Also, if the query can be written more efficiently I would like to know.

Upvotes: 0

Views: 446

Answers (1)

Josh
Josh

Reputation: 1616

I prefer to use the EXISTS clause in this case, because personally I feel like the logic is easier to follow.

SELECT DISTINCT ON (position)
    position,
    symbol,
  action,
  first_value(executed_at) OVER w as opened_at,
  last_value(executed_at) OVER w as closed_at,
  first_value(price) OVER w as entry_price,
  last_value(price) OVER w as close_price,
  sum(lot_size) OVER w as lot_size,
  sum(profit) OVER w as profit
FROM deals
WHERE EXISTS (SELECT 1 FROM deals d2 WHERE action='buy' AND d2.position=deals.position)
AND EXISTS (SELECT 1 FROM deals d2 WHERE action='sell' AND d2.position=deals.position)
WINDOW w AS (PARTITION BY position ORDER BY executed_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

If you haven't used them before - just notice that what you select does not matter, it is the join clause that makes the magic happen.

fiddle

Upvotes: 1

Related Questions