source: trunk/FACT++/spectrum/analysis.sql@ 19897

Last change on this file since 19897 was 19893, checked in by tbretz, 5 years ago
Updated queries, new query as resources
File size: 3.1 KB
Line 
1/* ************************************************************************
2
3 This is the analysis query. It returns two columns for all
4 signal/background events.
5
6 - `Weight` (Positive for signal (typ. 1),
7 negative for background (typ. -0.2))
8 - `LogEnergyEst` logarithm of estimated energy in GeV
9
10 In additon, all columns provided by the 100-clause must be
11 returned. (Note that you must not add a comma behind it)
12
13 100| %100:columns
14 101| %101:files:: table containing the `FileId`s to analyze.
15 102| %102:runinfo:: table with the run info data
16 103| %103:events:: table with the image parameters
17 104| %104:positions:: table with the source positions in the camera
18 105| %105:estimator:: estimator for log10 energy
19
20*************************************************************************** */
21
22WITH Table0 AS
23(
24 SELECT
25 %100:columns -- this could be removed if we can join events via the same columns (without CorsikaNumResuse)
26 Weight,
27 Size,
28 NumUsedPixels,
29 NumIslands,
30 Leakage1,
31 MeanX,
32 MeanY,
33 CosDelta,
34 SinDelta,
35 M3Long,
36 SlopeLong,
37 Width/Length AS WdivL,
38 PI()*Width*Length AS Area,
39 cosa*X - sina*Y AS PX,
40 cosa*Y + sina*X AS PY
41 FROM
42 %101:files
43 LEFT JOIN
44 %102:runinfo USING (FileId)
45 LEFT JOIN
46 %103:events USING (FileId) -- This could be replaced by a user uploaded temporary table
47 LEFT JOIN
48 %104:positions USING (FileId, EvtNumber)
49 CROSS JOIN
50 Wobble
51 WHERE
52 NumUsedPixels>5.5
53 AND
54 NumIslands<3.5
55 AND
56 Leakage1<0.1
57),
58
59Table1 AS
60(
61 SELECT
62 %100:columns
63 Weight,
64 Size, CosDelta, SinDelta, M3Long, SlopeLong, Leakage1, WdivL,
65 MeanX - PX/1.02e0 AS DX,
66 MeanY - PY/1.02e0 AS DY
67 FROM
68 Table0
69 WHERE
70 Area < LOG10(Size)*898e0 - 1535e0
71),
72
73Table2 AS
74(
75 SELECT
76 %100:columns
77 Weight,
78 Size, CosDelta, SinDelta, DX, DY, M3Long, SlopeLong, Leakage1, WdivL,
79 SQRT(DX*DX + DY*DY) AS Norm
80 FROM
81 Table1
82),
83
84Table3 AS
85(
86 SELECT
87 %100:columns
88 Weight,
89 Size, M3Long, SlopeLong, Leakage1, WdivL, Norm,
90 LEAST(GREATEST((CosDelta*DY - SinDelta*DX)/Norm, -1), 1) AS LX,
91 SIGN(CosDelta*DX + SinDelta*DY) AS Sign
92 FROM
93 Table2
94),
95
96Table5 AS
97(
98 SELECT
99 %100:columns
100 Weight,
101 Size, Leakage1, WdivL, LX,
102 Norm *0.0117193246260285378e0 AS Dist,
103 M3Long *Sign*0.0117193246260285378e0 AS M3L,
104 SlopeLong*Sign/0.0117193246260285378e0 AS Slope
105 FROM
106 Table3
107),
108
109Table6 AS
110(
111 SELECT
112 %100:columns
113 Weight,
114 Size, WdivL, Dist, LX, M3L, Slope,
115 1.39252e0 + 0.154247e0*Slope + 1.67972e0*(1-1/(1+4.86232e0*Leakage1)) AS Xi
116 FROM
117 Table5
118),
119
120Table7 AS
121(
122 SELECT
123 %100:columns
124 Weight,
125 Size, Dist, LX,
126 IF (M3L<-0.07 OR (Dist-0.5e0)*7.2e0-Slope<0, -Xi, Xi) * (1-WdivL) AS Disp
127 FROM
128 Table6
129)
130
131SELECT
132 %100:columns
133 Weight,
134 (Disp*Disp + Dist*Dist - 2*Disp*Dist*SQRT(1-LX*LX)) AS ThetaSq,
135 %105:estimator AS LogEnergyEst
136FROM
137 Table7
138HAVING
139 ThetaSq<0.024
Note: See TracBrowser for help on using the repository browser.