with raydium_lp_pairs(account_key, pair_name) as (
values
('58oQChx4yWmvKdwLLZzBi4ChoCc2fqCUWBkwMihLYQo2', 'SOL/USDC'),
('7XawhbbxtsRcQA8KTkHT9f9nc6d69UwqCDh6U5EEbEmX', 'SOL/USDT'),
('AVs9TA4nWDzfPJE9gGVNJMVhcQy3V9PGazuz33BfG2RA', 'RAY/SOL'),
('6UmmUiYoBjSrhakAobJw8BvkmJtDVxaeBtbt7rxWo1mg', 'RAY/USDC'),
('DVa7Qmb5ct9RCpaU7UTpSaf3GVMYz17vNVU67XpdCRut', 'RAY/USDT'),
('GaqgfieVmnmY4ZsZHHA6L5RSVzCGL3sKx4UgHBaYNy8m', 'RAY/SRMSOL'),
('6a1CsrpeZubDjEJE9s1CMVheB6HWM5d7m1cj2jkhyXhj', 'STSOL/USDC'),
('43UHp4TuwQ7BYsaULN1qfpktmg7GWs9GpR8TDb8ovu9c', 'APEX4/USDC')
)
select * from raydium_lp_pairs
第二种语法示例：
with token_plan as (
select token_name, hook_amount from (
values
('Token Type','BEP-20 on BNB Chain'),
('Total Token Supply','500,000,000 HOOK'),
('Private Sale Allocation','100,000,000 HOOK'),
('Private Sale Token Price','0.06 USD to 0.12 USD / HOOK'),
('Private Sale Amount Raised','~ 6,000,000 USD'),
('Binance Launchpad Sale Allocation','25,000,000 HOOK'),
('Binance Launchpad Sale Price','0.10 USD / HOOK'),
('Binance Launchpad Amount to be Raised','2,500,000 USD'),
('Initial Circ. Supply When Listed on Binance','50,000,000 HOOK (10.00%)')
) as tbl(token_name, hook_amount)
)
select * from token_plan
-- nft持仓成本分布
-- 0x306b1ea3ecdf94ab739f1910bbda052ed4a9f949 beanz
-- 0xED5AF388653567Af2F388E6224dC7C4b3241C544 azuki
with contract_transfer as (
select *
from nft.trades
where nft_contract_address = 0xe361f10965542ee57D39043C9c3972B77841F581
and tx_to != 0x0000000000000000000000000000000000000000
and amount_original is not null
),
transfer_rn as (
select row_number() over (partition by token_id order by block_time desc) as rn, *
from contract_transfer
),
latest_transfer as (
select * from transfer_rn
where rn = 1
),
min_max as (
select (cast({{max_price}} as double) - cast({{min_price}} as double))/20.0 as bin
),
bucket_trade as (select *,
case
when amount_original between {{min_price}}+0*bin and {{min_price}}+1*bin then 1*bin
when amount_original between {{min_price}}+1*bin and {{min_price}}+2*bin then 2*bin
when amount_original between {{min_price}}+2*bin and {{min_price}}+3*bin then 3*bin
when amount_original between {{min_price}}+3*bin and {{min_price}}+4*bin then 4*bin
when amount_original between {{min_price}}+4*bin and {{min_price}}+5*bin then 5*bin
when amount_original between {{min_price}}+5*bin and {{min_price}}+6*bin then 6*bin
when amount_original between {{min_price}}+6*bin and {{min_price}}+7*bin then 7*bin
when amount_original between {{min_price}}+7*bin and {{min_price}}+8*bin then 8*bin
when amount_original between {{min_price}}+8*bin and {{min_price}}+9*bin then 9*bin
when amount_original between {{min_price}}+9*bin and {{min_price}}+10*bin then 10*bin
when amount_original between {{min_price}}+10*bin and {{min_price}}+11*bin then 11*bin
when amount_original between {{min_price}}+11*bin and {{min_price}}+12*bin then 12*bin
when amount_original between {{min_price}}+12*bin and {{min_price}}+13*bin then 13*bin
when amount_original between {{min_price}}+13*bin and {{min_price}}+14*bin then 14*bin
when amount_original between {{min_price}}+14*bin and {{min_price}}+15*bin then 15*bin
when amount_original between {{min_price}}+15*bin and {{min_price}}+16*bin then 16*bin
when amount_original between {{min_price}}+16*bin and {{min_price}}+17*bin then 17*bin
when amount_original between {{min_price}}+17*bin and {{min_price}}+18*bin then 18*bin
when amount_original between {{min_price}}+18*bin and {{min_price}}+19*bin then 19*bin
when amount_original between {{min_price}}+19*bin and {{min_price}}+20*bin then 20*bin
ELSE 21*bin
end as gap
from latest_transfer,min_max
)
select gap, count(*) as num
from bucket_trade
group by gap
order by gap
with contract_transfer as (
select *
from nft.trades
where nft_contract_address = 0xe361f10965542ee57D39043C9c3972B77841F581
and tx_to != 0x0000000000000000000000000000000000000000
and amount_original is not null
),
transfer_rn as (
select row_number() over (partition by token_id order by block_time desc) as rn, *
from contract_transfer
),
latest_transfer as (
select *
from transfer_rn
where rn = 1
),
min_max as (
select (cast({{max_price}} as double) - cast({{min_price}} as double))/20.0 as bin
),
-- 生成一个1到20数字的单列表
num_series as (
select num from unnest(sequence(1, 20)) as tbl(num)
),
-- 生成分组价格区间的开始和结束价格
bin_gap as (
select (num - 1) * bin as gap,
(num - 1) * bin as price_lower,
num * bin as price_upper
from num_series
join min_max on true
union all
-- 补充一个额外的区间覆盖其他数据
select num * bin as gap,
num * bin as price_lower,
num * 1e4 * bin as price_upper
from num_series
join min_max on true
where num = 20
),
bucket_trade as (
select t.*,
b.gap
from latest_transfer t
join bin_gap b on t.amount_original >= b.price_lower and t.amount_original < b.price_upper
)
select gap, count(*) as num
from bucket_trade
group by gap
order by gap
在CTEnum_series中，我们使用unnest(sequence(1, 20)) as tbl(num)来生成了一个从1到20点数字序列并且转换为20行，每行一个数字。然后在bin_gap中，我们通过JOIN两个CTE计算得到了每一个区间的低点价格值和高点价格值。使用union all集合添加了一个额外的高点价格值足够大的区间来覆盖其他交易记录。接下来bucket_trade就可以简化为只需要简单关联bin_gap并比较价格落入对应区间即可。整体上逻辑得到了简化而显得更加清晰易懂。
select tokens, deltas, evt_tx_hash
from balancer_v2_arbitrum.Vault_evt_PoolBalanceChanged
where evt_tx_hash = 0x65a4f35d81fd789d93d79f351dc3f8c7ed220ab66cb928d2860329322ffff32c
上面查询返回的前两个字段都是数组类型（我处理了一下，显示如下图）：
我们可以使用cross join unnest(tokens) as tbl1(token)来将tokens数组字段拆分为多行：
select evt_tx_hash, deltas, token -- 返回拆分后的字段
from balancer_v2_arbitrum.Vault_evt_PoolBalanceChanged
cross join unnest(tokens) as tbl1(token) -- 拆分为多行，新字段命名为 token
where evt_tx_hash = 0x65a4f35d81fd789d93d79f351dc3f8c7ed220ab66cb928d2860329322ffff32c
select
json_query(vars, 'lax $.follower') AS follower, -- single value
json_query(vars, 'lax $.profileIds') AS profileIds, -- still string
from_hex(cast(json_extract(vars,'$.follower') as varchar)) as follower2, -- cast to varbinary
cast(json_extract(vars,'$.profileIds') as array(integer)) as profileIds2, -- cast to array
vars
from lens_polygon.LensHub_call_followWithSig
where cardinality(output_0) > 1
limit 10
