正确的列名选择

Question

#standardSQL
SELECT
  blockchain.num_tx AS `num_tx`,
  blockchain.timestamp AS `timestamp`,
  GDELT.GLOBALEVENTID AS GLOBALEVENTID,
  PARSE_TIMESTAMP('%Y%m%d', CAST(GDELT.SQLDATE AS STRING)) AS SQLDATE,
  GDELT.Actor1Code AS Actor1Code,
  GDELT.Actor1Name AS Actor1Name,
  GDELT.Actor2Code AS Actor2Code,
  GDELT.Actor2Name AS Actor2Name,
  GDELT.AvgTone AS AvgTone,
  GDELT.SOURCEURL AS SOURCEURL
FROM
  w205_final_project.blockchain_data AS blockchain
INNER JOIN
  w205_final_project.GDELT AS GDELT
ON
  blockchain.timestamp = GDELT.SQLDATE

返回以下内容：

错误:对于操作符=参数类型没有匹配的签名:时间戳，INT64。支持签名: ANY = 18:3的任何签名

GDELT.SQLDATE列数据以整数格式开始，就像YYYYMMDD一样；blockchain.timestamp列首先是timestamp格式，例如：

2015-01-01 04:29:21 UTC     

Answer 1

我不是BigQuery专家，但是SQLDAT(_PARSED)是一个只有在查询中的联接之后生成的字段，您不会在join子句中找到它作为SQLDATE(_PARSED)

你应该把你的GDELT.SQLDATE切换到PARSE_TIMESTAMP('%Y%m%d', CAST(GDELT.SQLDATE AS STRING))

#standardSQL
SELECT
  blockchain.num_tx AS `num_tx`,
  blockchain.timestamp AS `timestamp`,
  GDELT.GLOBALEVENTID AS GLOBALEVENTID,
  PARSE_TIMESTAMP('%Y%m%d', CAST(SQLDATE AS STRING)) AS SQLDATE,
  GDELT.Actor1Code AS Actor1Code,
  GDELT.Actor1Name AS Actor1Name,
  GDELT.Actor2Code AS Actor2Code,
  GDELT.Actor2Name AS Actor2Name,
  GDELT.AvgTone AS AvgTone,
  GDELT.SOURCEURL AS SOURCEURL
FROM
  final_project.blockchain_data AS blockchain
INNER JOIN
  final_project.GDELT AS GDELT
ON
  blockchain.timestamp = PARSE_TIMESTAMP('%Y%m%d', CAST(GDELT.SQLDATE AS STRING))

第二点，不要将列命名为timestamp，它是一个SQL关键字，它是保留的，time，date，.至少，尝试将您的别名重命名为类似blockchain.timestamp AScreated`的名称。

并避免将别名命名为与列相同的名称：

SELECT PARSE_TIMESTAMP('%Y%m%d', CAST(GDELT.SQLDATE AS STRING)) AS SQLDATE,
FROM blockchain_data AS blockchain
INNER JOIN GDELT 
    ON blockchain.timestamp = GDELT.SQLDATE;

与以下情况不同：

SELECT PARSE_TIMESTAMP('%Y%m%d', CAST(SQLDATE AS STRING)) AS SQLDATE_PARSED,
FROM blockchain_data AS blockchain
INNER JOIN GDELT 
    -- not what you want, but what you do, with error TIMESTAMP = INT64
    ON blockchain.timestamp = SQLDATE 
    -- what you want, but show ERROR column not fould
    ON blockchain.timestamp = SQLDATE_PARSED 
    -- Is the valid expression  
    ON blockchain.timestamp = PARSE_TIMESTAMP('%Y%m%d', CAST(SQLDATE AS STRING))