如何在SQL中找到时间戳的缺口(对于数据刮刀)

2024-03-28 09:57:00 发布

您现在位置:Python中文网/ 问答频道 /正文

我对使用DBs/SQL非常陌生,我正在尽最大努力进行调整,我正在使用SQLAlchemy/Postgres从pythonapi编写数据,我每10分钟编写一次市场数据(插入DB)。这个刮刀是为了得到股票分钟“蜡烛”,所以它应该插入10行到每个股票每次。我昨晚开始运行它,注意到数字有点不一致,因为我在pgcli中做了一个SELECT count(*) FROM exchange WHERE market='x_market';,我得到了900个数字,应该在1000左右(1k分钟前开始运行)。本质上,我想做的(如果可能的话)是查看行之间是否有任何间隙(它没有捕获的数据)。每一行都有一个unix时间戳,“在它下面”的每个时间戳应该是60000毫秒(1分钟)不同的。我知道在Python中我可以迭代并检查它,但是我对学习更多关于SQL的知识很感兴趣(只检查pgcli会更好)。可以(用SQL)检查这个吗?我附上一张截图来说明我的意思。 非常感谢。你知道吗

(对于pic,timestamp(unix)是第一列,其余的只是股票价格数据) enter image description here


Tags: 数据pythonapidbsqlsqlalchemy市场时间unix
1条回答
网友
1楼 · 发布于 2024-03-28 09:57:00

获取间隔或“丢失”时间戳的一种方法是使用generate_series创建一系列以60000ms分隔的时间戳,然后连接到表中。像这样:

  Simulate some data with a gap
WITH exchange(tstamp, val) AS
(
  VALUES
    (1551118800000, 0.03626),
    (1551118860000, 0.036243),
    (1551118980000, 0.03627)
)

  Return timestamp(s) that do not match to exchange table data
SELECT stamps.tstamp AS missing_timestamp
FROM exchange
RIGHT JOIN generate_series(1551118800000, 1551118980000, 60000) stamps(tstamp)
  ON exchange.tstamp = stamps.tstamp
WHERE exchange.tstamp IS NULL

  Or using a more explicit anti-JOIN (cleaner?)
SELECT stamps.tstamp AS missing_timestamp
FROM generate_series(1551118800000, 1551118980000, 60000) stamps(tstamp)
WHERE NOT EXISTS (SELECT 1 FROM exchange WHERE tstamp = stamps.tstamp)

generate_series中的第一个参数是最小时间戳,第二个参数是最大时间戳。您可以根据您的用例进行更改。最后一个参数是“步长”(60000ms)。你知道吗

结果(给出上述模拟数据):

missing_timestamp
   
1551118920000

相关问题 更多 >