通过 SQL 学习以太坊基础主题

针对开发者的以太坊教程很多，但对于数据分析师或希望不运行客户端或节点就能查看链上数据的人员，教育资源却稀缺。

本教程帮助读者通过 Dune Analytics(opens in a new tab) 提供的接口，使用结构化查询语言 (SQL) 查询链上数据，从而了解以太坊的基本概念，包括交易、区块和燃料。

链上数据可以帮助我们理解网络和算力经济 — 以太坊，并且帮助我们理解以太坊当前所面临的挑战（例如不断上涨的燃料），更重要的是，了解一些围绕扩容解决方案的讨论。

交易

用户以太坊之旅的第一步是初始化具有以太币余额的用户控制帐户或实体。 帐户类型分为两种 — 用户控制帐户或智能合约（参阅 ethereum.org）。

可以在诸如 Etherscan(opens in a new tab) 等区块浏览器上查看任何帐户。 区块浏览器是你访问以太坊数据的门户。 它们实时显示区块上的数据、交易、矿工、帐户和其他链上活动（参阅此处）。

然而，用户可能希望直接查询数据，以核对外部区块浏览器提供的信息。 Dune Analytics(opens in a new tab) 为任何对 SQL 有一定了解的人提供了这种功能。

作为参考，以太坊基金会 (EF) 的智能合约帐户可以在 Etherscan(opens in a new tab) 上查看。

值得注意的是，包括以太坊基金会帐户在内的所有帐户都有一个公共地址，可用来发送和接收交易。

Etherscan 上的帐户余额由常规交易和内部交易构成。 尽管使用了这一名称，但内部交易并不是改变链状态的真正交易。 它们是通过执行合约发起的价值转移（原文(opens in a new tab)）。 因为内部交易没有签名，它们没有包含在区块上，并且不能通过 Dune Analytics 查询。

因此，本教程将侧重于常规交易。 可以这样查询：

1WITH temp_table AS (
2SELECT
3    hash,
4    block_number,
5    block_time,
6    "from",
7    "to",
8    value / 1e18 AS ether,
9    gas_used,
10    gas_price / 1e9 AS gas_price_gwei
11FROM ethereum."transactions"
12WHERE "to" = '\xde0B295669a9FD93d5F28D9Ec85E40f4cb697BAe'
13ORDER BY block_time DESC
14)
15SELECT
16    hash,
17    block_number,
18    block_time,
19    "from",
20    "to",
21    ether,
22    (gas_used * gas_price_gwei) / 1e9 AS txn_fee
23FROM temp_table
显示全部

产生的信息与 Etherscan 交易页面提供的信息相同。 为了比较起见，下面是两种来源的信息：

Etherscan

Etherscan 上以太坊基金会的合约页面。(opens in a new tab)

Dune Analytics

可以在此处(opens in a new tab)找到仪表板。 点击表格查看查询（另请参阅上文）。

交易明细

提交的交易包含几条信息，包括（原文）：

• 接收者：接收地址（通过“to”查询）
• 签名：虽然由发送者的私钥签署交易，但我们可以通过 SQL 查询的是发送者的公共地址（“from”）。
• 价值：指的是转移的以太币数量（参阅 ether 列）。
• 数据：指的是经过哈希运算的任意数据（参阅 data 列）
• gasLimit – 交易可以消耗的最大数量的燃料单位。 燃料单位代表计算步骤
• maxPriorityFeePerGas - 作为矿工小费包含的最大燃料数量
• maxFeePerGas - 愿意为交易支付的最大燃料数量（包括 baseFeePerGas 和 maxPriorityFeePerGas）

我们可以向以太坊基金会公共地址查询这些具体的交易信息：

1SELECT
2    "to",
3    "from",
4    value / 1e18 AS ether,
5    data,
6    gas_limit,
7    gas_price / 1e9 AS gas_price_gwei,
8    gas_used,
9    ROUND(((gas_used / gas_limit) * 100),2) AS gas_used_pct
10FROM ethereum."transactions"
11WHERE "to" = '\xde0B295669a9FD93d5F28D9Ec85E40f4cb697BAe'
12ORDER BY block_time DESC
显示全部

区块

每笔交易都会改变以太坊虚拟机 (EVM) 的状态（原文）。 交易广播到网络上进行验证并被记录在一个区块中。 每笔交易都与一个区块编号相关联。 要查看数据，我们可以查询一个具体的区块编码：12396854（截至本文撰写日期 2021 年 11 月 5 日，以太坊基金会交易中最新的区块）。

此外，当我们查询下两个区块时，我们可以看到每个区块包含上一个区块的哈希值（即父哈希值），以此来说明区块链是如何形成的。

每个区块都包含对其父块的引用。 如下面的 hash 和 parent_hash 列所示（原文）：

下面是 Dune Analytics上的查询(opens in a new tab)：

1SELECT
2   time,
3   number,
4   hash,
5   parent_hash,
6   nonce
7FROM ethereum."blocks"
8WHERE "number" = 12396854 OR "number" = 12396855 OR "number" = 12396856
9LIMIT 10
显示全部

