Changeset 19893 for trunk/FACT++/spectrum
- Timestamp:
- 12/14/19 19:37:50 (5 years ago)
- Location:
- trunk/FACT++/spectrum
- Files:
-
- 3 added
- 3 edited
-
analysis.sql (modified) (1 diff)
-
data.sql (modified) (1 diff)
-
simulation.sql (modified) (2 diffs)
-
spectrum.sql (added)
-
summary-est.sql (added)
-
summary-sim.sql (added)
Legend:
- Unmodified
- Added
- Removed
-
trunk/FACT++/spectrum/analysis.sql
r19882 r19893 8 8 - `LogEnergyEst` logarithm of estimated energy in GeV 9 9 10 In additon, all columns provided by the %0:columnclause must be10 In additon, all columns provided by the 100-clause must be 11 11 returned. (Note that you must not add a comma behind it) 12 12 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 17 19 18 20 *************************************************************************** */ -
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` 1 SELECT 2 INTERVAL(fZenithDistanceMean, %106:theta) AS `.theta`, 3 INTERVAL(LogEnergyEst, %107:sparse) AS `.sparse_est`, 9 4 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`, 18 8 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` 24 12 FROM 25 Table0 13 Excess 14 GROUP BY 15 `.theta`, `.sparse_est` 16 ORDER BY 17 `.theta`, `.sparse_est` -
trunk/FACT++/spectrum/simulation.sql
r19890 r19893 2 2 ( 3 3 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`, 9 10 11 (%109:spectrum)/POW(Energy, SpectralIndex) AS SpectralWeight, -- FIXME: Is this correct for files with different Slopes? 10 12 LogEnergyEst - log10(Energy) AS Residual 11 13 FROM … … 16 18 -- INNER JOIN 17 19 -- factmc.RunInfoMC USING(FIleId) 18 ), 20 ) 21 SELECT 22 `.theta`, 23 `.sparse_est`, 24 `.sparse_sim`, 25 `.dense_est`, 26 `.dense_sim`, 19 27 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, 25 31 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 */ 37 39 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, 45 45 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, 52 49 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 85 52 86 53 FROM 87 Table1 54 Table0 55 INNER JOIN 56 ThetaDist USING(`.theta`) 57 GROUP BY 58 `.theta`, `.sparse_est`, `.sparse_sim`, `.dense_est`, `.dense_sim`
Note:
See TracChangeset
for help on using the changeset viewer.
