Changes between Version 87 and Version 88 of DatabaseBasedAnalysis

Timestamp:

08/07/18 13:42:06 (6 years ago)

Author:

tbretz

Comment:

--

DatabaseBasedAnalysis

@@ -810 +810 @@
 Conclusion: Wherever you use floating-point values in your query and you are not interested in a 100% machine independent result, write them as ''floating-point'' values by appending `e0` instead of writing them as an ''exact'' values. For integer values (numbers without fractional parts) this is not needed. They mix well with floating-point values and thus no internal conversion is required.
 }}}
+
+== CTE - Common Table Expressions ==
+
+Common table expression are explained in details at: https://dev.mysql.com/doc/refman/8.0/en/with.html. For our query, the advantage is that the maths in is listed in the order in which we want it to be executed, not in opposite order. (Note that CTE requires MySQL 8). Here is the query using common table expressions (`WITH`).
+
+{{{#!sql
+WITH Table9 AS
+(
+    WITH Table8 AS
+    (
+        WITH Table7 AS
+        (
+            WITH Table6 AS
+            ( 
+                WITH Table5 AS
+                (
+                    WITH Table4 AS
+                    (
+                        WITH Table3 AS
+                        (
+                            WITH Table2 AS
+                            (
+                                WITH Table1 AS
+                                (
+                                    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 Events   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.9*fR750Ref
+                                        AND
+                                            NumUsedPixels>5.5
+                                        AND
+                                            NumIslands<3.5
+                                        AND
+                                            Leakage1<0.1
+
+                                    ) -- AS Table0
+
+                                    SELECT -- 1
+                                        Weight, CosDelta, SinDelta, M3Long, SlopeLong, Leakage1, WdivL,
+                                        MeanX*1.025e0 - PX*1.0025e0 AS DX,
+                                        MeanY*1.025e0 - PY*1.0025e0 AS DY
+                                    FROM
+                                        Table0
+                                    WHERE
+                                        Area < LOG10(Size)*898-1535
+
+                                ) -- AS Table1
+
+                                SELECT -- 2
+                                    Weight, CosDelta, SinDelta, DX, DY, M3Long, SlopeLong, Leakage1, WdivL,
+                                    SQRT(DX*DX + DY*DY) AS Norm
+                                FROM
+                                    Table1
+
+                            ) -- AS Table2
+
+                            SELECT -- 3
+                                Weight, M3Long, SlopeLong, Leakage1, WdivL, Norm,
+                                TRUNCATE((CosDelta*DY - SinDelta*DX)/Norm, 6) AS LX,
+                                TRUNCATE((CosDelta*DX + SinDelta*DY)/Norm, 6) AS LY
+                            FROM
+                                Table2
+
+                        ) -- AS Table3
+
+                        SELECT -- 4
+                            Weight, M3Long, SlopeLong, Leakage1, WdivL, LX, Norm,
+                            ASIN(LX) AS Alpha,
+                            SIGN(LY) AS Sign
+                        FROM
+                            Table3
+
+                    ) -- AS Table4
+
+                    SELECT -- 5
+                        Weight, Leakage1, WdivL, LX,
+                        Norm          *0.0117193246260285378e0 AS Dist,
+                        M3Long   *Sign*0.0117193246260285378e0 AS M3L,
+                        SlopeLong*Sign/0.0117193246260285378e0 AS Slope
+                    FROM
+                        Table4
+
+                ) -- AS Table5
+
+                SELECT -- 6
+                    Weight, WdivL, Dist, LX,
+                    1.39252e0 + 0.154247e0*Slope + 1.67972e0*(1-1/(1+4.86232e0*Leakage1)) AS Xi,
+                    M3L+0.07e0             AS Sign1,
+                    (Dist-0.5e0)*7.2e0-Slope AS Sign2
+                FROM
+                    Table5
+
+            ) -- AS Table6
+
+            SELECT -- 7
+                Weight, Dist, LX,
+                IF (SIGN(Sign1)<0 || SIGN(Sign2)<0, -Xi, Xi) * (1-WdivL) AS Disp
+            FROM
+                Table6
+
+        ) -- AS Table7
+
+        SELECT -- 8
+            Weight,
+            (Disp*Disp + Dist*Dist - 2*Disp*Dist*SQRT(1-LX*LX)) AS ThetaSq
+        FROM
+            Table7
+
+    ) -- AS Table8
+
+    SELECT -- 9
+        COUNT(IF(Weight>0, 1, NULL)) AS `Signal`,
+        COUNT(IF(Weight<0, 1, NULL)) AS `Background`
+    FROM
+        Table8
+    WHERE
+        ThetaSq<0.024
+) -- AS Table9
+
+SELECT -- 10
+    *,
+    `Signal` - `Background`/5      AS `Excess`,
+    LiMa(`Signal`, `Background`/5) AS `Significance`
+FROM
+    Table9
+}}}