List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:September 18 2012 7:52pm
Subject:Re: getting certain rows from a group by
View as plain text  
On Tue, Sep 18, 2012 at 7:56 AM, Larry Martell <larry.martell@stripped> wrote:
> On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley
> <peter.brawley@stripped> wrote:
>> On 2012-09-18 5:53 AM, Larry Martell wrote:
>>>
>>> I have this query:
>>>
>>> SELECT data_target.name as Target,
>>>         q1.ep as EP,
>>>         COUNT(*) as Wafers,
>>>         Lots,
>>>         SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns > 0 THEN reruns
>>> ELSE 0 END)) as 'Sites/Wafer',
>>>         MAX(LastRun) as "Last Run",
>>>         SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END) as Rerun,
>>>         COUNT(*)+SUM(CASE WHEN reruns > 0 THEN reruns ELSE 0 END) as
> Runs,
>>>         avgbottom as "Avg Bottom",
>>>         3*stdbottom as "3 Sig",
>>>         maxbottom as Max,
>>>         minbottom as Min,
>>>         SUM(numonep) as Count,
>>>         SUM(numonep) - SUM(numbottoms) as NAs,
>>>         100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as "%
>>> Success",
>>>         3*stdbottom/avgbottom as "3Sig/Avg",
>>>         AVG(avgbottom) as 'Wafer Avg',
>>>         AVG(Wafer3Sigma) as 'Wafer 3 Sigma',
>>>         AVG(Ranges) as 'Avg Range',
>>>         3*STD(Ranges) as '3Sig of Ranges',
>>>         MAX(Ranges) as 'Max Range',
>>>         MIN(Ranges) as 'Min Range',
>>>         (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer'
>>> FROM (SELECT target_name_id,
>>>               ep,
>>>               wafer_id,
>>>               COUNT(bottom) as numbottoms,
>>>               AVG(bottom) as avgbottom,
>>>               STD(bottom) as stdbottom,
>>>               MAX(bottom) as maxbottom,
>>>               MIN(bottom) as minbottom,
>>>               MAX(date_time) as "LastRun",
>>>               COUNT(*) as numonep,
>>>               COUNT(DISTINCT target_name_id, ep, lot_id,
>>> data_file_id)-1 as reruns,
>>>               COUNT(DISTINCT(lot_id)) as Lots,
>>>               3*STD(bottom) as Wafer3Sigma,
>>>               MAX(bottom) - MIN(bottom) as Ranges
>>>        FROM data_cst
>>>        WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44,
>>> 116, 117, 118, 119, 120, 121)
>>>        AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND
>>> '2010-03-04 23:59:59'
>>>        GROUP BY target_name_id, ep, wafer_id
>>>        HAVING count(*) < 999) q1,
>>>       data_target
>>> WHERE data_target.id = target_name_id
>>> GROUP BY q1.target_name_id, q1.ep;
>>>
>>>
>>> This works fine. But now I need to get a certain column
>>> (image_measurer_id) with each row returned that corresponds to the row
>>> from the group that has bottom = Min(bottom), bottom = Max(bottom),
>>> bottom closest to Avg(bottom), and bottom from the row where date_time
>>> = Max(date_time).
>>>
>>> Is this even possible from one query?
>>
>>
>> Might be, but what's the importance of doing it as one query?
>
> Because it's part of a django based web app, and the class that this
> is part of only supports having a single query. To use multiple
> queries will require a fairly major rewrite of the server side of that
> app.
>
>> I'd start by
>> saving this result to a temp table and developing the new query. When that's
>> running, see if you can to optimise a query built by replacing the reference
>> to the temp table with the original query.
>
> Thanks, I'll look into this.

So by creating a temp table, and then using that in subsequent
queries, I can get what I need. But trying to incorporate that into
original query will seem to require a bunch of hairy subqueries (and
there already is one in the original query).

After running the above query into a temp table, rollup, here are the
4 queries I came up with to get what I need:

SELECT rollup.Target, rollup.EP, rollup.`Avg Bottom`, data_cst.id,
data_cst.bottom
FROM data_cst, rollup, data_target
WHERE data_target.name = rollup.Target
AND data_cst.ep = rollup.EP
AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59'
AND data_target.id = data_cst.target_name_id
GROUP BY rollup.Target, rollup.EP
ORDER BY ABS(data_cst.bottom - rollup.`Avg Bottom`);

SELECT rollup.Target, rollup.EP, rollup.`Last Run`, data_cst.id,
data_cst.date_time
FROM data_cst, rollup, data_target
WHERE data_target.name = rollup.Target
AND data_cst.ep = rollup.EP
AND data_cst.date_time = rollup.`Last Run`
AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59'
AND data_target.id = data_cst.target_name_id;

SELECT rollup.Target, rollup.EP, rollup.min, data_cst.id
FROM data_cst, rollup, data_target
WHERE data_target.name = rollup.Target
AND data_cst.ep = rollup.EP
AND data_cst.bottom = rollup.min
AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59'
AND data_target.id = data_cst.target_name_id;

SELECT rollup.Target, rollup.EP, rollup.max, data_cst.id
FROM data_cst, rollup, data_target
WHERE data_target.name = rollup.Target
AND data_cst.ep = rollup.EP
AND data_cst.bottom = rollup.max
AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59'
AND data_target.id = data_cst.target_name_id;

What would be the best way to incorporate these into original query?
Thread
getting certain rows from a group byLarry Martell18 Sep
  • Re: getting certain rows from a group byPeter Brawley18 Sep
    • Re: getting certain rows from a group byLarry Martell18 Sep
      • RE: getting certain rows from a group byRick James18 Sep
        • Re: getting certain rows from a group byLarry Martell18 Sep
          • RE: getting certain rows from a group byRick James18 Sep
            • Re: getting certain rows from a group byLarry Martell18 Sep
              • RE: getting certain rows from a group byRick James18 Sep
                • Re: getting certain rows from a group byLarry Martell19 Sep
                  • Re: getting certain rows from a group byLarry Martell19 Sep
                    • Re: getting certain rows from a group byLarry Martell19 Sep
                      • RE: getting certain rows from a group byRick James19 Sep
                        • Re: getting certain rows from a group byLarry Martell20 Sep
      • Re: getting certain rows from a group byLarry Martell18 Sep
  • Re: getting certain rows from a group byhsv19 Sep
    • Re: getting certain rows from a group byLarry Martell19 Sep
      • Re: getting certain rows from a group byLarry Martell19 Sep
    • Re: getting certain rows from a group byLarry Martell19 Sep
      • Re: getting certain rows from a group byhsv19 Sep