source: trunk/FACT++/spectrum/summary-est.sql@ 19900

WITH Theta AS  -- Get Total Observation Time
(
   SELECT SUM(OnTime) AS ObsTime FROM ThetaDist
),
Area AS -- Get total simulated area
(
   SELECT POW(MinImpactHi,2)*PI() AS Area FROM MonteCarloArea
),
Binning AS -- Get binning and calculate weights for each bin
(
   SELECT
      *,
      (POW(10,hi)-POW(10,lo))/1000  AS  Width,
      Area*ObsTime                  AS  AreaTime
   FROM
      BinningEnergy_%100:binning
   CROSS JOIN
      Theta, Area
),
Analyzed AS -- Summarizy data after cuts (analyzed) in bins
(
   SELECT
      `.%100:binning:_est`,

      SUM(SignalN)        AS  SignalN,
      SUM(SignalW)        AS  SignalW,
      SUM(SignalW2)       AS  SignalW2,

      SUM(BackgroundN)    AS  BackgroundN,
      SUM(BackgroundW)    AS  BackgroundW,
      SUM(BackgroundW2)   AS  BackgroundW2,

      SUM(ResidualW)      AS  ResidualW,
      SUM(ResidualW2)     AS  ResidualW2,

      SUM(SumEnergySimW)  AS  SumEnergySimW,
      SUM(SumEnergyEstW)  AS  SumEnergyEstW
   FROM
      AnalysisMC
   GROUP BY
      `.%100:binning:_est`
),
Integrated AS
(
   SELECT -- Return Result
      *,

      SignalN - BackgroundN/5                           AS  ExcessN,

      SQRT(SignalN + BackgroundN/25)                    AS  ErrExcessN,
      SQRT(SignalN)                                     AS  ErrSignalN,
      SQRT(BackgroundN)                                 AS  ErrBackgroundN,

      SignalW                          /Width/AreaTime  AS  SignalFluxW,
      BackgroundW/5                    /Width/AreaTime  AS  BackgroundFluxW,
      (SignalW - BackgroundW/5)        /Width/AreaTime  AS  ExcessFluxW,
      SQRT(SignalW2)                   /Width/AreaTime  AS  ErrSignalFluxW,
      SQRT(BackgroundW2)/5             /Width/AreaTime  AS  ErrBackgroundFluxW,
      SQRT(SignalW2 + BackgroundW2/25) /Width/AreaTime  AS  ErrExcessFluxW,

      SumEnergyEstW/SignalW                             AS  AvgEnergyEstW,
      SumEnergySimW/SignalW                             AS  AvgEnergySimW,

      IF(SignalW>0, ResidualW/SignalW,                                                             NULL)  AS  BiasW,
      IF(SignalW>0, ResidualW/SignalW*SQRT(ResidualW2/POW(ResidualW,2) + SignalW2/POW(SignalW,2)), NULL)  AS  ErrBiasW,
      IF(SignalW>0, SQRT(ResidualW2/SignalW - POW(ResidualW/SignalW, 2)),                          NULL)  AS  ResolutionW,

      (SUM(SignalW)       OVER Integral)                     AS  IntegralSignalW,
      (SUM(SignalW)       OVER Integral) / AreaTime          AS  IntegralSignalFluxW,
      (SUM(BackgroundW)   OVER Integral) / AreaTime          AS  IntegralBackgroundFluxW,
      (SUM(SignalW2)      OVER Integral) / POW(AreaTime, 2)  AS  IntegralSignalFluxW2,
      (SUM(BackgroundW2)  OVER Integral) / POW(AreaTime, 2)  AS  IntegralBackgroundFluxW2,

      (SUM(SumEnergySimW) OVER Integral)                     AS  IntegralEnergySimW,
      (SUM(SumEnergyEstW) OVER Integral)                     AS  IntegralEnergyEstW
   FROM
      Analyzed
   INNER JOIN
      Binning ON `.%100:binning:_est`=bin
   WINDOW
      Integral AS (ORDER BY `.%100:binning:_est` DESC)
)
SELECT
   *,

   IntegralSignalFluxW-IntegralBackgroundFluxW/5           AS  IntegralExcessFluxW,
   SQRT(IntegralSignalFluxW2+IntegralBackgroundFluxW2/25)  AS  ErrIntegralExcessFluxW,
   SQRT(IntegralSignalFluxW2)                              AS  ErrIntegralSignalFluxW,
   SQRT(IntegralBackgroundFluxW2)                          AS  ErrIntegralBackgroundFluxW,

   IntegralEnergyEstW/IntegralSignalW                      AS  AvgIntegralEnergyEstW,
   IntegralEnergySimW/IntegralSignalW                      AS  AvgIntegralEnergySimW

FROM
   Integrated