在 "GROUP BY" 子句中重用 select 表达式的结果？

Question

在mysql中，我可以有这样的查询：

select  
    cast(from_unixtime(t.time, '%Y-%m-%d %H:00') as datetime) as timeHour
    , ... 
from
    some_table t 
group by
    timeHour, ...
order by
    timeHour, ...

其中GROUP BY中的timeHour是选择表达式的结果。

，但我只是尝试了与Sqark SQL中类似的查询，我有一个错误

Error: org.apache.spark.sql.AnalysisException: 
cannot resolve '`timeHour`' given input columns: ...

我对Spark SQL的查询是：

select  
      cast(t.unixTime as timestamp) as timeHour
    , ...
from
    another_table as t
group by
    timeHour, ...
order by
    timeHour, ...

Spark SQL中的这种构造是否可能？

Answer 1

在Spark SQL？

中是否可以使用此构造

是的，它是。您可以通过2种使用GROUP BY和ORDER BY条款中的新列的方式使其在Spark SQL中起作用

方法1使用子查询：

SELECT timeHour, someThing FROM (SELECT  
      from_unixtime((starttime/1000)) AS timeHour
    , sum(...)                          AS someThing
    , starttime
FROM
    some_table) 
WHERE
    starttime >= 1000*unix_timestamp('2017-09-16 00:00:00')
      AND starttime <= 1000*unix_timestamp('2017-09-16 04:00:00')
GROUP BY
    timeHour
ORDER BY
    timeHour
LIMIT 10;

方法2使用//优雅方式：

-- create alias 
WITH table_aliase AS(SELECT  
      from_unixtime((starttime/1000)) AS timeHour
    , sum(...)                          AS someThing
    , starttime
FROM
    some_table)
-- use the same alias as table
SELECT timeHour, someThing FROM table_aliase
WHERE
    starttime >= 1000*unix_timestamp('2017-09-16 00:00:00')
      AND starttime <= 1000*unix_timestamp('2017-09-16 04:00:00')
GROUP BY
    timeHour
ORDER BY
    timeHour
LIMIT 10;

使用Scara的Spark DataFrame（WO SQL）API的替代方案：

// This code may need additional import to work well
val df = .... //load the actual table as df
import org.apache.spark.sql.functions._
df.withColumn("timeHour", from_unixtime($"starttime"/1000))
  .groupBy($"timeHour")
  .agg(sum("...").as("someThing"))
  .orderBy($"timeHour")
  .show()
//another way - as per eliasah comment
df.groupBy(from_unixtime($"starttime"/1000).as("timeHour"))
  .agg(sum("...").as("someThing"))
  .orderBy($"timeHour")
  .show()

Answer 2

我试图在这里提供答案...

在我看来，我们必须重写查询并重复按子句中组中选择表达式的计算。例如：

select  
      from_unixtime((t.starttime/1000)) as timeHour
    , sum(...)                          as someThing
from
    some_table as t
where
    t.starttime>=1000*unix_timestamp('2017-09-16 00:00:00')
      and t.starttime<=1000*unix_timestamp('2017-09-16 04:00:00')
group by
    from_unixtime((t.starttime/1000))
order by
    from_unixtime((t.starttime/1000))
limit 10;