区块链数据解析（1）：Chain Data

个人介绍/内容介绍

个人目前在职于 Footprint Analytics 从事区块链数据分析相关工作，致力于研究和分析区块链技术及其应用的数据。

主要工作内容是研究链上数据的特征，通过大数据分析的工具，组织链上数据的结构，包括比特币、以太坊、智能合约、去中心化应用、NFT 等领域，从而为客户提供精准、可靠的数据分析报告和洞察。

Footprint 介绍

• Footprint Analytics 是一个链上数据分析工具，覆盖24条公链，17个Marketplace，包括EVM和非EVM兼容链.
• Footprint 提供可视化工具和数据表来发掘和可视化整个区块链的数据，例如NFT和GameFi的数据表.
• Footprint 数据表抽象了业务逻辑，提供了用户数、Volume等指标，可以轻松进行跨链分析，极大提高了用户使用数据的效率.

Data Overview

链上数据结构

链式结构

链式结构不方便进行数据分析

区块链上的数据是以链式结构保存的，每个区块保存了一部分交易数据，并链接到前一个区块，形成一个不可篡改的链条。这种结构对于区块链的安全和可信性非常重要，但也带来了数据分析上的挑战，因为数据是分布在多个区块中的，而且每个区块的大小有限制。这使得数据的分析和处理变得非常困难，需要针对链式结构进行特殊的处理和分析。

单一索引

只有 block hash，transactions hash 等索引，无法进行多维度的分析

在区块链上，每个区块、每笔交易都有唯一的标识符，例如区块的hash值、交易的hash值等，这些标识符通常是单一索引，无法提供多维度的数据分析。例如，如果想要分析某个地址的所有交易数据，需要遍历所有的交易记录，并逐一进行匹配，非常耗时且效率低下.

批量获取困难

需要大规模的机器集群获取并处理数据，普通分析师进入门槛高

区块链上数据安全性是重要考虑的，分析不是，因此数据获取只能部署链节点，通过对外的接口获取数据源，而这种获取数据的方式是低效的，一次性只能获取部分数据进行分析，而看不清整个数据的全貌，同时部署的机器成本是高昂的，解析的成本高昂。

EVM Data Provided by Footprint

Relational Databases

• 通过部署大规模的机器节点，我们获取链上数据后解析成关系型数据库结构，可以使用传统大数据分析工具轻易访问.

Provides on chain raw data

• 例如结构图中是我们对链上的数据进行关系转换后的数据结构，有这样的一些特点：
  • blocks 包含多个 transactions
  • 一笔 transactions 会产生多个 logs/events
  • 一笔 transactions 会产生多个 token 的转移，即 token_transfers
  • 链上的 logs 是加密后的数据，我们结合 ABI (Application Binary Interface) 解析出可视化的 events 数据

Provides silver layer data

Provides silver level data generated by combining off chain and on chain data

• 光有链上数据分析还是有困难的，需要结合部分链下的数据进行分析，例如 protocol 跟 contracts 的关系，在关系图中 silver 级别的数据有部分需要链下提供，数据关系有这样的特点：
  • 一个 protocol 包含多个 contracts
  • 一个 contracts 包含多个 events

Data Mapping for Etherscan

• 通过常用的以太坊数据浏览器来解释一下数据表与区块链浏览器之间的关联关系.

Transactions Analysis Scenarios

重点概念

• gas_price（燃气价格）：是指交易中所设置的燃气单价，即为执行该笔交易所支付的每单位燃气费用。它是由交易发起者设定，并且由于以太币（ETH）的价格波动，其价值也可能随时变化
• receipt_effective_gas_price（收据有效燃气价格）：指矿工在打包交易时实际计算出来的燃气单价。在交易被打包并确认后，以太坊会生成一个收据（receipt），其中包含了交易执行的详细信息，包括实际消耗的燃气数量和收据有效燃气价格。收据有效燃气价格通常会高于交易中所设定的燃气单价，因为交易被矿工打包确认时，存在其他交易竞争矿工计算能力的情况，而实际消耗的燃气量可能会超过交易发起者所预估的量，这时矿工就需要对燃气价格进行重新计算。
• receipt_gas_used（收据燃气使用量）：是在执行交易时实际消耗的燃气数量，它是由以太坊网络根据交易执行结果计算出来的
• transaction_fee 计算公式：transaction_fee = receipt_effective_gas_price * receipt_gas_used

实际平均交易手续费

• 我们通过分析 ethereum_transactions 上的数据，可以知道历史的交易手续费的情况，从图上我们观察到在 22 年 11 月份一段手续费飙高的情况。

SELECT 
  DATE(block_timestamp) AS date,
  AVG(receipt_effective_gas_price) / 1e9 AS avg_gas_price,
  AVG(receipt_effective_gas_price * CAST( receipt_gas_used AS double )) / 1e18 AS avg_transaction_fee
FROM ethereum_transactions
WHERE block_timestamp >= current_timestamp - INTERVAL '30' DAY
GROUP BY DATE(block_timestamp)
ORDER BY DATE(block_timestamp) ASC
-- CAST(receipt_gas_used AS double) 两个数字相乘的结果太大，无法用 bigint 类型存储，转 double
-- gas_price 一般使用 GWei 表示，这里进行单位换算 (receipt_effective_gas_price) / 1e9，transaction_fee 使用 ether 展示同理需要转换

