| -- SORT_QUERY_RESULTS |
| |
| -- Test value based windowing spec |
| |
| drop table if exists emp_n0; |
| |
| create table emp_n0(empno smallint, |
| ename varchar(10), |
| job varchar(10), |
| manager smallint, |
| hiredate date, |
| hirets timestamp, |
| salary double, |
| bonus double, |
| stock decimal(10,2), |
| deptno tinyint) |
| row format delimited |
| fields terminated by '|'; |
| |
| load data local inpath '../../data/files/emp2.txt' into table emp_n0; |
| |
| -- No order by |
| select hirets, salary, sum(salary) over (partition by hirets range between current row and unbounded following) from emp_n0; |
| |
| |
| -- Support date datatype |
| select deptno, empno, hiredate, salary, |
| sum(salary) over (partition by deptno order by hiredate range 90 preceding), |
| sum(salary) over (partition by deptno order by hiredate range between 90 preceding and 90 following), |
| sum(salary) over (partition by deptno order by hiredate range between 90 preceding and 10 preceding), |
| sum(salary) over (partition by deptno order by hiredate range between 10 following and 90 following), |
| sum(salary) over (partition by deptno order by hiredate range between 10 following and unbounded following), |
| sum(salary) over (partition by deptno order by hiredate range between unbounded preceding and 10 following) |
| from emp_n0; |
| |
| -- Support timestamp datatype. Value in seconds (90days = 90 * 24 * 3600 seconds) |
| select deptno, empno, hirets, salary, |
| sum(salary) over (partition by deptno order by hirets range 7776000 preceding), |
| sum(salary) over (partition by deptno order by hirets range between 7776000 preceding and 7776000 following), |
| sum(salary) over (partition by deptno order by hirets range between 7776000 preceding and 864000 preceding), |
| sum(salary) over (partition by deptno order by hirets range between 864000 following and 7776000 following), |
| sum(salary) over (partition by deptno order by hirets range between 864000 following and unbounded following), |
| sum(salary) over (partition by deptno order by hirets range between unbounded preceding and 864000 following) |
| from emp_n0; |
| |
| -- Support double datatype |
| select deptno, empno, bonus, |
| avg(bonus) over (partition by deptno order by bonus range 200 preceding), |
| avg(bonus) over (partition by deptno order by bonus range between 200 preceding and 200 following), |
| avg(bonus) over (partition by deptno order by bonus range between 200 preceding and 100 preceding), |
| avg(bonus) over (partition by deptno order by bonus range between 100 following and 200 following), |
| avg(bonus) over (partition by deptno order by bonus range between 200 following and unbounded following), |
| avg(bonus) over (partition by deptno order by bonus range between unbounded preceding and 200 following) |
| from emp_n0; |
| |
| -- Support Decimal datatype |
| select deptno, empno, stock, salary, |
| avg(salary) over (partition by deptno order by stock range 200 preceding), |
| avg(salary) over (partition by deptno order by stock range between 200 preceding and 200 following), |
| avg(salary) over (partition by deptno order by stock range between 200 preceding and 100 preceding), |
| avg(salary) over (partition by deptno order by stock range between 100 following and 200 following), |
| avg(salary) over (partition by deptno order by stock range between 200 following and unbounded following), |
| avg(salary) over (partition by deptno order by stock range between unbounded preceding and 200 following) |
| from emp_n0; |