Google BigQuery Sum 返回错误结果

Question

伙计们，我正在对公共区块链数据运行此查询，以获取已销毁的代币总数。但是 SUM 返回的结果比真实的要少得多（在 Pandas 中运行没有 sum 的相同查询并运行 sum）。它给出 8306 而熊猫 328608。log.data - 十六进制数SELECT  SUM(SAFE_CAST(log.data as INT64)/POW(10,18))FROM  `bigquery-public-data.ethereum_blockchain.logs` AS logWHERE TRUE  AND log.address = '0xf53ad2c6851052a81b42133467480961b2321c09'  AND log.block_timestamp >= '2018-01-01 00:00:01'  AND log.block_timestamp <= '2018-12-01 00:00:01'  AND SUBSTR(log.topics[SAFE_OFFSET(0)], 1, 10) IN ('0x42696c68','0xcc16f5db')我不太明白为什么会发生这种情况。将不胜感激回答）

Answer 1

问题是一些log.data值被排除在 之外SUM，因为它们不适合范围，INT64因此SAFE_CAST(log.data AS INT64)返回NULL。作为一个例子，0x00000000000000000000000000000000000000000000000080b7978da47c78d2是大于max更大INT64的值9223372036854775807，这是0x7FFFFFFFFFFFFFFF十六进制的。

您可以改为将log.data值强制转换为FLOAT64类型，这会产生更接近您使用 Pandas 看到的结果：

SELECT

  SUM(CAST(log.data as FLOAT64)/POW(10,18))

FROM

  `bigquery-public-data.ethereum_blockchain.logs` AS log

WHERE TRUE

  AND log.address = '0xf53ad2c6851052a81b42133467480961b2321c09'

  AND log.block_timestamp >= '2018-01-01 00:00:01'

  AND log.block_timestamp <= '2018-12-01 00:00:01'

  AND SUBSTR(log.topics[SAFE_OFFSET(0)], 1, 10) IN ('0x42696c68','0xcc16f5db')

这返回329681.7942642243.