Changeset 31511 for trunk/ippTools/share/laptool_exposures.sql

Timestamp:

May 10, 2011, 1:51:21 PM (15 years ago)

Author:

watersc1

Message:

Update laptool_definerun to not attempt to define two rows for the same exp_id. Speed up laptool_exposures, as well as add better quality handling for lap_science.pl

File:

• trunk/ippTools/share/laptool_exposures.sql (modified) (1 diff)

trunk/ippTools/share/laptool_exposures.sql

@@ -1 @@
-SELECT DISTINCT 
-    D.*,diffRun.state,
-    coalesce(CONVERT(sum(others.private),SIGNED),0) AS needs_remade 
---      0 AS needs_remade
-    FROM (
-  SELECT DISTINCT 
-      W.*,CONVERT(IFNULL(diff1.diff_id,diff2.diff_id),SIGNED) AS diff_id FROM (
-    SELECT DISTINCT
-        lap_id,lapRun.tess_id,projection_cell,filter,lapRun.state as lapRun_state, lapRun.registered, lapRun.fault, lapRun.label, lapRun.dist_group,
-        lapExp.exp_id,lapExp.chip_id,lapExp.pair_id,private,pairwise,active,lapExp.data_state,
-        chipRun.state as chipRun_state, 
-        coalesce(CONVERT(sum(chipProcessedImfile.fault),SIGNED),0) as chip_faults, 
-        coalesce(CONVERT(sum(chipProcessedImfile.quality),SIGNED),0) as chip_quality,
-        camRun.cam_id, camRun.state as camRun_state,   
-        coalesce(CONVERT(sum(camProcessedExp.fault),SIGNED),0) AS cam_faults, 
-        coalesce(CONVERT(sum(camProcessedExp.quality),SIGNED),0) AS cam_quality,
-        fakeRun.fake_id, fakeRun.state as fakeRun_state, 
-        coalesce(CONVERT(sum(fakeProcessedImfile.fault),SIGNED),0) as fake_faults,
-        warpRun.warp_id, warpRun.state as warpRun_state, 
-        coalesce(CONVERT(sum(warpSkyfile.fault),SIGNED),0) as warp_faults, 
-        coalesce(CONVERT(sum(warpSkyfile.quality),SIGNED),0) as warp_quality,
-        warpRun.magicked
-    FROM lapRun JOIN lapExp USING(lap_id)
-    LEFT JOIN chipRun USING(chip_id)
-    LEFT JOIN chipProcessedImfile USING(chip_id)
-    LEFT JOIN camRun USING(chip_id) LEFT JOIN camProcessedExp USING(cam_id)
-    LEFT JOIN fakeRun USING(cam_id) LEFT JOIN fakeProcessedImfile USING(fake_id)
-    LEFT JOIN warpRun USING(fake_id) LEFT JOIN warpSkyfile USING(warp_id)
-    WHERE    @WHERE@
-    AND (warpSkyfile.quality IS NULL OR
-         (warpSkyfile.quality != 8007      -- known cases where quality != 0, but everything's fine.
-          AND warpSkyfile.quality != 3006  -- known cases where quality != 0, but everything's fine.
-          ))
-    GROUP BY lap_id,exp_id
-    ) AS W
--- This was unreasonably slow in testing, so that's why I'm using a subquery here.
-  LEFT JOIN diffInputSkyfile AS diff1 ON (W.warp_id = diff1.warp1)
-  LEFT JOIN diffInputSkyfile AS diff2 ON (W.warp_id = diff2.warp2)
-) AS D
+select DISTINCT V3.*,
+       diffRun.diff_id,diffRun.state as diffRun_state,
+       coalesce(CONVERT(sum(diffSkyfile.quality != 0),SIGNED),0) AS diff_bad_quality,
+       coalesce(CONVERT(count(diffSkyfile.diff_id),SIGNED),0) AS diff_component_count
+       FROM
+( select V2.*,
+       warpRun.warp_id,warpRun.state as warpRun_state,
+       coalesce(CONVERT(sum(warpSkyfile.quality != 0),SIGNED),0) AS warp_bad_quality,
+       coalesce(CONVERT(count(warpSkyfile.warp_id),SIGNED),0) AS warp_component_count,
+       warpRun.magicked
+       FROM
+( select V1.*,
+       camRun.cam_id,camRun.state as camRun_state,
+       coalesce(CONVERT(sum(camProcessedExp.quality != 0),SIGNED),0) AS cam_bad_quality,
+       coalesce(CONVERT(count(camProcessedExp.cam_id),SIGNED),0) AS cam_component_count,
+       fakeRun.fake_id,fakeRun.state as fakeRun_state FROM
+( SELECT DISTINCT
+       lap_id,lapRun.tess_id,projection_cell,filter,lapRun.state as lapRun_state, lapRun.registered, lapRun.fault, lapRun.label, lapRun.dist_group,
+       lapExp.exp_id,lapExp.chip_id,lapExp.pair_id,private,pairwise,active,lapExp.data_state,
+       chipRun.state as chipRun_state,
+       coalesce(CONVERT(sum(chipProcessedImfile.quality != 0),SIGNED),0) AS chip_bad_quality,
+       coalesce(CONVERT(count(chipProcessedImfile.chip_id),SIGNED),0) AS chip_component_count
+       FROM lapRun JOIN lapExp USING(lap_id)
+       LEFT JOIN chipRun USING(chip_id) LEFT JOIN chipProcessedImfile USING(chip_id)
+WHERE @WHERE@ 
+       GROUP BY lap_id,exp_id
+       ) AS V1
+       LEFT JOIN camRun USING(chip_id) LEFT JOIN camProcessedExp USING(cam_id)
+       LEFT JOIN fakeRun USING(cam_id)
+       GROUP BY lap_id,exp_id
+  ) AS V2
+  LEFT JOIN warpRun USING(fake_id) LEFT JOIN warpSkyfile USING(warp_id)
+  GROUP BY lap_id,exp_id
+) AS V3
+LEFT JOIN
+  (SELECT DISTINCT diff_id,warp1,warp2 FROM diffInputSkyfile) AS DI ON
+  (DI.warp1 = warp_id OR DI.warp2 = warp_id)
 LEFT JOIN diffRun USING(diff_id)
-LEFT JOIN lapExp AS others ON (D.chip_id = others.chip_id AND D.lap_id != others.lap_id)
+LEFT JOIN diffSkyfile USING(diff_id)
 GROUP BY lap_id,exp_id