以太坊主网查询示例

本页提供了以太坊 Mainnet 的区块链分析查询示例。

如需了解有关使用 BigQuery 的说明，请参阅 BigQuery 文档。

查看已编入索引的第一个和最后一个块

此查询可显示数据的新鲜度。

在 Google Cloud 控制台中，前往 BigQuery 页面。

以下查询已加载到编辑器字段中：

SELECT
  MIN(block_number) AS `First block`,
  MAX(block_number) AS `Newest block`,
  COUNT(1) AS `Total number of blocks`
FROM
  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks;

以下示例展示了结果：

结果示例
首次屏蔽	最新区块	块总数
0	17665654	17665655

直观呈现过去 6 个月内每天的交易次数

此查询列出了过去六个月内每天的交易总数。

在 Google Cloud 控制台中，前往 BigQuery 页面。

前往 BigQuery

以下查询已加载到编辑器字段中：

SELECT
  TIMESTAMP_TRUNC(block_timestamp, DAY) AS timestamp1, COUNT(1) AS txn_count
FROM
  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.transactions
WHERE
  block_timestamp >= CAST(DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH) AS TIMESTAMP)
GROUP BY timestamp1
ORDER BY timestamp1

以下示例展示了结果：

结果示例
timestamp1	txn_count
2023-01-10 00:00:00.000000 UTC	1061055
2023-01-11 00:00:00.000000 UTC	1083178
2023-01-12 00:00:00.000000 UTC	1085563
2023-01-13 00:00:00.000000 UTC	1076328
2023-01-14 00:00:00.000000 UTC	1107804
2023-01-15 00:00:00.000000 UTC	1000777
2023-01-16 00:00:00.000000 UTC	1057284
2023-01-17 00:00:00.000000 UTC	1018353
2023-01-18 00:00:00.000000 UTC	1118225
2023-01-19 00:00:00.000000 UTC	1007125
2023-01-20 00:00:00.000000 UTC	1024504

每日广告资源利用率

统计自合并以来每个日历日添加的区块数量。自那时起，有 7,200 个可用于屏蔽的槽，但并非每个槽都会被使用。

在 Google Cloud 控制台中，前往 BigQuery 页面。

前往 BigQuery

以下查询已加载到编辑器字段中：

SELECT
  DATE(block_timestamp) AS block_date,
  COUNT(block_number) AS daily_blocks,
  7200 - COUNT(block_number) AS skipped_slots
FROM
  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks
WHERE
  DATE(block_timestamp) BETWEEN DATE("2022-09-16") AND CURRENT_DATE("UTC") - 1 /* Only count complete days after The Merge */
GROUP BY block_date

以下示例展示了结果：

结果示例
block_date	daily_blocks	skipped_slots
2023-06-26	7105	95
2023-06-25	7109	91
2023-06-24	7110	90
2023-06-23	7111	89
2023-06-22	7114	86
2023-06-21	7135	65
2023-06-20	7120	80
2023-06-19	7121	79
2023-06-18	7126	74
2023-06-17	7142	58

已质押 ETH 的提款总额

使用 UDF 解决 UINT256 问题的无损示例

在 Google Cloud 控制台中，前往 BigQuery 页面。

前往 BigQuery

以下查询已加载到编辑器字段中：

WITH withdrawals AS (
  SELECT
    w.amount_lossless AS amount,
    DATE(b.block_timestamp) AS block_date
  FROM
    bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks AS b
    CROSS JOIN UNNEST(withdrawals) AS w
)
SELECT
  block_date,
  bqutil.fn.bignumber_div(bqutil.fn.bignumber_sum(ARRAY_AGG(amount)), "1000000000") AS eth_withdrawn
FROM
  withdrawals
GROUP BY 1 ORDER BY 1 DESC

有损示例

在 Google Cloud 控制台中，前往 BigQuery 页面。

前往 BigQuery

以下查询已加载到编辑器字段中：

WITH withdrawals AS (
  SELECT
    u.amount AS amount
  FROM
    bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks
    CROSS JOIN UNNEST(withdrawals) AS u
)
SELECT
  SUM(withdrawals.amount) / POW(10,9) AS total_eth_withdrawn
