Changes between Version 257 and Version 258 of DatabaseBasedAnalysis/Spectrum


Ignore:
Timestamp:
12/10/19 15:39:53 (6 years ago)
Author:
tbretz
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseBasedAnalysis/Spectrum

    v257 v258  
    549549The placeholder `%0:energyest` is the binnings (as used previously) for the logarithm (base 10) of the estimated energy. `%1:spectrum` is the (unknown) 'true' source spectrum, for example `POW(Energy, -2.4)`.
    550550
    551 
    552551=== Result (Spectrum) ===
    553552
     553This query combines the results from the data analysis (!AnalysisData), the !MonteCarlo analysis (!AnalysisMC) and the simulated data (!SimulatedSpectrum) to calculate the final result. The result is stored in a temporary table (`Spectrum`). For convenience, bin edged are joined as well.
     554
     555{{{#!Spoiler
     556{{{#!sql
     557CREATE TEMPORARY TABLE Spectrum
     558(
     559   `.energy`      SMALLINT UNSIGNED NOT NULL COMMENT 'Bin Index [Energy]' PRIMARY KEY,
     560   lo             DOUBLE            NOT NULL COMMENT 'Lower edge of energy bin in lg(E/GeV)',
     561   hi             DOUBLE            NOT NULL COMMENT 'Upper edge of energy bin in lg(E/GeV)',
     562   `Signal`       DOUBLE            NOT NULL COMMENT 'Number of signal events',
     563   `Background`   DOUBLE            NOT NULL COMMENT 'Average number of background events',
     564   `Excess`       DOUBLE            NOT NULL COMMENT 'Number of excess events',
     565   ErrSignal      DOUBLE            NOT NULL COMMENT 'Poisson error on number of signal events',
     566   ErrBackground  DOUBLE            NOT NULL COMMENT 'Poisson error on number of background events',
     567   `ErrExcess`    DOUBLE            NOT NULL COMMENT 'Error of excess events',
     568   `Significance` DOUBLE            NOT NULL COMMENT 'Li/Ma sigficance',
     569   `ExcessN`      DOUBLE            NOT NULL COMMENT 'Number of excess events in simulated data',
     570   `ExcessW`      DOUBLE            NOT NULL COMMENT 'Weighted number of excess events in simulated data',
     571   `ErrExcessN`   DOUBLE            NOT NULL COMMENT 'Error or number of excess events in simulated data',
     572   `ErrExcessW`   DOUBLE            NOT NULL COMMENT 'Error of weighted number of excess events in simulated data',
     573   SignalW        DOUBLE            NOT NULL COMMENT 'Weighted number of signal events in simulated data',
     574   BackgroundW    DOUBLE            NOT NULL COMMENT 'Weighted number of background events in simulated data',
     575   ErrSignalW     DOUBLE            NOT NULL COMMENT 'Error of weighted number of signal events in simulated data',
     576   ErrBackgroundW DOUBLE            NOT NULL COMMENT 'Error of weighted number of background events in simulated data',
     577   Flux           DOUBLE            NOT NULL COMMENT 'dN/dA/dt [m^-2 s-^1]',
     578   ErrFlux        DOUBLE            NOT NULL COMMENT 'dN/dA/dt [m^-2 s-^1]',
     579   Bias           DOUBLE            NOT NULL COMMENT 'Energy Bias, average residual in lg(E)',
     580   Resolution     DOUBLE            NOT NULL COMMENT 'Energy resolution, standard divation of residual in lg(E)',
     581   EfficiencyN    DOUBLE            NOT NULL COMMENT 'Simulated cut efficiency (weighted)',
     582   EfficiencyW    DOUBLE            NOT NULL COMMENT 'Simulated cut efficiency (unweighted)',
     583   ErrEfficiencyN DOUBLE            NOT NULL COMMENT 'Error of simulated cut efficiency (weighted)',
     584   ErrEfficiencyW DOUBLE            NOT NULL COMMENT 'Error of simulated cut efficiency (unweighted)'
     585) ENGINE=Memory
     586AS
     587(
     588   WITH ThetaSums AS
     589   (
     590      SELECT
     591         SUM(CountN) AS CountSim,
     592         SUM(OnTime) AS ObsTime
     593      FROM
     594         ThetaHist
     595   ),
     596   ResultMC AS
     597   (
     598      SELECT
     599         `.energyest`             AS `.energy`,
     600         ANY_VALUE(SignalW)       AS SignalW,
     601         ANY_VALUE(SignalW2)      AS SignalW2,
     602         ANY_VALUE(BackgroundW)   AS BackgroundW,
     603         ANY_VALUE(BackgroundW2)  AS BackgroundW2,
     604         ANY_VALUE(SignalN)       AS SignalN,
     605         ANY_VALUE(BackgroundN)   AS BackgroundN,
     606         ANY_VALUE(ExcessW)       AS ExcessW,
     607         ANY_VALUE(ExcessN)       AS ExcessN,
     608         ANY_VALUE(ErrExcessW)    AS ErrExcessW,
     609         ANY_VALUE(ErrExcessN)    AS ErrExcessN,
     610         ANY_VALUE(BiasEst)       AS Bias,
     611         ANY_VALUE(ResolutionEst) AS Resolution
     612      FROM
     613         AnalysisMC
     614      GROUP BY
     615         `.energy`
     616      ORDER BY
     617         `.energy`
     618   )
     619   SELECT
     620      `.energy`, lo, hi,
     621      `Signal`, `Background`/5 AS `Background`, `Excess`, `ErrExcess`, `Significance`,
     622      SQRT(`Signal`)         AS ErrSignal,
     623      SQRT(`SignalW2`)       AS ErrSignalW,
     624      SQRT(`Background`)/5   AS ErrBackground,
     625      SQRT(`BackgroundW2`)/5 AS ErrBackgroundW,
     626      ExcessN, ExcessW, ErrExcessN, ErrExcessW, SignalW, BackgroundW,
     627      AnalysisData.Excess/ResultMC.ExcessW*SimulatedSpectrum.CountW * 1000/(POW(10,hi)-POW(10,lo)) /(%0:area)/ObsTime / CountSim*ObsTime AS Flux,
     628      AnalysisData.Excess/ResultMC.ExcessW*SimulatedSpectrum.CountW * 1000/(POW(10,hi)-POW(10,lo)) /(%0:area)/ObsTime / CountSim*ObsTime
     629         * SQRT(
     630             + POW(AnalysisData.ErrExcess / AnalysisData.Excess, 2)
     631             + POW(ResultMC.ErrExcessW    / ResultMC.ExcessW,    2)
     632             + SimulatedSpectrum.CountW2  / POW(SimulatedSpectrum.CountW,2)
     633           ) AS ErrFlux,
     634      Bias,
     635      Resolution,
     636      ResultMC.ExcessW/SimulatedSpectrum.CountW * CountSim/ObsTime AS EfficiencyW,
     637      ResultMC.ExcessN/SimulatedSpectrum.CountN AS EfficiencyN,
     638      ( POW(ResultMC.ErrExcessW/ResultMC.ExcessW, 2) + POW(SQRT(SimulatedSpectrum.CountW2)/SimulatedSpectrum.CountW, 2) )
     639         * POW(ResultMC.ExcessW/SimulatedSpectrum.CountW, 2) * CountSim/ObsTime AS ErrEfficiencyW,
     640      ( POW(ResultMC.ErrExcessN, 2) + POW(ResultMC.ExcessN, 2)/SimulatedSpectrum.CountN)/POW(SimulatedSpectrum.CountN, 2) AS ErrEfficiencyN
     641   FROM
     642      AnalysisData
     643   INNER JOIN
     644      ResultMC USING(`.energy`)
     645   INNER JOIN
     646      SimulatedSpectrum USING(`.energy`)
     647   INNER JOIN
     648      BinningEnergyEst ON `.energy`=bin
     649   CROSS JOIN
     650      ThetaSums
     651   WHERE
     652      AnalysisData.Excess>0
     653   ORDER BY
     654      `.energy`
     655)
     656}}}
     657}}}
     658
     659`%0:area` is a placeholder for the maximum simulated area.
     660
    554661=== Result (Threshold) ===
    555662
     663Similar to the previous query, the following query summarized results based on the simulated spectrum in bins of the simulated energy not the estimated energy.
     664
     665{{{#!Spoiler
     666{{{#!sql
     667CREATE TEMPORARY TABLE Threshold ENGINE=Memory AS
     668(
     669   WITH
     670      ThetaSums AS
     671      (
     672         SELECT
     673            SUM(CountN) AS CountSim,
     674            SUM(OnTime) AS ObsTime
     675         FROM
     676            ThetaHist
     677      ),
     678      ResultMC AS
     679      (
     680         SELECT
     681            `.energysim`             AS `.energy`,
     682            ANY_VALUE(ThresholdW)    AS ThresholdW,
     683            ANY_VALUE(ThresholdW2)   AS ThresholdW2,
     684            ANY_VALUE(ThresholdN)    AS ThresholdN,
     685            ANY_VALUE(BiasSim)       AS Bias,
     686            ANY_VALUE(ResolutionSim) AS Resolution
     687         FROM
     688            AnalysisMC
     689         GROUP BY
     690            `.energy`
     691      )
     692   SELECT
     693      `.energy`, lo, hi,
     694      ThresholdW,
     695      SQRT(ThresholdW2) AS ErrThresholdW,
     696      ThresholdN,
     697      SQRT(ThresholdN)  AS ErrThresholdN,
     698      ThresholdW        * 1000/(POW(10,hi)-POW(10,lo)) / (%0:area) / CountSim*ObsTime AS Flux,
     699      SQRT(ThresholdW2) * 1000/(POW(10,hi)-POW(10,lo)) / (%0:area) / CountSim*ObsTime AS ErrFlux,
     700      Bias,
     701      Resolution
     702   FROM
     703      ResultMC
     704   INNER JOIN
     705      BinningEnergySim ON `.energy`=bin
     706   CROSS JOIN
     707      ThetaSums
     708   WHERE
     709      ThresholdW>0 AND ThresholdW2>0
     710   ORDER BY
     711      `.energy`
     712)
     713}}}
     714}}}
     715
     716`%0:area` is again a placeholder for the maximum simulated area.
     717
     718
    556719=== Result (Migration) ===
     720
     721Similar to the previous queries, this one extracts what is called the 'Migration Matrix' in bins of simulated and estimated energy.
     722
     723{{{#!Spoiler
     724{{{#!sql
     725CREATE TEMPORARY TABLE Migration ENGINE=Memory AS
     726(
     727   SELECT
     728      `.energyest`,
     729      `.energysim`,
     730      BinningEnergySim.lo   AS EsimLo,
     731      BinningEnergySim.hi   AS EsimHi,
     732      BinningEnergyEst.lo   AS EestLo,
     733      BinningEnergyEst.hi   AS EestHi,
     734      ANY_VALUE(MigrationW) AS MigrationW,
     735      ANY_VALUE(MigrationN) AS MigrationN
     736   FROM
     737      AnalysisMC
     738   INNER JOIN
     739      BinningEnergyEst ON `.energyest`=BinningEnergyEst.bin
     740   INNER JOIN
     741      BinningEnergySim ON `.energysim`=BinningEnergySim.bin
     742   GROUP BY
     743      `.energyest`, `.energysim`
     744   ORDER BY
     745      `.energyest`, `.energysim`
     746)
     747}}}
     748}}}