Início / dba / Perguntas / 326406
Accepted
Leon
Asked: 2023-04-25 23:20:15 +0800 CST

Componha uma consulta complexa no Postgresql

  • 772

Eu tenho uma tabela enorme como a seguir:

CREATE TABLE public.huge_table (
    sampl_day date NOT NULL,
    tick_time timestamp(6) with time zone NOT NULL,
    crit_feat integer NOT NULL,
    ---
    --- other fields
    ---
    CONSTRAINT pkey_huge_table PRIMARY KEY (tick_time)
);
CREATE INDEX idx_huge_table_day_time
ON public.huge_table USING brin (sampl_day, tick_time);

Esta tabela armazena muitas amostras (linhas) de uma série temporal, uma linha para uma amostra. Na maioria das vezes, o campo "crit_feat" tem um valor muito baixo, por exemplo 1 ou 2. Em algum momento anormal, ele iria muito alto, por exemplo 100.

É muito fácil descobrir essas linhas especiais em uma única consulta. Vamos chamá-los de "pontos de tempo especiais".

E agora preciso visualizar as linhas que aconteceram em 5s antes/depois de cada um dos pontos de tempo especiais acima em um conjunto de resultados de consulta, para descobrir o que aconteceu antes/depois de um evento especial.

Se eu precisar apenas descobrir um único ponto de tempo especial e linhas em 5s antes / depois dele, é relativamente mais fácil.

eu posso fazer assim:

WITH spec_time as (
    SELECT tick_time tp
    FROM huge_table
    ORDER BY crit_feat DESC LIMIT 1)

SELECT *
FROM huge_table
WHERE tick_time BETWEEN (SELECT tp FROM spec_time) - INTERVAL '5s'
                    AND (SELECT tp FROM spec_time) + INTERVAL '5s'
ORDER BY tick_time;

Mas preciso fazer isso com todos os pontos especiais e UNIONtodos eles!

O número de todos os pontos especiais é de cerca de 20k no mínimo, e os eventos que devem ser selecionados NÃO são APENAS cujo campo "crit_feat" tem o maior valor, MAS TAMBÉM incluem aqueles com um valor maior que um limite especificado, por exemplo: o valor mediano do campo "crit_feat" em todo o conjunto de dados.

Acho que posso obtê-lo com tabela temporária e cursor em um procedimento/função de armazenamento, mas estou esperando um método mais simples!

Desculpe pelo meu inglês feio, espero ter expressado corretamente as coisas que quero dizer.

Obrigado!

postgresql

1 respostas

  1. Best Answer

    Tenho certeza de que, se entendi corretamente, você pode atingir seu objetivo com apenas uma junção automática da seguinte forma:

    SELECT Results.* -- Don't actually use *, it's an anti-pattern. Instead, list out only the fields you need.
FROM huge_table AS Threshold
INNER JOIN huge_table AS Results
    ON Threshold.tick_time 
        BETWEEN 
            Results.tick_time - INTERVAL '5s'
            AND Results.tick_time + INTERVAL '5s'
WHERE Threshold.crit_feat >= 100 -- Input your threshold value here

    Você provavelmente desejará um índice minimamente em (tick_time, crit_feat).

    Além disso, o acima pode retornar a mesma linha da Resultsinstância da tabela várias vezes, se corresponder a várias linhas da Thresholdinstância da tabela. Se for assim e você quiser a lista exclusiva de linhas, basta adicionar a DISTINCTpalavra-chave após a SELECTpalavra-chave.

    • 1

relate perguntas