主页 / dba / 问题 / 323017
Accepted
Thomas
Asked: 2023-02-04 00:20:21 +0800 CST

使用 postgres 获取行并使用下一行的时间戳为每一行添加一列

  • 772

我有以下对象：

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)
);

我想做一个将返回这样的行的查询：

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

但是，对于每一行，添加一个列，其中包含下一行的时间戳（按 ts 排序）。

由于我只有 SQL 的基本知识，所以我使用 ChatGPT 生成了一个解决方案，但它非常慢（以分钟计与毫秒计）

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;

实现这一目标的更好方法是什么？

postgresql

1 个回答

  1. Best Answer

    使用窗口函数：

    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;

    BETWEEN请注意，与值一起使用timestamp通常是一个逻辑错误。2020-01-30 00:00:01在您的情况下，不会选择值为的行，因为'2020-1-30'已转换为'2020-1-30 00:00:00'并将上限与 进行比较<=，以便排除当天午夜后的值。

    对于时间戳值，最好使用带有 >= 和 < 的范围，其中上限是第二天

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

    事实上，如果您打算获取一月份的所有行，您也会忘记最后一天，所以也许这就是您真正想要的：

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

相关问题