On Tue, Sep 18, 2012 at 7:56 AM, Larry Martell <larry.mart...@gmail.com> wrote: > On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley > <peter.braw...@earthlink.net> 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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
