新手请教一个hive语句

nextuser 发表于 2017-3-27 07:44
select substr(data,1,4),substr(data,5,4),max(substr(data,9,2)) from table2 group by substr(data,1, ...

呃。。。一眼就看出跟你回复的一眼的报错。。substr(data,5,4)不在group by 范围里

wangmy03 发表于 2017-3-27 12:02
呃。。。一眼就看出跟你回复的一眼的报错。。substr(data,5,4)不在group by 范围里

select substr(data,1,4),substr(data,5,4),max(substr(data,9,2)) from table2 group by substr(data,1,4)，substr(data,5,4);

nextuser 发表于 2017-3-27 12:15
select substr(data,1,4),substr(data,5,4),max(substr(data,9,2)) from table2 group by substr(data,1, ...

group by substr(data,1,4), substr(data,5,4) 相当于 7楼回复的错误所在。。因为这个条件实际就是group by substr(data,1,8)  的聚合条件。

wangmy03 发表于 2017-3-27 12:26
group by substr(data,1,4), substr(data,5,4) 相当于 7楼回复的错误所在。。因为这个条件实际就是group  ...

select data,max(substr(data,9,2)) from table2 group by substr(data,1,4);
上面多出温度数据，应该能看到结果。

select concat(A.y,B.d),B.mt
from
(select substr(tempdata,1,4) y,max(substr(tempdata,9,2)) mt from temp
group by substr(tempdata,1,4)) A
join
(select substr(tempdata,1,4) y,substr(tempdata,5,4) d,max(substr(tempdata,9,2)) mt from temp
group by substr(tempdata,1,4),substr(tempdata,5,4)) B
on A.y=B.y and A.mt=B.mt;

20010105        29
20070109        99
20080103        37
20100103        17
20120107        32
20130109        29
20140103        17
20150109        99

select B.d,B.mt
from
(select substr(tempdata,1,4) y,max(substr(tempdata,9,2)) mt from temp
group by substr(tempdata,1,4)) A
join
(select substr(tempdata,1,4) y,substr(tempdata,1,8) d,max(substr(tempdata,9,2)) mt from temp
group by substr(tempdata,1,4),substr(tempdata,1,8)) B
on A.y=B.y and A.mt=B.mt;
不用concat也可以

[mw_shl_code=sql,true]
SELECT substr(DATA,1,4) ,
       substr(DATA,5,2) ,
       substr(DATA,7,2) ,
       substr(DATA,9,2) ,
       row_number() over(partition BY substr(DATA,1,4)
                         ORDER BY substr(DATA,9,2) DESC) rank
FROM exam;[/mw_shl_code]

取rank=1 就可以了