hello -
haven't worked with analytic sql in a little bit so maybe I am just rusty.
Goal: run query to return 1) current week data, and 2) a view of a 52 week avg
Sample data and ddl:
ddl:
CREATE TABLE my_fact
(product_id NUMBER,
customer_id NUMBER,
week_no number,
transaction_date DATE,
stock_on_hand NUMBER,
stock_in_transit number);
INSERT INTO my_fact VALUES (1,45, 29, SYSDATE, 100,70);
INSERT INTO my_fact VALUES (1,45, 29, SYSDATE, 200,170);
INSERT INTO my_fact VALUES (1,45, 15, SYSDATE -100, 300,70); -- added
INSERT INTO my_fact VALUES (1,55, 29, SYSDATE, 100,70);
INSERT INTO my_fact VALUES (2,32, 15, SYSDATE -100, 100,70);
INSERT INTO my_fact VALUES (2,32, 10, SYSDATE-130, 100,70);
INSERT INTO my_fact VALUES (3,32, 20, SYSDATE-66, 100,70);
INSERT INTO my_fact VALUES (3,78, 29, SYSDATE, 100,70);
now my query:
SELECT product_id, customer_id, week_no,
avg(stock_on_hand) avg_current_week,
avg(avg(stock_on_hand)) OVER (ORDER BY (trunc(transaction_date)) RANGE BETWEEN INTERVAL '364' DAY(3) PRECEDING AND INTERVAL '1' PRECEDING) avg_52week
FROM my_fact
WHERE to_char(SYSDATE, 'WW') = week_no -- OTHER THAN THE ANALYTICAL AGGREGATE JUST WANT TO LOOK AT CURRENT WEEK
GROUP BY product_id, customer_id, week_no, TRUNC(transacion_date)
receiving the ora-30089 error and not sure why?
Also, based on my goal above is this query structured correctly (the analytics piece)?
thanks!
Edited by: padawan on Jul 19, 2010 11:32 AM