| 1 | WITH Table0 AS
|
|---|
| 2 | (
|
|---|
| 3 | SELECT
|
|---|
| 4 | Weight, Energy, LogEnergyEst,
|
|---|
| 5 | INTERVAL(Theta, %107:theta) AS `.theta`,
|
|---|
| 6 | INTERVAL(LogEnergyEst, %108:sparse) AS `.sparse_est`,
|
|---|
| 7 | INTERVAL(LogEnergyEst, %109:dense) AS `.dense_est`,
|
|---|
| 8 | INTERVAL(LOG10(Energy), %108:sparse) AS `.sparse_sim`,
|
|---|
| 9 | INTERVAL(LOG10(Energy), %109:dense) AS `.dense_sim`,
|
|---|
| 10 |
|
|---|
| 11 | (%110:spectrum)/POW(Energy, SpectralIndex) AS SpectralWeight, -- FIXME: Is this correct for files with different Slopes?
|
|---|
| 12 | LogEnergyEst - log10(Energy) AS Residual
|
|---|
| 13 | FROM
|
|---|
| 14 | Excess
|
|---|
| 15 | -- Instead of using %%0:columns, we could join back with the data we need
|
|---|
| 16 | -- INNER JOIN
|
|---|
| 17 | -- factmc.EventsMC USING(FileId, EvtNumber, CorsikaNumReuse)
|
|---|
| 18 | -- INNER JOIN
|
|---|
| 19 | -- factmc.RunInfoMC USING(FIleId)
|
|---|
| 20 | )
|
|---|
| 21 | SELECT
|
|---|
| 22 | `.theta`,
|
|---|
| 23 | `.sparse_est`,
|
|---|
| 24 | `.sparse_sim`,
|
|---|
| 25 | `.dense_est`,
|
|---|
| 26 | `.dense_sim`,
|
|---|
| 27 |
|
|---|
| 28 | -- Without any weight applied
|
|---|
| 29 | COUNT(IF(Weight>0, 1, NULL)) AS SignalN,
|
|---|
| 30 | COUNT(IF(Weight<0, 1, NULL)) AS BackgroundN,
|
|---|
| 31 |
|
|---|
| 32 | -- Without ZdWeight applied
|
|---|
| 33 | /*
|
|---|
| 34 | SUM( IF(Weight>0, SpectralWeight, 0)) AS Signal,
|
|---|
| 35 | SUM( IF(Weight<0, SpectralWeight, 0)) AS Background,
|
|---|
| 36 | SUM( IF(Weight>0, POW(SpectralWeight,2), 0)) AS Signal2,
|
|---|
| 37 | SUM( IF(Weight<0, POW(SpectralWeight,2), 0)) AS Background2,
|
|---|
| 38 | */
|
|---|
| 39 |
|
|---|
| 40 | -- Binning in estimated energy: Signal, Background, Excess
|
|---|
| 41 | SUM( IF(Weight>0, ZdWeight*SpectralWeight, 0)) AS SignalW,
|
|---|
| 42 | SUM( IF(Weight<0, ZdWeight*SpectralWeight, 0)) AS BackgroundW,
|
|---|
| 43 | SUM( IF(Weight>0, POW(ErrZdWeight*SpectralWeight,2), 0)) AS SignalW2,
|
|---|
| 44 | SUM( IF(Weight<0, POW(ErrZdWeight*SpectralWeight,2), 0)) AS BackgroundW2,
|
|---|
| 45 |
|
|---|
| 46 | -- Energy Estimation
|
|---|
| 47 | SUM(IF(Weight>0, Residual* ZdWeight*SpectralWeight, 0)) AS ResidualW,
|
|---|
| 48 | SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight, 0)) AS ResidualW2,
|
|---|
| 49 |
|
|---|
| 50 | SUM(IF(Weight>0, Energy *ZdWeight*SpectralWeight, 0)) AS SumEnergySimW,
|
|---|
| 51 | SUM(IF(Weight>0, POW(10, LogEnergyEst)*ZdWeight*SpectralWeight, 0)) AS SumEnergyEstW
|
|---|
| 52 |
|
|---|
| 53 | FROM
|
|---|
| 54 | Table0
|
|---|
| 55 | INNER JOIN
|
|---|
| 56 | ThetaDist USING(`.theta`)
|
|---|
| 57 | GROUP BY
|
|---|
| 58 | `.theta`, `.sparse_est`, `.sparse_sim`, `.dense_est`, `.dense_sim`
|
|---|
