| 1 | WITH 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 | )
|
|---|
| 24 | SELECT
|
|---|
| 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 |
|
|---|
| 58 | FROM
|
|---|
| 59 | Table0
|
|---|
| 60 | INNER JOIN
|
|---|
| 61 | ThetaDist USING(`.theta`)
|
|---|
| 62 | GROUP BY
|
|---|
| 63 | `.theta`, `.sparse_est`, `.sparse_sim`, `.dense_est`, `.dense_sim`, `.impact`
|
|---|