我们可以通过查询时间、区块编号、难度、哈希值、父哈希值及随机数来检查一个区块。

此查询唯一不包含的内容是交易列表，需要通过下面的单独查询和状态根来查看它。 完整或归档节点将存储所有交易和状态转换，允许客户端随时查询链的状态。 因为这需要非常大的存储空间，我们可以将链数据与状态数据分开：

• 链数据（区块列表、交易）
• 状态数据（每次交易状态转换的结果）

状态根属于状态数据，是隐式数据（未存储在链上），而链数据是显式数据并存储在链上（原文(opens in a new tab)）。

在本教程中，我们将侧重于可以在 Dune Analytics 上使用 SQL 查询的链上数据。

如上所述，每个区块都包含一个交易列表，我们可以通过筛选一个特定区块来查询它。 我们将尝试最新的区块 12396854：

1SELECT * FROM ethereum."transactions"
2WHERE block_number = 12396854
3ORDER BY block_time DESC`

下面是 Dune 上的 SQL 输出：

添加到链中的这个单独区块改变了以太坊虚拟机 (EVM) 的状态。 几十笔，有时数百笔交易会同时进行验证。 在本例中，记录了 222 笔交易。

要查看实际有多少笔成功交易，我们将添加另一个筛选器来计算成功的交易：

1WITH temp_table AS (
2    SELECT * FROM ethereum."transactions"
3    WHERE block_number = 12396854 AND success = true
4    ORDER BY block_time DESC
5)
6SELECT
7    COUNT(success) AS num_successful_txn
8FROM temp_table

对于区块 12396854，在 222 笔交易中，有 204 笔成功验证：

交易请求每秒发生数十次，但区块大约每 15 秒提交一次（原文）。

要了解大约每 15 秒产生一个区块，我们可以用一天的总秒数 (86400) 除以 15，得到估算的每日平均区块数量（大约 5760 个）。

每天产生的以太坊区块数量图表（2016 年至今）如下所示：

这段时间内每天生产的平均区块数量大约 5,874 个：

查询如下所示：

1# query to visualize number of blocks produced daily since 2016
2
3SELECT
4    DATE_TRUNC('day', time) AS dt,
5    COUNT(*) AS block_count
6FROM ethereum."blocks"
7GROUP BY dt
8OFFSET 1
9
10# average number of blocks produced per day
11
12WITH temp_table AS (
13SELECT
14    DATE_TRUNC('day', time) AS dt,
15    COUNT(*) AS block_count
16FROM ethereum."blocks"
17GROUP BY dt
18OFFSET 1
19)
20SELECT
21    AVG(block_count) AS avg_block_count
22FROM temp_table
显示全部

2016 年以来每天产生的平均区块数量略高于 5,874 个。 或者，将 86400 秒除以平均区块数量 5874 应得到 14.7 秒，或大约每 15 秒一个区块。

燃料

区块的大小是有限的。 最大区块大小是动态的，根据网络需求在 12,500,000 到 25,000,000 个单位之间变化。 需要设置大小限制，防止任意大的区块大小给全节点造成磁盘空间和速度要求方面的压力（出处）。

了解区块燃料限额的一种方法是将其视为区块空间（可在其中批量处理交易）的供应。 可以查询并显示从 2016 年至今的区块燃料限额：

1SELECT
2    DATE_TRUNC('day', time) AS dt,
3    AVG(gas_limit) AS avg_block_gas_limit
4FROM ethereum."blocks"
5GROUP BY dt
6OFFSET 1

每天都有实际的燃料使用量，用于支付在以太坊链上完成的计算（例如，发送交易、调用智能合同、铸造非同质化代币）。 这是对以太坊可用区块空间的需求：

1SELECT
2    DATE_TRUNC('day', time) AS dt,
3    AVG(gas_used) AS avg_block_gas_used
4FROM ethereum."blocks"
5GROUP BY dt
6OFFSET 1

我们还可以将这两个图表合并在一起，看看需求和供应如何契合：

因此，考虑到现有的供应情况，我们可以理解燃料价格是以太坊区块空间需求量的函数。

最后，我们可能想查询以太坊链的日均燃料价格，但种查询会造成特别长的查询时间，所以我们将进行筛选，查询以太坊基金会对每笔交易支付的平均燃料数额。

我们可以看到多年来为以太坊基金会地址上进行的全部交易而支付的燃料价格。 查询如下：

1SELECT
2    block_time,
3    gas_price / 1e9 AS gas_price_gwei,
4    value / 1e18 AS eth_sent
5FROM ethereum."transactions"
6WHERE "to" = '\xde0B295669a9FD93d5F28D9Ec85E40f4cb697BAe'
7ORDER BY block_time DESC

总结

在本教程中，我们经过查询和感受链上数据，了解了以太坊基础概念以及以太坊区块链工作原理。

包含本教程中所用全部代码的仪表板可以在此处(opens in a new tab)找到。

若要更多地使用数据来探索 Web3，请在推特上找到我(opens in a new tab)。