接下来在holder_balance_until_date中，我们使用日期序列和每周余额两个CTE做一个关联查询，来计算截止到date_series中每一个日期为止时每一个地址的累计账户余额。注意from date_series d inner join holder_summary_weekly w on w.block_date <= d.block_date这里的条件，我们使用了 <= 来把holder_summary_weekly表中所有在当前date_series日期之前（包括当天）的记录匹配出来。也就是对于每一个date_series的日期值，将匹配到holder_summary_weekly中的一批记录。这种操作有点类似于笛卡尔乘积。另外注意，在SELECT子句中，我们取的是d.block_date而不是w.block_date，这一点对于正确汇总数据非常关键。
ETH 属于Ethereum区块链的原生代币，不属于ERC20代币，其交易数据没有保存在evt_Transfer表中，所以不能用ERC20代币的方式来计算ETH余额和持有者等信息。智能合约可以在调用支持转账的方法时同时进行ETH转账，甚至在创建（部署）新的智能合约时或者智能合约进行自我销毁时，也可能发生ETH的转账。Ethereum区块链的燃料费也是用ETH来支付的。所有这些ETH转账的信息全部记录在ethereum.traces表中，而在ethereum.transactions表中则只有那些直接转账交易的信息。因此，计算ETH的余额或者统计持有者数量时，我们必须使用traces表。除了数据源不同，实现方式跟计算ERC20代币余额是相似的。这里有一个我之前创建的跟踪ETH账户余额的数据看板，里面的相关查询演示了具体的实现。
with transfer_detail as (
select evt_block_time,
evt_tx_hash,
contract_address,
"to" as address,
cast(value as decimal(38, 0)) as amount
from erc20_ethereum.evt_Transfer
where contract_address = 0x50d1c9771902476076ecfc8b2a83ad6b9355a4c9
union all
select evt_block_time,
evt_tx_hash,
contract_address,
"from" as address,
-1 * cast(value as decimal(38, 0)) as amount
from erc20_ethereum.evt_Transfer
where contract_address = 0x50d1c9771902476076ecfc8b2a83ad6b9355a4c9
),
address_balance as (
select address,
sum(amount) as balance_amount
from transfer_detail
group by address
)
select count(*) as holder_count,
sum(balance_amount / 1e18) as supply_amount
from address_balance
where balance_amount > 0
with transfer_detail as (
select evt_block_time,
evt_tx_hash,
contract_address,
"to" as address,
cast(value as decimal(38, 0)) as amount
from erc20_ethereum.evt_Transfer
where contract_address = {{token_contract_address}}
union all
select evt_block_time,
evt_tx_hash,
contract_address,
"from" as address,
-1 * cast(value as decimal(38, 0)) as amount
from erc20_ethereum.evt_Transfer
where contract_address = {{token_contract_address}}
),
address_balance as (
select address,
sum(amount / 1e18) as balance_amount
from transfer_detail
group by address
)
select address,
balance_amount
from address_balance
order by 2 desc
limit 100
with transfer_detail as (
-- Same as previous sample
),
address_balance as (
select address,
sum(amount / 1e18) as balance_amount
from transfer_detail
group by address
)
select (case when balance_amount >= 10000 then '>= 10000'
when balance_amount >= 1000 then '>= 1000'
when balance_amount >= 500 then '>= 500'
when balance_amount >= 100 then '>= 100'
when balance_amount >= 10 then '>= 10'
when balance_amount >= 1 then '>= 1'
else '< 1.0'
end) as amount_area_type,
(case when balance_amount >= 10000 then 10000
when balance_amount >= 1000 then 1000
when balance_amount >= 500 then 500
when balance_amount >= 100 then 100
when balance_amount >= 10 then 10
when balance_amount >= 1 then 1
else 0
end) as amount_area_id,
count(address) as holder_count,
avg(balance_amount) as average_balance_amount
from address_balance
group by 1, 2
order by 2 desc
with transfer_detail as (
-- Same as previous sample
),
address_balance as (
select address,
floor(log2(sum(amount / 1e18))) as log_balance_amount,
sum(amount / 1e18) as balance_amount
from transfer_detail
group by address
having balance_amount >= pow(10, -4)
)
select (case when log_balance_amount <= 0 then 0 else pow(2, log_balance_amount) * 1.0 end) as min_balance_amount,
count(*) as holder_count
from address_balance
group by 1
order by 1
with transfer_detail as (
select evt_block_time,
"to" as address,
cast(value as decimal(38, 0)) as value,
evt_tx_hash
from ftt_ethereum.FTT_Token_evt_Transfer
union all
select evt_block_time,
"from" as address,
-1 * cast(value as decimal(38, 0)) as value,
evt_tx_hash
from ftt_ethereum.FTT_Token_evt_Transfer
),
holder_balance_weekly as (
select date_trunc('week', evt_block_time) as block_date,
address,
sum(value/1e18) as balance_amount
from transfer_detail
group by 1, 2
),
holder_summary_weekly as (
select block_date,
address,
sum(balance_amount) over (partition by address order by block_date) as balance_amount
from holder_balance_weekly
order by 1, 2
),
min_max_date as (
select min(block_date) as start_date,
max(block_date) as end_date
from holder_balance_weekly
),
date_series as (
SELECT dt.block_date
FROM min_max_date as mm
CROSS JOIN unnest(sequence(date(mm.start_date), date(mm.end_date), interval '7' day)) AS dt(block_date)
),
holder_balance_until_date as (
select distinct d.block_date,
w.address,
-- get the last balance of same address on same date or before (when no date on same date)
first_value(balance_amount) over (partition by w.address order by w.block_date desc) as last_balance_amount
from date_series d
inner join holder_summary_weekly w on w.block_date <= d.block_date
),
holder_count_summary as (
select block_date,
count(address) as holder_count,
sum(last_balance_amount) as balance_amount
from holder_balance_until_date
where last_balance_amount > 0
group by block_date
)
select block_date,
holder_count,
balance_amount,
(holder_count - lag(holder_count, 1) over (order by block_date)) as holder_count_change,
(balance_amount - lag(balance_amount, 1) over (order by block_date)) as balance_amount_change
from holder_count_summary
order by block_date
