I've got the following table (scaled down to a MWE): Fiddle here.
CREATE TABLE weekly
( yearnum int,
weeknum int,
location varchar2(5),
weekly_count int,
weekly_sum int);
INSERT INTO weekly values (2012,1,'X',5,10);
INSERT INTO weekly values (2012,2,'X',6,8);
INSERT INTO weekly values (2012,3,'X',7,14);
INSERT INTO weekly values (2012,4,'X',5,12);
INSERT INTO weekly values (2012,3,'Y',1,22);
INSERT INTO weekly values (2012,4,'Y',5,100);
INSERT INTO weekly values (2012,1,'Z',100,1022);
INSERT INTO weekly values (2012,2,'Z',120,1205);
INSERT INTO weekly values (2012,3,'Z',60,700);
I need the average of the last 26 entries by location, so I've got this:
SELECT
t.location,
sum(t.weekly_sum)/sum(t.weekly_count) AS avg_26
FROM
(
SELECT
w.location,
w.weekly_sum,
w.weekly_count,
row_number() over (partition BY w.location
ORDER BY w.yearnum DESC,w.weeknum DESC) rn
FROM weekly w
) t
WHERE t.rn <= 26
GROUP BY t.location;
My problem is that it appears this is always going to be a full table scan of weekly
. Is there any index or strategy I could do to improve this, since I can only filter it by the row_number() result?