-- SORT_QUERY_RESULTS

set hive.support.concurrency=true;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
set hive.strict.checks.cartesian.product=false;
set hive.stats.fetch.column.stats=true;

create table emps_n10 (
  empid int,
  deptno int,
  name varchar(256),
  salary float,
  commission int)
stored as orc TBLPROPERTIES ('transactional'='true');
insert into emps_n10 values (100, 10, 'Bill', 10000, 1000), (200, 20, 'Eric', 8000, 500),
  (150, 10, 'Sebastian', 7000, null), (110, 10, 'Theodore', 10000, 250), (110, 10, 'Bill', 10000, 250);

-- EXAMPLE 1
create materialized view mv1_n10 as
select deptno, sum(salary), count(salary) as a
from emps_n10 group by deptno;

explain
select deptno, avg(salary) as a
from emps_n10 group by deptno;

select deptno, avg(salary) as a
from emps_n10 group by deptno;

drop materialized view mv1_n10;

-- EXAMPLE 2
create materialized view mv1_n10 as
select salary, sum(salary), count(salary) as a
from emps_n10 group by salary;

explain
select salary, avg(salary) as a
from emps_n10 group by salary;

select salary, avg(salary) as a
from emps_n10 group by salary;

drop materialized view mv1_n10;

-- EXAMPLE 3
create materialized view mv1_n10 as
select salary, sum(salary), count(salary) as a
from emps_n10 where salary > 0 group by salary;

explain
select salary, avg(salary) as a
from emps_n10 where salary > 0 group by salary;

select salary, avg(salary) as a
from emps_n10 where salary > 0 group by salary;

drop materialized view mv1_n10;

-- EXAMPLE 4
create table emps_n10_2 (
  empid int,
  deptno int,
  name varchar(256),
  salary tinyint,
  commission int)
stored as orc TBLPROPERTIES ('transactional'='true');
insert into emps_n10_2 values (100, 10, 'Bill', 1, 1000), (200, 20, 'Eric', 2, 500),
  (150, 10, 'Sebastian', 2, null), (110, 10, 'Theodore', 3, 250), (110, 10, 'Bill', 0, 250);

create materialized view mv1_n10 as
select salary, sum(salary), count(salary) as a
from emps_n10_2 where salary > 0 group by salary;

explain
select avg(salary)
from emps_n10_2 where salary > 0;

select avg(salary)
from emps_n10_2 where salary > 0;

drop materialized view mv1_n10;

drop table emps_n10;
drop table emps_n10_2;