PostgreSQL中如何在主查询中复用子查询结果

数据老陈

2026-8-3

解决方案

方法1：CTE + 交叉连接

将定义好的CTE通过交叉连接（CROSS JOIN）引入主查询的FROM子句，即可直接引用CTE中的列：

WITH tick_size AS
    (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT')
SELECT
    (array_agg(ts))[1] as ts,
    (array_agg(ticker))[1] as ticker,
    round(avg(m1)::decimal, ts.price_tick_size) as m1,
    round(avg(m5)::decimal, ts.price_tick_size) as m5,
    round(avg(m15)::decimal, ts.price_tick_size) as m15,
    round(avg(m30)::decimal, ts.price_tick_size) as m30
FROM your_main_table
CROSS JOIN tick_size ts
-- 若主查询包含GROUP BY，需将ts.price_tick_size加入分组字段
-- GROUP BY ts.price_tick_size

方法2：直接在FROM中嵌入单行子查询

省略CTE，直接将获取tick_size的子查询作为临时表引入FROM，写法更紧凑：

SELECT
    (array_agg(ts))[1] as ts,
    (array_agg(ticker))[1] as ticker,
    round(avg(m1)::decimal, ts.price_tick_size) as m1,
    round(avg(m5)::decimal, ts.price_tick_size) as m5,
    round(avg(m15)::decimal, ts.price_tick_size) as m15,
    round(avg(m30)::decimal, ts.price_tick_size) as m30
FROM your_main_table
CROSS JOIN (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT') ts
-- 按需添加GROUP BY ts.price_tick_size

方法3：会话级变量（适合单次操作）

如果是会话内的一次性查询，可以先将tick_size存入变量，后续直接复用：

-- 先把值存入变量
SELECT price_tick_size INTO tick_size_var FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT';

-- 后续查询引用变量
SELECT
    (array_agg(ts))[1] as ts,
    (array_agg(ticker))[1] as ticker,
    round(avg(m1)::decimal, tick_size_var) as m1,
    round(avg(m5)::decimal, tick_size_var) as m5,
    round(avg(m15)::decimal, tick_size_var) as m15,
    round(avg(m30)::decimal, tick_size_var) as m30
FROM your_main_table;