hive 运行sql报错Expression Not In Group By Key

案例：

Select tmp.username,date,sum(tmp.su) over(partition by tmp.username order by tmp.date ) totle
    From  (
        Select username,sum(cost_money) su,date
        From table_test1
        Group by date,username
            ) tmp
    Group by tmp.date,tmp.username
    Order by tmp.username;

 

mysql运行结果：

 

 

hive提示：Semantic Exception: Line 1:24 Expression not in GROUP BY key 'su' (state=42000,code=40000)

原因：1.Hive不允许直接访问非group by字段；

解决：
1.对于非group by字段，可以用Hive的collect_set函数收集这些字段，返回一个数组；使用数字下标，可以直接访问数组中的元素,比如：collect_set(username)[0]；

2. first() 函数；注：first() 函数是分组第一个元素，与之对应的还有 last()，是分组最后一个元素。

也可以直接将字段放入group by中：

Select tmp.username,date,sum(tmp.su) over(partition by tmp.username order by tmp.date ) totle
    From  (
        Select username,sum(cost_money) su,date
        From table_test1
        Group by date,username
            ) tmp
    Group by tmp.date,tmp.username,su
    Order by tmp.username;