按生成的列进行分组

Question

我想按分钟对数据进行分组，所以尝试了这个查询：

SELECT FROM_UNIXTIME(
     unix_timestamp (time, 'yyyy-mm-dd hh:mm:ss'), 'yyyy-mm-dd hh:mm') as ts,
     count (*) as cnt 
     from toucher group by ts limit 10;

然后Hive告诉我没有这样的列：

失败：语义异常[错误10004]：第1行134列无效的表别名或列引用“ts”：（可能的列名是：time，ip，username，code）

那么Hive不支持它吗？

- daisy

---

列 TS 在此处不在范围内！您需要在外部查询中编写它。 - Ashutosh Arya

Answer 1

SELECT FROM_UNIXTIME(unix_timestamp (time, 'yyyy-mm-dd hh:mm:ss'), 'yyyy-mm-dd hh:mm') as ts,
     count (*) as cnt 
from toucher 
group by FROM_UNIXTIME(unix_timestamp (time, 'yyyy-mm-dd hh:mm:ss'), 'yyyy-mm-dd hh:mm') limit 10;

或者并且更好

 select t.ts, count(*) from
(SELECT FROM_UNIXTIME(unix_timestamp (time, 'yyyy-mm-dd hh:mm:ss'), 'yyyy-mm-dd hh:mm') as ts             
    from toucher ) t
    group by t.ts limit 10;

- Andrey Khmelev

---

修正了次要查询，谢谢。@LukasVermeer 第二个查询更好，因为FROM_UNIXTIME和unix_timestamp函数只执行一次。 - Andrey Khmelev

确实，在第二种方法中，FROM_UNIXTIME只执行一次，但您还有两个嵌套查询和一些未知数量的中间数据。您认为FROM_UNIXTIME调用比子查询更影响性能的原因是什么？ - Lukas Vermeer

@mr.dump 我不懂俄语，但是那篇文章似乎涉及MySQL，而不是Hive？它还表明，在WHERE子句中使用FROM_UNIXTIME会影响性能，因为它在MySQL中强制进行全表扫描？Hive与MySQL非常不同，我不确定这个性能论点在这里是否相关。 - Lukas Vermeer

@LukasVermeer 我知道很多数据库，它们都差不多。Tom Kyte也这么说。但是测试总是有可能的。 - Andrey Khmelev

@mr.dump 我的观点是Hive并不是真正的数据库；它的工作方式与MySQL不同。你关于为什么一种方法比另一种更好的论点在这里并不适用。 - Lukas Vermeer

Answer 2

大多数关系型数据库系统都是如此，SELECT 子句在 GROUP BY 子句之后处理。这意味着您不能在 GROUP BY 中使用别名列（例如此示例中的 ts）。

基本上有两种方法可以解决这个问题。两种方法都是正确的，但有些人出于各种原因更喜欢其中一种。

首先，您可以按原始表达式进行分组，而不是按别名进行分组。这会导致重复代码，因为您将在 SELECT 和 GROUP BY 子句中具有完全相同的表达式。

SELECT 
    FROM_UNIXTIME(unix_timestamp(time,'yyyy-mm-dd hh:mm:ss'),'yyyy-mm-dd hh:mm') as ts,
    COUNT(*) as cnt 
FROM toucher 
GROUP BY FROM_UNIXTIME(unix_timestamp(time,'yyyy-mm-dd hh:mm:ss'),'yyyy-mm-dd hh:mm')
LIMIT 10;

第二种方法是在子查询中包装您的表达式和别名。这意味着您不必重复表达式，但将有两个嵌套的查询，这可能会影响性能。

SELECT 
    ts,
    COUNT(*) as cnt 
FROM 
    (SELECT
        FROM_UNIXTIME(unix_timestamp(time,'yyyy-mm-dd hh:mm:ss'),'yyyy-mm-dd hh:mm') as ts,
     FROM toucher) x
GROUP BY x.ts
LIMIT 10;

两者应该有相同的结果。在这种情况下，你应该使用哪一个取决于你的特定用途；或者也可能取决于个人偏好。

希望这可以帮到你。

- Lukas Vermeer