select * from prices.usd
where symbol = 'WETH'
and blockchain = 'ethereum'
and minute >= now() - interval '6' hour
order by minute desc
limit 1
使用代币的合约地址读取prices.usd表的最新价格：
select * from prices.usd
where contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 -- WETH
and minute >= now() - interval '6' hour
order by minute desc
limit 1
从prices.usd_latest表读取最新价格信息：
select * from prices.usd_latest
where symbol = 'WETH'
and blockchain = 'ethereum'
select * from prices.usd_latest
where symbol in ('WETH', 'WBTC', 'USDC')
and blockchain = 'ethereum'
从prices.usd表读取多个代币的最新价格信息：
select symbol, decimals, price, minute
from (
select row_number() over (partition by symbol order by minute desc) as row_num, *
from prices.usd
where symbol in ('WETH', 'WBTC', 'USDC')
and blockchain = 'ethereum'
and minute >= now() - interval '6' hour
order by minute desc
) p
where row_num = 1
因为我们要同时读取多个代币的最新价格，就不能简单地使用limit子句限制结果数量来得到需要的结果。因为我们实际需要返回的是每个不同的代币分别按minute字段降序排序后取第一条记录。上面的查询中，我们使用了row_number() over (partition by symbol order by minute desc) as row_num来生成一个新的列，这个列的值按照symbol分组并按minute字段降序排序来生成，即每个不同的代币都会生成自己的1，2，3，4...这样的行号序列值。我们将其放到一个子查询中，外层查询中筛选where row_num = 1的记录，就是每个代币最新的记录。这种方法看起来稍显复杂，但是实际应用中经常需要用到类似的查询，通过row_number()函数生成新的列然后用于过滤数据。
select date_trunc('day', minute) as block_date,
avg(price) as price
from prices.usd
where symbol = 'WETH'
and blockchain = 'ethereum'
and minute >= date('2023-01-01')
group by 1
order by 1
select date_trunc('day', minute) as block_date,
symbol,
decimals,
contract_address,
avg(price) as price
from prices.usd
where symbol = 'WETH'
and blockchain = 'ethereum'
and minute >= date('2023-01-01')
group by 1, 2, 3, 4
order by 1
select date_trunc('day', minute) as block_date,
symbol,
decimals,
contract_address,
avg(price) as price
from prices.usd
where symbol in ('WETH', 'WBTC', 'USDC')
and blockchain = 'ethereum'
and minute >= date('2022-10-01')
group by 1, 2, 3, 4
order by 2, 1 -- Order by symbol first
with xen_price_in_usdc as (
select date_trunc('hour', evt_block_time) as block_date,
'XEN' as symbol,
'0x06450dee7fd2fb8e39061434babcfc05599a6fb8' as contract_address, -- XEN
18 as decimals,
avg(amount1 / amount0) / pow(10, (6-18)) as price --USDC: 6 decimals, XEN: 18 decimals
from (
select contract_address,
abs(amount0) as amount0,
abs(amount1) as amount1,
evt_tx_hash,
evt_block_time
from uniswap_v3_ethereum.Pair_evt_Swap
where contract_address = '0x353bb62ed786cdf7624bd4049859182f3c1e9e5d' -- XEN-USDC 1.00% Pair
and evt_block_time > '2022-10-07'
and evt_block_time > now() - interval '30 days'
) s
group by 1, 2, 3, 4
),
usdc_price as (
select date_trunc('hour', minute) as block_date,
avg(price) as price
from prices.usd
where contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' -- USDC
and minute > '2022-10-07'
and minute > now() - interval '30 days'
group by 1
)
select x.block_date,
x.price * u.price as price_usd
from xen_price_in_usdc x
inner join usdc_price u on x.block_date = u.block_date
order by x.block_date
with trade_detail as (
select block_time,
tx_hash,
amount_usd,
token_bought_amount,
token_bought_symbol,
token_sold_amount,
token_sold_symbol
from dex.trades
where project_contract_address = 0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8
and block_date >= now() - interval '3' day
order by block_time desc
limit 1000
)
select avg(
case when token_bought_symbol = 'WETH' then amount_usd / token_bought_amount
else amount_usd / token_sold_amount
end
) as price
from trade_detail
with token_mapping_to_ethereum(aave_token_address, ethereum_token_address, token_symbol) as (
values
(0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9, 0xdac17f958d2ee523a2206206994597c13d831ec7, 'USDT'),
(0x2f2a2543b76a4166549f7aab2e75bef0aefc5b0f, 0x2260fac5e5542a773aa44fbcfedf7c193bc2c599, 'WBTC'),
(0xd22a58f79e9481d1a88e00c343885a588b34b68b, 0xdb25f211ab05b1c97d595516f45794528a807ad8, 'EURS'),
(0xff970a61a04b1ca14834a43f5de4533ebddb5cc8, 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 'USDC'),
(0xf97f4df75117a78c1a5a0dbb814af92458539fb4, 0x514910771af9ca656af840dff83e8264ecf986ca, 'LINK'),
(0x82af49447d8a07e3bd95bd0d56f35241523fbab1, 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2, 'WETH'),
(0xda10009cbd5d07dd0cecc66161fc93d7c9000da1, 0x6b175474e89094c44da98b954eedeac495271d0f, 'DAI'),
(0xba5ddd1f9d7f570dc94a51479a000e3bce967196, 0x7fc66500c84a76ad7e9c93437bfc5ac33e2ddae9, 'AAVE')
),
latest_token_price as (
select date_trunc('hour', minute) as price_date,
contract_address,
symbol,
decimals,
avg(price) as price
from prices.usd
where contract_address in (
select ethereum_token_address
from token_mapping_to_ethereum
)
and minute > now() - interval '1' day
group by 1, 2, 3, 4
),
latest_token_price_row_num as (
select price_date,
contract_address,
symbol,
decimals,
price,
row_number() over (partition by contract_address order by price_date desc) as row_num
from latest_token_price
),
current_token_price as (
select contract_address,
symbol,
decimals,
price
from latest_token_price_row_num
where row_num = 1
)
select * from current_token_price
select 'ID:' as name, cast(id as varchar) as value
from decoding.evm_signatures
where id = 0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822
union all
select 'Signature:' as name, signature as value
from decoding.evm_signatures
where id = 0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822
union all
select 'ABI:' as name, abi as value
from decoding.evm_signatures
where id = 0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822
with op_price as (
select 0x4200000000000000000000000000000000000042 as token_address,
'OP' as token_symbol,
18 as decimals,
avg(
(case when amount0_in > 0 then amount1_out else amount1_in end)
/
(case when amount0_in > 0 then amount0_in else amount0_out end)
) as price
from (
select tx_hash,
index,
cast(bytearray_to_uint256(bytearray_substring(data, 1, 32)) as decimal(38, 0)) / 1e18 as amount0_in,
cast(bytearray_to_uint256(bytearray_substring(data, 1 + 32, 32)) as decimal(38, 0)) / 1e6 as amount1_in,
cast(bytearray_to_uint256(bytearray_substring(data, 1 + 32 * 2, 32)) as decimal(38, 0)) / 1e18 as amount0_out,
cast(bytearray_to_uint256(bytearray_substring(data, 1 + 32 * 3, 32)) as decimal(38, 0)) / 1e6 as amount1_out
from optimism.logs
where block_time >= now() - interval '2' day
and contract_address = 0x47029bc8f5cbe3b464004e87ef9c9419a48018cd -- OP - USDC Pair
and topic0 = 0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822 -- Swap
order by block_time desc
limit 1000
)
)
select * from op_price
