group-by - 按分组字段的大查询顺序

Question

我有一个按日期分组的查询，效果很好。

SELECT EXTRACT(date FROM DATETIME(timestamp, 'US/Eastern')) date, SUM(users) total_users FROM `mydataset.mytable` 
GROUP BY EXTRACT(date FROM DATETIME(timestamp, 'US/Eastern'))

但是当我尝试按日期订购时：

SELECT EXTRACT(date FROM DATETIME(timestamp, 'US/Eastern')) date, SUM(users) total_users FROM `mydataset.mytable` 
GROUP BY EXTRACT(date FROM DATETIME(timestamp, 'US/Eastern'))
ORDER BY EXTRACT(date FROM DATETIME(timestamp, 'US/Eastern'));

我收到以下错误：

SELECT list expression references column timestamp which is neither grouped nor aggregated at [1:35]

时间戳列显然是 group by 的一部分，甚至更奇怪的是它在没有ORDER BY子句的情况下也可以工作......这里发生了什么？

标签： group-bygoogle-cloud-platformgoogle-bigquerysql-order-by

Answer 1

#standardSQL
SELECT 
  EXTRACT(DATE FROM DATETIME(timestamp, 'US/Eastern')) date, 
  SUM(users) total_users 
FROM `mydataset.mytable` 
GROUP BY 1
ORDER BY 1