Sascha Mayr's questions

Meu objetivo é detectar tendências de preços de criptomoedas. Para isso, estou obtendo dados de preços OHLC para múltiplas moedas versus vários símbolos de cotação (por exemplo, velas para Bitcoin/USD, Bitcoin/EUR, Litecoin/USD etc.). Para realizar o cálculo de tendências utilizo o indicador Supertrend . Eu tenho as 2 tabelas a seguir:

-- This is where price candles AKA OHLC data gets stored. Interval can be '1h', '4h', '1d', etc.
CREATE TABLE ohlc (
    id integer DEFAULT nextval('ohlc_id_seq'::regclass) PRIMARY KEY,
    open numeric(65,30) NOT NULL,
    high numeric(65,30) NOT NULL,
    low numeric(65,30) NOT NULL,
    close numeric(65,30) NOT NULL,
    coinid character varying(255) NOT NULL REFERENCES coin(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    closetime timestamp(3) without time zone NOT NULL,
    quotesymbol text NOT NULL,
    interval text NOT NULL
);

-- This is a caching table that gets automatically populated by a stored procedure that runs AFTER every ohlc insert. "trend" can be 'UP' or 'DOWN'.
CREATE TABLE supertrend (
    id integer DEFAULT nextval('supertrend_id_seq'::regclass) PRIMARY KEY,
    coinid character varying(255) NOT NULL REFERENCES coin(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    quotesymbol text NOT NULL,
    date timestamp(3) without time zone NOT NULL,
    trend text NOT NULL,
    interval text NOT NULL
);

Dados de amostra para a tabela de supertendências:

Digamos que eu queira consultar a ORDER BY date DESCtendência mais recente () para cada coinid/quotesymbol com intervalo = '1d' (1 dia). Ou, em outras palavras WHERE interval = '1d' GROUP BY coinid, quotesymbol, quero também saber a tendência da tendência, por quantos períodos essa tendência já é a mesma.

Dados os dados da tabela de supertendências do exemplo acima, meu resultado deve ser semelhante a:

Para esclarecer o resultado acima: 'Bitcoin', 'usd' está 'UP' por 3 períodos, porque a tendência para Bitcoin/USD foi de alta em 18 de abril, 17 de abril e 16 de abril. E 'DOWN' em 15 de abril, veja o exemplo tabela de supertendências.

Além disso, gostaria de saber a supertendência agregada sobre todos os símbolos de cotação (via função de modo). Esse resultado deve ficar assim:

Para esclarecer o resultado acima: A tendência mais recente do Bitcoin/USD é 'UP, Bitcoin/EUR é 'EUR' é 'UP' e Bitcoin/CNY é 'DOWN'. A função mode pega ('UP', 'UP', 'DOWN') e retorna o valor que ocorre mais frequentemente ('UP').

Como posso consultar esses dados de maneira eficiente, considerando que a tabela de supertendências tem milhões ou bilhões de linhas?

Eu estava pensando em uma visão materializada, mas os intervalos mais baixos possíveis para essas supertendências são de 1 minuto e podem ser escritos em momentos distintos, de modo que pode ser uma taxa de atualização necessária muito alta.

Estou tentando evitar o uso de uma solução de cache separada como o Redis para manter simples a quantidade de arquitetura/ambientes necessários, mas acho que isso poderia ser uma opção especialmente para contadores.