如何在滚动时间内为PostgreSQL数据库中存储的市场数据计算价格变化标准

我有一个PostgreSQL 10数据库,其中有一个名为Period的表,该表存储了几种加密货币的历史价格数据。我正在尝试查询此数据,以查找价格在滚动时间内上涨了一定百分比的地方,即一天之内上涨了5%。相关的表列为“ product_id”,“ unix纪元以来的秒数”(“ end_time”)和“关闭”该期间的收盘价。

给出以下简化数据:

id     product_id     start_time     end_time     close
0       BTC-USD         100000        100060       100
1       BTC-USD         100060        100120        99
2       BTC-USD         100120        100180       101
3       BTC-USD         100180        100240       105
4       BTC-USD         100240        100300       104
5       BTC-USD         100300        100360       102

我希望能够使用额外的计算列来查询这些时间段,以指示何时发生局部最小值/最大值以及何时最小值和最大值之间的差异大于阈值。例如:

id     product_id     start_time     end_time     close     buy_signal     sell_signal
0       BTC-USD         100000        100060       100
1       BTC-USD         100060        100120        99         true
2       BTC-USD         100120        100180       101
3       BTC-USD         100180        100240       105                         true
4       BTC-USD         100240        100300       104
5       BTC-USD         100300        100360       102

该查询的复杂性远远超出了我的SQL经验,我花了数周的时间在空闲时间浏览堆栈溢出答案和SQL功能,但据我所知,这个丑陋的查询已达到:

SELECT
    end_time,
    close,
    MAX(close) OVER (ORDER BY end_time ROWS BETWEEN CURRENT ROW AND 1439 FOLLOWING) AS max_close,
    MIN(close) OVER (ORDER BY end_time ROWS BETWEEN CURRENT ROW AND 1439 FOLLOWING) AS min_close,
    ((MAX(close) OVER (ORDER BY end_time ROWS BETWEEN CURRENT ROW AND 1439 FOLLOWING) - 
      MIN(close) OVER (ORDER BY end_time ROWS BETWEEN CURRENT ROW AND 1439 FOLLOWING)) / 
      MIN(close) OVER (ORDER BY end_time ROWS BETWEEN CURRENT ROW AND 1439 FOLLOWING)) AS change_percent,
    CASE WHEN
        ((MAX(close) OVER (ORDER BY end_time ROWS BETWEEN CURRENT ROW AND 1439 FOLLOWING) - 
          MIN(close) OVER (ORDER BY end_time ROWS BETWEEN CURRENT ROW AND 1439 FOLLOWING)) / 
          MIN(close) OVER (ORDER BY end_time ROWS BETWEEN CURRENT ROW AND 1439 FOLLOWING)) > 0.05
        THEN true
    END AS buy_signal
FROM Period
WHERE product_id='BTC-USD'

显而易见的问题是,这不能区分最小和最大的顺序;它选择窗口中的最小值,而不考虑基于end_time最小值在时间上早于最大值。我一直在努力将时间过滤器与窗口结合起来,以找到价格最高的价格之前的最低价格,然后将所有内容组合在一起。另一个(可以接受的)限制是,一次只能用于一个product_id,但理想情况下,我希望查询结果包括end_time排序的多个不同product_id,且信号是product_id特定的。

我的目标是将分页查询结果输入到Java应用程序(我正在使用Hibernate)中,该应用程序使用买卖信号来训练模型。我可以使用Java应用程序对数据进行预处理,但要执行数百万行的数据,要执行和添加比查询选项更多的复杂性将花费数小时。很长的问题很抱歉,但我不想遗漏任何内容。任何帮助将不胜感激。