我有下表。
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
code | text | | not null |
price_at | timestamp without time zone | | not null |
price | double precision | | not null |
我需要使用此表中的数据创建一个包含 6 小时烛台的烛台图。每个烛台都由字段表示,{ x, open, close, low, high }
其中 x 是周期开始的 UNIX 时间戳。
以下查询有效,但使用distinct
会导致查询花费更长的时间。通常,当人们不想使用 unique 时,他们会使用它group by
,但我不能将其与窗口函数一起使用,而且我不确定它是否会有帮助。有没有一种方法可以消除此查询中使用的不同,以使其更快并仍然返回相同的结果?
with price_quotes as (
select
extract (epoch from price_at) - (extract (epoch from price_at) % extract (epoch from '6 hours'::interval)) as period_begin,
extract (epoch from price_at) as quote_time,
price
from quote)
select distinct
period_begin as x,
first_value (price) over (partition by period_begin order by quote_time asc) as open,
last_value (price) over (partition by period_begin order by quote_time asc rows between current row and unbounded following) as close,
min (price) over (partition by period_begin) as low,
max (price) over (partition by period_begin) as high
from price_quotes
order by x asc