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

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:

Group by position
Get the executed_at of the first row as opened_at
Get the executed_at of the last row as closed_at
Get the price of the first row as entry_price
Get the price of the last row as close_price
Sum of all the positions their profit
Sum of all the positions their lot_size
Only show results where a buy AND a sell action is present (or DISTINCT COUNT(action) = 2)

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.

CodePudding user response：

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