PostgreSQL中如何实现同一日期不同时段timestamp数据的关联查询？

原代码存在的问题 #

1. 关联逻辑缺失：自连接时未关联tick字段，会导致不同tick的跨时段记录错误匹配，结果无意义。
2. 性能低下：用to_char转换timestamp来筛选日期和时段，无法利用datahora字段的索引，数据量大时查询速度慢。
3. 语法错误：where tick = 'ANY'写法错误，ANY是用于子查询/数组的操作符，不能直接作为字符串值使用。

正确实现方案 #

场景1：每个日期+时段+tick仅一条记录 #

如果每个tick在目标时段内每天只有一条数据，可直接优化自连接逻辑：

SELECT 
    tr.tick,
    tr.cot AS cot_early,
    tr2.cot AS cot_late,
    tr2.cot - tr.cot AS dif
FROM tb_registros tr
JOIN tb_registros tr2 
    ON DATE(tr.datahora) = DATE(tr2.datahora)  -- 匹配同一日期
    AND tr.tick = tr2.tick  -- 匹配同一tick
    -- 筛选早时段：8:55:00 ~ 8:56:10
    AND tr.datahora >= DATE_TRUNC('day', tr.datahora) + INTERVAL '8h55m'
    AND tr.datahora <= DATE_TRUNC('day', tr.datahora) + INTERVAL '8h56m10s'
    -- 筛选晚时段：9:10:00 ~ 9:11:10
    AND tr2.datahora >= DATE_TRUNC('day', tr2.datahora) + INTERVAL '9h10m'
    AND tr2.datahora <= DATE_TRUNC('day', tr2.datahora) + INTERVAL '9h11m10s'
-- 如需筛选特定tick，取消注释并替换值；否则删除此行
-- WHERE tr.tick = '目标tick值';

场景2：每个日期+时段+tick有多条记录 #

如果时段内有多条数据，需先对每个时段的数据做聚合（如取平均、最大值、最新值等），再计算差值：

WITH early_period AS (
    SELECT
        tick,
        DATE(datahora) AS record_date,
        AVG(cot) AS avg_cot_early  -- 可替换为MAX(cot)/MIN(cot)，或用窗口函数取最新记录
    FROM tb_registros
    WHERE 
        datahora >= DATE_TRUNC('day', datahora) + INTERVAL '8h55m'
        AND datahora <= DATE_TRUNC('day', datahora) + INTERVAL '8h56m10s'
    GROUP BY tick, DATE(datahora)
),
late_period AS (
    SELECT
        tick,
        DATE(datahora) AS record_date,
        AVG(cot) AS avg_cot_late  -- 需与early_period的聚合方式一致
    FROM tb_registros
    WHERE 
        datahora >= DATE_TRUNC('day', datahora) + INTERVAL '9h10m'
        AND datahora <= DATE_TRUNC('day', datahora) + INTERVAL '9h11m10s'
    GROUP BY tick, DATE(datahora)
)
SELECT
    ep.tick,
    ep.avg_cot_early,
    lp.avg_cot_late,
    lp.avg_cot_late - ep.avg_cot_early AS dif
FROM early_period ep
JOIN late_period lp 
    ON ep.tick = lp.tick 
    AND ep.record_date = lp.record_date
-- 如需筛选特定tick，取消注释并替换值；否则删除此行
-- WHERE ep.tick = '目标tick值';

补充说明 #

• 用DATE()或DATE_TRUNC('day', ...)提取日期，比to_char更高效且能利用索引。
• 若要匹配多个tick，可将WHERE条件改为tick IN ('tick1', 'tick2')或tick = ANY(ARRAY['tick1', 'tick2'])。

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