postgresql 如何将查询结果与从另一个查询接收到的值联接？

8fq7wneg 于 2022-12-12 发布在 PostgreSQL

关注(0)|答案(1)|浏览(138)

I want to calculate transaction costs in USD for a number of most recent transactions on the Rootstock blockchain. I have a PostgreSQL database table with token prices reports.token_prices from which I select the value of the latest available RBTC price in USD:

select tp.price_in_usd  
from reports.token_prices tp
where tp.chain_id = 30 
and tp.coingecko_token_id = 'rootstock'
order by tp.dt desc
limit 1

(note that tp.dt is a timestamp)
Result of the query:

16995.771

Then I have a table with all transactions, chain_rsk_mainnet.block_transactions , from which I select the gas fees for the 5 most recent ones:

select
bt.fees_paid
from chain_rsk_mainnet.block_transactions bt
order by bt.block_id desc, bt.tx_offset 
limit 5

(note that instead of using a timestamp, I'm using bt.block_id and bt.tx_offset for transaction order)
Result:

0
4469416300800
4469416300800
16450260000000
0

Now I want to multiply each of these numbers by the result of the first query. How can I do this in SQL?

postgresql

来源：https://stackoverflow.com/questions/74705886/how-to-join-a-query-result-with-a-value-received-from-another-query

1条答案

按热度按时间

7gcisfzg1#

Without further information your simplest option would be just convert the first query into a CTE then Join that result in the second query.

with  price_cte(price_in_usd) as 
      (select tp.price_in_usd  
         from reports.token_prices tp
        where tp.chain_id = 30 
          and tp.coingecko_token_id = 'rootstock'
        order by tp.dt desc
        limit 1
      )
select bt.fees_paid * p.price_in_usd)  "Fees Paid in USD"
  from chain_rsk_mainnet.block_transactions bt
  cross join price_cte p
 order by bt.block_id desc, bt.tx_offset 
 limit 5;

NOTE: Not tested, no sample data nor results.

赞(0）回复(0）举报 2022-12-12

我来回答

postgresql 如何将查询结果与从另一个查询接收到的值联接？

1条答案

相关问题

热门标签

最新问答