Changeset 23229 for trunk/ippTools/share/dettool_tonormalizedstat.sql

Timestamp:

Mar 8, 2009, 4:50:46 PM (17 years ago)

Author:

eugene

Message:

merging changes from branches/eam_branches/eam_branch_20090303 (detrend normalization resequence, cleanup error state changes; SVN_VERSION from psbuild)

Location:

trunk

Files:

• . (modified) (1 prop)
• ippTools/share/dettool_tonormalizedstat.sql (modified) (1 diff)

trunk/ippTools/share/dettool_tonormalizedstat.sql

@@ -1 @@
--- select detRun.det_id (det_id)
--- select detRun.iteration
--- by:
--- find the current iteration bassed on det_id
--- find all exp_ids in the current det_id/iteration from detInputExp
--- sort to detInputExp.imfiles to find the largest value per det_id/iter
--- compare imfiles to the number of detStackedImfiles by class_id
--- and:
--- ???
--- det_id is not in detStackedImfile;
--- iteration is not in detStackedImfile;
-
-SELECT
-    det_id,
-    det_type,
-    iteration,
-    camera,
-    workdir,
-    class_id
-FROM
-    (SELECT DISTINCT
-        detRun.det_id,
-        detRun.det_type,
-        detRun.iteration,
-        detRun.workdir,
-        rawExp.camera,
-        detStackedImfile.class_id,
-        rawImfile.class_id as rawimfile_class_id
-    FROM detRun
-    JOIN detInputExp
-        ON detRun.det_id = detInputExp.det_id
-        AND detRun.iteration = detInputExp.iteration
-    JOIN rawExp
-        ON detInputExp.exp_id = rawExp.exp_id
-    JOIN rawImfile
-        ON rawExp.exp_id = rawImfile.exp_id
-    LEFT JOIN detStackedImfile
-        ON detInputExp.det_id = detStackedImfile.det_id
-        AND detInputExp.iteration = detStackedImfile.iteration
-        AND rawImfile.class_id = detStackedImfile.class_id
-    LEFT JOIN detNormalizedStatImfile
-        ON detStackedImfile.det_id = detNormalizedStatImfile.det_id
-        AND detStackedImfile.iteration = detNormalizedStatImfile.iteration
-        AND detStackedImfile.class_id = detNormalizedStatImfile.class_id
-    WHERE
-        detRun.state = 'run'
-        AND detRun.mode = 'master'
-        AND detNormalizedStatImfile.det_id IS NULL
-        AND detNormalizedStatImfile.iteration IS NULL
-        AND detNormalizedStatImfile.class_id IS NULL
-    GROUP BY
---        rawExp.exp_id,
-        detRun.iteration,
-        detRun.det_id
-    HAVING
-        COUNT(rawImfile.class_id) = COUNT(detStackedImfile.class_id)
-        AND SUM(detStackedImfile.fault) = 0
-    ) as tonormalizedstat
+-- a det_run + iteration is ready for normstat when: all detResidImfile entries for that iteration corresponding to all detProcessedImfile entries are available 
+SELECT 
+  detRun.det_id, 
+  detRun.det_type, 
+  detRun.iteration, 
+  detRun.camera, 
+  detRun.workdir, 
+  detProcessedImfile.class_id,
+  COUNT(detProcessedImfile.class_id),
+  COUNT(detResidImfile.class_id)
+FROM detRun
+JOIN detProcessedImfile
+    ON  detProcessedImfile.det_id    = detRun.det_id
+LEFT JOIN detResidImfile
+    ON  detResidImfile.det_id    = detProcessedImfile.det_id
+    AND detResidImfile.class_id  = detProcessedImfile.class_id
+    AND detResidImfile.iteration = detRun.iteration
+LEFT JOIN detNormalizedStatImfile
+    ON  detNormalizedStatImfile.det_id    = detRun.det_id    
+    AND detNormalizedStatImfile.iteration = detRun.iteration 
+    AND detNormalizedStatImfile.class_id  = detProcessedImfile.class_id
+WHERE
+    detRun.state = 'run'
+    AND detRun.mode = 'master'
+    AND detNormalizedStatImfile.det_id IS NULL
+    AND detNormalizedStatImfile.iteration IS NULL
+    AND detNormalizedStatImfile.class_id IS NULL
+GROUP BY
+    detRun.iteration,
+    detRun.det_id
+HAVING
+    COUNT(detProcessedImfile.class_id) = COUNT(detResidImfile.class_id)
+    AND SUM(detResidImfile.fault) = 0