Changes between Version 255 and Version 256 of DatabaseBasedAnalysis/Spectrum

Timestamp:

12/10/19 15:22:24 (5 years ago)

Author:

tbretz

Comment:

--

DatabaseBasedAnalysis/Spectrum

@@ -403 +403 @@
 === Analyze Data ===
 
+The previous query is used to create a temporary table (Excess) with a common table expression (CTE). This table is referred to in the following query and produces a summary of the observation which is then stored in another temporary table (AnalysisData).
+
+{{{#!Spoiler
+{{{#!sql
+WITH Table0 AS
+(
+   SELECT
+      INTERVAL(LogEnergyEst, %6:bins)  AS  `.energy`,
+      COUNT(IF(Weight>0, 1, NULL))     AS  `Signal`,
+      COUNT(IF(Weight<0, 1, NULL))/5   AS  `Background`
+   FROM
+      Excess
+   GROUP BY
+      `.energy`
+   ORDER BY
+      `.energy`
+)
+SELECT
+   *,
+   `Signal` - `Background`        AS `Excess`,
+   LiMa(`Signal`, `Background`)   AS `Significance`,
+   ExcErr(`Signal`, `Background`) AS `ErrExcess`
+FROM
+   Table0
+}}}
+}}}
+
+`%6:bins` is a placeholder for a binning if log-energy, e.g. `2.5, 3.0, 3.5, 4.0, 4.5, 5.0` (five bins between 2.5 and 5.0 plus underflow and overflow).
+
 === Analyze Monte Carlo Data ===
+
+Similarly to the analysis of Data, another query summarizes the !MonteCarlo Analysis.
+
+{{{#!Spoiler
+{{{#!sql
+WITH Table0 AS
+(
+   SELECT
+      Weight,
+      INTERVAL(Zd,            %6:theta)        AS `.theta`,
+      INTERVAL(LogEnergyEst,  %7:energyest)    AS `.energyest`,
+      INTERVAL(log10(Energy), %8:energysim)    AS `.energysim`,
+      (%9:spectrum)/pow(Energy, SpectralIndex) AS SpectralWeight,
+      LogEnergyEst - log10(Energy) AS Residual
+   FROM
+      Excess
+),
+Table1 AS
+(
+   SELECT
+      `.energyest`,
+      `.energysim`,
+
+      -- Signal, Background, Excess
+      SUM(  IF(Weight>0,     ZdWeight*SpectralWeight,       0)) OVER EnergyEst  AS  `SignalW`,
+      SUM(  IF(Weight<0,     ZdWeight*SpectralWeight,       0)) OVER EnergyEst  AS  `BackgroundW`,
+      SUM(  IF(Weight>0, POW(ErrZdWeight*SpectralWeight,2), 0)) OVER EnergyEst  AS  `SignalW2`,
+      SUM(  IF(Weight<0, POW(ErrZdWeight*SpectralWeight,2), 0)) OVER EnergyEst  AS  `BackgroundW2`,
+      COUNT(IF(Weight>0, 1, NULL))                              OVER EnergyEst  AS  `SignalN`,
+      COUNT(IF(Weight<0, 1, NULL))                              OVER EnergyEst  AS  `BackgroundN`,
+      -- SUM(  IF(Table9.Weight>0,     ThetaHist.Weight*spectrum/Energy,    0)) OVER EnergyEst  AS  `SignalW`,
+      -- SUM(  IF(Table9.Weight<0,     ThetaHist.Weight*spectrum/Energy,    0)) OVER EnergyEst  AS  `BackgroundW`,
+      -- SUM(  IF(Table9.Weight>0, POW(ThetaHist.Weight*spectrum/Energy,2), 0)) OVER EnergyEst  AS  `SignalW2`,
+      -- SUM(  IF(Table9.Weight<0, POW(ThetaHist.Weight*spectrum/Energy,2), 0)) OVER EnergyEst  AS  `BackgroundW2`,
+
+      -- Threshold
+      SUM(    Weight *    ZdWeight*SpectralWeight   ) OVER EnergySim  AS  `ThresholdW`,
+      SUM(POW(Weight * ErrZdWeight*SpectralWeight,2)) OVER EnergySim  AS  `ThresholdW2`,
+      SUM(    Weight                                ) OVER EnergySim  AS  `ThresholdN`,
+      -- SUM(     Table9.Weight/Energy * ThetaHist.Weight*spectrum     ) OVER EnergySim  AS  `ThresholdW`,
+      -- SUM( POW(Table9.Weight/Energy * ThetaHist.Weight*spectrum,2)  ) OVER EnergySim  AS  `ThresholdW2`,
+      -- SUM(     Table9.Weight/Energy                                 ) OVER EnergySim  AS  `ThresholdN`
+
+      -- Estimators
+      SUM(IF(Weight>0,                 ZdWeight*SpectralWeight,  0)) OVER EnergySim  AS  SimW,
+      SUM(IF(Weight>0,     Residual*   ZdWeight*SpectralWeight,  0)) OVER EnergyEst  AS  EstSum,
+      SUM(IF(Weight>0,     Residual*   ZdWeight*SpectralWeight,  0)) OVER EnergySim  AS  SimSum,
+      SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight,  0)) OVER EnergyEst  AS  EstSum2,
+      SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight,  0)) OVER EnergySim  AS  SimSum2,
+
+      -- Migration
+      SUM(Weight * ZdWeight*SpectralWeight) OVER Migration  AS  `MigrationW`,
+      SUM(Weight                          ) OVER Migration  AS  `MigrationN`
+   FROM
+      Table0
+   INNER JOIN
+      ThetaHist USING(`.theta`)
+   WINDOW
+      EnergyEst AS (PARTITION BY `.energyest`),
+      EnergySim AS (PARTITION BY `.energysim`),
+      Migration AS (PARTITION BY `.energysim`,`.energyest`)
+)
+SELECT DISTINCT
+   *,
+   `SignalW` - `BackgroundW`/5 AS `ExcessW`,
+   `SignalN` - `BackgroundN`/5 AS `ExcessN`,
+   ExcErr(`SignalW2`, `BackgroundW2`/5) AS `ErrExcessW`,
+   ExcErr(`SignalN`,  `BackgroundN` /5) AS `ErrExcessN`,
+   IF(SignalW=0, 0, EstSum / SignalW) AS BiasEst,
+   IF(SimW   =0, 0, SimSum / SimW)    AS BiasSim,
+   IF(SignalW=0, 0, SQRT(EstSum2/SignalW - POW(EstSum/SignalW, 2))) AS ResolutionEst,
+   IF(SimW   =0, 0, SQRT(SimSum2/SimW    - POW(SimSum/SimW,    2))) AS ResolutionSim
+FROM
+   Table1
+}}}
+}}}
+
+The placeholders `%6:theta`, `%7:energyest` and `%8:energysim` are the binnings (as used previously) for the zenith angle and the logarithm (base 10) of the estimated and true energy. `%9:spectrum` is the (unknown) 'true' source spectrum, for example `POW(Energy, -2.4)`.
 
 === Summarize Corsika Production ===