ql/src/test/queries/clientpositive/temp_table_windowing_expressions.q

--! qt:dataset:part
drop table over10k;

create temporary table over10k(
           t tinyint,
           si smallint,
           i int,
           b bigint,
           f float,
           d double,
           bo boolean,
           s string,
	   ts timestamp, 
           `dec` decimal(4,2),  
           bin binary)
       row format delimited
       fields terminated by '|';

load data local inpath '../../data/files/over10k' into table over10k;

select p_mfgr, p_retailprice, p_size,
round(sum(p_retailprice) over w1 , 2) = round(sum(lag(p_retailprice,1,0.0)) over w1 + last_value(p_retailprice) over w1 , 2), 
max(p_retailprice) over w1 - min(p_retailprice) over w1 = last_value(p_retailprice) over w1 - first_value(p_retailprice) over w1
from part
window w1 as (distribute by p_mfgr sort by p_retailprice)
;
select p_mfgr, p_retailprice, p_size,
rank() over (distribute by p_mfgr sort by p_retailprice) as r,
sum(p_retailprice) over (distribute by p_mfgr sort by p_retailprice rows between unbounded preceding and current row) as s2,
sum(p_retailprice) over (distribute by p_mfgr sort by p_retailprice rows between unbounded preceding and current row) -5 as s1
from part
;

select s, si, f, si - lead(f, 3) over (partition by t order by bo,s,si,f desc) from over10k limit 100;
select s, i, i - lead(i, 3, 0) over (partition by si order by i,s) from over10k limit 100;
select s, si, d, si - lag(d, 3) over (partition by b order by si,s,d) from over10k limit 100;
select s, lag(s, 3, 'fred') over (partition by f order by b) from over10k limit 100;

select p_mfgr, avg(p_retailprice) over(partition by p_mfgr, p_type order by p_mfgr) from part;

select p_mfgr, avg(p_retailprice) over(partition by p_mfgr order by p_type,p_mfgr rows between unbounded preceding and current row) from part;

-- multi table insert test
create table t1_n144 (a1 int, b1 string); 
create table t2_n84 (a1 int, b1 string);
from (select sum(i) over (partition by ts order by i), s from over10k) tt insert overwrite table t1_n144 select * insert overwrite table t2_n84 select * ;
select * from t1_n144 limit 3;
select * from t2_n84 limit 3;

select p_mfgr, p_retailprice, p_size,
round(sum(p_retailprice) over w1 , 2) + 50.0 = round(sum(lag(p_retailprice,1,50.0)) over w1 + (last_value(p_retailprice) over w1),2)
from part
window w1 as (distribute by p_mfgr sort by p_retailprice)
limit 11;