FROM
  withdrawals

自 EIP-1559 以来获得的挖矿交易费用

自 EIP-1559 以来，交易的基本费用会被销毁，矿工只能赚取优先费用。以下查询计算了自伦敦硬分叉以来矿工赚取的总费用。

在 Google Cloud 控制台中，前往 BigQuery 页面。

前往 BigQuery

以下查询已加载到编辑器字段中：

WITH tgas AS (
  SELECT t.block_number, gas_used, effective_gas_price FROM
    bigquery-public-data.goog_blockchain_ethereum_mainnet_us.receipts AS r
    JOIN bigquery-public-data.goog_blockchain_ethereum_mainnet_us.transactions AS t
    ON t.block_number = r.block_number AND t.transaction_hash = r.transaction_hash
)
SELECT
  /* Cast needed to avoid INT64 overflow when doing multiplication. */
  SUM(CAST(tgas.effective_gas_price - b.base_fee_per_gas AS BIGNUMERIC) * tgas.gas_used)
FROM
  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks b JOIN tgas
  ON b.block_number = tgas.block_number
WHERE
  b.block_number >= 12965000 /* The London hard fork. */

以下示例展示了结果：

结果示例
f0_
645681358899882340722378

跳过的信标链槽

查找自 Beacon Chain 升级以来缺失、分叉或以其他方式跳过的 slot 的 epoch 和 slot 编号。

在 Google Cloud 控制台中，前往 BigQuery 页面。

前往 BigQuery

以下查询已加载到编辑器字段中：

CREATE TEMP FUNCTION SlotNumber(slot_time TIMESTAMP) AS (
  (SELECT DIV(TIMESTAMP_DIFF(slot_time, "2020-12-01 12:00:23 UTC", SECOND), 12))
);

CREATE TEMP FUNCTION EpochNumber(slot_time TIMESTAMP) AS (
  (SELECT DIV(SlotNumber(slot_time), 32))
);

/* Beacon Chain slot timestamps. */
WITH slots AS (
  /* Directly generate the first day's slots. */
  SELECT * FROM UNNEST(GENERATE_TIMESTAMP_ARRAY("2020-12-01 12:00:23 UTC", "2020-12-01 23:59:59 UTC", INTERVAL 12 SECOND)) AS slot_time
  UNION ALL
  /* Join dates and times to generate up to yesterday's slots. Attempting this directly overflows the generator functions. */
  SELECT TIMESTAMP(DATETIME(date_part, TIME(time_part))) AS slot_time
  FROM UNNEST(GENERATE_DATE_ARRAY("2020-12-02", CURRENT_DATE("UTC") - 1)) AS date_part
  CROSS JOIN
  UNNEST(GENERATE_TIMESTAMP_ARRAY("1970-01-01 00:00:11 UTC", "1970-01-01 23:59:59 UTC", INTERVAL 12 SECOND)) AS time_part
)
SELECT
  EpochNumber(slot_time) AS epoch,
  SlotNumber(slot_time) AS slot,
  slot_time,
  FORMAT("https://beaconcha.in/slot/%d", SlotNumber(slot_time)) AS beaconchain_url,
FROM
  slots LEFT JOIN bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks
  ON slot_time = block_timestamp
WHERE
  block_number IS NULL AND slot_time BETWEEN "2022-09-15 06:42:59 UTC" AND CURRENT_TIMESTAMP()
ORDER BY slot_time DESC;

以下示例展示了结果：

