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
[email protected]

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job