Início / dba / Perguntas / 323017
Accepted
Thomas
Asked: 2023-02-04 00:20:21 +0800 CST

obtendo linhas e adicionando uma coluna a cada uma com o timestamp da próxima linha, com postgres

  • 772

Tenho o seguinte objeto:

CREATE TABLE IF NOT EXISTS {schemaName}.{tableName}
(
    id                   VARCHAR      NOT NULL,
    ticker               VARCHAR(16)  NOT NULL,
    interval             VARCHAR(3)   NOT NULL,
    ts                   TIMESTAMP    NOT NULL,
    ...
    UNIQUE (ticker, interval, ts)
);

Eu gostaria de fazer uma consulta que retornaria linhas como esta:

SELECT * FROM analysis
WHERE ticker = 'BTCUSDT' AND ts BETWEEN '2020-1-1' AND '2020-1-30'
ORDER BY ts;

mas, para cada linha, adiciona uma coluna que contém o timestamp da próxima linha (classificada por ts).

Como tenho apenas conhecimento básico de SQL, usei o ChatGPT para gerar uma solução, mas é incrivelmente lento (em minutos x milissegundos)

SELECT t1.ts as ts_start, t2.ts as ts_end, t1.ticker, t1.interval, ...
FROM analysis t1
JOIN analysis t2
ON t1.ticker = t2.ticker AND t1.interval = t2.interval AND t1.ts < t2.ts
WHERE t1.ticker = 'BTCUSDT' AND t1.ts BETWEEN '2020-1-1' AND '2020-1-30'
ORDER BY t1.ts;

Qual seria a melhor maneira de fazer isso acontecer?

postgresql

1 respostas

  1. Best Answer

    Use uma função de janela:

    SELECT *,
       lead(ts) over (partition by ticker order by ts) as next_ts 
FROM analysis
WHERE ticker = 'BTCUSDT' 
  AND ts BETWEEN '2020-1-1' AND '2020-1-30'
ORDER BY ts;

    Observe que usar BETWEENcom timestampvalores geralmente é um erro lógico. No seu caso, as linhas com um valor de 2020-01-30 00:00:01não seriam selecionadas porque '2020-1-30'são convertidas '2020-1-30 00:00:00'e o limite superior é comparado, <=de modo que os valores após a meia-noite desse dia sejam excluídos.

    Com valores de carimbo de data/hora é sempre melhor usar um intervalo com >= e < onde o limite superior é o dia seguinte

    AND ts >= '2020-01-01' 
AND ts < '2020-01-31'

    Na verdade, se você pretendia obter todas as linhas de janeiro, também esqueceria o último dia, então talvez seja isso que você realmente queria:

    AND ts >= '2020-01-01' 
AND ts < '2020-02-01'
    • 1

relate perguntas