| --! qt:dataset:src |
| set hive.mapred.mode=nonstrict; |
| -- SORT_QUERY_RESULTS |
| |
| explain |
| select key, value, avg(key + 1) from src |
| group by value, key with rollup |
| order by key, value limit 20; |
| |
| drop view v_n13; |
| create view v_n13 as |
| with q1 as ( select key from src where key = '5') |
| select * from q1; |
| desc formatted v_n13; |
| |
| drop view v_n13; |
| create view v_n13 as |
| select b.key, count(*) as c |
| from src b |
| group by b.key |
| having exists |
| (select a.key |
| from src a |
| where a.key = b.key and a.value > 'val_9' |
| ) |
| ; |
| desc formatted v_n13; |
| |
| drop view v_n13; |
| create view v_n13 as |
| select * |
| from src b |
| where not exists |
| (select distinct a.key |
| from src a |
| where b.value = a.value and a.value > 'val_2' |
| ) |
| ; |
| desc formatted v_n13; |
| |
| drop view v_n13; |
| create view v_n13 as select a.key from src a join src b on a.key=b.key; |
| desc formatted v_n13; |
| |
| CREATE VIEW view15_n0 AS |
| SELECT key,COUNT(value) AS value_count |
| FROM src |
| GROUP BY key; |
| desc formatted view15_n0; |
| |
| CREATE VIEW view16_n0 AS |
| SELECT DISTINCT value |
| FROM src; |
| |
| desc formatted view16_n0; |
| |
| drop view v_n13; |
| create view v_n13 as select key from src; |
| desc formatted v_n13; |
| |
| drop view v_n13; |
| create view v_n13 as select * from src; |
| desc formatted v_n13; |
| |
| drop view v_n13; |
| create view v_n13 as select * from src intersect select * from src; |
| desc formatted v_n13; |
| |
| drop view v_n13; |
| create view v_n13 as select * from src except select * from src; |
| desc formatted v_n13; |
| |
| explain select * from v_n13; |