I have a table which contains stock data for various companies. The data goes back as far as 2003, and there are approx 40M rows for each timeframe.
CREATE TABLE stocks_data.bars ( timeframe varchar(3) NOT NULL, "timestamp" timestamp NOT NULL, "open" float8 NULL, high float8 NULL, low float8 NULL, "close" float8 NULL, volume int8 NULL, "security" varchar(12) NOT NULL, ext bool NOT NULL DEFAULT false, realtime bool NOT NULL DEFAULT false, CONSTRAINT bars_pkey PRIMARY KEY ("timestamp", security, timeframe) )
I want to perform some technical calculations on these rows, for each ticker symbol ("AAPL" for example). I am using plpython3u to wrap these technical calculation functions. Now say, I want to calculate a stochastic RSI on all 40M rows, unique for each ticker ("security" column).
What would be the ideal approach for handling and passing around this data in postgres? Current naive function:
-- function should take in a list of numbers, then calculate the stochRSI and return these values CREATE OR REPLACE FUNCTION test_01(input double precision) RETURNS setof double precision AS $$ import talib k, d = talib.STOCHRSI(input) return [k, d] $$ LANGUAGE plpython3u;
I have tried:
- Using the procedural function as a window function, which fails because it is not a window function:
select *, test_01(close) over (partition by "security") as tech from stocks_data.bars bars group by bars."security";
- Using a lateral join, but this errors because then the function is only passed a single value (stochastic RSI should be calculated on a list of values). There is also no way to separate the calculations by "security" here.
SELECT f.* FROM stocks_data.bars bars, test_01(bars.close) f WHERE bars.timeframe = '1d';
Are there any working & performant options here?