大查询是否支持自定义排序?

I am trying to sort data by applying case when statement in the order by clause but looks like Big Query doesn't support even though it worked fine in other SQL environments. Can somebody share your thoughts on this. Thanks.

我试图通过在order by子句中应用case when语句对数据进行排序，但看起来大查询不支持，尽管它在其他SQL环境中工作得很好。有人能分享一下你的想法吗?谢谢。

2 个解决方案

#1

select x 
from (
  select x ,
  case when x = 'a' then 'z' else x end as y
  from 
    (select 'a' as x),
    (select 'b' as x),
    (select 'c' as x),
    (select 'd' as x)
  )
order by y desc

#2

I think the documentation is pretty clear:

我认为文件很清楚:

ORDER BY clause

ORDER BY子句

... ORDER BY field1|alias1 [DESC|ASC], field2|alias2 [DESC|ASC] ...

…通过field1|alias1 [DESC|ASC]， field2|alias2 [DESC|ASC]…

The ORDER BY clause sorts the results of a query in ascending or descending order of one or more fields. Use DESC (descending) or ASC (ascending) to specify the sort direction. ASC is the default.

ORDER BY子句按照一个或多个字段的升序或降序对查询结果进行排序。使用DESC(降序)或ASC(升序)来指定排序方向。ASC是默认的。

You can sort by field names or by aliases from the SELECT clause. To sort by multiple fields or aliases, enter them as a comma-separated list. The results are sorted on the fields in the order in which they are listed.

可以从SELECT子句中按字段名或别名进行排序。若要按多个字段或别名进行排序，请将它们作为逗号分隔的列表输入。结果按列出的顺序对字段进行排序。

So, BigQuery doesn't allow expressions in the ORDER BY. However, you can include the expression in the SELECT and then refer to it by the alias. So, BigQuery does support "custom sorting", but only by expressions in the SELECT.

因此，BigQuery不允许按顺序进行表达式。但是，您可以在SELECT中包含表达式，然后通过别名引用它。因此，BigQuery确实支持“自定义排序”，但只支持SELECT中的表达式。

Interestingly, Hive has a similar limitation.

有趣的是，Hive也有类似的限制。

#1

select x 
from (
  select x ,
  case when x = 'a' then 'z' else x end as y
  from 
    (select 'a' as x),
    (select 'b' as x),
    (select 'c' as x),
    (select 'd' as x)
  )
order by y desc

#2