BigQuery /以太坊数据集-如何编写代码 [英] BigQuery/ Ethereum dataset - how to write the code

问题描述

对于以太坊数据集，任何人都可以告诉我，如果我知道特定合约的最后一个月的交易，我应该如何在BigQuery中编写？
例如，如果我想知道上个月针对合同地址 0x41dbecc1cdc5517c6f76f6a6e836adbee2754de3进行了多少笔交易，则每次尝试分析该地址时，它都会返回零。例如

For the ethereum dataset, anyone could tell me how should I write in BigQuery if I'd know the last month transactions of a particular contract? For example, if i would know how many transactions are made in the last month for the contract address " 0x41dbecc1cdc5517c6f76f6a6e836adbee2754de3 " his a Everytime I try to analyze this address, it returns zero. For example

SELECT
  from_address,
  to_address,
  value,
  transaction_hash
FROM
  `bigquery-public-data.crypto_ethereum.token_transfers` AS token_trs
JOIN
  `bigquery-public-data.crypto_ethereum.contracts` AS contracts
ON
  (contracts.address = token_trs.token_address)
WHERE
  contracts.address = ' 0x41dbecc1cdc5517c6f76f6a6e836adbee2754de3 '

请帮助我！

推荐答案

您只是简单地获取了仅存在于一个表中的地址而不是另一个-因此JOIN将其从结果中排除

You just simply took the address that exists only in one table and not in another - thus JOIN excludes it from result

如果您感兴趣的地址在一个（第一个）表中而不是在另一个表中，则可以使用LEFT JOIN代替JOIN在另一个（第二个）中

You can use LEFT JOIN instead of JOIN in case if address of your interest is in one (first) table but not in another (second)

如以下示例所示

#standardSQL
SELECT
  from_address,
  to_address,
  value,
  transaction_hash
FROM
  `bigquery-public-data.crypto_ethereum.token_transfers` AS token_trs
LEfT JOIN
  `bigquery-public-data.crypto_ethereum.contracts` AS contracts
ON
  (contracts.address = token_trs.token_address)
WHERE
  token_trs.token_address = '0x41dbecc1cdc5517c6f76f6a6e836adbee2754de3'   

In如果由于某种原因需要JOIN才能工作-首先在查询下面运行以获取两个表中都存在的地址

In case if you for some reason need JOIN to work - run first below query to obtain the address that is present in both tables

#standardSQL
SELECT contracts.address
FROM `bigquery-public-data.crypto_ethereum.token_transfers` AS token_trs
JOIN `bigquery-public-data.crypto_ethereum.contracts` AS contracts
ON contracts.address = token_trs.token_address
LIMIT 10   

从结果中获取任何地址并运行您的原始查询

Take any address from result and run your original query with it

例如：

#standardSQL
SELECT
  from_address,
  to_address,
  value,
  transaction_hash
FROM
  `bigquery-public-data.crypto_ethereum.token_transfers` AS token_trs
JOIN
  `bigquery-public-data.crypto_ethereum.contracts` AS contracts
ON
  (contracts.address = token_trs.token_address)
WHERE
  contracts.address = '0x298683bd77f17bca4f3fb37b5bf02f82ee81d3ef'

注意：我在您的地址值中看到多余的空格-最有可能是复制粘贴问题，但想提及

Note: I see extra spaces in your address value - most likely copy paste issue but wanted to mention

这篇关于BigQuery /以太坊数据集-如何编写代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！