List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:September 18 2012 7:53pm
Subject:Re: getting certain rows from a group by
View as plain text  
On Tue, Sep 18, 2012 at 2:05 PM, Rick James <rjames@stripped> wrote:
> For single query...
>
> Plan A:
> Repeat the SELECT as once for each (MIN, AVG, etc) as a subquery in
>    WHERE bottom = ( SELECT MIN(bottom) ... )
>
> Plan B:
>    FROM ( SELECT @min = MIN(bottom),
>                  @max = MAX(bottom), ... )
>    JOIN data_cst mn  ON bottom = @min
>    JOIN data_cst mx  ON bottom = @max


Can you elaborate on this? I don't see how this will give me what I
need. I'm not looking for the min or max bottom - I already have that
- I'm looking for the row from each group that has the min and max
bottom.

> Plan C:
> Get rid of 3rd party packages that eventually get in your way instead of 'helping'.

It's not django that is getting in the way. django is a fantastic
framework for web development. It's just changing requirements. The
original developer (who no longer works here) created a few different
classes, and then based on the original requirements, chose to use the
one that supports a single query. That worked then, but may not work
now. There are other classes that allow multiple queries, but then you
have to parse the data and stuff it into the context that will be
picked up by the browser yourself. The single query class does that
for you. I may yet go that way, but I'm trying to avoid a big rewrite
if I can.

>> -----Original Message-----
>> From: Larry Martell [mailto:larry.martell@stripped]
>> Sent: Tuesday, September 18, 2012 4:57 AM
>> To: peter.brawley@stripped
>> Cc: mysql@stripped
>> Subject: Re: getting certain rows from a group by
>>
>> 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.
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