| set hive.auto.convert.join.noconditionaltask=true; |
| set hive.auto.convert.join.noconditionaltask.size=1431655765; |
| set hive.optimize.topnkey=false; |
| |
| |
| create table testtable_n1000 (s_state string, ss_net_profit double); |
| insert into testtable_n1000 values |
| ('AA', 101), |
| ('AB', 102), |
| ('AC', 103), |
| ('AD', 104), |
| ('AE', 105), |
| ('AF', 106), |
| ('AG', 107), |
| ('AH', 108), |
| ('AI', 109), |
| ('AJ', 110); |
| |
| explain |
| select s_state, ranking |
| from ( |
| select s_state as s_state, |
| sum(ss_net_profit), |
| rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking |
| from testtable_n1000 |
| group by s_state) tmp1 |
| where ranking <= 5; |
| |
| select s_state, ranking |
| from ( |
| select s_state as s_state, |
| sum(ss_net_profit), |
| rank() over ( partition by s_state order by sum(ss_net_profit) desc) as ranking |
| from testtable_n1000 |
| group by s_state) tmp1 |
| where ranking <= 5; |
| |
| drop table testtable_n1000; |