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

Last change on this file was 19971, checked in by tbretz, 4 years ago
Added a binning in Impact Parameter for crosschecks
File size: 2.3 KB
Line 
1WITH Table0 AS
2(
3 SELECT
4 Weight, Energy, LogEnergyEst,
5
6 -- Convert variable to bin index
7 INTERVAL(Theta, %107:theta) AS `.theta`,
8 INTERVAL(LogEnergyEst, %108:sparse) AS `.sparse_est`,
9 INTERVAL(LogEnergyEst, %109:dense) AS `.dense_est`,
10 INTERVAL(LOG10(Energy), %108:sparse) AS `.sparse_sim`,
11 INTERVAL(LOG10(Energy), %109:dense) AS `.dense_sim`,
12 INTERVAL(Impact/100, %110:impact) AS `.impact`,
13
14 (%111:spectrum)/POW(Energy, SpectralIndex) AS SpectralWeight, -- FIXME: Is this correct for files with different Slopes?
15 LogEnergyEst - log10(Energy) AS Residual
16 FROM
17 Excess
18-- Instead of using %%0:columns, we could join back with the data we need
19-- INNER JOIN
20-- factmc.EventsMC USING(FileId, EvtNumber, CorsikaNumReuse)
21-- INNER JOIN
22-- factmc.RunInfoMC USING(FIleId)
23)
24SELECT
25 `.theta`,
26 `.sparse_est`,
27 `.sparse_sim`,
28 `.dense_est`,
29 `.dense_sim`,
30 `.impact`,
31
32 -- Without any weight applied
33 COUNT(IF(Weight>0, 1, NULL)) AS SignalN,
34 COUNT(IF(Weight<0, 1, NULL)) AS BackgroundN,
35
36 -- Without ZdWeight applied
37/*
38 SUM( IF(Weight>0, SpectralWeight, 0)) AS Signal,
39 SUM( IF(Weight<0, SpectralWeight, 0)) AS Background,
40 SUM( IF(Weight>0, POW(SpectralWeight,2), 0)) AS Signal2,
41 SUM( IF(Weight<0, POW(SpectralWeight,2), 0)) AS Background2,
42*/
43
44 -- Binning in estimated energy: Signal, Background
45 SUM( IF(Weight>0, ZdWeight*SpectralWeight, 0)) AS SignalW,
46 SUM( IF(Weight<0, ZdWeight*SpectralWeight, 0)) AS BackgroundW,
47 SUM( IF(Weight>0, POW(ErrZdWeight*SpectralWeight,2), 0)) AS SignalW2,
48 SUM( IF(Weight<0, POW(ErrZdWeight*SpectralWeight,2), 0)) AS BackgroundW2,
49
50 -- Energy Estimation: Bias=ResidualW/SignalW; Resolution=sigma(Bias)
51 SUM(IF(Weight>0, Residual* ZdWeight*SpectralWeight, 0)) AS ResidualW,
52 SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight, 0)) AS ResidualW2,
53
54 -- Average Energy: SumEnergyX/SignalW
55 SUM(IF(Weight>0, Energy *ZdWeight*SpectralWeight, 0)) AS SumEnergySimW,
56 SUM(IF(Weight>0, POW(10, LogEnergyEst)*ZdWeight*SpectralWeight, 0)) AS SumEnergyEstW
57
58FROM
59 Table0
60INNER JOIN
61 ThetaDist USING(`.theta`)
62GROUP BY
63 `.theta`, `.sparse_est`, `.sparse_sim`, `.dense_est`, `.dense_sim`, `.impact`
Note: See TracBrowser for help on using the repository browser.