Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

If we can declare location, yearnum, and weeknum as NOT NULL, you can create a composite index on location, yearnum, weeknum that can be used instead of doing the full table scan. Here is a sqlfiddle showing that approach. Note that I applied a hint to force the index to be used since there isn't enough data in the example for a full scan of the index to be less expensive than a full scan of the table. The index full scan path would probably be more efficient in the scaled up example assuming each location has data for many more than the past 26 weeks. That's probably not going to be an order of magnitude improvement, though, unless the table is substantially larger than the index.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.