oracle multi-field row query with more than one row

150 pts.
Tags:
Oracle
Query
SQL
I have a table that specifies the year, and a quantity for each month, in the same row. I need to get a 12 month rolling average, which involves particular fields from one row, and addition fields from another, based on the sysdate. There are several tables that need to be joined to ensure I have the correct record. I am at a loss as to how to do this. For instance, I need months 8 to 12 from row 1, and 1 to 7 from row two, to compute the average. Making it more complex is the month fields are not specified with month number, but as the month spelled out fully with a suffix. ie. sc_may_quantity. Thanks for your help.
ASKED: January 31, 2008  5:38 PM
UPDATED: May 1, 2008  3:29 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

This sounds like a textbook case for analytic functions. The analytic AVG function can produce a 12-month rolling average, and the analytic LAG and LEAD functions can reference values from other rows of the same result set.
There are lots of ways to deal with the columns named “sc_may_quantity” etc. You may need to pivot them into separate rows, with a DATE column. Without more information, I can’t be sure.

Please post a few rows of sample data and the results you want to produce from that data. This appears to be a very interesting problem.

Here is a sample of a few rows of data:
ID YEAR SC_JANUARY_QTY SC_FEBRUARY_QTY SC_MARCH_QTY SC_APRIL_QTY SC_MAY_QTY SC_JUNE_QTY SC_JULY_QTY SC_AUGUST_QTY SC_SEPTEMBER_QTY SC_OCTOBER_QTY SC_NOVEMBER_QTY SC_DECEMBER_QTY
———- ———- ————– ————— ———— ———— ———- ———– ———– ————- —————- ————– ————— ————–
54896532 2006 845 795 736 852 988 1026 1154 1207 1002 894 658 599
54896532 2007 856 803 754 889 1015 1132 1196 1225 1197 951 708 623
54896532 2008 872
If the date is August 2007, I would need the rolling average of August 2006 (row 1) through July 2007 (row 2), for a monthly average of 917.08. If I was running the report today, I would need the rolling average of February 2007 (row 2) through January 2008 (row 3), for a monthly average of 947.08. Thanks for you help.

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Seashellm
    Here are some sample rows. ID YEAR SC_JANUARY_QTY SC_FEBRUARY_QTY SC_MARCH_QTY SC_APRIL_QTY SC_MAY_QTY SC_JUNE_QTY SC_JULY_QTY SC_AUGUST_QTY SC_SEPTEMBER_QTY SC_OCTOBER_QTY SC_NOVEMBER_QTY SC_OCTOBER_QTY ---------- ---------- -------------- --------------- ------------ ------------ ---------- ----------- ----------- ------------- ---------------- -------------- --------------- -------------- 54896532 2006 845 795 736 852 988 1026 1154 1207 1002 894 658 599 54896532 2007 856 803 754 889 1015 1132 1196 1225 1197 951 708 623 54896532 2008 872 Example: If the day is May 2007, I would need the average of May 2006 through April 2007. If today's date is used, I would need the average of February 2007 through January 2008. Thanks!
    150 pointsBadges:
    report
  • FrankKulash
    Hi, This is interesting, for a very surprising reason. Though you could use AVG and/or LAG/LEAD to solve this, I beleive the simplest solution uses neither, only a simple self join:
    SELECT	(	-- Begin list of items to average
    		CASE WHEN mnth < 2	THEN p.sc_january_qty
    					ELSE c.sc_january_qty
    		END
    	+	CASE WHEN mnth < 3	THEN p.sc_february_qty
    					ELSE c.sc_february_qty
    		END
    	+	CASE WHEN mnth < 4	THEN p.sc_march_qty
    					ELSE c.sc_march_qty
    		END
    	+	CASE WHEN mnth < 5	THEN p.sc_april_qty
    					ELSE c.sc_april_qty
    		END
    	+	CASE WHEN mnth < 6	THEN p.sc_may_qty
    					ELSE c.sc_may_qty
    		END
    	+	CASE WHEN mnth < 7	THEN p.sc_june_qty
    					ELSE c.sc_june_qty
    		END
    	+	CASE WHEN mnth < 8	THEN p.sc_july_qty
    					ELSE c.sc_july_qty
    		END
    	+	CASE WHEN mnth < 9	THEN p.sc_august_qty
    					ELSE c.sc_august_qty
    		END
    	+	CASE WHEN mnth < 10	THEN p.sc_september_qty
    					ELSE c.sc_september_qty
    		END
    	+	CASE WHEN mnth < 11	THEN p.sc_october_qty
    					ELSE c.sc_october_qty
    		END
    	+	CASE WHEN mnth < 12	THEN p.sc_november_qty
    					ELSE c.sc_november_qty
    		END
    	+				p.sc_december_qty
    	)	-- End list of items to average
    	/ 12	AS rolling_avg
    FROM	(	-- Begin in-line view v to produce yr, mnth
    	SELECT	54896532				AS id
    	,	TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY'))	AS yr
    	,	TO_NUMBER (TO_CHAR (SYSDATE, 'MM'))	AS mnth
    -- DEBUGGING: Either the two lines above or the two lines below should be commented out
    --	,	2007	AS yr
    --	,	8	AS mnth
    	FROM	dual
    	) v	-- End in-line view v to produce yr, mnth
    JOIN	sc	c	ON	v.yr = c.year		-- c = current year
    			AND	c.id = c.id
    JOIN	sc	p	ON	v.yr = p.year + 1	-- p = previous year
    			AND	c.id = p.id
    ;
    
    I assume that id is a parameter. If not, simply remove all references to id.
    1,240 pointsBadges:
    report
  • FrankKulash
    I just noticed that double-dashes look like minus signs on this site (at least on my browser). My previous message does not use subtraction: anything that looks like a minus sign is the beginning of a comment.
    1,240 pointsBadges:
    report
  • Seashellm
    I too thought analytics would be the answer. Thank you so much for your help!
    150 pointsBadges:
    report
  • Seashellm
    The self join works very well, but it does not return data where there is just a record for the current year. The particular ID does not have a record for the previous year. Although the rolling average will be skewed, the data still needs to appear in the query results. Any ideas? Thanks.
    150 pointsBadges:
    report
  • FrankKulash
    That's right: the join between c and p is an inner join: only rows that exist in both tables will be included. If you want to include rows from c even if there is no match in p, make it an outer join, that is, where the posted solution says
    FROM ... sc c ... JOIN sc p ...
    
    you should say
    FROM ... sc c ... LEFT OUTER JOIN sc p ...
    
    As you pointed out, this will give the total divided by 12, regardless of how many months were actually found. You can get a correct average by replacing the magic number 12 with a computation of how many months were not NULL, using CASE, DECODE or NVL2. I think the resulting code would be more complicated than that needed to pivot the data into a one-row-per-month format and use the analytic AVG function. If I have time later today, I'll post an example of the analytic solution.
    1,240 pointsBadges:
    report
  • FrankKulash
    Hi, again, This message contains (1) a correction to my original solution (2) details on how to make the self-join work with an OUTER JOIN (and get the right average) (3) Pivot solution -- ******************** (1) a correction to my original solution ******************** There's a typo in the query dated Feb. 1, 6:24PM. About four lines from the end, I said:
    AND	c.id = c.id
    
    I should have said:
    AND	v.id = c.id
    
    -- ***** (2) details on how to make the self-join work with an OUTER JOIN (and get the right average) ***** If you do modify the self-join, as I suggested Feb. 7, 4:53PM, the mnth column tells you how many values you're getting from c and how many from p, so you don't have to test the individual columns for NULL to get the correct divisor to replace 12. However, you DO have to test each value for NULL to get the correct total, since x + y is NULL if either x or y is NULL. So if you do the self-join, the SELECT claus will end like this:
    ...	+	NVL	( CASE WHEN mnth < 12	THEN p.sc_november_qty
    						ELSE c.sc_november_qty
    			  END
    			, 0
    			)
    	+	NVL	( p.sc_december_qty
    			, 0
    			)
    	) /	-- End list of items to average
    	(	CASE
    			WHEN	p.year	IS NOT NULL
    			THEN	12
    			WHEN	mnth	> 1
    			THEN	mnth - 1
    		END
    	)	AS rolling_avg
    
    -- ************************* (3) Pivot solution ************************* Somewhat shorter, but perhaps harder to understand, is this solution, where each month is pivoted onto a separate row: SELECT avg (quantity) AS rolling_avg FROM ( -- Begin in-line view to compute dt and quantity SELECT ADD_MONTHS (jan_1_dt, n) AS dt , DECODE ( n , 0 , sc_january_qty , 1 , sc_february_qty , 2 , sc_march_qty , 3 , sc_april_qty , 4 , sc_may_qty , 5 , sc_june_qty , 6 , sc_july_qty , 7 , sc_august_qty , 8 , sc_september_qty , 9 , sc_october_qty , 10 , sc_november_qty , 11 , sc_december_qty ) AS quantity , q_dt FROM ( -- Begin in-line view v to produce yr, mnth, q_dt SELECT v2.* , TO_DATE ( TO_CHAR (yr) || TO_CHAR (mnth) , 'YYYYMM' ) AS q_dt FROM ( -- Begin in-line view v2 to produce yr, mnth SELECT 54896532 AS id , TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY')) AS yr , TO_NUMBER (TO_CHAR (SYSDATE, 'MM')) AS mnth -- DEBUGGING: Either the two lines above or the two lines below should be commented out -- , 2007 AS yr -- , 8 AS mnth FROM dual ) v2 -- End in-line view v2 to produce yr, mnth ) v -- End in-line view v to produce yr, mnth, q_dt JOIN ( -- Begin in-line view j to compute jan_1_dt SELECT s.* , TO_DATE ('01-Jan-' || TO_CHAR (year) , 'DD-Mon-YYYY' ) AS jan_1_dt FROM sc s ) j -- End in-line view j to compute jan_1_dt ON v.id = j.id CROSS JOIN ( -- Begin in-line view i of integers 0-11 SELECT n FROM intgr WHERE n BETWEEN 0 AND 11 ) -- End in-line view i of integers 0-11 ) -- End in-line view to compute dt and quantity WHERE dt >= ADD_MONTHS (q_dt, -12) AND dt < q_dt ; This solution uses a table called intgr:
    CREATE TABLE intgr
    ( n NUMBER (2));
    
    My intgr table has 100 rows, with n ranging from 0 to 99. This is called a "counter table", and is very useful for mimicing iteration in SQL. You can also use an in-line view as a counter table, for example:
    SELECT ROWNUM AS n
    FROM all_views
    WHERE ROWNUM <= 12 -- or any other limit
    
    or, in Oracle 10
    SELECT LEVEL AS n
    FROM dual
    CONNECT BY LEVEL <= 12 -- or any other limit
    
    This solution uses lots of in-line views. As with any query involving in-line views, if you run just the in-line views as separate queries, you'll get a better idea of what each one is doing. As you can see, the above solution still does not need analytic functions: the plain, old aggregate AVG is sufficient. The more I think about this, the less it surprises me. When you're only producing one row of output, you can restrict what rows contribute to the output in the WHERE-clause. You might need analytic functions if you were displaying rolling averages for several target dates on several output rows of the same query, especially if the periods overlapped. If you do need to do something like that, you can adapt the query above to use the analytic AVG.
    1,240 pointsBadges:
    report
  • smford
    What is the difference between CODE block on double dashes, --, 
    Versus pre tags <pre> these are double dashes -- </pre>
    20 pointsBadges:
    report
  • smford
    Ok, what happens if I use an HTML specified code tag?? <code> These are double dashes, -- </code>
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following