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.
