python - Google bigQuery 拆分列

Question

我正在使用谷歌 bigQuery 来分析亚马逊书评数据集。数据集有一个“有用”列，如下所示:“有用”:[0, 0]，其中第一个元素是“赞成”票，第二个元素是“总票数”。

我上传数据客栈bigQuery时，由于不支持数组格式，所以将列拆分为两行。 bigQuery screenshot .在 python pandas 中使用“应用系列”方法，我可以将有用的列分成两列——“有用的投票”和“总投票”: jupyter notebook screenshot

如果我编写此查询以在 BQ 中执行相同的操作:

SELECT TA1.reviewerID, TA1.helpful AS yes, TA2.helpful AS total
FROM table_name as TA1

LEFT JOIN table_name as TA2
ON TA1.reviewerID = TA2.reviewerID and TA2.helpful != TA1.helpful
GROUP BY TA1.reviewerID

我收到以下错误:错误:(L1:27):GROUP BY 列表中不存在表达式“TA1.helpful”。

同样的查询在我的 sqlite3 中有效。我在 BQ 中做错了什么？

谢谢，

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT 
  reviewerID, 
  helpful[OFFSET(0)] AS helpfulVote, 
  helpful[OFFSET(1)] AS totalVote 
FROM table_name  

你可以用下面的虚拟数据来测试它

#standardSQL
WITH table_name AS (
  SELECT 'A' AS reviewerID, [0,0] AS helpful UNION ALL
  SELECT 'B' AS reviewerID, [0,2] AS helpful
)
SELECT 
  reviewerID, 
  helpful[OFFSET(0)] AS helpfulVote, 
  helpful[OFFSET(1)] AS totalVote 
FROM table_name 

查看更多 Accessing Array Elements

如果出于某种原因您仍在使用 BigQuery Legacy SQL，则可以使用以下版本(同时请参阅标准 SQL 的 Migrating from legacy SQL，因为这是首选版本)

#legacydSQL
SELECT 
  reviewerID,
  MIN(helpful) WITHIN RECORD AS helpfulVote,
  MAX(helpful) WITHIN RECORD AS totalVote
FROM table_name  

The same query works in my sqlite3. What am I doing wrong in BQ?

最后，如果您希望您的原始查询有效/固定 - 请参阅下文并阅读更多关于 GROUP BY 的信息作品

#legacydSQL
SELECT 
  TA1.reviewerID as reviewerID, 
  MIN(TA1.helpful) AS yes, 
  MAX(TA2.helpful) AS total
FROM table_name   as TA1
LEFT JOIN table_name   as TA2
ON TA1.reviewerID = TA2.reviewerID -- and TA2.helpful != TA1.helpful
GROUP BY TA1.reviewerID