Changeset 19893


Ignore:
Timestamp:
12/14/19 19:37:50 (5 years ago)
Author:
tbretz
Message:
Updated queries, new query as resources
Location:
trunk/FACT++/spectrum
Files:
3 added
3 edited

Legend:

Unmodified
Added
Removed

  • trunk/FACT++/spectrum/analysis.sql

    r19882 r19893  
    88     - `LogEnergyEst` logarithm of estimated energy in GeV
    99
    10    In additon, all columns provided by the %0:column clause must be
     10   In additon, all columns provided by the 100-clause must be
    1111   returned. (Note that you must not add a comma behind it)
    1212
    13    %101:files      table containing the `FileId`s to analyze.
    14    %102:runinfo    table with the run info data
    15    %103:events     table with the image parameters
    16    %104:positions  table with the source positions in the camera
     13   100| %100:columns
     14   101| %101:files:: table containing the `FileId`s to analyze.
     15   102| %102:runinfo:: table with the run info data
     16   103| %103:events:: table with the image parameters
     17   104| %104:positions:: table with the source positions in the camera
     18   105| %105:estimator:: estimator for log10 energy
    1719
    1820*************************************************************************** */

  • trunk/FACT++/spectrum/data.sql

    r19882 r19893  
    1 WITH Table0 AS
    2 (
    3    SELECT
    4       INTERVAL(LogEnergyEst, %106:bins)  AS  `.energy`,
    5       COUNT(IF(Weight>0, 1, NULL))     AS  `Signal`,
    6       COUNT(IF(Weight<0, 1, NULL))/5   AS  `Background`
    7       -- SUM(IF(Weight>0, 1./log10(Size), 0)) AS  `Signal`,
    8       -- SUM(IF(Weight<0, 1./log10(Size), 0)) AS  `Background`
     1SELECT
     2   INTERVAL(fZenithDistanceMean, %106:theta)  AS `.theta`,
     3   INTERVAL(LogEnergyEst, %107:sparse)  AS  `.sparse_est`,
    94
    10       -- FIXME: Add excess vs theta
    11    FROM
    12       Excess
    13    GROUP BY
    14       `.energy`
    15    ORDER BY
    16       `.energy`
    17 )
     5   -- Signal and Background counts
     6   COUNT(IF(Weight>0, 1, NULL))  AS  `Signal`,
     7   COUNT(IF(Weight<0, 1, NULL))  AS  `Background`,
    188
    19 SELECT
    20    *,
    21    `Signal` - `Background`        AS `Excess`,
    22    LiMa(`Signal`, `Background`)   AS `Significance`,
    23    ExcErr(`Signal`, `Background`) AS `ErrExcess`
     9   -- Average Energy
     10   SUM(Weight*POW(10, LogEnergyEst)) AS `SumEnergyEst`,
     11   SUM(Weight)                       AS `SumW`
    2412FROM
    25    Table0
     13   Excess
     14GROUP BY
     15   `.theta`, `.sparse_est`
     16ORDER BY
     17   `.theta`, `.sparse_est`

  • trunk/FACT++/spectrum/simulation.sql

    r19890 r19893  
    22(
    33   SELECT
    4       Weight,
    5       INTERVAL(Zd,            %106:theta)        AS `.theta`,
    6       INTERVAL(LogEnergyEst,  %107:energyest)    AS `.energyest`,
    7       INTERVAL(log10(Energy), %108:energysim)    AS `.energysim`,
    8       (%109:spectrum)/pow(Energy, SpectralIndex) AS SpectralWeight,  -- FIXME: Is this correct for files with different Slopes?
     4      Weight, Energy, LogEnergyEst,
     5      INTERVAL(Zd, %106:theta)  AS `.theta`,
     6      INTERVAL(LogEnergyEst, %107:sparse)  AS `.sparse_est`,
     7      INTERVAL(LogEnergyEst, %108:dense)  AS `.dense_est`,
     8      INTERVAL(LOG10(Energy), %107:sparse)  AS `.sparse_sim`,
     9      INTERVAL(LOG10(Energy), %108:dense)  AS `.dense_sim`,
    910
     11      (%109:spectrum)/POW(Energy, SpectralIndex) AS SpectralWeight,  -- FIXME: Is this correct for files with different Slopes?
    1012      LogEnergyEst - log10(Energy) AS Residual
    1113   FROM
    … …  
    1618--   INNER JOIN
    1719--      factmc.RunInfoMC USING(FIleId)
    18 ),
     20)
     21SELECT
     22   `.theta`,
     23   `.sparse_est`,
     24   `.sparse_sim`,
     25   `.dense_est`,
     26   `.dense_sim`,
    1927
    20 Table1 AS
    21 (
    22    SELECT
    23       `.energyest`,
    24       `.energysim`,
     28   -- Without any weight applied
     29   COUNT(IF(Weight>0, 1, NULL))  AS  SignalN,
     30   COUNT(IF(Weight<0, 1, NULL))  AS  BackgroundN,
    2531
    26       -- Signal, Background, Excess
    27       SUM(  IF(Weight>0,     ZdWeight*SpectralWeight,       0)) OVER EnergyEst  AS  `SignalW`,
    28       SUM(  IF(Weight<0,     ZdWeight*SpectralWeight,       0)) OVER EnergyEst  AS  `BackgroundW`,
    29       SUM(  IF(Weight>0, POW(ErrZdWeight*SpectralWeight,2), 0)) OVER EnergyEst  AS  `SignalW2`,
    30       SUM(  IF(Weight<0, POW(ErrZdWeight*SpectralWeight,2), 0)) OVER EnergyEst  AS  `BackgroundW2`,
    31       COUNT(IF(Weight>0, 1, NULL))                              OVER EnergyEst  AS  `SignalN`,
    32       COUNT(IF(Weight<0, 1, NULL))                              OVER EnergyEst  AS  `BackgroundN`,
    33       -- SUM(  IF(Table9.Weight>0,     ThetaHist.Weight*spectrum/Energy,    0)) OVER EnergyEst  AS  `SignalW`,
    34       -- SUM(  IF(Table9.Weight<0,     ThetaHist.Weight*spectrum/Energy,    0)) OVER EnergyEst  AS  `BackgroundW`,
    35       -- SUM(  IF(Table9.Weight>0, POW(ThetaHist.Weight*spectrum/Energy,2), 0)) OVER EnergyEst  AS  `SignalW2`,
    36       -- SUM(  IF(Table9.Weight<0, POW(ThetaHist.Weight*spectrum/Energy,2), 0)) OVER EnergyEst  AS  `BackgroundW2`,
     32   -- Without ZdWeight applied
     33/*
     34   SUM(  IF(Weight>0,     SpectralWeight,    0))  AS  Signal,
     35   SUM(  IF(Weight<0,     SpectralWeight,    0))  AS  Background,
     36   SUM(  IF(Weight>0, POW(SpectralWeight,2), 0))  AS  Signal2,
     37   SUM(  IF(Weight<0, POW(SpectralWeight,2), 0))  AS  Background2,
     38*/
    3739
    38       -- Threshold
    39       SUM(    Weight *    ZdWeight*SpectralWeight   ) OVER EnergySim  AS  `ThresholdW`,
    40       SUM(POW(Weight * ErrZdWeight*SpectralWeight,2)) OVER EnergySim  AS  `ThresholdW2`,
    41       SUM(    Weight                                ) OVER EnergySim  AS  `ThresholdN`,
    42       -- SUM(     Table9.Weight/Energy * ThetaHist.Weight*spectrum     ) OVER EnergySim  AS  `ThresholdW`,
    43       -- SUM( POW(Table9.Weight/Energy * ThetaHist.Weight*spectrum,2)  ) OVER EnergySim  AS  `ThresholdW2`,
    44       -- SUM(     Table9.Weight/Energy                                 ) OVER EnergySim  AS  `ThresholdN`
     40   -- Binning in estimated energy: Signal, Background, Excess
     41   SUM(  IF(Weight>0,        ZdWeight*SpectralWeight,    0))  AS  SignalW,
     42   SUM(  IF(Weight<0,        ZdWeight*SpectralWeight,    0))  AS  BackgroundW,
     43   SUM(  IF(Weight>0, POW(ErrZdWeight*SpectralWeight,2), 0))  AS  SignalW2,
     44   SUM(  IF(Weight<0, POW(ErrZdWeight*SpectralWeight,2), 0))  AS  BackgroundW2,
    4545
    46       -- Estimators
    47       SUM(IF(Weight>0,                 ZdWeight*SpectralWeight,  0)) OVER EnergySim  AS  SimW,
    48       SUM(IF(Weight>0,     Residual*   ZdWeight*SpectralWeight,  0)) OVER EnergyEst  AS  EstSum,
    49       SUM(IF(Weight>0,     Residual*   ZdWeight*SpectralWeight,  0)) OVER EnergySim  AS  SimSum,
    50       SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight,  0)) OVER EnergyEst  AS  EstSum2,
    51       SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight,  0)) OVER EnergySim  AS  SimSum2,
     46   -- Energy Estimation
     47   SUM(IF(Weight>0,     Residual*   ZdWeight*SpectralWeight, 0))  AS  ResidualW,
     48   SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight, 0))  AS  ResidualW2,
    5249
    53       -- Migration
    54       SUM(Weight * ZdWeight*SpectralWeight) OVER Migration  AS  `MigrationW`,
    55       SUM(Weight                          ) OVER Migration  AS  `MigrationN`
    56 
    57       -- FIXME: Add ExcessN vs theta
    58       -- FIXME: Add ExcessW vs theta
    59 
    60    FROM
    61       Table0
    62    INNER JOIN
    63       ThetaDistribution USING(`.theta`)
    64    WINDOW
    65       EnergyEst AS (PARTITION BY `.energyest`),
    66       EnergySim AS (PARTITION BY `.energysim`),
    67       Migration AS (PARTITION BY `.energysim`,`.energyest`)
    68 )
    69 
    70 SELECT DISTINCT
    71 
    72    *,
    73 
    74    `SignalW` - `BackgroundW`/5 AS `ExcessW`,
    75    `SignalN` - `BackgroundN`/5 AS `ExcessN`,
    76 
    77    ExcErr(`SignalW2`, `BackgroundW2`/5) AS `ErrExcessW`,
    78    ExcErr(`SignalN`,  `BackgroundN` /5) AS `ErrExcessN`,
    79 
    80    IF(SignalW=0, 0, EstSum / SignalW) AS BiasEst,  -- FIMXE: Is NULL better?
    81    IF(SimW   =0, 0, SimSum / SimW)    AS BiasSim,  -- FIMXE: Is NULL better?
    82 
    83    IF(SignalW=0, 0, SQRT(EstSum2/SignalW - POW(EstSum/SignalW, 2))) AS ResolutionEst,
    84    IF(SimW   =0, 0, SQRT(SimSum2/SimW    - POW(SimSum/SimW,    2))) AS ResolutionSim
     50   SUM(IF(Weight>0, Energy               *ZdWeight*SpectralWeight, 0))  AS  SumEnergySimW,
     51   SUM(IF(Weight>0, POW(10, LogEnergyEst)*ZdWeight*SpectralWeight, 0))  AS  SumEnergyEstW
    8552
    8653FROM
    87    Table1
     54   Table0
     55INNER JOIN
     56   ThetaDist USING(`.theta`)
     57GROUP BY
     58   `.theta`, `.sparse_est`, `.sparse_sim`, `.dense_est`, `.dense_sim`
Note: See TracChangeset for help on using the changeset viewer.