Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 25, 1997

Calculating Averages Within Arbitrary Groups

Question:
[Joe Celko's Production Report Puzzle]

We get a production report from work centers which has a date, center code and how many widgets were produced from each batch of raw materials sent to them that day. It looks like this:

    CREATE TABLE Production
        (center INTEGER NOT NULL,
        wkdate DATE NOT NULL,
        batchno INTEGER NOT NULL,
        widgets INTEGER NOT NULL,
        PRIMARY KEY (center, wkdate, batchno));
The boss comes in and says he wants to know the average number of widgets Produced by date and center. We say no problem and do it. The next day he comes back and wants the same data in groups of three. In other words, if on 1994 Feb 24 in center 42, we processed nine batches, the report will show the average number of widgets made from the first three batches, the second three batches and the last three batches.

Answer:
The first query is very straightforward:

    SELECT center, wkdate, COUNT(batchno), AVG(widgets)
        FROM Production
        GROUP BY center, wkdate;
We have to make some assumptions about the second query and decide how to handle daily work where the number of batches is not evenly divisible by three. I am throwing anyone on the border into the lower third; thus, eight batches would be in ordered thirds of 3, 3 and 2 batches each. We can assume the batches are numbered from 1 to (n) for each day's work and make life easier.

Using the CASE expression in SQL-92, we can find which third a batchno is in with a VIEW, thus:

    CREATE VIEW Prod3 (center, wkdate, third, widgets)
        AS SELECT center, wkdate,
            CASE WHEN batchno <= MAX(batchno)/3 THEN 1
            WHEN batchno > (2*MAX(batchno))/3 THEN 3
            ELSE 2
            END, widgets
        FROM Production;
If you do not have this in your SQL, then you might try something like this:
    CREATE VIEW Prod3 (center, wkdate, third, batchno, widgets)
        AS SELECT center, wkdate, 1, batchno, widgets
            FROM Production AS P1
            WHERE batchno <= (SELECT MAX(batchno)
                FROM Production AS P2
                WHERE P1.center = P2.center
                    AND P1.wkdate = P2.wkdate)
        UNION
        SELECT center, wkdate, 2, batchno, widgets
            FROM Production AS P1
            WHERE batchno > (SELECT MAX(batchno)
                FROM Production AS P2
                WHERE P1.center = P2.center
                    AND P1.wkdate = P2.wkdate)
            AND batchno <= (SELECT 2 * MAX(batchno)
                FROM Production AS P2
                WHERE P1.center = P2.center
                    AND P1.wkdate = P2.wkdate)
        UNION
        SELECT center, wkdate, 3, batchno, widgets
            FROM Production AS P1
            WHERE batchno > (SELECT 2 * MAX(batchno)
                FROM Production AS P2
                WHERE P1.center = P2.center
                    AND P1.wkdate = P2.wkdate);
Either way, we end up with the final query:
    SELECT center, wkdate, third, COUNT(batchno), AVG(widgets)
        FROM Prod3
        GROUP BY center, wkdate, third;

Puzzle provided courtesy of:
Joe Celko
71062.1056@compuserve.com

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date