为何部分地址无法从BigQuery获取ETH余额？

使用BigQuery的bigquery-public-data.crypto_ethereum.balances表查询多数钱包地址的ETH余额正常，但查询贾斯汀·比伯的钱包地址0xE21DC18513e3e68a52F9fcDaCfD56948d43a11c6时无结果返回，希望排查原因并通过该数据源解决。

可能的原因 #

• balances表快照机制限制：该表是基于特定区块高度的快照生成的，仅保留快照时点有非零余额的地址。如果目标地址在快照时点余额为0，或者后续才收到ETH，就不会出现在表中。
• 地址大小写匹配问题：以太坊地址本身大小写不敏感，但BigQuery的字符串查询是大小写敏感的。若表中存储的地址格式（全小写/全大写）与你使用的校验和格式不一致，会导致匹配失败。
• 数据同步延迟或缺失：公共数据集偶尔会出现区块同步延迟，部分新地址或交易可能未及时同步到balances表中。

解决方法 #

1. 通过交易表直接计算余额（最可靠） #

如果balances表没有数据，可通过transactions和traces表计算地址的实际余额，这能覆盖所有历史交易，包括合约调用的转出。示例SQL：

WITH incoming AS (
    SELECT SUM(value) AS total_in
    FROM `bigquery-public-data.crypto_ethereum.transactions`
    WHERE to_address = '0xE21DC18513e3e68a52F9fcDaCfD56948d43a11c6'
), outgoing AS (
    SELECT SUM(value) AS total_out
    FROM `bigquery-public-data.crypto_ethereum.transactions`
    WHERE from_address = '0xE21DC18513e3e68a52F9fcDaCfD56948d43a11c6'
), contract_out AS (
    SELECT SUM(value) AS total_contract_out
    FROM `bigquery-public-data.crypto_ethereum.traces`
    WHERE from_address = '0xE21DC18513e3e68a52F9fcDaCfD56948d43a11c6'
      AND status = 1
)
SELECT 
  COALESCE((SELECT total_in FROM incoming), 0) - 
  COALESCE((SELECT total_out FROM outgoing), 0) - 
  COALESCE((SELECT total_contract_out FROM contract_out), 0) AS eth_balance

2. 忽略地址大小写查询 #

修改查询条件，统一转为小写匹配，避免格式不一致的问题：

SELECT eth_balance
FROM `bigquery-public-data.crypto_ethereum.balances`
WHERE LOWER(address) = LOWER('0xE21DC18513e3e68a52F9fcDaCfD56948d43a11c6')

3. 确认快照区块高度 #

查看balances表的元数据，确认快照对应的区块高度。若目标地址的ETH是在该区块之后获得的，只能通过交易表计算，或等待下一次快照更新。

修改后的测试代码 #

将原代码中的SQL替换为上述计算余额的语句即可：

function main() {

    // Import the Google Cloud client library
    const {BigQuery} = require('@google-cloud/bigquery');

    async function getWalletBalances() {
        // Create a client
        const bigqueryClient = new BigQuery();

        // 修改为计算余额的SQL
        const sqlQuery = `WITH incoming AS (
            SELECT SUM(value) AS total_in
            FROM \`bigquery-public-data.crypto_ethereum.transactions\`
            WHERE to_address = '0xE21DC18513e3e68a52F9fcDaCfD56948d43a11c6'
        ), outgoing AS (
            SELECT SUM(value) AS total_out
            FROM \`bigquery-public-data.crypto_ethereum.transactions\`
            WHERE from_address = '0xE21DC18513e3e68a52F9fcDaCfD56948d43a11c6'
        ), contract_out AS (
            SELECT SUM(value) AS total_contract_out
            FROM \`bigquery-public-data.crypto_ethereum.traces\`
            WHERE from_address = '0xE21DC18513e3e68a52F9fcDaCfD56948d43a11c6'
              AND status = 1
        )
        SELECT 
          COALESCE((SELECT total_in FROM incoming), 0) - 
          COALESCE((SELECT total_out FROM outgoing), 0) - 
          COALESCE((SELECT total_contract_out FROM contract_out), 0) AS eth_balance`;

        console.log(sqlQuery);
        const options = {
        query: 
            sqlQuery,
            // Location must match that of the dataset(s) referenced in the query.
            location: 'US',
            params: {
            },
        };

        // Run the query
        const [rows] = await bigqueryClient.query(options);

        console.log('Rows:');
        rows.forEach(row => console.log(row));
    }

    getWalletBalances();
  }

main();

内容的提问来源于stack exchange，提问作者user2497586