This is a continuation of a question I asked on the best way to compute statistics on a list of rows unique per column, which can be found here (along with the table schema)
I have a table, which holds millions of rows of stock data and I want to compute custom aggregates on these rows. The idea is to append each input value in the state transition function. Then the finalfunc will compute the number on this array. The aggregate is defined as:
create or replace aggregate RSI(input float8) ( SFUNC=tech_float8_accum, STYPE=float8, FINALFUNC=RSI_Func );
Where I have naively implemented the array aggregation function in plpython:
-- Append the next input value to the state array CREATE OR REPLACE function tech_float8_accum(agg float8, input float8) returns float8 AS $$ return agg + [input] if agg != None else [input] $$ LANGUAGE plpython3u;
The finalfunc is also written in plpython, and from experience this quite fast at least outside of a database context given it uses Cython under the hood.
CREATE OR REPLACE FUNCTION RSI_Func(input float8, out val float8) AS $$ import talib import numpy as np cd=np.array(input) rsi = talib.RSI(cd) return rsi[-1] $$ LANGUAGE plpython3u;
select "security", RSI(ordered.close) from ( select "security", close from stocks_data.bars where "timeframe" = '1d' and "timestamp" >= '2022-11-02'::timestamp order by "timestamp" asc ) as ordered group by ordered.security;
This takes approx 3 minutes, when in reality I need something around 3 seconds or less like the built in AVG function offers.
Is there something I can do to drastically improve this approach, or should I take another approach altogether? It is too much data to bring in-memory.