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函数收集这些字段，返回一个数组；
2.使用数字下标，可以直接访问数组中的元素；

也可以直接将字段放入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;