结果示例
周期数	槽	slot_time	beaconchain_url
211159	6757113	2023-06-27 23:42:59 UTC	https://beaconcha.in/slot/6757113
211159	6757088	2023-06-27 23:37:59 UTC	https://beaconcha.in/slot/6757088
211158	6757061	2023-06-27 23:32:35 UTC	https://beaconcha.in/slot/6757061
211145	6756660	2023-06-27 22:12:23 UTC	https://beaconcha.in/slot/6756660
211145	6756642	2023-06-27 22:08:47 UTC	https://beaconcha.in/slot/6756642
211142	6756564	2023-06-27 21:53:11 UTC	https://beaconcha.in/slot/6756564
211136	6756379	2023-06-27 21:16:11 UTC	https://beaconcha.in/slot/6756379
211136	6756374	2023-06-27 21:15:11 UTC	https://beaconcha.in/slot/6756374
211135	6756320	2023-06-27 21:04:23 UTC	https://beaconcha.in/slot/6756320
211132	6756225	2023-06-27 20:45:23 UTC	https://beaconcha.in/slot/6756225

USDC 令牌发行

分析 2023 年 3 月第一周 USDC 的净发行量。

在 Google Cloud 控制台中，前往 BigQuery 页面。

前往 BigQuery

以下查询已加载到编辑器字段中：

CREATE TEMP FUNCTION IFMINT(input STRING, ifTrue ANY TYPE, ifFalse ANY TYPE) AS (
  CASE
    WHEN input LIKE "0x40c10f19%" THEN ifTrue
    ELSE ifFalse
  END
);

CREATE TEMP FUNCTION USD(input FLOAT64) AS (
  CAST(input AS STRING FORMAT "$999,999,999,999")
);

SELECT
  DATE(block_timestamp) AS `Date`,
  USD(SUM(IFMINT(input, 1, -1) * CAST(CONCAT("0x", LTRIM(SUBSTRING(input, IFMINT(input, 75, 11), 64), "0")) AS FLOAT64) / 1000000)) AS `Total Supply Change`,
FROM
  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.transactions
WHERE
  DATE(block_timestamp) BETWEEN "2023-03-01" AND "2023-03-07"
  AND to_address = "0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48" -- USDC Coin Token
  AND (
    input LIKE "0x42966c68%" -- Burn
    OR input LIKE "0x40c10f19%" -- Mint
  )
GROUP BY `Date`
ORDER BY `Date` DESC;

以下示例展示了结果：

结果示例
日期	总供给量变化
2023-03-07	-257,914,457 美元
2023-03-06	-223,014,422 美元
2023-03-05	200,060,388 美元
2023-03-04	$234,929,175
2023-03-03	463,882,301 美元
2023-03-02	$631,198,459
2023-03-01	$172,338,818

USDC 账户余额排名前 10 位

分析当前 USDC 代币的顶级持有者。

在 Google Cloud 控制台中，前往 BigQuery 页面。

前往 BigQuery

以下查询已加载到编辑器字段中：

WITH Transfers AS (
  SELECT address token, to_address account, 0 _out, CAST(quantity AS BIGNUMERIC) _in
  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers`

  UNION ALL

  SELECT address token, from_address account, CAST(quantity AS BIGNUMERIC) _out, 0 _in
  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers`
)

/* Top 10 Holders of USDC */
SELECT account, (SUM(_in) - SUM(_out)) / 1000000 balance
FROM Transfers
WHERE token = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
GROUP BY account
ORDER BY balance DESC
LIMIT 10;

以下示例展示了结果：

结果示例
账号	余额
0xcee284f754e854890e311e3280b767f80797180d	934249404.099105
0x40ec5b33f54e0e8a33a975908c5ba1c14e5bbbdf	608860969.753471
0x47ac0fb4f2d84898e4d9e7b4dab3c24507a6d503	422999999.84
0x0a59649758aa4d66e25f08dd01271e891fe52199	382469988.743467
0xd54f502e184b6b739d7d27a6410a67dc462d69c8	335866305.446392
0x99c9fc46f92e8a1c0dec1b1747d010903e884be1	300569267.063296
0xda9ce944a37d218c3302f6b82a094844c6eceb17	231000000
0x51edf02152ebfb338e03e30d65c15fbf06cc9ecc	230000000.000002
0x7713974908be4bed47172370115e8b1219f4a5f0	218307714.860457
0x78605df79524164911c144801f41e9811b7db73d	211737271.4

BAYC 代币的 5 大最活跃交易者

分析哪些 EOA 转移的 Bored Ape NFT 最多。

