List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:September 18 2012 9:56pm
Subject:Re: getting certain rows from a group by
View as plain text  
On Tue, Sep 18, 2012 at 4:01 PM, Rick James <rjames@stripped> wrote:
> SELECT mn.*
>        mx.*
>> >    FROM ( SELECT @min = MIN(bottom),
>> >                  @max = MAX(bottom), ... )
>> >    JOIN data_cst mn  ON bottom = @min
>> >    JOIN data_cst mx  ON bottom = @max
>
> That is, the FROM finds the value for the desired row.
> The JOIN then gets to the rest of the fields.
> Caveat:  If two rows have bottom = @min, it returns both.


Thanks a lot Rick! This was super helpful. I've got it working for 3
of the 4 cases - min(bottom), max(bottom), and max(date_time). But I
can't figure out how to work in the last case - where bottom is
closest to avg(bottom). In an individual query I can get it with an
order by, like this:

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`);

Any way to work that into another join?

Thanks!

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