set hive.cli.print.header=true;
SET hive.vectorized.execution.enabled=true;
set hive.vectorized.execution.ptf.enabled=true;
set hive.fetch.task.conversion=none;

create table vector_ptf_part_simple_text(p_mfgr string, p_name string, p_retailprice double)
        ROW FORMAT DELIMITED
        FIELDS TERMINATED BY '\t'
        STORED AS TEXTFILE;
LOAD DATA LOCAL INPATH '../../data/files/vector_ptf_part_simple.txt' OVERWRITE INTO TABLE vector_ptf_part_simple_text;

create table vector_ptf_part_simple_orc(p_mfgr string, p_name string, p_retailprice double) stored as orc;
INSERT INTO TABLE vector_ptf_part_simple_orc SELECT * FROM vector_ptf_part_simple_text;

select * from vector_ptf_part_simple_orc;

-- ROW_NUMBER, RANK, DENSE_RANK, FIRST_VALUE, LAST_VALUE, COUNT, COUNT(*)

-- PARTITION BY

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr) as rn,
rank() over(partition by p_mfgr) as r,
dense_rank() over(partition by p_mfgr) as dr,
first_value(p_retailprice) over(partition by p_mfgr) as fv,
last_value(p_retailprice) over(partition by p_mfgr) as lv,
count(p_retailprice) over(partition by p_mfgr) as c,
count(*) over(partition by p_mfgr) as cs
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr) as rn,
rank() over(partition by p_mfgr) as r,
dense_rank() over(partition by p_mfgr) as dr,
first_value(p_retailprice) over(partition by p_mfgr) as fv,
last_value(p_retailprice) over(partition by p_mfgr) as lv,
count(p_retailprice) over(partition by p_mfgr) as c,
count(*) over(partition by p_mfgr) as cs
from vector_ptf_part_simple_orc;

