| --! qt:dataset:src |
| set hive.mapred.mode=nonstrict; |
| |
| |
| set hive.map.aggr=false; |
| set hive.groupby.skewindata=false; |
| explain select key, count(*) from src b group by b.key; |
| explain select b.key, count(*) from src b group by key; |
| |
| set hive.map.aggr=false; |
| set hive.groupby.skewindata=true; |
| explain select key, count(*) from src b group by b.key; |
| explain select b.key, count(*) from src b group by key; |
| |
| set hive.map.aggr=true; |
| set hive.groupby.skewindata=false; |
| explain select key, count(*) from src b group by b.key; |
| explain select b.key, count(*) from src b group by key; |
| |
| set hive.map.aggr=true; |
| set hive.groupby.skewindata=true; |
| explain select key, count(*) from src b group by b.key; |
| explain select b.key, count(*) from src b group by key; |
| |
| -- windowing after group by |
| select key, count(*), rank() over(order by count(*)) |
| from src b |
| where key < '12' |
| group by b.key |
| order by b.key; |
| |
| -- having after group by |
| select key, count(*) |
| from src b |
| group by b.key |
| having key < '12' |
| order by b.key; |
| |
| -- having and windowing |
| select key, count(*), rank() over(order by count(*)) |
| from src b |
| group by b.key |
| having key < '12' |
| order by b.key |
| ; |
| |
| explain |
| select key, count(*), rank() over(order by count(*)) |
| from src b |
| group by b.key |
| having key < '12' |
| ; |
| |
| -- order by |
| select key |
| from src t |
| where key < '12' |
| group by t.key |
| order by t.key; |
| |
| -- cluster by |
| EXPLAIN |
| SELECT x.key, x.value as key FROM SRC x CLUSTER BY key; |