source: trunk/FACT++/spectrum/spectrum.sql@ 19900

WITH BinnedData AS -- Bin data and create sums per bin
(
   SELECT
      %101:bin,

      SUM(`Signal`)        AS  `Signal`,
      SUM(`Background`)/5  AS  `Background`,
      SUM(SumEnergyEst)    AS  SumEnergyEst,
      SUM(SumW)            AS  SumW
   FROM
      SummaryData
   GROUP BY
      %101:bin
),
Data AS -- Integral is a placeholder for the bin and all following bins
(
   SELECT
      *,
      SUM(`Signal`)     OVER Integral     AS  SignalI,
      SUM(`Background`) OVER Integral /5  AS  BackgroundI,
      SUM(SumEnergyEst) OVER Integral     AS  EnergyEstI,
      SUM(SumW)         OVER Integral     AS  SumWI
   FROM
      BinnedData
   WINDOW
      Integral AS (ORDER BY %101:bin DESC)
),
CombinedData AS -- Joind data together and for conveninece (easier reading of the query) rename the columns
(
   SELECT
      %102:id.%101:bin,
      %102:id.lo,
      %102:id.hi,
      (%102:id.lo+%102:id.hi)/2  AS  center,
      %102:id.Width,
      %102:id.AreaTime,

      `Signal`,
      `Background`,

      SignalI,
      BackgroundI,

      `Signal` - `Background`         AS  Excess,
      SQRT(`Signal`)                  AS  ErrSignal,
      SQRT(`Background`)              AS  ErrBackground,
      ExcErr(`Signal`, `Background`)  AS  ErrExcess,
      LiMa(  `Signal`, `Background`)  AS  Significance,

      SignalI - BackgroundI           AS  ExcessI,
      SQRT(SignalI)                   AS  ErrSignalI,
      SQRT(BackgroundI)               AS  ErrBackgroundI,
      ExcErr(SignalI, BackgroundI)    AS  ErrExcessI,
      LiMa(  SignalI, BackgroundI)    AS  SignificanceI,

      SumEnergyEst/SumW               AS  AvgEnergyEst,
      EnergyEstI/SumWI                AS  AvgEnergyEstI,

      %102:id.ExcessFluxW              AS  SimExcess,
      %102:id.ErrExcessFluxW           AS  ErrSimExcess,
      %102:id.IntegralExcessFluxW      AS  SimExcessI,
      %102:id.ErrIntegralExcessFluxW   AS  ErrSimExcessI,

      -- For flux-vs-theta: Correction for already applied ZdWeights
      Sim.SimFluxW/%103:weight  AS  SimFluxW,
      Sim.ErrSimFluxW/%104:errweight  AS  ErrSimFluxW,
      Sim.IntegralSimFluxW/%103:weight  AS  SimFluxI,
      Sim.ErrIntegralSimFluxW/%104:errweight  AS  ErrSimFluxI
   FROM
      Data
   INNER JOIN
      %105:join1
   INNER JOIN
      %106:join2
),
Spectrum AS
(
SELECT -- Return final result
   *,

   -- Differetial Spectrum

   SimExcess/SimFluxW  AS  Efficiency,

   Excess/SimExcess/Width/AreaTime  AS  ExcessRatio,
   Excess/SimExcess/Width/AreaTime
      * SQRT(
          + POW(ErrExcess    / Excess,    2)
          + POW(ErrSimExcess / SimExcess, 2)
        )  AS  ErrExcessRatio,


   Excess/SimExcess*SimFluxW/Width/AreaTime  AS  Flux,
   Excess/SimExcess*SimFluxW/Width/AreaTime
      * SQRT(
          + POW(ErrExcess    / Excess,    2)
          + POW(ErrSimExcess / SimExcess, 2)
          + POW(ErrSimFluxW  / SimFluxW,  2)
        )  AS  ErrFlux,

   -- Integral Spectrum

   SimExcessI/SimFluxI  AS  EfficiencyI,

   ExcessI/SimExcessI/AreaTime  AS  ExcessRatioI,
   ExcessI/SimExcessI/AreaTime
      * SQRT(
          + POW(ErrExcessI    / ExcessI,    2)
          + POW(ErrSimExcessI / SimExcessI, 2)
        )  AS  ErrExcessRatioI,


   ExcessI/SimExcessI*SimFluxI/AreaTime  AS  FluxI,
   ExcessI/SimExcessI*SimFluxI/AreaTime
      * SQRT(
          + POW(ErrExcessI    / ExcessI,    2)
          + POW(ErrSimExcessI / SimExcessI, 2)
          + POW(ErrSimFluxI   / SimFluxI,   2)
        )  AS  ErrFluxI

FROM
   CombinedData
WHERE
   Excess>0
ORDER BY
   %101:bin
)
SELECT
   *,

   -- Integrated Spectrum

   SUM(Flux*Width) OVER Integral AS IntegratedFlux,
   SQRT(SUM(POW(ErrFlux*Width,2)) OVER Integral) AS ErrIntegratedFlux

FROM
   Spectrum

WINDOW
   Integral AS (ORDER BY %101:bin DESC)