-- RANGE

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr range between unbounded preceding and unbounded following) as rn,
rank() over(partition by p_mfgr range between unbounded preceding and unbounded following) as r,
dense_rank() over(partition by p_mfgr range between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as lv,
count(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as c,
count(*) over(partition by p_mfgr range between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr range between unbounded preceding and unbounded following) as rn,
rank() over(partition by p_mfgr range between unbounded preceding and unbounded following) as r,
dense_rank() over(partition by p_mfgr range between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as lv,
count(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as c,
count(*) over(partition by p_mfgr range between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

-- ROWS

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr rows between unbounded preceding and unbounded following) as rn,
rank() over(partition by p_mfgr rows between unbounded preceding and unbounded following) as r,
dense_rank() over(partition by p_mfgr rows between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as lv,
count(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as c,
count(*) over(partition by p_mfgr rows between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr rows between unbounded preceding and unbounded following) as rn,
rank() over(partition by p_mfgr rows between unbounded preceding and unbounded following) as r,
dense_rank() over(partition by p_mfgr rows between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as lv,
count(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as c,
count(*) over(partition by p_mfgr rows between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

-- PARTITION BY, ORDER BY

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr order by p_name) as rn,
rank() over(partition by p_mfgr order by p_name) as r,
dense_rank() over(partition by p_mfgr order by p_name) as dr,
first_value(p_retailprice) over(partition by p_mfgr order by p_name) as fv,
last_value(p_retailprice) over(partition by p_mfgr order by p_name) as lv,
count(p_retailprice) over(partition by p_mfgr order by p_name) as c,
count(*) over(partition by p_mfgr order by p_name) as cs
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr order by p_name) as rn,
rank() over(partition by p_mfgr order by p_name) as r,
dense_rank() over(partition by p_mfgr order by p_name) as dr,
first_value(p_retailprice) over(partition by p_mfgr order by p_name) as fv,
last_value(p_retailprice) over(partition by p_mfgr order by p_name) as lv,
count(p_retailprice) over(partition by p_mfgr order by p_name) as c,
count(*) over(partition by p_mfgr order by p_name) as cs
from vector_ptf_part_simple_orc;

-- RANGE

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr order by p_name range between unbounded preceding and unbounded following) as rn,
rank() over(partition by p_mfgr order by p_name range between unbounded preceding and unbounded following) as r,
dense_rank() over(partition by p_mfgr order by p_name range between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as lv,
count(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as c,
count(*) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr order by p_name range between unbounded preceding and unbounded following) as rn,
rank() over(partition by p_mfgr order by p_name range between unbounded preceding and unbounded following) as r,
dense_rank() over(partition by p_mfgr order by p_name range between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as lv,
count(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as c,
count(*) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

-- ROWS

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr order by p_name rows between unbounded preceding and unbounded following) as rn,
rank() over(partition by p_mfgr order by p_name rows between unbounded preceding and unbounded following) as r,
dense_rank() over(partition by p_mfgr order by p_name rows between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as lv,
count(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as c,
count(*) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr order by p_name rows between unbounded preceding and unbounded following) as rn,
rank() over(partition by p_mfgr order by p_name rows between unbounded preceding and unbounded following) as r,
dense_rank() over(partition by p_mfgr order by p_name rows between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as lv,
count(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as c,
count(*) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

-- ORDER BY

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
row_number() over(order by p_name) as rn,
rank() over(order by p_name) as r,
dense_rank() over(order by p_name) as dr,
first_value(p_retailprice) over(order by p_name) as fv,
last_value(p_retailprice) over(order by p_name) as lv,
count(p_retailprice) over(order by p_name) as c,
count(*) over(order by p_name) as cs
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
row_number() over(order by p_name) as rn,
rank() over(order by p_name) as r,
dense_rank() over(order by p_name) as dr,
first_value(p_retailprice) over(order by p_name) as fv,
last_value(p_retailprice) over(order by p_name) as lv,
count(p_retailprice) over(order by p_name) as c,
count(*) over(order by p_name) as cs
from vector_ptf_part_simple_orc;

-- RANGE

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
row_number() over(order by p_name range between unbounded preceding and unbounded following) as rn,
rank() over(order by p_name range between unbounded preceding and unbounded following) as r,
dense_rank() over(order by p_name range between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(order by p_name range between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(order by p_name range between unbounded preceding and current row) as lv,
count(p_retailprice) over(order by p_name range between unbounded preceding and current row) as c,
count(*) over(order by p_name range between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
row_number() over(order by p_name range between unbounded preceding and unbounded following) as rn,
rank() over(order by p_name range between unbounded preceding and unbounded following) as r,
dense_rank() over(order by p_name range between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(order by p_name range between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(order by p_name range between unbounded preceding and current row) as lv,
count(p_retailprice) over(order by p_name range between unbounded preceding and current row) as c,
count(*) over(order by p_name range between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

-- ROWS

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
row_number() over(order by p_name rows between unbounded preceding and unbounded following) as rn,
rank() over(order by p_name rows between unbounded preceding and unbounded following) as r,
dense_rank() over(order by p_name rows between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as lv,
count(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as c,
count(*) over(order by p_name rows between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
row_number() over(order by p_name rows between unbounded preceding and unbounded following) as rn,
rank() over(order by p_name rows between unbounded preceding and unbounded following) as r,
dense_rank() over(order by p_name rows between unbounded preceding and unbounded following) as dr,
first_value(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as fv,
last_value(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as lv,
count(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as c,
count(*) over(order by p_name rows between unbounded preceding and current row) as cs
from vector_ptf_part_simple_orc;

-- SUM, MIN, MAX, avg

-- PARTITION BY

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr) as s,
min(p_retailprice) over(partition by p_mfgr) as mi,
max(p_retailprice) over(partition by p_mfgr) as ma,
avg(p_retailprice) over(partition by p_mfgr) as av 
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr) as s,
min(p_retailprice) over(partition by p_mfgr) as mi,
max(p_retailprice) over(partition by p_mfgr) as ma,
avg(p_retailprice) over(partition by p_mfgr) as av 
from vector_ptf_part_simple_orc;

-- RANGE

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as s,
min(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as mi,
max(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as ma,
avg(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as s,
min(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as mi,
max(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as ma,
avg(p_retailprice) over(partition by p_mfgr range between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;


-- ROW

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as s,
min(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as mi,
max(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as ma,
avg(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as s,
min(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as mi,
max(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as ma,
avg(p_retailprice) over(partition by p_mfgr rows between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;

-- PARTITION BY, ORDER BY

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr order by p_name) as s,
min(p_retailprice) over(partition by p_mfgr order by p_name) as mi,
max(p_retailprice) over(partition by p_mfgr order by p_name) as ma,
avg(p_retailprice) over(partition by p_mfgr order by p_name) as av 
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr order by p_name) as s,
min(p_retailprice) over(partition by p_mfgr order by p_name) as mi,
max(p_retailprice) over(partition by p_mfgr order by p_name) as ma,
avg(p_retailprice) over(partition by p_mfgr order by p_name) as av 
from vector_ptf_part_simple_orc;

-- RANGE

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as s,
min(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as mi,
max(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as ma,
avg(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as s,
min(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as mi,
max(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as ma,
avg(p_retailprice) over(partition by p_mfgr order by p_name range between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;


-- ROW

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s,
min(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as mi,
max(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as ma,
avg(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as s,
min(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as mi,
max(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as ma,
avg(p_retailprice) over(partition by p_mfgr order by p_name rows between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;

-- PARTITION BY, ORDER BY

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(order by p_name) as s,
min(p_retailprice) over(order by p_name) as mi,
max(p_retailprice) over(order by p_name) as ma,
avg(p_retailprice) over(order by p_name) as av 
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(order by p_name) as s,
min(p_retailprice) over(order by p_name) as mi,
max(p_retailprice) over(order by p_name) as ma,
avg(p_retailprice) over(order by p_name) as av 
from vector_ptf_part_simple_orc;

-- RANGE

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(order by p_name range between unbounded preceding and current row) as s,
min(p_retailprice) over(order by p_name range between unbounded preceding and current row) as mi,
max(p_retailprice) over(order by p_name range between unbounded preceding and current row) as ma,
avg(p_retailprice) over(order by p_name range between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(order by p_name range between unbounded preceding and current row) as s,
min(p_retailprice) over(order by p_name range between unbounded preceding and current row) as mi,
max(p_retailprice) over(order by p_name range between unbounded preceding and current row) as ma,
avg(p_retailprice) over(order by p_name range between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;


-- ROW

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as s,
min(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as mi,
max(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as ma,
avg(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as s,
min(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as mi,
max(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as ma,
avg(p_retailprice) over(order by p_name rows between unbounded preceding and current row) as av 
from vector_ptf_part_simple_orc;

-- DECIMAL

create table vector_ptf_part_simple_text_decimal(p_mfgr string, p_name string, p_retailprice decimal(38,18))
        ROW FORMAT DELIMITED
        FIELDS TERMINATED BY '\t'
        STORED AS TEXTFILE;
LOAD DATA LOCAL INPATH '../../data/files/vector_ptf_part_simple.txt' OVERWRITE INTO TABLE vector_ptf_part_simple_text_decimal;

create table vector_ptf_part_simple_orc_decimal(p_mfgr string, p_name string, p_retailprice decimal(38,18)) stored as orc;
INSERT INTO TABLE vector_ptf_part_simple_orc_decimal SELECT * FROM vector_ptf_part_simple_text_decimal;

explain vectorization detail
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr) as s,
min(p_retailprice) over(partition by p_mfgr) as mi,
max(p_retailprice) over(partition by p_mfgr) as ma,
avg(p_retailprice) over(partition by p_mfgr) as av 
from vector_ptf_part_simple_orc_decimal;

select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr) as s,
min(p_retailprice) over(partition by p_mfgr) as mi,
max(p_retailprice) over(partition by p_mfgr) as ma,
avg(p_retailprice) over(partition by p_mfgr) as av 
from vector_ptf_part_simple_orc_decimal;


explain vectorization detail
select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr order by p_name) as s,
min(p_retailprice) over(partition by p_mfgr order by p_name) as mi,
max(p_retailprice) over(partition by p_mfgr order by p_name) as ma,
avg(p_retailprice) over(partition by p_mfgr order by p_name) as av 
from vector_ptf_part_simple_orc_decimal;

select p_mfgr,p_name, p_retailprice,
sum(p_retailprice) over(partition by p_mfgr order by p_name) as s,
min(p_retailprice) over(partition by p_mfgr order by p_name) as mi,
max(p_retailprice) over(partition by p_mfgr order by p_name) as ma,
avg(p_retailprice) over(partition by p_mfgr order by p_name) as av 
from vector_ptf_part_simple_orc_decimal;


create table vector_ptf_part_simple_orc_long(p_mfgr string, p_name string, p_bigint bigint) stored as orc;
INSERT INTO TABLE vector_ptf_part_simple_orc_long SELECT p_mfgr, p_name, cast(p_retailprice * 100 as bigint) FROM vector_ptf_part_simple_text_decimal;

explain vectorization detail
select p_mfgr,p_name, p_bigint,
sum(p_bigint) over(partition by p_mfgr) as s,
min(p_bigint) over(partition by p_mfgr) as mi,
max(p_bigint) over(partition by p_mfgr) as ma,
avg(p_bigint) over(partition by p_mfgr) as av 
from vector_ptf_part_simple_orc_long;

select p_mfgr,p_name, p_bigint,
sum(p_bigint) over(partition by p_mfgr) as s,
min(p_bigint) over(partition by p_mfgr) as mi,
max(p_bigint) over(partition by p_mfgr) as ma,
avg(p_bigint) over(partition by p_mfgr) as av 
from vector_ptf_part_simple_orc_long;


explain vectorization detail
select p_mfgr,p_name, p_bigint,
sum(p_bigint) over(partition by p_mfgr order by p_name) as s,
min(p_bigint) over(partition by p_mfgr order by p_name) as mi,
max(p_bigint) over(partition by p_mfgr order by p_name) as ma,
avg(p_bigint) over(partition by p_mfgr order by p_name) as av 
from vector_ptf_part_simple_orc_long;

select p_mfgr,p_name, p_bigint,
sum(p_bigint) over(partition by p_mfgr order by p_name) as s,
min(p_bigint) over(partition by p_mfgr order by p_name) as mi,
max(p_bigint) over(partition by p_mfgr order by p_name) as ma,
avg(p_bigint) over(partition by p_mfgr order by p_name) as av 
from vector_ptf_part_simple_orc_long;


-- Omit p_name columns

explain vectorization detail
select p_mfgr, p_retailprice,
rank() over(partition by p_mfgr) as r
from vector_ptf_part_simple_orc;

select p_mfgr, p_retailprice,
rank() over(partition by p_mfgr) as r
from vector_ptf_part_simple_orc;


explain vectorization detail
select p_mfgr, p_retailprice,
rank() over(partition by p_mfgr order by p_name) as r
from vector_ptf_part_simple_orc;

select p_mfgr, p_retailprice,
rank() over(partition by p_mfgr order by p_name) as r
from vector_ptf_part_simple_orc;


-- Calculated partition key

explain vectorization detail
select p_mfgr, p_name, p_retailprice,
rank() over(partition by p_mfgr, case when p_mfgr == "Manufacturer#2" then timestamp "2000-01-01 00:00:00" end) as r
from vector_ptf_part_simple_orc;

select p_mfgr, p_name, p_retailprice,
rank() over(partition by p_mfgr, case when p_mfgr == "Manufacturer#2" then timestamp "2000-01-01 00:00:00" end) as r
from vector_ptf_part_simple_orc;

explain vectorization detail
select p_mfgr, p_name, p_retailprice,
rank() over(partition by p_mfgr, case when p_mfgr == "Manufacturer#2" then timestamp "2000-01-01 00:00:00" end order by p_name) as r
from vector_ptf_part_simple_orc;

select p_mfgr, p_name, p_retailprice,
rank() over(partition by p_mfgr, case when p_mfgr == "Manufacturer#2" then timestamp "2000-01-01 00:00:00" end order by p_name) as r
from vector_ptf_part_simple_orc;


--
-- Run some tests with these parameters that force spilling to disk.
--
set hive.vectorized.ptf.max.memory.buffering.batch.count=1;
set hive.vectorized.testing.reducer.batch.size=2;

select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr) as rn,
rank() over(partition by p_mfgr) as r,
dense_rank() over(partition by p_mfgr) as dr,
first_value(p_retailprice) over(partition by p_mfgr) as fv,
last_value(p_retailprice) over(partition by p_mfgr) as lv,
count(p_retailprice) over(partition by p_mfgr) as c,
count(*) over(partition by p_mfgr) as cs
from vector_ptf_part_simple_orc;

select p_mfgr,p_name, p_retailprice,
row_number() over(partition by p_mfgr order by p_name) as rn,
rank() over(partition by p_mfgr order by p_name) as r,
dense_rank() over(partition by p_mfgr order by p_name) as dr,
first_value(p_retailprice) over(partition by p_mfgr order by p_name) as fv,
last_value(p_retailprice) over(partition by p_mfgr order by p_name) as lv,
count(p_retailprice) over(partition by p_mfgr order by p_name) as c,
count(*) over(partition by p_mfgr order by p_name) as cs
from vector_ptf_part_simple_orc;


explain vectorization detail
select p_mfgr, p_retailprice,
rank() over(partition by p_mfgr) as r
from vector_ptf_part_simple_orc;

select p_mfgr, p_retailprice,
rank() over(partition by p_mfgr) as r
from vector_ptf_part_simple_orc;

explain vectorization detail
select p_mfgr, p_retailprice,
rank() over(partition by p_mfgr order by p_name) as r
from vector_ptf_part_simple_orc;

select p_mfgr, p_retailprice,
rank() over(partition by p_mfgr order by p_name) as r
from vector_ptf_part_simple_orc;

explain vectorization detail
select p_mfgr, p_name, p_retailprice,
rank() over(partition by p_mfgr, case when p_mfgr == "Manufacturer#2" then timestamp "2000-01-01 00:00:00" end order by p_name) as r
from vector_ptf_part_simple_orc;

select p_mfgr, p_name, p_retailprice,
rank() over(partition by p_mfgr, case when p_mfgr == "Manufacturer#2" then timestamp "2000-01-01 00:00:00" end order by p_name) as r
from vector_ptf_part_simple_orc;

explain vectorization detail
select p_mfgr, p_name, p_retailprice,
rank() over(partition by p_mfgr, case when p_mfgr == "Manufacturer#2" then timestamp "2000-01-01 00:00:00" end) as r
from vector_ptf_part_simple_orc;

select p_mfgr, p_name, p_retailprice,
rank() over(partition by p_mfgr, case when p_mfgr == "Manufacturer#2" then timestamp "2000-01-01 00:00:00" end) as r
from vector_ptf_part_simple_orc;