How to re-use the result of a subquery in the main query, with Postgres-CodePudding

Here is a crude example of the problem I'm trying to solve:

SELECT
    (array_agg(ts))[1] as ts,
    (array_agg(ticker))[1] as ticker,
    round(avg(m1)::decimal,  (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT')) as m1,
    round(avg(m5)::decimal,  (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT')) as m5,
    round(avg(m15)::decimal, (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT')) as m15,
    round(avg(m30)::decimal, (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT')) as m30,
    ...

I need to know the rounding value for the data, load it once and reuse it everywhere.

I thought about using 'USING' above the query to get the rounding value:

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, tick_size.price_tick_size),
    round(avg(m5)::decimal, tick_size.price_tick_size),
    ...

But then I get the following error:

missing FROM-clause entry for table "tick_size"

So, I can do this:

round(avg(m1)::decimal, (SELECT price_tick_size FROM tick_size)) as m1,

But I can't help to think there has to be something simpler.

CodePudding user response：

You can put the subquery in the FROM clause of your main query, as long as it is guaranteed to return exactly a single row:

SELECT
    (array_agg(ts))[1] as ts,
    (array_agg(ticker))[1] as ticker,
    round(avg(m1)::decimal, tick_size.price_tick_size),
    round(avg(m5)::decimal, tick_size.price_tick_size),
    ...
FROM
    ...,
    (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT') AS tick_size