[Question] Use of guage_agg for calculating price change and volume change in financial data

[rnbokade]

I am working on indian stock market data and trying to create different visualizations and analysis views out of it. I am getting following tick by tick data. (Timestamp,symbol,price,volume)

I intend to create 1min ohlcv data as First(price) as open Max(price) as high Min(price) as low Last(price) as close

Now to get the volume in of this particular candle can I treat volume as counter or Guage and get the delta. Also I want to show Price Change as well. Now thing is some people are suggesting I better do a Last-First for both of these. However I have observed in candles that previous close doesn't necessarily equals current open. Especially for day end and day start candles. Please help me understand if I could use delta in some way by taking delta of closes And delta of volume

[rtwalker]

Hi @rnbokade,

We don't currently have a delta function that is designed for financial data (though it might be something we should consider adding). However, if I understand you correctly, using a window function might be sufficient for your needs.

Would something similar to the following query work for you?

WITH candlestick AS (
    SELECT
        time_bucket('1 day', "time") AS ts,
        toolkit_experimental.candlestick_agg("time", price, day_volume) AS cs
    FROM stocks_real_time
    WHERE symbol = 'AAPL'
    GROUP BY ts
)
SELECT
    ts,
    toolkit_experimental.open(cs),
    toolkit_experimental.high(cs),
    toolkit_experimental.low(cs),
    toolkit_experimental.close(cs),
    toolkit_experimental.close(cs) - LAG(toolkit_experimental.close(cs)) OVER (ORDER BY ts) AS close_delta
FROM candlestick
ORDER BY ts;

           ts           │  open  │   high   │     low      │    close     │     close_delta      
────────────────────────┼────────┼──────────┼──────────────┼──────────────┼──────────────────────
 2022-10-09 20:00:00-04 │ 140.22 │ 147.3107 │ 138.70500183 │       140.74 │               <null>
 2022-10-10 20:00:00-04 │ 139.11 │   141.35 │ 138.48060608 │ 140.15499878 │  -0.5850012200000094
 2022-10-11 20:00:00-04 │ 139.32 │ 145.9132 │     133.8779 │       138.61 │   -1.544998779999986
 2022-10-12 20:00:00-04 │ 138.52 │  149.933 │          134 │       142.83 │    4.219999999999999
 2022-10-13 20:00:00-04 │ 143.07 │   144.66 │          138 │       138.08 │                -4.75
 2022-10-17 20:00:00-04 │ 144.75 │ 146.7312 │       140.67 │       144.99 │    6.909999999999997
 2022-10-18 20:00:00-04 │ 144.03 │ 144.9389 │       141.47 │      143.035 │  -1.9550000000000125
 2022-10-19 20:00:00-04 │ 142.18 │  149.385 │       142.05 │       142.69 │ -0.34499999999999886
 2022-10-20 20:00:00-04 │ 142.66 │   147.83 │       141.55 │       147.58 │    4.890000000000015
 2022-10-23 20:00:00-04 │ 147.09 │   173.53 │      143.065 │       149.53 │   1.9499999999999886
 2022-10-24 20:00:00-04 │ 149.62 │ 159.2907 │       143.48 │        150.8 │   1.2700000000000102
 2022-10-25 20:00:00-04 │  151.5 │  152.225 │     145.9132 │        149.8 │                   -1
 2022-10-26 20:00:00-04 │ 148.99 │  151.065 │       136.44 │        145.4 │   -4.400000000000006
 2022-10-27 20:00:00-04 │ 144.79 │   157.48 │        144.5 │       155.84 │   10.439999999999998
 2022-10-30 20:00:00-04 │    155 │ 159.3494 │       151.95 │       153.47 │  -2.3700000000000045
 2022-10-31 20:00:00-04 │    154 │   155.65 │       149.14 │       150.41 │  -3.0600000000000023
 2022-11-01 20:00:00-04 │  150.5 │  155.975 │        144.1 │       144.12 │   -6.289999999999992
 2022-11-02 20:00:00-04 │ 144.78 │  149.915 │        137.5 │      137.725 │    -6.39500000000001
 2022-11-03 20:00:00-04 │ 138.22 │   142.66 │     134.3897 │       138.12 │  0.39500000000001023
 2022-11-06 19:00:00-05 │ 137.97 │ 145.4954 │       135.45 │      139.025 │   0.9050000000000011
 2022-11-07 19:00:00-05 │    139 │   141.43 │      137.185 │       139.83 │   0.8050000000000068
 2022-11-08 19:00:00-05 │ 139.87 │      140 │     133.8779 │       135.05 │   -4.780000000000001
 2022-11-09 19:00:00-05 │ 135.06 │   136.23 │       134.91 │       136.05 │                    1
(23 rows)

[rnbokade]

Can I use this in Materialized view? As fas as I know, I guess materialized view/continous aggregate don't support window functions right?

[WireBaron]

@rnbokade You are correct in that continuous aggregates will not work with window functions.

In this case however, the heavy lifting is being done by the candlestick aggregate while the delta and other accessors are cheap to compute on demand. So what we can do is create a continuous aggregate containing the candlestick aggregates and then build a normal view to get the values we want from that.

CREATE MATERIALIZED VIEW candlesticks WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', "time") AS ts,
    toolkit_experimental.candlestick_agg("time", price, day_volume) AS cs
FROM stocks_real_time
WHERE symbol = 'AAPL'
GROUP BY ts;

CREATE VIEW aapl_daily_report AS
SELECT
    ts,
    toolkit_experimental.open(cs),
    toolkit_experimental.high(cs),
    toolkit_experimental.low(cs),
    toolkit_experimental.close(cs),
    toolkit_experimental.close(cs) - LAG(toolkit_experimental.close(cs)) OVER (ORDER BY ts) AS close_delta
FROM candlesticks
ORDER BY ts;