Changes between Version 112 and Version 113 of DatabaseBasedAnalysis

Timestamp:

11/28/19 12:59:36 (5 years ago)

Author:

tbretz

Comment:

--

DatabaseBasedAnalysis

@@ -1137 +1137 @@
 }}}
 
+Another way to write the same query is
+{{{#!spoiler
+{{{#!sql
+WITH 
+   Table0 AS
+   (
+      SELECT -- 0
+         Weight,
+         Size,
+         NumUsedPixels,
+         NumIslands,
+         Leakage1,
+         MeanX,
+         MeanY,
+         CosDelta,
+         SinDelta,
+         M3Long,
+         SlopeLong,
+         Width/Length      AS WdivL,
+         PI()*Width*Length AS Area,
+         cosa*X - sina*Y   AS PX,
+         cosa*Y + sina*X   AS PY
+      FROM RunInfo
+      LEFT JOIN Images   USING (FileId)
+      LEFT JOIN Position USING (FileId, EvtNumber)
+      CROSS JOIN Wobble
+      WHERE
+          fSourceKey=5
+      AND
+          fRunTypeKey=1
+      AND
+          FileId BETWEEN 131101000 AND 131107000
+      AND
+          fZenithDistanceMax<35
+      AND
+          fR750Cor>0.9e0*fR750Ref
+      AND
+          NumUsedPixels>5.5
+      AND
+          NumIslands<3.5
+      AND
+          Leakage1<0.1
+   ),
+
+   Table1 AS 
+   (
+      SELECT
+          Weight, CosDelta, SinDelta, M3Long, SlopeLong, Leakage1, WdivL,
+          MeanX - PX/1.02e0 AS DX,
+          MeanY - PY/1.02e0 AS DY
+      FROM
+          Table0
+      WHERE
+          Area < LOG10(Size)*898e0-1535e0
+   ),
+
+   Table2 AS
+   (
+      SELECT
+          Weight, CosDelta, SinDelta, DX, DY, M3Long, SlopeLong, Leakage1, WdivL,
+          SQRT(DX*DX + DY*DY) AS Norm
+      FROM
+          Table1
+   ),
+
+   Table3 AS
+   (
+      SELECT
+           Weight, M3Long, SlopeLong, Leakage1, WdivL, Norm,
+           LEAST(GREATEST((CosDelta*DY - SinDelta*DX)/Norm, -1), 1) AS LX,
+           SIGN(CosDelta*DX + SinDelta*DY) AS Sign
+      FROM
+           Table2
+   ),
+
+   Table5 AS 
+   (
+       SELECT
+           Weight, Leakage1, WdivL, LX,
+           Norm          *0.0117193246260285378e0 AS Dist,
+           M3Long   *Sign*0.0117193246260285378e0 AS M3L,
+           SlopeLong*Sign/0.0117193246260285378e0 AS Slope
+       FROM
+           Table3
+   ),
+
+   Table6 AS
+   (
+       SELECT
+           Weight, WdivL, Dist, LX, M3L, Slope,
+           1.39252e0 + 0.154247e0*Slope + 1.67972e0*(1-1/(1+4.86232e0*Leakage1)) AS Xi
+       FROM
+           Table5
+   ),
+
+   Table7 AS
+   (
+       SELECT
+           Weight, Dist, LX,
+           IF (M3L<-0.07 || (Dist-0.5e0)*7.2e0-Slope<0, -Xi, Xi) * (1-WdivL) AS Disp
+       FROM
+           Table6
+   ),
+
+   Table8 AS
+   (
+        SELECT
+            Weight,
+            (Disp*Disp + Dist*Dist - 2*Disp*Dist*SQRT(1-LX*LX)) AS ThetaSq
+        FROM
+            Table7
+   ),
+   
+   Table9 AS
+   (
+      SELECT -- 9
+         COUNT(IF(Weight>0, 1, NULL))   AS `Signal`,
+         COUNT(IF(Weight<0, 1, NULL))/5 AS `Background`
+      FROM
+         Table8
+      WHERE
+         ThetaSq<0.024
+   )
+
+SELECT
+    *,
+    `Signal` - `Background`      AS `Excess`,
+    LiMa(`Signal`, `Background`) AS `Significance`
+FROM
+    Table9
+
+}}}
+
+}}}
+
 Almost as fast as the 4.5s with C++ where the C++ compiler optimizes the maths. The difference is most likely overhead for example from the SQL optimizer.