-- Window Functions
-- WindowAgg Node Type

SELECT dname, empno, sal, avg(sal) OVER (PARTITION BY dname) FROM empsalary;
SELECT sal, sum(sal) OVER () FROM empsalary;
SELECT sal, sum(sal) OVER (ORDER BY sal) FROM empsalary;

SELECT
   dname, empno, sal
FROM
   (SELECT
       dname, empno, sal,
       rank() OVER (PARTITION BY dname ORDER BY sal DESC, empno) AS pos
    FROM empsalary
   ) AS ss
WHERE pos < 3;

SELECT
  sum(sal) OVER w, avg(sal) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY dname ORDER BY sal DESC);

SELECT * FROM
 ( SELECT
     ROW_NUMBER() OVER (ORDER BY empno ASC) AS row_number,
     *
   FROM emp
 ) AS foo
WHERE row_number <= 10;

SELECT * FROM (
  SELECT
    RANK() OVER (ORDER BY sal ASC) AS ranking,
    empno,
    ename,
    sal
  FROM emp
) AS foo
WHERE ranking <= 10;

DROP TABLE IF EXISTS WindowFuncTestTbl;

CREATE TABLE WindowFuncTestTbl(
id           NUMERIC(2),
value        NUMERIC(6,2)
);

INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (1,9);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (2,2.11);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (3,3.44);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (4,-4.21);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (8,123.45);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (9,98.23);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (10,938.23);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (11,984.23);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (12,198.23);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (13,928.87);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (14,25.37);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (15,918.3);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (16,9.23);
INSERT INTO WindowFuncTestTbl(ID,  value) VALUES (17,8.23);

SELECT
   id, value,
   AVG(value) OVER(ORDER BY id) ma
FROM WindowFuncTestTbl
ORDER BY id;

DROP TABLE WindowFuncTestTbl;



CREATE TABLE WindowFuncTestTbl(
   ID          CHARACTER VARYING(32)         NOT NULL PRIMARY KEY,
   fname       CHARACTER VARYING(80),
   lname       CHARACTER VARYING(80),
   joindate    DATE,
   retiredate  DATE,
   sal         NUMERIC(8,2),
   city        CHARACTER VARYING(80),
   state       CHARACTER VARYING(80),
   desc        CHARACTER VARYING(120)
);

INSERT INTO WindowFuncTestTbl(ID,  fname, lname, joindate, retiredate, sal, city, desc) VALUES ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer');
INSERT INTO WindowFuncTestTbl(ID,  fname, lname, joindate, retiredate, sal, city, desc) VALUES('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver', 'Tester');
INSERT INTO WindowFuncTestTbl(ID,  fname, lname, joindate, retiredate, sal, city, desc) VALUES('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver', 'Tester');
INSERT INTO WindowFuncTestTbl(ID,  fname, lname, joindate, retiredate, sal, city, desc) VALUES('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver', 'Manager');
INSERT INTO WindowFuncTestTbl(ID,  fname, lname, joindate, retiredate, sal, city, desc) VALUES('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver', 'Tester');
INSERT INTO WindowFuncTestTbl(ID,  fname, lname, joindate, retiredate, sal, city, desc) VALUES('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78, 'New York', 'Tester');
INSERT INTO WindowFuncTestTbl(ID,  fname, lname, joindate, retiredate, sal, city, desc) VALUES('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78, 'New York', 'Manager');
INSERT INTO WindowFuncTestTbl(ID,  fname, lname, joindate, retiredate, sal, city, desc) VALUES('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78, 'Vancouver', 'Tester');

SELECT
 city, SUM(sal) AS city_sal,
 SUM(SUM(sal)) OVER
  (ORDER BY city)
  AS cumulative_sal
FROM WindowFuncTestTbl
GROUP BY city
ORDER BY city;

SELECT
 city, SUM(sal) AS city_sal,
 AVG(SUM(sal)) OVER
  (ORDER BY city)
  AS moving_average
FROM WindowFuncTestTbl
GROUP BY city
ORDER BY city;

SELECT id, sal,
  AVG(sal) OVER(ORDER BY id) ma
FROM WindowFuncTestTbl
ORDER BY id;

SELECT id, sal,
  AVG(sal) OVER(ORDER BY id) ma,
  SUM(sal) OVER(ORDER BY id) sum,
  (SUM(sal) OVER(ORDER BY id))/3 "Sum/3"
FROM WindowFuncTestTbl
ORDER BY id;

SELECT id, sal,
  COUNT(sal) OVER(ORDER BY id)
FROM WindowFuncTestTbl
ORDER BY id;

SELECT id, sal,
  AVG(sal) OVER(ORDER BY id) ma,
  FIRST_VALUE(sal) OVER(ORDER BY id) first,
  LAST_VALUE(sal) OVER(ORDER BY id) last
FROM WindowFuncTestTbl
ORDER id;

-- clean the table
DROP TABLE WindowFuncTestTbl;

