PostgreSQL查询1/5分钟粒度OHLC数据及现有SQL无返回问题问询

数据表存储结构如下： #

问题原因 #

原SQL查询无数据主要有两个致命错误：

1. 交易对过滤条件不匹配：示例数据表中code字段值为USDTBTC，但SQL中硬编码了p.code = 'USDCNY'，直接导致关联不到任何数据。
2. 时间对齐逻辑错误：原SQL通过字符串拼接计算5分钟粒度的操作存在逻辑错误，将计算得到的5分钟基准值拼接到了时间的秒级位置，转换后的时间与generate_series生成的时间序列完全不匹配，关联失败。

正确实现方案 #

直接使用PostgreSQL原生的date_trunc函数按指定粒度截断时间戳即可实现对齐，逻辑简洁性能更高。

5分钟粒度OHLC查询

SELECT
    g AS time_bucket,
    COALESCE((SELECT p.price FROM product_trend p WHERE date_trunc('5 minutes', TO_TIMESTAMP(p.created_at)) = g AND p.code = 'USDTBTC' ORDER BY p.created_at ASC LIMIT 1), 0) AS open,
    COALESCE(MAX(p.price), 0) AS high,
    COALESCE(MIN(p.price), 0) AS low,
    COALESCE((SELECT p.price FROM product_trend p WHERE date_trunc('5 minutes', TO_TIMESTAMP(p.created_at)) = g AND p.code = 'USDTBTC' ORDER BY p.created_at DESC LIMIT 1), 0) AS close
FROM generate_series(
    date_trunc('minute', NOW() - '1 day'::INTERVAL),
    date_trunc('minute', NOW()),
    '5 minute'::INTERVAL
) AS g
LEFT JOIN product_trend p
ON 
    p.code = 'USDTBTC'
    AND date_trunc('5 minutes', TO_TIMESTAMP(p.created_at)) = g
GROUP BY g
ORDER BY g;

1分钟粒度OHLC查询

仅需修改两处参数即可：

• generate_series的步长改为'1 minute'::INTERVAL
• date_trunc的截断粒度改为'minute'

SELECT
    g AS time_bucket,
    COALESCE((SELECT p.price FROM product_trend p WHERE date_trunc('minute', TO_TIMESTAMP(p.created_at)) = g AND p.code = 'USDTBTC' ORDER BY p.created_at ASC LIMIT 1), 0) AS open,
    COALESCE(MAX(p.price), 0) AS high,
    COALESCE(MIN(p.price), 0) AS low,
    COALESCE((SELECT p.price FROM product_trend p WHERE date_trunc('minute', TO_TIMESTAMP(p.created_at)) = g AND p.code = 'USDTBTC' ORDER BY p.created_at DESC LIMIT 1), 0) AS close
FROM generate_series(
    date_trunc('minute', NOW() - '1 day'::INTERVAL),
    date_trunc('minute', NOW()),
    '1 minute'::INTERVAL
) AS g
LEFT JOIN product_trend p
ON 
    p.code = 'USDTBTC'
    AND date_trunc('minute', TO_TIMESTAMP(p.created_at)) = g
GROUP BY g
ORDER BY g;

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