0

I have the following query:

SELECT
    date_trunc('minute', ts) ts,
    instrument,
    MAX(price) high,
    MIN(price) low,
    (SUM(price * price * quantity) / SUM(price * quantity)) midpoint,
    SUM(price * quantity) volume,
    (SUM(direction * price * quantity) / SUM(price * quantity)) direction,
    EXTRACT(SECOND FROM MIN(ts)) < 15 AND EXTRACT(SECOND FROM MAX(ts)) > 45 AND count(*) > 20 complete
FROM {tableName exchangeName}
WHERE instrument = '{instrument.Ticker}' AND ts BETWEEN '{fromTime}' AND '{toTime}'
GROUP BY date_trunc('minute', ts), instrument
ORDER BY ts
LIMIT 4500

It takes a list of financial trades and aggregates them into candles for a given time range.

I'm trying to add a few fields:

  • Open, which is be the first price (sorted by ts)
  • Close, which is the last price (sorted by ts)
  • Count, which is the amount of trades / rows in the time range
  • and update Direction which is a bit more complex:

the trades are constructed like this:

    instrument     VARCHAR                     NOT NULL,
    ts             TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    quantity       FLOAT8                      NOT NULL,
    price          FLOAT8                      NOT NULL,
    direction      INTEGER                     NOT NULL

and the direction field can have two values: +1 or -1 (it's essentially a bool) and, for a group of trades / candle, the calculations are like this:

candle.volume = trades -> add (trade.quantity * trade.price)
candle.direction = trades -> keep only when direction is +1 -> add (trade.quantity * trade.price) / candle.volume

The first three are very simple, but I'm not sure what to google for the syntax to use. The last one, I'm not sure how to write that in SQL.

Any help would be greatly appreciated.

edit:

based on the link provided by LukStorms, I came up with something ugly for open and close. Maybe someone can propose something nicer:

(array_agg(price order by ts))[1] open,
(array_agg(price order by ts))[array_upper((array_agg(price order by ts)), 1)] close,
Thomas
  • 10,933
  • 14
  • 65
  • 136
  • Maybe for open & close something like the FIRST_VALUE function. Example [here](https://stackoverflow.com/a/54896159/4003419). – LukStorms Jan 06 '22 at 23:08
  • That didn't seem very straightforward since I have to use OVER with another expression. But based on that answer, I came up with something super ugly that could work. Hopefully something can propose something cleaner. I'll edit the question to incorporate it. – Thomas Jan 06 '22 at 23:41
  • Are your Open/Close values at the '{fromTime}' AND '{toTime}' range level, or at the date_trunc('minute', ts) level, or the ts level? From your description, it seems that Count is aggregated at the time range level. – Amw 5G Jan 06 '22 at 23:53
  • at the date_trunc('minute', ts) level since, in a time range, I need to get one candle per minute with an open / close – Thomas Jan 06 '22 at 23:54
  • The definition for direction needs to be elaborated. – shawnt00 Jan 07 '22 at 00:22
  • @shawnt00: candle.direction is (SUM (price * quantity) for all rows where row.direction=1) / SUM (price * quantity) – Thomas Jan 07 '22 at 00:45
  • Sorry, I overlooked that direction was a trade attribute too. – shawnt00 Jan 07 '22 at 00:49
  • `sum(price * quantity) filter (where direction = 1) / sum(price * quantity)` – shawnt00 Jan 07 '22 at 00:51
  • @shawnt00, great, thanks! I was trying to make this work with a MATCH statement. Is there a better way to get the last item of a list with Postgres than what I put in the question's edit? – Thomas Jan 07 '22 at 01:04
  • 1
    `first_value(price) over (partition by date_trunc('minute', ts), instrument)` You'll need a subquery /cte to compute this prior to aggregating. – shawnt00 Jan 07 '22 at 01:24

0 Answers0