手续费中位数

• gas 费的平均数不代表普通用户的交易情况，用中位数更贴近用户的使用感受，因为链上很多交易机器人，会导致平均 gas 偏高或者偏低，观察这个中位数的统计，可以看到每一天不同时间段的交易情况，你可以选择你交易的时机，以便节省 gas 费。

SELECT hour("block_timestamp") AS "hours",
(approx_percentile("gas_price", 0.5) * 1.0E-9) AS "median_gas_price", 
(avg("gas_price") * 1.0E-9) AS "avg_gas_price"
FROM ethereum_transactions
WHERE "block_timestamp" >= date(date_add('day', -30, now()))
GROUP BY hour("block_timestamp")
ORDER BY hour("block_timestamp") ASC
-- 获取预估手续费的中位数和平均数做对比 

最近 90 天 usd 计价手续费

• 看 gas 费的同时，价格也是一个很重要的因素，因为价格反映你实际的支出，结合 price 表 ，可以比较方便地计算实际的支出

WITH fet AS (
  SELECT DATE(block_timestamp) AS on_date,
         AVG(receipt_effective_gas_price * CAST(receipt_gas_used AS double)) / 1e18 AS avg_gas_fee
  FROM ethereum_transactions
  WHERE block_timestamp >= current_timestamp - INTERVAL '90' DAY
  GROUP BY DATE(block_timestamp)
  ORDER BY DATE(block_timestamp) ASC
)
SELECT DATE(fet.on_date) AS on_date, SUM(tds.price * fet.avg_gas_fee) AS gas_fee_in_usd FROM fet
LEFT JOIN footprint.token_daily_stats AS tds
ON DATE(fet.on_date) = DATE(tds.on_date) AND tds.token_slug = 'ethereum-eth'
GROUP BY DATE(fet.on_date)
ORDER BY DATE(fet.on_date) ASC
-- 使用 with 子句，先查询 avg_gas_price，再 join 币价数据获取当天平均价格

Token Transfer Analysis Scenarios

Token 交易 Volume

• 通过 token transfer ，我们知道每次 token 的转移数量，结合币价，我们可以分析每个 token 的交易情况。当然也可以计算其他指标，例如换手率，持有情况，长期持有者的数量等等。

SQL 分析思路

获取某个 token 最近的币价

  SELECT 
    token_address, 
    timestamp, 
    price
  FROM token_price_5min
  WHERE token_address = '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984' 
  AND timestamp >= current_timestamp - INTERVAL '2' DAY
  AND chain = 'Ethereum'
-- 这里以 UNI 为例 

获取某个 token 最近的交易情况

  SELECT 
    token_address, 
    block_timestamp, 
    from_address, 
    to_address, 
    transaction_hash,
    amount_raw / 1e18 as amount
  FROM ethereum_token_transfers
  WHERE token_address = '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984' 
    AND block_timestamp >= date_add('day', -2, current_date) and block_timestamp < current_date

查询交易时候的近似价格

WITH token_prices AS (
  SELECT 
    token_address, 
    timestamp, 
    price
  FROM token_price_5min
  WHERE token_address = '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984' 
  AND  timestamp >= date_add('day', -2, current_date) and timestamp < current_date
  AND chain = 'Ethereum'
)
SELECT 
  token_transfers.token_address, 
  token_transfers.block_timestamp, 
  token_transfers.from_address, 
  token_transfers.to_address, 
  token_transfers.transaction_hash,
  token_transfers.amount_raw / 1e18 as amount,
  amount_raw / 1e18 * t_price.price as value
FROM 
  ethereum_token_transfers AS token_transfers
LEFT JOIN 
  token_prices AS t_price
ON 
  token_transfers.token_address = t_price.token_address
  AND t_price.timestamp = from_unixtime(floor(to_unixtime(token_transfers.block_timestamp)/300) * 300)
WHERE token_transfers.block_timestamp >= date_add('day', -2, current_date) and timestamp < current_date
-- 因两个表的时间不对称，所以要根据天做时间戳截取
-- floor(to_unixtime(token_transfers.block_timestamp)/300) 需要向下取整获取到近似 5min 时间才能进行 join 表 

计算每小时 volume 情况

WITH token_prices AS (
  SELECT 
    token_address, 
    timestamp, 
    price
  FROM token_price_5min
  WHERE token_address = '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984' 
  AND  timestamp >= date_add('day', -2, current_date) and timestamp < current_date
  AND chain = 'Ethereum'
)
SELECT 
  date_format(block_timestamp, '%Y/%m/%d %H:00:00') as day,
  SUM(amount_raw / 1e18 * t_price.price) as volume
FROM 
  ethereum_token_transfers AS token_transfers
LEFT JOIN 
  token_prices AS t_price
ON 
  token_transfers.token_address = t_price.token_address
  AND t_price.timestamp = from_unixtime(floor(to_unixtime(token_transfers.block_timestamp)/300) * 300)
WHERE token_transfers.block_timestamp >= date_add('day', -2, current_date) and timestamp < current_date
GROUP BY 1
ORDER BY 1