Advanced SQL Programming Fall 2002
Take-home exercises, Nov. 25th
/*====================================================================*/
1. (Question about ranking students based on GPA)
I have to build a query which will rank all students in a single table:
ContactID FileName GPA
7 Morales, Sara 2.9333
8 Wallace, Ben 2.7667
6 Bruce, Tricia 3.2833
9 Davis, Heather 2.9333
This query should produce a recordset which should look like this:
ContactID FileName GPA RANKING
6 Bruce, Tricia 3.2833 1
9 Davis, Heather 2.9333 2
7 Morales, Sara 2.9333 2
8 Wallace, Ben 2.7667 3
but [with the query I am using] the ranking shows gaps (ranking #3
is missing):
ContactID FileName GPA RANKING
6 Bruce, Tricia 3.2833 1
9 Davis, Heather 2.9333 2
7 Morales, Sara 2.9333 2
8 Wallace, Ben 2.7667 4
Is there any way to implement this using a single sql string?
Query that was being used (in MS Access):
SELECT t1.ContactID, t1.FileName, t1.GPA,
(SELECT DISTINCT COUNT(GPA)
FROM GPARanking as t2
WHERE t2.GPA > t1.GPA) + 1 AS [RANKING]
FROM GPARanking as t1
ORDER BY t1.FileName, t1.GPA DESC
Proposed solutions:
==================
- Using MS SQL Server:
SELECT t1.ContactID, t1.FileName, t1.GPA,
(SELECT COUNT(DISTINCT(GPA))
FROM GPARanking as t2
WHERE t2.GPA > t1.GPA) + 1 AS [RANKING]
FROM GPARanking as t1
ORDER BY t1.FileName, t1.GPA DESC
- Using 2 queries (Access) or a view (Sybase, Oracle):
Save a DISTINCT query first:
qryUniqueGPA:
SELECT DISTINCT GPA
FROM GPARanking
Now, solve your problem according to spec:
SELECT t1.ContactID, t1.FileName, t1.GPA,
(SELECT COUNT(*)
FROM qryUniqueGPA as t2
WHERE t2.GPA > t1.GPA) + 1 AS [RANKING]
FROM GPARanking as t1
ORDER BY t1.FileName, t1.GPA DESC
/*===================================================================*/
2. (Question about "moving" or "rolling" average)
I am trying to make a Moving average in a query, but I can't figure how
to do it.
Tables name: Item_Stocks
Field name: stocks.Item_Stocks
Query name: Stocks_MA
I want this moving average to be set on a 5 days period.
The formula for a moving average is:
1st record: (S1+S2+...+S8) / 8
2nd record: (S2+S3+...+S9) / 8
...
Nth record: (Sn+Sn+1...+Sn+7) / 8
So far the best I could get is:
Query1 (NOTE: For Sybase, think "view" and devise a way to find "top
8".)
Select 8 records
SELECT TOP 8 [Item_Stocks].[Item_Stocks] AS [8_Records]
FROM Item_Stocks;
Query2 (NOTE: distinctrow = distinct)
SELECT DISTINCTROW Avg([Query1].[8_Records]) AS [MyAvg]
FROM Query1;
a. Rewrite the two queries so that they will work in Sybase.
b. Do the entire calculation in one query.
Sample data provided later by user:
==================================
>Here is an example:
>DATE STOCK
>Monday Jan 1,2000 5
>Tuesday Jan 2,2000 7
>Wednesday Jan 3,2000 5
>Thursday Jan 4,2000 6
>Friday Jan 5,2000 2
>Monday Jan 8,2000 3
>Tuesday Jan 9,2000 4
>Wednseday Jan 10,2000 5
>The average will be 4.625
>and then
>
>Thursday Jan 11,2000 9
>Friday Jan 12,2000 7
>Friday Jan 19,2000 5
>Monday Jan 22,2000 2
>Tuesday Jan 23,2000 10
>Thursday Jan 25,2000 6
>Friday Jan 26,2000 8
>Monday Jan 29,2000 8
>The average will be 6.875
Simplest Sybase solution for "top n" rows:
=========================================
set rowcount n
go
E.g., for first 8 prices by date in titles (pubs2):
set rowcount 8
go
select t1.price, t1.pubdate,
(select count(*)
from titles t2
where t2.pubdate< t1.pubdate) +1 Rank
from titles t1
order by t1.pubdate
go
price pubdate Rank
------------------------ -------------------------- -----------
11.95 Jun 12 1985 12:00AM 1
2.99 Jun 18 1985 12:00AM 2
2.99 Jun 30 1985 12:00AM 3
19.99 Jun 12 1986 12:00AM 4
22.95 Jun 30 1986 12:00AM 5
14.99 Jun 12 1987 12:00AM 6
20.00 Jun 12 1987 12:00AM 6
19.99 Jun 22 1987 12:00AM 8
(8 rows affected)
*** Now turn rowcount off! ***
set rowcount 0
go
Solution to the two questions posed, in general terms:
=====================================================
a. Create a view with 8 rows and calculate the average
using the view.
b. Select average in "ranking" query using the last (8th)
date as a condition:
1> select avg(t1.price), t1.pubdate,
2> (select count(*)
3> from titles t2
4> where t2.pubdate < t1.pubdate) + 1
5> from titles t1
6> where t1.pubdate <= '6/22/1987'
7> order by t1.pubdate
8>
9> go
pubdate
------------------------ -------------------------- -----------
14.48 Jun 12 1985 12:00AM 1
14.48 Jun 18 1985 12:00AM 2
14.48 Jun 30 1985 12:00AM 3
14.48 Jun 12 1986 12:00AM 4
14.48 Jun 30 1986 12:00AM 5
14.48 Jun 12 1987 12:00AM 6
14.48 Jun 12 1987 12:00AM 6
14.48 Jun 22 1987 12:00AM 8
(8 rows affected)
*** Remember to turn rowcount off! ***
Solution proposed in the discussion group:
==========================================
Moving averages are normally continuous: ie. the average of the
eight records after Jan 12, then 8 after Jan 19, 8 after Jan 22,
etc.
One way to do this might be to apply a ranking query to your data
first, to get the following result set:
Rank StartDate Stock
1 Jan 1,2000 5
2 Jan 2,2000 7
3 Jan 3,2000 5
4 Jan 4,2000 6
5 Jan 5,2000 2
6 Jan 8,2000 3
7 Jan 9,2000 4
8 Jan 10,2000 5
The SQL to calculate the moving average would then be something
like:
SELECT StartDate,Stock,
(SELECT AVG(Stock) FROM MyQuery AS Q2
WHERE Q2.Rank > Q1.Rank
AND Q2.Rank < Q1.Rank + 8) AS MovingAverage
FROM MyQuery AS Q1
Which would generate the following results:
StartDate Stock MovingAverage
Jan 1,2000 5 4.25
Jan 2,2000 7 5.125 'Did these by hand
Jan 3,2000 5 5.125 'and might have got
Jan 4,2000 6 5.125 'them wrong!!!
Jan 5,2000 2 4.25
Jan 8,2000 3 5.675
Jan 9,2000 4 6.00
Jan 10,2000 5 5.375
Last response from original user:
================================
It works great, thanks,
Final solution
SELECT Q1.ID, Q1.DATE, Q1.STOCK, (SELECT AVG(STOCK) FROM THETABLE Q2
WHERE Q2.ID > Q1.ID-1
AND Q2.ID < Q1.ID-1 + (SELECT TOP 1 [P].[DELTA] FROM [P];);) AS MA
FROM THETABLE AS Q1;
Note:
- P.DELTA is a variable number for the moving average in the table P
- ID is the autonumber set by access as a primary key (set to 1 by default)
- I use Q1.ID-1 in order to have the moving average include the current day
(since the autonumber starts at 1)
/*=======================================================================*/