| 279 | | {{{#!sql |
| 280 | | WITH Table0 AS |
| 281 | | ( |
| 282 | | SELECT |
| 283 | | %0:columns -- this could be removed if we can join events via the same columns (without CorsikaNumResuse) |
| 284 | | Weight, |
| 285 | | Size, |
| 286 | | NumUsedPixels, |
| 287 | | NumIslands, |
| 288 | | Leakage1, |
| 289 | | MeanX, |
| 290 | | MeanY, |
| 291 | | CosDelta, |
| 292 | | SinDelta, |
| 293 | | M3Long, |
| 294 | | SlopeLong, |
| 295 | | Width/Length AS WdivL, |
| 296 | | PI()*Width*Length AS Area, |
| 297 | | cosa*X - sina*Y AS PX, |
| 298 | | cosa*Y + sina*X AS PY |
| 299 | | FROM |
| 300 | | %1:files |
| 301 | | LEFT JOIN |
| 302 | | %2:runinfo USING (FileId) |
| 303 | | LEFT JOIN |
| 304 | | %3:events USING (FileId) -- This could be replaced by a user uploaded temporary table |
| 305 | | LEFT JOIN |
| 306 | | %4:positions USING (FileId, EvtNumber) |
| 307 | | CROSS JOIN |
| 308 | | Wobble |
| 309 | | WHERE |
| 310 | | NumUsedPixels>5.5 |
| 311 | | AND |
| 312 | | NumIslands<3.5 |
| 313 | | AND |
| 314 | | Leakage1<0.1 |
| 315 | | ), |
| 316 | | |
| 317 | | Table1 AS |
| 318 | | ( |
| 319 | | SELECT |
| 320 | | %0:columns |
| 321 | | Weight, |
| 322 | | Size, CosDelta, SinDelta, M3Long, SlopeLong, Leakage1, WdivL, |
| 323 | | MeanX - PX/1.02e0 AS DX, |
| 324 | | MeanY - PY/1.02e0 AS DY |
| 325 | | FROM |
| 326 | | Table0 |
| 327 | | WHERE |
| 328 | | Area < LOG10(Size)*898e0 - 1535e0 |
| 329 | | ), |
| 330 | | |
| 331 | | Table2 AS |
| 332 | | ( |
| 333 | | SELECT |
| 334 | | %0:columns |
| 335 | | Weight, |
| 336 | | Size, CosDelta, SinDelta, DX, DY, M3Long, SlopeLong, Leakage1, WdivL, |
| 337 | | SQRT(DX*DX + DY*DY) AS Norm |
| 338 | | FROM |
| 339 | | Table1 |
| 340 | | ), |
| 341 | | |
| 342 | | Table3 AS |
| 343 | | ( |
| 344 | | SELECT |
| 345 | | %0:columns |
| 346 | | Weight, |
| 347 | | Size, M3Long, SlopeLong, Leakage1, WdivL, Norm, |
| 348 | | LEAST(GREATEST((CosDelta*DY - SinDelta*DX)/Norm, -1), 1) AS LX, |
| 349 | | SIGN(CosDelta*DX + SinDelta*DY) AS Sign |
| 350 | | FROM |
| 351 | | Table2 |
| 352 | | ), |
| 353 | | |
| 354 | | Table5 AS |
| 355 | | ( |
| 356 | | SELECT |
| 357 | | %0:columns |
| 358 | | Weight, |
| 359 | | Size, Leakage1, WdivL, LX, |
| 360 | | Norm *0.0117193246260285378e0 AS Dist, |
| 361 | | M3Long *Sign*0.0117193246260285378e0 AS M3L, |
| 362 | | SlopeLong*Sign/0.0117193246260285378e0 AS Slope |
| 363 | | FROM |
| 364 | | Table3 |
| 365 | | ), |
| 366 | | |
| 367 | | Table6 AS |
| 368 | | ( |
| 369 | | SELECT |
| 370 | | %0:columns |
| 371 | | Weight, |
| 372 | | Size, WdivL, Dist, LX, M3L, Slope, |
| 373 | | 1.39252e0 + 0.154247e0*Slope + 1.67972e0*(1-1/(1+4.86232e0*Leakage1)) AS Xi |
| 374 | | FROM |
| 375 | | Table5 |
| 376 | | ), |
| 377 | | |
| 378 | | Table7 AS |
| 379 | | ( |
| 380 | | SELECT |
| 381 | | %0:columns |
| 382 | | Weight, |
| 383 | | Size, Dist, LX, |
| 384 | | IF (M3L<-0.07 OR (Dist-0.5e0)*7.2e0-Slope<0, -Xi, Xi) * (1-WdivL) AS Disp |
| 385 | | FROM |
| 386 | | Table6 |
| 387 | | ) |
| 388 | | |
| 389 | | SELECT |
| 390 | | %0:columns |
| 391 | | Weight, |
| 392 | | (Disp*Disp + Dist*Dist - 2*Disp*Dist*SQRT(1-LX*LX)) AS ThetaSq, |
| 393 | | %5:estimator AS LogEnergyEst |
| 394 | | FROM |
| 395 | | Table7 |
| 396 | | HAVING |
| 397 | | ThetaSq<0.024 |
| 398 | | }}} |
| | 279 | [[Include(source:trunk/FACT++/spectrum/analysis.sql)]] |
| 439 | | {{{#!sql |
| 440 | | WITH Table0 AS |
| 441 | | ( |
| 442 | | SELECT |
| 443 | | Weight, |
| 444 | | INTERVAL(Zd, %6:theta) AS `.theta`, |
| 445 | | INTERVAL(LogEnergyEst, %7:energyest) AS `.energyest`, |
| 446 | | INTERVAL(log10(Energy), %8:energysim) AS `.energysim`, |
| 447 | | (%9:spectrum)/pow(Energy, SpectralIndex) AS SpectralWeight, |
| 448 | | LogEnergyEst - log10(Energy) AS Residual |
| 449 | | FROM |
| 450 | | Excess |
| 451 | | ), |
| 452 | | Table1 AS |
| 453 | | ( |
| 454 | | SELECT |
| 455 | | `.energyest`, |
| 456 | | `.energysim`, |
| 457 | | |
| 458 | | -- Signal, Background, Excess |
| 459 | | SUM( IF(Weight>0, ZdWeight*SpectralWeight, 0)) OVER EnergyEst AS `SignalW`, |
| 460 | | SUM( IF(Weight<0, ZdWeight*SpectralWeight, 0)) OVER EnergyEst AS `BackgroundW`, |
| 461 | | SUM( IF(Weight>0, POW(ErrZdWeight*SpectralWeight,2), 0)) OVER EnergyEst AS `SignalW2`, |
| 462 | | SUM( IF(Weight<0, POW(ErrZdWeight*SpectralWeight,2), 0)) OVER EnergyEst AS `BackgroundW2`, |
| 463 | | COUNT(IF(Weight>0, 1, NULL)) OVER EnergyEst AS `SignalN`, |
| 464 | | COUNT(IF(Weight<0, 1, NULL)) OVER EnergyEst AS `BackgroundN`, |
| 465 | | |
| 466 | | -- Threshold |
| 467 | | SUM( Weight * ZdWeight*SpectralWeight ) OVER EnergySim AS `ThresholdW`, |
| 468 | | SUM(POW(Weight * ErrZdWeight*SpectralWeight,2)) OVER EnergySim AS `ThresholdW2`, |
| 469 | | SUM( Weight ) OVER EnergySim AS `ThresholdN`, |
| 470 | | |
| 471 | | -- Estimators |
| 472 | | SUM(IF(Weight>0, ZdWeight*SpectralWeight, 0)) OVER EnergySim AS SimW, |
| 473 | | SUM(IF(Weight>0, Residual* ZdWeight*SpectralWeight, 0)) OVER EnergyEst AS EstSum, |
| 474 | | SUM(IF(Weight>0, Residual* ZdWeight*SpectralWeight, 0)) OVER EnergySim AS SimSum, |
| 475 | | SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight, 0)) OVER EnergyEst AS EstSum2, |
| 476 | | SUM(IF(Weight>0, POW(Residual,2)*ZdWeight*SpectralWeight, 0)) OVER EnergySim AS SimSum2, |
| 477 | | |
| 478 | | -- Migration |
| 479 | | SUM(Weight * ZdWeight*SpectralWeight) OVER Migration AS `MigrationW`, |
| 480 | | SUM(Weight ) OVER Migration AS `MigrationN` |
| 481 | | FROM |
| 482 | | Table0 |
| 483 | | INNER JOIN |
| 484 | | ThetaHist USING(`.theta`) |
| 485 | | WINDOW |
| 486 | | EnergyEst AS (PARTITION BY `.energyest`), |
| 487 | | EnergySim AS (PARTITION BY `.energysim`), |
| 488 | | Migration AS (PARTITION BY `.energysim`,`.energyest`) |
| 489 | | ) |
| 490 | | SELECT DISTINCT |
| 491 | | *, |
| 492 | | `SignalW` - `BackgroundW`/5 AS `ExcessW`, |
| 493 | | `SignalN` - `BackgroundN`/5 AS `ExcessN`, |
| 494 | | ExcErr(`SignalW2`, `BackgroundW2`/5) AS `ErrExcessW`, |
| 495 | | ExcErr(`SignalN`, `BackgroundN` /5) AS `ErrExcessN`, |
| 496 | | IF(SignalW=0, 0, EstSum / SignalW) AS BiasEst, |
| 497 | | IF(SimW =0, 0, SimSum / SimW) AS BiasSim, |
| 498 | | IF(SignalW=0, 0, SQRT(EstSum2/SignalW - POW(EstSum/SignalW, 2))) AS ResolutionEst, |
| 499 | | IF(SimW =0, 0, SQRT(SimSum2/SimW - POW(SimSum/SimW, 2))) AS ResolutionSim |
| 500 | | FROM |
| 501 | | Table1 |
| 502 | | }}} |
| | 299 | [[Include(source:trunk/FACT++/spectrum/simulation.sql)]] |
