AVG(DISTINCT | ALL <expression>) OVER (analytic clause)
CREATE TABLE votes (
submit_date DATE NOT NULL,
num_votes NUMBER NOT NULL);
INSERT INTO votes VALUES (TRUNC(SYSDATE)-4, 100);
INSERT INTO votes VALUES (TRUNC(SYSDATE)-3, 150);
INSERT INTO votes VALUES (TRUNC(SYSDATE)-2, 75);
INSERT INTO votes VALUES (TRUNC(SYSDATE)-3, 25);
INSERT INTO votes VALUES (TRUNC(SYSDATE)-1, 50);
COMMIT;
SELECT * FROM votes;
SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AVG_VOTE_PER_DAY
FROM votes
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 votes
ORDER BY submit_date;