| 1 | WITH BinnedData AS -- Bin data and create sums per bin
|
|---|
| 2 | (
|
|---|
| 3 | SELECT
|
|---|
| 4 | %101:bin,
|
|---|
| 5 | SUM(`Signal`) AS `Signal`,
|
|---|
| 6 | SUM(`Background`)/5 AS `Background`,
|
|---|
| 7 | SUM(SumEnergyEst) AS SumEnergyEst,
|
|---|
| 8 | SUM(SumW) AS SumW
|
|---|
| 9 | FROM
|
|---|
| 10 | SummaryData
|
|---|
| 11 | GROUP BY
|
|---|
| 12 | %101:bin
|
|---|
| 13 | ),
|
|---|
| 14 | ResultData AS -- Calculate results for all bins
|
|---|
| 15 | (
|
|---|
| 16 | SELECT
|
|---|
| 17 | *,
|
|---|
| 18 | SQRT(`Signal`) AS ErrSignal,
|
|---|
| 19 | SQRT(`Background`) AS ErrBackground,
|
|---|
| 20 | `Signal` - `Background` AS Excess,
|
|---|
| 21 | ExcErr(`Signal`, `Background`) AS ErrExcess,
|
|---|
| 22 | LiMa( `Signal`, `Background`) AS Significance
|
|---|
| 23 | FROM
|
|---|
| 24 | BinnedData
|
|---|
| 25 | ),
|
|---|
| 26 | CombinedData AS -- Joind data together and for conveninece (easier reading of the query) rename the columns
|
|---|
| 27 | (
|
|---|
| 28 | SELECT
|
|---|
| 29 | %102:id.%101:bin,
|
|---|
| 30 | %102:id.lo,
|
|---|
| 31 | %102:id.hi,
|
|---|
| 32 | (%102:id.lo+%102:id.hi)/2 AS center,
|
|---|
| 33 | %102:id.Scale,
|
|---|
| 34 | Data.Excess,
|
|---|
| 35 | Data.Signal,
|
|---|
| 36 | Data.Background,
|
|---|
| 37 | Data.ErrExcess,
|
|---|
| 38 | Data.ErrSignal,
|
|---|
| 39 | Data.ErrBackground,
|
|---|
| 40 | Significance,
|
|---|
| 41 | SumEnergyEst/SumW AS AvgEnergyEst,
|
|---|
| 42 | %102:id.ExcessFluxW AS SimExcess,
|
|---|
| 43 | %102:id.ErrExcessFluxW AS ErrSimExcess,
|
|---|
| 44 | Sim.SimFluxW/%103:weight AS SimFluxW, -- Possible correcton for already applied ZdWeights
|
|---|
| 45 | Sim.ErrSimFluxW/%104:errweight AS ErrSimFluxW -- Possible correcton for already applied ZdWeights
|
|---|
| 46 | FROM
|
|---|
| 47 | ResultData Data
|
|---|
| 48 | INNER JOIN
|
|---|
| 49 | %105:join1
|
|---|
| 50 | INNER JOIN
|
|---|
| 51 | %106:join2
|
|---|
| 52 | )
|
|---|
| 53 | SELECT -- Return final result
|
|---|
| 54 | *,
|
|---|
| 55 | SimExcess/SimFluxW AS Efficiency,
|
|---|
| 56 | Excess/SimExcess*Scale AS ExcessRatio,
|
|---|
| 57 | Excess/SimExcess*Scale
|
|---|
| 58 | * SQRT(
|
|---|
| 59 | + POW(ErrExcess / Excess, 2)
|
|---|
| 60 | + POW(ErrSimExcess / SimExcess, 2)
|
|---|
| 61 | ) AS ErrExcessRatio,
|
|---|
| 62 | Excess/SimExcess*SimFluxW*Scale AS Flux,
|
|---|
| 63 | Excess/SimExcess*SimFluxW*Scale
|
|---|
| 64 | * SQRT(
|
|---|
| 65 | + POW(ErrExcess / Excess, 2)
|
|---|
| 66 | + POW(ErrSimExcess / SimExcess, 2)
|
|---|
| 67 | + POW(ErrSimFluxW / SimFluxW, 2)
|
|---|
| 68 | ) AS ErrFlux
|
|---|
| 69 | FROM
|
|---|
| 70 | CombinedData
|
|---|
| 71 | WHERE
|
|---|
| 72 | Excess>0
|
|---|
| 73 | ORDER BY
|
|---|
| 74 | %101:bin
|
|---|
