PostgreSQL计算滚动直方图与组

Question

我有股票交易数据在postgresql数据库与时间刻度。我用这样的查询

计算了一天的价格(体积配置文件)的体积直方图。

CREATE MATERIALIZED VIEW
volume_profile_daily
WITH (timescaledb.continuous)
AS
SELECT FLOOR(close/1.00)*1 as close, 
SUM(volume) AS volume,
time_bucket('1 day', date) as date,
asset
FROM trade_cgl
GROUP BY time_bucket('1 day', date), asset, FLOOR(close/1.00)*1

bucket range = 1

现在我正试图计算移动窗口的直方图(过去n个交易日)。

我可以像这样计算某一天的时间:

select close, SUM(volume) AS volume
from volume_profile_daily
where date in (select distinct(date) from volume_profile_daily
where date <= '2021-09-01'
order by date desc
limit 30)
and asset = 'ASSET_NAME'
GROUP BY close

子查询返回最近30个交易日。在此基础上，我创建了30天的新直方图。

编辑:我想计算30天期间直方图的每一天，我有在数据库中。查询指定日期只是举例。

是否可以预先计算并存储在物化视图/表中?我不想每次都计算它。我很难找出通解。

Answer 1

你不能使用TimescaleDB的连续聚合，因为它们不能在连续聚合上定义，但是你可以使用PostgreSQL的实体化视图

我相信下面的方法应该有效:

CREATE MATERIALIZED VIEW volume_30_traiding_days AS
select close, SUM(volume) AS volume
from volume_profile_daily
where date in 
(select distinct(date) from volume_profile_daily
where date <= '2021-09-01'
order by date desc
limit 30)
and asset = 'ASSET_NAME'
GROUP BY close
WITH DATA;

它将立即运行查询并将其具体化。如果有必要刷新它，例如，包含所需时间段的新数据，则可以使用refresh MATERIALIZED VIEW。请注意，它将重新计算整个物化，相当于删除并重新创建物化视图。

如果有必要做一些比问题更有趣的事情，例如，定期刷新它，我建议查看TimescaleDB的用户定义动作。

Answer 2

啊，直方图是因为我们在看收盘价这是桶。我想我有点明白你的意思了。您可以按照您建议的方式计算它，但它可能有点低效，特别是如果您想要更长时间的计算。对于这样的事情，窗口函数可能会更好。

-- create your original continuous aggregate
CREATE MATERIALIZED VIEW
volume_profile_daily
WITH (timescaledb.continuous)
AS
SELECT FLOOR(close/1.00)*1 as close, 
SUM(volume) AS volume,
time_bucket('1 day', date) as date,
asset
FROM trade_cgl
GROUP BY time_bucket('1 day', date), asset, FLOOR(close/1.00)*1;
--now do a sum over the previous 30 days using a window function
WITH windowed as (SELECT *, sum(volume) OVER prev_thirty
FROM volume_profile_daily 
WHERE asset = 'ASSET_NAME'
AND date >= now()-'90 days'::interval
WINDOW prev_thirty AS (PARTITION BY close, asset ORDER BY date DESC ROWS 30 PRECEDING)) 
SELECT * FROM windowed 
ORDER BY date desc limit 30;

请注意，我现在在查询的第一部分使用where子句- 90天，这是为了确保我想要的所有日子(最后30天左右，我在SELECT * FROM windowed ORDER BY date desc limit 30中得到)都有正确的数据量，所以我添加了一个蒙混因子，因为我很确定为了获得过去30个交易日的数据，我不需要过去90天的数据。然而，添加where子句可以使视图在底层更有效地工作，因为它不需要返回太远的时间——这在重新聚合步骤中节省了大量时间。否则，它将做很多工作来获取一些值，这可能就是为什么查询变慢的原因。因此，虽然你不能在连续aggs中直接使用窗口函数，但它们应该给你足够的缓存来使计算相当高效，你只需要给它们一个查询结束，这样它们就可以限制它们必须计算的内容。

希望这能回答你的一些问题，正如我们在评论中澄清的那样，并能帮助你到达你需要去的地方，但请随时要求更多的澄清!