analytical query

Previous value and Next value
----------------------------

select empno , sal,
lead(sal) over(order by to_number(empno)) lead1,
lag(sal) over(order by to_number(empno)) lag1
from emp

Runing avg balance
-------------------

CREATE TABLE demo (
submit_date DATE NOT NULL,
num_votes NUMBER NOT NULL);

INSERT INTO demo VALUES (TRUNC(SYSDATE)-
4, 100);

INSERT INTO demo VALUES (TRUNC(SYSDATE)-
3, 150);

INSERT INTO demo VALUES (TRUNC(SYSDATE)-
2, 75);

INSERT INTO demo VALUES (TRUNC(SYSDATE)-
3, 25);

INSERT INTO demo VALUES (TRUNC(SYSDATE)-
1, 50);

COMMIT;

SELECT * FROM demo;

SELECT submit_date, num_votes, TRUNC(AVG
(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED
PRECEDING)) AVG_VOTE_PER_DAY
FROM demo
ORDER BY submit_date;

SELECT submit_date, num_votes, TRUNC(AVG
(num_votes)
OVER(PARTITION BY submit_date ORDER BY
submit_date ROWS UNBOUNDED PRECEDING))
AVG_VOTE_PER_DAY
FROM demo
ORDER BY submit_date;


Returns a running count of all records or by partition
-------------------------------------------------------
COUNT(<*, [DISTINCT | ALL] >) OVER ()

SELECT submit_date, num_votes, TRUNC(COUNT(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AS DAY_COUNT
FROM demo
ORDER BY submit_date;

SELECT submit_date, COUNT(*)
OVER(PARTITION BY submit_date ORDER BY submit_date
ROWS UNBOUNDED PRECEDING) NUM_RECS
FROM demo;

Return a frequency distribution
-----------------------------

CREATE TABLE test (
prod1 NUMBER(3),
prod2 NUMBER(3),
prod3 NUMBER(3));

INSERT INTO test VALUES (34,23,45);
INSERT INTO test VALUES (34,22,34);
INSERT INTO test VALUES (54,44,45);
INSERT INTO test VALUES (23,22,45);
INSERT INTO test VALUES (45,22,34);

SELECT prod1, COUNT(prod1) OVER (PARTITION BY prod1) freq1,
prod2, COUNT(prod2) OVER (PARTITION BY prod2) freq2,
prod3, COUNT(prod3) OVER (PARTITION BY prod3) freq3
FROM test;


MAX

Returns the maximum value by partition
---------------------------------------
MAX ( expression) OVER ()
conn oe/oe

SELECT manager_id, last_name, salary
FROM (
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM employees)
WHERE salary = rmax_sal;


Returns the minimum value by partition
--------------------------------------
MIN ( expression) OVER ()
conn oe/oe

SELECT manager_id, last_name, salary
FROM (
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM employees)
WHERE salary = rmax_sal;