我有一个按日期分组的查询,效果很好。
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]
时间戳列显然是分组的一部分,更奇怪的是,它没有ORDER BY
子句即可工作......这是怎么回事?
#standardSQL
SELECT
EXTRACT(DATE FROM DATETIME(timestamp, 'US/Eastern')) date,
SUM(users) total_users
FROM `mydataset.mytable`
GROUP BY 1
ORDER BY 1
您可以尝试子选择:
#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