Changes between Version 10 and Version 11 of DatabaseBasedAnalysis/Examples

Timestamp:

08/15/18 09:44:50 (6 years ago)

Author:

tbretz

Comment:

--

DatabaseBasedAnalysis/Examples

@@ -411 +411 @@
 }
 }}}
+
+== Day-by-day light-curve ==
+
+{{{#!sql
+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
+                                        FileId,
+                                        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
+                                        fR750Cor>0.9e0*fR750Ref
+                                    AND
+                                        NumUsedPixels>5.5
+                                    AND
+                                        NumIslands<3.5
+                                    AND
+                                        Leakage1<0.1
+
+                                ) -- AS Table0
+
+                                SELECT -- 1
+                                    FileId, 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)*898-1535
+
+                            ) -- AS Table1
+
+                            SELECT -- 2
+                                FileId, Weight, CosDelta, SinDelta, DX, DY, M3Long, SlopeLong, Leakage1, WdivL,
+                                SQRT(DX*DX + DY*DY) AS Norm
+                            FROM
+                                Table1
+
+                        ) -- AS Table2
+
+                        SELECT -- 3
+                            FileId, 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
+
+                    ) -- AS Table3
+
+                    SELECT -- 4
+                        FileId, Weight, Leakage1, WdivL, LX,
+                        Norm          *0.0117193246260285378e0 AS Dist,
+                        M3Long   *Sign*0.0117193246260285378e0 AS M3L,
+                        SlopeLong*Sign/0.0117193246260285378e0 AS Slope
+                    FROM
+                        Table3
+
+                ) -- AS Table4
+
+                SELECT -- 5
+                    FileId, Weight, WdivL, Dist, LX, M3L, Slope,
+--                    1.39252e0 + 0.154247e0*Slope + 1.67972e0*(1-1/(1+4.86232e0*Leakage1)) AS Xi
+                    1.34e0 + 0.0755e0*Slope + 1.67972e0*(1-1/(1+4.86232e0*Leakage1)) AS Xi
+                FROM
+                    Table4
+
+            ) -- AS Table5
+
+            SELECT -- 6
+                FileId, Weight, Dist, LX,
+                IF (M3L<-0.07 || (Dist-0.5e0)*7.2e0-Slope<0, -Xi, Xi) * (1-WdivL) AS Disp
+            FROM
+                Table5
+
+        ) -- AS Table6
+
+        SELECT -- 7
+            FileId, Weight,                         -- cos(alpha) = sqrt(1-lx^2)
+            (Disp*Disp + Dist*Dist - 2*Disp*Dist*SQRT(1-LX*LX)) AS ThetaSq
+        FROM
+            Table6
+
+    ) -- AS Table8
+
+    SELECT -- 9
+        FileId,
+        COUNT(*)                       AS `Count`,
+        COUNT(IF(Weight>0, 1, NULL))   AS `Signal`,
+        COUNT(IF(Weight<0, 1, NULL))/5 AS `Background`
+    FROM
+        Table7
+    WHERE
+        ThetaSq<0.024336
+    GROUP BY
+        FileId
+
+) -- AS Table8
+
+SELECT -- 9
+    FileId,
+    20000000+FLOOR(FileId/1000)  AS fNight,
+    FileId%1000                  AS fRunID,
+    `Count`                      AS fNumRuns,
+    `Signal`                     AS fNumSigEvts,
+    `Background`                 AS fNumBgEvts,
+    `Signal` - `Background`      AS fNumExcEvts
+FROM
+    Table8
+}}}
+
+Note that this query can easily run 30min or more!