source: trunk/FACT++/spectrum/spectrum.sql

Last change on this file was 19907, checked in by tbretz, 5 years ago
Fixed Error calculation for final spectrum, it is difficult to keep usage of the zenith angle consistent. To avoid strange features in the plots, the best is to use the telescope pointing for data and correlate it with the particle direction for simulations.
File size: 4.5 KB
Line 
1WITH BinnedData AS -- Bin data and create sums per bin
2(
3 SELECT
4 %101:bin,
5
6 SUM(`Signal`) AS `Signal`,
7 SUM(`Background`)/5 AS `Background`,
8 SUM(SumEnergyEst) AS SumEnergyEst,
9 SUM(SumW) AS SumW
10 FROM
11 AnalysisData
12 GROUP BY
13 %101:bin
14),
15Data AS -- Integral is a placeholder for the bin and all following bins
16(
17 SELECT
18 *,
19 SUM(`Signal`) OVER Integral AS SignalI,
20 SUM(`Background`) OVER Integral AS BackgroundI,
21 SUM(SumEnergyEst) OVER Integral AS EnergyEstI,
22 SUM(SumW) OVER Integral AS SumWI
23 FROM
24 BinnedData
25 WINDOW
26 Integral AS (ORDER BY %101:bin DESC)
27),
28CombinedData AS -- Joind data together and for conveninece (easier reading of the query) rename the columns
29(
30 SELECT
31 %102:id.%101:bin,
32 %102:id.lo,
33 %102:id.hi,
34 (%102:id.lo+%102:id.hi)/2 AS center,
35 %102:id.Width,
36 %102:id.AreaTime,
37
38 `Signal`,
39 `Background`,
40
41 SignalI,
42 BackgroundI,
43
44 `Signal` - `Background` AS Excess,
45 SQRT(`Signal`) AS ErrSignal,
46 SQRT(`Background`/5) AS ErrBackground,
47 ExcErr(`Signal`, `Background`) AS ErrExcess,
48 LiMa( `Signal`, `Background`) AS Significance,
49
50 SignalI - BackgroundI AS ExcessI,
51 SQRT(SignalI) AS ErrSignalI,
52 SQRT(BackgroundI/5) AS ErrBackgroundI,
53 ExcErr(SignalI, BackgroundI) AS ErrExcessI,
54 LiMa( SignalI, BackgroundI) AS SignificanceI,
55
56 SumEnergyEst/SumW AS AvgEnergyEst,
57 EnergyEstI/SumWI AS AvgEnergyEstI,
58
59 %102:id.ExcessFluxW AS SimExcess,
60 %102:id.ErrExcessFluxW AS ErrSimExcess,
61 %102:id.IntegralExcessFluxW AS SimExcessI,
62 %102:id.ErrIntegralExcessFluxW AS ErrSimExcessI,
63
64 -- For flux-vs-theta: Correction for already applied ZdWeights
65 Sim.SimFluxW/%103:weight AS SimFluxW,
66 Sim.ErrSimFluxW/%104:errweight AS ErrSimFluxW,
67 Sim.IntegralSimFluxW/%103:weight AS SimFluxI,
68 Sim.ErrIntegralSimFluxW/%104:errweight AS ErrSimFluxI
69 FROM
70 Data
71 INNER JOIN
72 %105:join1
73 INNER JOIN
74 %106:join2
75),
76Flux AS
77(
78 SELECT -- Calculate Flux and Relative Errors
79 *,
80
81 -- Differential Spectrum
82
83 SimExcess/SimFluxW AS Efficiency,
84
85 Excess/SimExcess/Width/AreaTime AS ExcessRatio,
86 SQRT(
87 + POW(ErrExcess / Excess, 2)
88 + POW(ErrSimExcess / SimExcess, 2)
89 ) AS RelErrExcessRatio,
90
91
92 Excess/SimExcess*SimFluxW/Width/AreaTime AS Flux,
93 SQRT(
94 + POW(ErrExcess / Excess, 2)
95 + POW(ErrSimExcess / SimExcess, 2)
96 + POW(ErrSimFluxW / SimFluxW, 2)
97 ) AS RelErrFlux,
98
99 -- Integral Spectrum
100
101 SimExcessI/SimFluxI AS EfficiencyI,
102
103 ExcessI/SimExcessI/AreaTime AS ExcessRatioI,
104 SQRT(
105 + POW(ErrExcessI / ExcessI, 2)
106 + POW(ErrSimExcessI / SimExcessI, 2)
107 ) AS RelErrExcessRatioI,
108
109
110 ExcessI/SimExcessI*SimFluxI/AreaTime AS FluxI,
111 SQRT(
112 + POW(ErrExcessI / ExcessI, 2)
113 + POW(ErrSimExcessI / SimExcessI, 2)
114 + POW(ErrSimFluxI / SimFluxI, 2)
115 ) AS RelErrFluxI
116
117 FROM
118 CombinedData
119),
120Errors AS
121(
122 SELECT -- Calculate Inverse of Relative Errors (Sigma) and Absolute Errors
123 *,
124
125 IF(RelErrExcessRatio =0, NULL, 1/RelErrExcessRatio ) AS SigmaExcessRatio,
126 IF(RelErrExcessRatioI=0, NULL, 1/RelErrExcessRatioI) AS SigmaExcessRatioI,
127 IF(RelErrFlux =0, NULL, 1/RelErrFlux ) AS SigmaFlux,
128 IF(RelErrFluxI =0, NULL, 1/RelErrFluxI ) AS SigmaFluxI,
129
130 IF(Excess =0, ErrExcess /SimExcess /AreaTime/Width, ExcessRatio *RelErrExcessRatio ) AS ErrExcessRatio,
131 IF(ExcessI=0, ErrExcessI/SimExcessI /AreaTime, ExcessRatioI*RelErrExcessRatioI) AS ErrExcessRatioI,
132 IF(Excess =0, ErrExcess /Efficiency /AreaTime/Width, ABS(Flux) *RelErrFlux ) AS ErrFlux,
133 IF(ExcessI=0, ErrExcessI/EfficiencyI /AreaTime, ABS(FluxI) *RelErrFluxI ) AS ErrFluxI
134 FROM
135 Flux
136)
137SELECT
138 *,
139
140 -- Integrate Differential Spectrum
141
142 SUM(Flux*Width) OVER Integral AS IntegratedFlux,
143 SQRT(SUM(POW(ErrFlux*Width,2)) OVER Integral) AS ErrIntegratedFlux
144
145FROM
146 Errors
147WINDOW
148 Integral AS (ORDER BY %101:bin DESC)
149ORDER BY
150 %101:bin ASC
Note: See TracBrowser for help on using the repository browser.