| 1 | WITH Table0 AS
|
|---|
| 2 | (
|
|---|
| 3 | SELECT
|
|---|
| 4 | Weight,
|
|---|
| 5 | INTERVAL(Zd, %106:theta) AS `.theta`,
|
|---|
| 6 | INTERVAL(LogEnergyEst, %107:energyest) AS `.energyest`,
|
|---|
| 7 | INTERVAL(log10(Energy), %108:energysim) AS `.energysim`,
|
|---|
| 8 | (%109:spectrum)/pow(Energy, SpectralIndex) AS SpectralWeight, -- FIXME: Is this correct for files with different Slopes?
|
|---|
| 9 |
|
|---|
| 10 | LogEnergyEst - log10(Energy) AS Residual
|
|---|
| 11 | FROM
|
|---|
| 12 | Excess
|
|---|
| 13 | -- Instead of using %%0:columns, we could join back with the data we need
|
|---|
| 14 | -- INNER JOIN
|
|---|
| 15 | -- factmc.EventsMC USING(FileId, EvtNumber, CorsikaNumReuse)
|
|---|
| 16 | -- INNER JOIN
|
|---|
| 17 | -- factmc.RunInfoMC USING(FIleId)
|
|---|
| 18 | ),
|
|---|
| 19 |
|
|---|
| 20 | Table1 AS
|
|---|
| 21 | (
|
|---|
| 22 | SELECT
|
|---|
| 23 | `.energyest`,
|
|---|
| 24 | `.energysim`,
|
|---|
| 25 |
|
|---|
| 26 | -- Signal, Background, Excess
|
|---|
| 27 | SUM( IF(Weight>0, ZdWeight*SpectralWeight, 0)) OVER EnergyEst AS `SignalW`,
|
|---|
| 28 | SUM( IF(Weight<0, ZdWeight*SpectralWeight, 0)) OVER EnergyEst AS `BackgroundW`,
|
|---|
| 29 | SUM( IF(Weight>0, POW(ErrZdWeight*SpectralWeight,2), 0)) OVER EnergyEst AS `SignalW2`,
|
|---|
| 30 | SUM( IF(Weight<0, POW(ErrZdWeight*SpectralWeight,2), 0)) OVER EnergyEst AS `BackgroundW2`,
|
|---|
| 31 | COUNT(IF(Weight>0, 1, NULL)) OVER EnergyEst AS `SignalN`,
|
|---|
| 32 | COUNT(IF(Weight<0, 1, NULL)) OVER EnergyEst AS `BackgroundN`,
|
|---|
| 33 | -- SUM( IF(Table9.Weight>0, ThetaHist.Weight*spectrum/Energy, 0)) OVER EnergyEst AS `SignalW`,
|
|---|
| 34 | -- SUM( IF(Table9.Weight<0, ThetaHist.Weight*spectrum/Energy, 0)) OVER EnergyEst AS `BackgroundW`,
|
|---|
| 35 | -- SUM( IF(Table9.Weight>0, POW(ThetaHist.Weight*spectrum/Energy,2), 0)) OVER EnergyEst AS `SignalW2`,
|
|---|
| 36 | -- SUM( IF(Table9.Weight<0, POW(ThetaHist.Weight*spectrum/Energy,2), 0)) OVER EnergyEst AS `BackgroundW2`,
|
|---|
| 37 |
|
|---|
| 38 | -- Threshold
|
|---|
| 39 | SUM( Weight * ZdWeight*SpectralWeight ) OVER EnergySim AS `ThresholdW`,
|
|---|
| 40 | SUM(POW(Weight * ErrZdWeight*SpectralWeight,2)) OVER EnergySim AS `ThresholdW2`,
|
|---|
| 41 | SUM( Weight ) OVER EnergySim AS `ThresholdN`,
|
|---|
| 42 | -- SUM( Table9.Weight/Energy * ThetaHist.Weight*spectrum ) OVER EnergySim AS `ThresholdW`,
|
|---|
| 43 | -- SUM( POW(Table9.Weight/Energy * ThetaHist.Weight*spectrum,2) ) OVER EnergySim AS `ThresholdW2`,
|
|---|
| 44 | -- SUM( Table9.Weight/Energy ) OVER EnergySim AS `ThresholdN`
|
|---|
| 45 |
|
|---|
| 46 | -- Estimators
|
|---|
| 47 | SUM(IF(Weight>0, ZdWeight*SpectralWeight, 0)) OVER EnergySim AS SimW,
|
|---|
| 48 | SUM(IF(Weight>0, Residual* ZdWeight*SpectralWeight, 0)) OVER EnergyEst AS EstSum,
|
|---|
| 49 | SUM(IF(Weight>0, Residual* ZdWeight*SpectralWeight, 0)) OVER EnergySim AS SimSum,
|
|---|
| 50 | SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight, 0)) OVER EnergyEst AS EstSum2,
|
|---|
| 51 | SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight, 0)) OVER EnergySim AS SimSum2,
|
|---|
| 52 |
|
|---|
| 53 | -- Migration
|
|---|
| 54 | SUM(Weight * ZdWeight*SpectralWeight) OVER Migration AS `MigrationW`,
|
|---|
| 55 | SUM(Weight ) OVER Migration AS `MigrationN`
|
|---|
| 56 |
|
|---|
| 57 | -- FIXME: Add ExcessN vs theta
|
|---|
| 58 | -- FIXME: Add ExcessW vs theta
|
|---|
| 59 |
|
|---|
| 60 | FROM
|
|---|
| 61 | Table0
|
|---|
| 62 | INNER JOIN
|
|---|
| 63 | ThetaHist USING(`.theta`)
|
|---|
| 64 | WINDOW
|
|---|
| 65 | EnergyEst AS (PARTITION BY `.energyest`),
|
|---|
| 66 | EnergySim AS (PARTITION BY `.energysim`),
|
|---|
| 67 | Migration AS (PARTITION BY `.energysim`,`.energyest`)
|
|---|
| 68 | )
|
|---|
| 69 |
|
|---|
| 70 | SELECT DISTINCT
|
|---|
| 71 |
|
|---|
| 72 | *,
|
|---|
| 73 |
|
|---|
| 74 | `SignalW` - `BackgroundW`/5 AS `ExcessW`,
|
|---|
| 75 | `SignalN` - `BackgroundN`/5 AS `ExcessN`,
|
|---|
| 76 |
|
|---|
| 77 | ExcErr(`SignalW2`, `BackgroundW2`/5) AS `ErrExcessW`,
|
|---|
| 78 | ExcErr(`SignalN`, `BackgroundN` /5) AS `ErrExcessN`,
|
|---|
| 79 |
|
|---|
| 80 | IF(SignalW=0, 0, EstSum / SignalW) AS BiasEst, -- FIMXE: Is NULL better?
|
|---|
| 81 | IF(SimW =0, 0, SimSum / SimW) AS BiasSim, -- FIMXE: Is NULL better?
|
|---|
| 82 |
|
|---|
| 83 | IF(SignalW=0, 0, SQRT(EstSum2/SignalW - POW(EstSum/SignalW, 2))) AS ResolutionEst,
|
|---|
| 84 | IF(SimW =0, 0, SQRT(SimSum2/SimW - POW(SimSum/SimW, 2))) AS ResolutionSim
|
|---|
| 85 |
|
|---|
| 86 | FROM
|
|---|
| 87 | Table1
|
|---|
