能否访问Timescale双时态表审计数据及扩展双时态查询？

问题解答 #

1. 能否从TimescaleDB双时态表中访问审计数据？

当然可以！TimescaleDB的双时态表设计本身就支持同时跟踪数据的有效时间（比如你的time列）和记录/事务时间（比如time_recorded列），而像recorded_by这类审计元数据字段，完全可以被正常查询、过滤和聚合——这正是双时态模型的优势之一，你可以轻松追溯数据的修改历史、记录来源等审计信息。

2. 修改查询以返回time_recorded和recorded_by列

针对你的需求，我们可以用两种简洁的方式实现，都能精准返回每个天桶+资产组内time_recorded最新的那条记录的所有目标字段：

方法一：使用DISTINCT ON（PostgreSQL原生语法，TimescaleDB完全支持）

这种方式直观且高效，适合需要获取整行关联数据的场景：

SELECT
  time_bucket('1 day', time) AS day,
  asset_code,
  price,
  time_recorded,
  recorded_by
FROM prices
WHERE time > '2017-01-01'
ORDER BY day, asset_code, time_recorded DESC
DISTINCT ON (day, asset_code);

逻辑说明：

• DISTINCT ON (day, asset_code)会为每个(day, asset_code)组合保留排序后的第一行数据
• 我们通过time_recorded DESC确保每个组内保留的是最新记录的那一行，正好匹配你的期望输出。

方法二：结合TimescaleDB的last()聚合函数

如果你更习惯用聚合的方式实现，可以通过多次调用last()函数分别获取每个字段的最新值：

SELECT
  time_bucket('1 day', time) AS day,
  asset_code,
  last(price, time_recorded) AS price,
  last(time_recorded, time_recorded) AS time_recorded,
  last(recorded_by, time_recorded) AS recorded_by
FROM prices
WHERE time > '2017-01-01'
GROUP BY day, asset_code
ORDER BY day DESC, asset_code;

逻辑说明：

• last(column, sort_column)函数会返回每个分组内按sort_column排序后的最后一个column值
• 这里我们用time_recorded作为排序依据，分别获取price、time_recorded和recorded_by的最新值，最终得到你需要的结果。

针对你给出的输入表，两种方法都会输出符合预期的结果：

内容的提问来源于stack exchange，提问作者Jon Freedman