在 Google Cloud 控制台中，前往 BigQuery 页面。

前往 BigQuery

以下查询已加载到编辑器字段中：

WITH Transfers AS (
  SELECT address AS token, to_address AS account, COUNT(*) transfer_count
  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers`
  GROUP BY token, account

  UNION ALL

  SELECT address AS token, from_address AS account, COUNT(*) transfer_count
  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers`
  WHERE from_address != '0x0000000000000000000000000000000000000000'
  GROUP BY token, account
)

SELECT account, SUM(transfer_count) quantity
FROM Transfers LEFT JOIN `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.accounts` ON account = address
WHERE NOT is_contract AND token = '0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d' /* BAYC */
GROUP BY account
ORDER BY quantity DESC
LIMIT 5;

以下示例展示了结果：

结果示例
账号	quantity
0xed2ab4948ba6a909a7751dec4f34f303eb8c7236	6036
0x020ca66c30bec2c4fe3861a94e4db4a498a35872	2536
0xd387a6e4e84a6c86bd90c158c6028a58cc8ac459	2506
0x8ae57a027c63fca8070d1bf38622321de8004c67	2162
0x721931508df2764fd4f70c53da646cb8aed16ace	968

Uniswap 上 WETH 的平均每日价格（以 USDC 为单位）

查看 Uniswap USDC/WETH 0.05% 费用池中的平均每日兑换价格。

在 Google Cloud 控制台中，前往 BigQuery 页面。

前往 BigQuery

以下查询已加载到编辑器字段中：

With Swaps AS (
  SELECT block_timestamp, transaction_hash,
    STRING(args[0]) sender,
    STRING(args[1]) recipient,
    SAFE_CAST(STRING(args[2]) AS BIGNUMERIC) amount0, /* USDC amount */
    SAFE_CAST(STRING(args[3]) AS BIGNUMERIC) amount1, /* WETH amount */
    SAFE_CAST(STRING(args[4]) AS BIGNUMERIC) sqrtPriceX96,
    CAST(STRING(args[5]) AS BIGNUMERIC) liquidity,
    CAST(STRING(args[6]) AS INT64) tick
  FROM `bigquery-public-data.blockchain_analytics_ethereum_mainnet_us.decoded_events`
  WHERE event_signature = 'Swap(address,address,int256,int256,uint160,uint128,int24)' /* Uniswap v3 Swaps */
  AND address = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' /* USDC/ETH 0.05% Pool */
),
EtherUSDC AS (
  SELECT block_timestamp,
    ABS(amount0) / 1000000 usdc_amount, /* USDC uses 6 decimals */
    ABS(amount1) / 1000000000000000000 eth_amount, /* WETH uses 18 decimals */
    ABS(SAFE_DIVIDE(amount0, amount1)) * 1000000000000 usd_eth /* USDC/ETH has 12 decimal difference */
  FROM Swaps
)

SELECT EXTRACT(DATE FROM block_timestamp) `date`, CAST(AVG(usd_eth) AS STRING FORMAT '$9,999.00') `avg_price`, COUNT(*) `swap_count`
FROM EtherUSDC
WHERE usdc_amount >= 1.00 /* Ignore miniscule swaps */
GROUP BY `date`
ORDER BY `date` DESC

以下示例展示了结果：

结果示例
日期	avg_price	swap_count
2023-10-03	$1,658.24	3819
2023-10-02	$1,704.98	5136
2023-10-01	$1,689.63	3723
2023-09-30	$1,675.90	2988
2023-09-29	$1,665.99	4173

以太坊主网查询示例 使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

查看已编入索引的第一个和最后一个块

直观呈现过去 6 个月内每天的交易次数

每日广告资源利用率

已质押 ETH 的提款总额

使用 UDF 解决 UINT256 问题的无损示例

有损示例

自 EIP-1559 以来获得的挖矿交易费用

跳过的信标链槽

USDC 令牌发行

USDC 账户余额排名前 10 位

BAYC 代币的 5 大最活跃交易者

Uniswap 上 WETH 的平均每日价格（以 USDC 为单位）

以太坊主网查询示例