按分组字段排序的BigQuery结果

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'));

I get the following error:

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

时间戳列显然是分组依据的一部分，更奇怪的是它在没有ORDER BY子句的情况下也能正常工作...这是怎么回事呢？

- RSHAP

---

如果你使用 ORDER BY 1 会怎样？ - Elliott Brossard

仍然出现相同的错误。 - RSHAP

我猜BigQuery Sql中存在一个bug，即当你使用order by时，无法在group by中使用表达式。 - nomadSK25

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 

- Mikhail Berlyant

---

你和我一起进展得很顺利 :-) 有什么想法是怎么回事吗？我猜我的使用名称“date”和“timestamp”太自由了？ - RSHAP

Answer 2

你可以尝试使用子查询：

#standardSQL
SELECT
  date,
  total_users
FROM (
  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
   date

- Vasily Bronsky