如何在SQL中将交易费查询结果与RBTC最新美元价关联计算？

需求说明 #

将Rootstock区块链上最近5笔交易的Gas费，与最新的RBTC美元价格相乘，得到每笔交易的美元成本。

数据源说明 #

1. reports.token_prices：存储代币价格，可获取最新RBTC美元价格
2. chain_rsk_mainnet.block_transactions：存储Rootstock主网交易数据，可获取最近交易的Gas费

实现方案 #

注意：RBTC的Gas费以最小单位存储（1 RBTC = 10^18 最小单位），计算时需先转换为RBTC单位，再乘以美元价格。以下提供两种简洁的SQL实现方式：

方式1：标量子查询

SELECT
    bt.fees_paid,
    (SELECT 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) AS latest_rbtc_usd_price,
    -- 转换单位并计算美元成本
    (bt.fees_paid / 1e18) * (SELECT 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) AS transaction_cost_usd
FROM
    chain_rsk_mainnet.block_transactions bt
ORDER BY bt.block_id DESC, bt.tx_offset 
LIMIT 5;

方式2：CTE（公共表表达式，可读性更强）

WITH latest_rbtc_price AS (
    -- 获取最新RBTC美元价格
    SELECT 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,
    lrp.price_in_usd AS latest_rbtc_usd_price,
    -- 转换单位并计算美元成本
    (bt.fees_paid / 1e18) * lrp.price_in_usd AS transaction_cost_usd
FROM
    chain_rsk_mainnet.block_transactions bt
-- 交叉连接价格表（仅一行，不会产生冗余数据）
CROSS JOIN latest_rbtc_price lrp
ORDER BY bt.block_id DESC, bt.tx_offset 
LIMIT 5;

结果示例 #

以提供的测试数据计算：

• 最新RBTC价格：16995.771 USD
• 第二笔交易Gas费：4469416300800 → 转换为RBTC：4469416300800 / 1e18 = 0.0044694163008 → 美元成本：0.0044694163008 * 16995.771 ≈ 75.96 USD

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