使用PostgreSQL array_agg查询TimeScaleDB物化视图返回空值问题

背景 #

基于PostgreSQL的TimeScaleDB扩展搭建时序数据库，用于管理tick数据的多分辨率OHLCV值，聚合、持续聚合及物化视图功能均正常。为优化性能，尝试直接从物化视图提取数据映射至POJO以避免循环映射损耗，但执行带array_agg的原生查询后返回空值，在PgAdmin中直接执行该查询也得到空值。

相关定义 #

响应POJO #

@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
public class TVHistoryDataResponse {
    private String s;
    private String errmsg;
    private List<Long> t;
    private List<BigDecimal> o;
    private List<BigDecimal> h;
    private List<BigDecimal> l;
    private List<BigDecimal> c;
    private List<BigDecimal> v;

    public TVHistoryDataResponse(){
        this.c = new ArrayList<>();
        this.o = new ArrayList<>();
        this.h = new ArrayList<>();
        this.l = new ArrayList<>();
        this.v = new ArrayList<>();
        this.t = new ArrayList<>();
    }
}

预期输出 #

{
"s":"ok",
"t":[1666447200,1666461600,1666476000,1666490400],
"o":[19214.2,19223.8,19175.0,19195.1],
"h":[19231.3,19236.8,19217.1,19211.1],
"l":[19200.0,19122.0,19164.1,19155.0],
"c":[19223.7,19175.1,19195.2,19188.9],
"v":[5921.40400000014,38568.243999996805,19393.156000002222,18732.651000003843]
}

物化视图one_minute结构 #

day |               market |    high |  open | close | low |  volume
 
"2022-10-22 13:35:00+05:30" "BTCUSDT"   19166.5 19166.4 19165   19165   41.954999999999984
"2022-10-22 13:36:00+05:30" "BTCUSDT"   19165.1 19165.1 19162.7 19162.7 13.671999999999999
"2022-10-22 14:18:00+05:30" "BTCUSDT"   19149.6 19149.6 19149.6 19149.5 5.623999999999998
"2022-10-22 14:19:00+05:30" "BTCUSDT"   19149.6 19149.6 19149.5 19149.5 26.797000000000004

尝试的代码 #

查询语句 #

@Query(nativeQuery = true,
           value = "SELECT  \n" +
                   "array_agg(extract(epoch from day))as t,\n" +
                   "array_agg(open)as o,\n" +
                   "array_agg(high)as h,\n" +
                   "array_agg(low)as l,\n" +
                   "array_agg(close)as c,\n" +
                   "array_agg(volume)as v FROM one_minute WHERE market=:market AND day>=:start AND day <=:end group BY day ORDER BY day ASC limit 1000;")
    Optional<TVHistoryDataResponse> getAggregatedResponse(
            @Param(value = "market")String market,
            @Param(value = "start")ZonedDateTime start,
            @Param(value = "end")ZonedDateTime end);

调用方法 #

public TVHistoryDataResponse fetchHistoryData(Market market, long from, long to, String r) {

    TVHistoryDataResponse tvHistoryDataResponse = new TVHistoryDataResponse();
    DateTimeFormatter responseFormatter=DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.S");

    ZonedDateTime startTime=ZonedDateTime.ofInstant(Instant.ofEpochSecond(from), ZoneOffset.systemDefault());
    ZonedDateTime endTime=ZonedDateTime.ofInstant(Instant.ofEpochSecond(to), ZoneOffset.systemDefault());

    Optional<TVHistoryDataResponse> testResponse=tradingDataRepository.getAggregatedResponse(market.name(), startTime, endTime);
    // 后续处理省略
}

解决方案 #

1. 修正查询语句的核心错误 #

原查询中GROUP BY day会将每一行数据单独分组，导致每个array_agg仅包含单个元素，且返回多行结果，无法映射到单个TVHistoryDataResponse对象。同时extract(epoch from day)返回double类型，需转换为bigint以匹配Java的Long类型。

修正后的查询：

@Query(nativeQuery = true,
       value = "SELECT  \n" +
               "array_agg(extract(epoch from day)::bigint) as t,\n" +
               "array_agg(open) as o,\n" +
               "array_agg(high) as h,\n" +
               "array_agg(low) as l,\n" +
               "array_agg(close) as c,\n" +
               "array_agg(volume) as v \n" +
               "FROM one_minute \n" +
               "WHERE market=:market AND day>=:start AND day <=:end \n" +
               "ORDER BY day ASC;")
Optional<TVHistoryDataResponse> getAggregatedResponse(
        @Param(value = "market")String market,
        @Param(value = "start")ZonedDateTime start,
        @Param(value = "end")ZonedDateTime end);

2. 匹配时区避免数据过滤错误 #

物化视图中day字段使用+05:30时区，而代码中使用ZoneOffset.systemDefault()生成时间范围，若系统时区与物化视图时区不一致，会导致查询条件无法匹配数据。需将时间转换为物化视图对应的时区：

// 替换为物化视图使用的时区，示例为Asia/Kolkata（+05:30）
ZoneId targetZone = ZoneId.of("Asia/Kolkata");
ZonedDateTime startTime=ZonedDateTime.ofInstant(Instant.ofEpochSecond(from), targetZone);
ZonedDateTime endTime=ZonedDateTime.ofInstant(Instant.ofEpochSecond(to), targetZone);

3. 处理空结果场景 #

查询无数据时Optional为空，需在代码中补充处理逻辑：

if (testResponse.isPresent()) {
    TVHistoryDataResponse result = testResponse.get();
    result.setS("ok");
    return result;
} else {
    tvHistoryDataResponse.setS("error");
    tvHistoryDataResponse.setErrmsg("No data found");
    return tvHistoryDataResponse;
}

4. 验证驱动兼容性 #

确保使用的PostgreSQL JDBC驱动版本（如postgresql依赖）≥42.0.0，以支持数组类型与JavaList的自动映射。

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