Changes between Version 255 and Version 256 of DatabaseBasedAnalysis/Spectrum


Ignore:
Timestamp:
Dec 10, 2019, 3:22:24 PM (2 months ago)
Author:
tbretz
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • DatabaseBasedAnalysis/Spectrum

    v255 v256  
    403403=== Analyze Data ===
    404404
     405The 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).
     406
     407{{{#!Spoiler
     408{{{#!sql
     409WITH Table0 AS
     410(
     411   SELECT
     412      INTERVAL(LogEnergyEst, %6:bins)  AS  `.energy`,
     413      COUNT(IF(Weight>0, 1, NULL))     AS  `Signal`,
     414      COUNT(IF(Weight<0, 1, NULL))/5   AS  `Background`
     415   FROM
     416      Excess
     417   GROUP BY
     418      `.energy`
     419   ORDER BY
     420      `.energy`
     421)
     422SELECT
     423   *,
     424   `Signal` - `Background`        AS `Excess`,
     425   LiMa(`Signal`, `Background`)   AS `Significance`,
     426   ExcErr(`Signal`, `Background`) AS `ErrExcess`
     427FROM
     428   Table0
     429}}}
     430}}}
     431
     432`%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).
     433
    405434=== Analyze Monte Carlo Data ===
     435
     436Similarly to the analysis of Data, another query summarizes the !MonteCarlo Analysis.
     437
     438{{{#!Spoiler
     439{{{#!sql
     440WITH Table0 AS
     441(
     442   SELECT
     443      Weight,
     444      INTERVAL(Zd,            %6:theta)        AS `.theta`,
     445      INTERVAL(LogEnergyEst,  %7:energyest)    AS `.energyest`,
     446      INTERVAL(log10(Energy), %8:energysim)    AS `.energysim`,
     447      (%9:spectrum)/pow(Energy, SpectralIndex) AS SpectralWeight,
     448      LogEnergyEst - log10(Energy) AS Residual
     449   FROM
     450      Excess
     451),
     452Table1 AS
     453(
     454   SELECT
     455      `.energyest`,
     456      `.energysim`,
     457
     458      -- Signal, Background, Excess
     459      SUM(  IF(Weight>0,     ZdWeight*SpectralWeight,       0)) OVER EnergyEst  AS  `SignalW`,
     460      SUM(  IF(Weight<0,     ZdWeight*SpectralWeight,       0)) OVER EnergyEst  AS  `BackgroundW`,
     461      SUM(  IF(Weight>0, POW(ErrZdWeight*SpectralWeight,2), 0)) OVER EnergyEst  AS  `SignalW2`,
     462      SUM(  IF(Weight<0, POW(ErrZdWeight*SpectralWeight,2), 0)) OVER EnergyEst  AS  `BackgroundW2`,
     463      COUNT(IF(Weight>0, 1, NULL))                              OVER EnergyEst  AS  `SignalN`,
     464      COUNT(IF(Weight<0, 1, NULL))                              OVER EnergyEst  AS  `BackgroundN`,
     465      -- SUM(  IF(Table9.Weight>0,     ThetaHist.Weight*spectrum/Energy,    0)) OVER EnergyEst  AS  `SignalW`,
     466      -- SUM(  IF(Table9.Weight<0,     ThetaHist.Weight*spectrum/Energy,    0)) OVER EnergyEst  AS  `BackgroundW`,
     467      -- SUM(  IF(Table9.Weight>0, POW(ThetaHist.Weight*spectrum/Energy,2), 0)) OVER EnergyEst  AS  `SignalW2`,
     468      -- SUM(  IF(Table9.Weight<0, POW(ThetaHist.Weight*spectrum/Energy,2), 0)) OVER EnergyEst  AS  `BackgroundW2`,
     469
     470      -- Threshold
     471      SUM(    Weight *    ZdWeight*SpectralWeight   ) OVER EnergySim  AS  `ThresholdW`,
     472      SUM(POW(Weight * ErrZdWeight*SpectralWeight,2)) OVER EnergySim  AS  `ThresholdW2`,
     473      SUM(    Weight                                ) OVER EnergySim  AS  `ThresholdN`,
     474      -- SUM(     Table9.Weight/Energy * ThetaHist.Weight*spectrum     ) OVER EnergySim  AS  `ThresholdW`,
     475      -- SUM( POW(Table9.Weight/Energy * ThetaHist.Weight*spectrum,2)  ) OVER EnergySim  AS  `ThresholdW2`,
     476      -- SUM(     Table9.Weight/Energy                                 ) OVER EnergySim  AS  `ThresholdN`
     477
     478      -- Estimators
     479      SUM(IF(Weight>0,                 ZdWeight*SpectralWeight,  0)) OVER EnergySim  AS  SimW,
     480      SUM(IF(Weight>0,     Residual*   ZdWeight*SpectralWeight,  0)) OVER EnergyEst  AS  EstSum,
     481      SUM(IF(Weight>0,     Residual*   ZdWeight*SpectralWeight,  0)) OVER EnergySim  AS  SimSum,
     482      SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight,  0)) OVER EnergyEst  AS  EstSum2,
     483      SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight,  0)) OVER EnergySim  AS  SimSum2,
     484
     485      -- Migration
     486      SUM(Weight * ZdWeight*SpectralWeight) OVER Migration  AS  `MigrationW`,
     487      SUM(Weight                          ) OVER Migration  AS  `MigrationN`
     488   FROM
     489      Table0
     490   INNER JOIN
     491      ThetaHist USING(`.theta`)
     492   WINDOW
     493      EnergyEst AS (PARTITION BY `.energyest`),
     494      EnergySim AS (PARTITION BY `.energysim`),
     495      Migration AS (PARTITION BY `.energysim`,`.energyest`)
     496)
     497SELECT DISTINCT
     498   *,
     499   `SignalW` - `BackgroundW`/5 AS `ExcessW`,
     500   `SignalN` - `BackgroundN`/5 AS `ExcessN`,
     501   ExcErr(`SignalW2`, `BackgroundW2`/5) AS `ErrExcessW`,
     502   ExcErr(`SignalN`,  `BackgroundN` /5) AS `ErrExcessN`,
     503   IF(SignalW=0, 0, EstSum / SignalW) AS BiasEst,
     504   IF(SimW   =0, 0, SimSum / SimW)    AS BiasSim,
     505   IF(SignalW=0, 0, SQRT(EstSum2/SignalW - POW(EstSum/SignalW, 2))) AS ResolutionEst,
     506   IF(SimW   =0, 0, SQRT(SimSum2/SimW    - POW(SimSum/SimW,    2))) AS ResolutionSim
     507FROM
     508   Table1
     509}}}
     510}}}
     511
     512The 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)`.
    406513
    407514=== Summarize Corsika Production ===