| 1 | #!/usr/bin/env -S mysql -h ihp-pc45.ethz.ch
|
|---|
| 2 |
|
|---|
| 3 | -- You might want to change the table name
|
|---|
| 4 | CREATE TABLE Calibration
|
|---|
| 5 | (
|
|---|
| 6 | WITH Table1 AS
|
|---|
| 7 | (
|
|---|
| 8 | SELECT
|
|---|
| 9 | -- NIGHT and RUNID of the data file
|
|---|
| 10 | R1.NIGHT AS NIGHT,
|
|---|
| 11 | R1.RUNID AS RUNID,
|
|---|
| 12 | -- NIGHT and RUNID of the DRS file
|
|---|
| 13 | R2.NIGHT AS DrsNight,
|
|---|
| 14 | R2.RUNID AS DrsRunID,
|
|---|
| 15 | -- Time difference between the two
|
|---|
| 16 | LEAST(
|
|---|
| 17 | ABS(UNIX_TIMESTAMP(R2.`DATE-OBS`)-UNIX_TIMESTAMP(R1.`DATE-OBS`)),
|
|---|
| 18 | ABS(UNIX_TIMESTAMP(R2.`DATE-OBS`)-UNIX_TIMESTAMP(R1.`DATE-END`)),
|
|---|
| 19 | ABS(UNIX_TIMESTAMP(R2.`DATE-END`)-UNIX_TIMESTAMP(R1.`DATE-OBS`)),
|
|---|
| 20 | ABS(UNIX_TIMESTAMP(R2.`DATE-END`)-UNIX_TIMESTAMP(R1.`DATE-END`))
|
|---|
| 21 | ) AS DeltaT,
|
|---|
| 22 | -- Assign a row number to each combination of data-file and drs-file
|
|---|
| 23 | -- after sorting them by time difference
|
|---|
| 24 | ROW_NUMBER() OVER(PARTITION BY R1.NIGHT, R1.RUNID ORDER BY
|
|---|
| 25 | LEAST(
|
|---|
| 26 | ABS(UNIX_TIMESTAMP(R2.`DATE-OBS`)-UNIX_TIMESTAMP(R1.`DATE-OBS`)),
|
|---|
| 27 | ABS(UNIX_TIMESTAMP(R2.`DATE-OBS`)-UNIX_TIMESTAMP(R1.`DATE-END`)),
|
|---|
| 28 | ABS(UNIX_TIMESTAMP(R2.`DATE-END`)-UNIX_TIMESTAMP(R1.`DATE-OBS`)),
|
|---|
| 29 | ABS(UNIX_TIMESTAMP(R2.`DATE-END`)-UNIX_TIMESTAMP(R1.`DATE-END`))
|
|---|
| 30 | )
|
|---|
| 31 | ) AS RowNumber
|
|---|
| 32 | FROM
|
|---|
| 33 | RawData R1
|
|---|
| 34 | -- Join the table with itself to get all possible combinations of runs
|
|---|
| 35 | CROSS JOIN
|
|---|
| 36 | RawData R2
|
|---|
| 37 | -- Join the DRS files with the table that tells you whether they can be used
|
|---|
| 38 | -- if the table does not (yet) exists, this can be omitted
|
|---|
| 39 | LEFT JOIN
|
|---|
| 40 | DoNotUse ON (R2.NIGHT=DoNotUse.NIGHT AND R2.RUNID=DoNotUse.RUNID)
|
|---|
| 41 | WHERE
|
|---|
| 42 | -- Select all combinations that have a data file in R1 and a drs file (at step 1) in R2
|
|---|
| 43 | ISNULL(R1.DRSSTEP) AND R2.DRSSTEP=1
|
|---|
| 44 | AND
|
|---|
| 45 | -- Only DRS files with 100 events and the correct ROI are considered
|
|---|
| 46 | R2.ZNAXIS2=1000 AND R1.NROI=R2.NROI
|
|---|
| 47 | AND
|
|---|
| 48 | -- Check if they are valid
|
|---|
| 49 | IFNULL(DoNotUse.WrongVoltage,false)!=true
|
|---|
| 50 | -- This can be used to restrict the number of combinations
|
|---|
| 51 | -- AND ABS(UNIX_TIMESTAMP(R2.`DATE-OBS`)-UNIX_TIMESTAMP(R1.`DATE-OBS`))<78*3600
|
|---|
| 52 | ORDER BY
|
|---|
| 53 | R1.NIGHT, R1.RUNID, DeltaT
|
|---|
| 54 | )
|
|---|
| 55 | SELECT
|
|---|
| 56 | NIGHT,
|
|---|
| 57 | RUNID,
|
|---|
| 58 | DrsNight,
|
|---|
| 59 | DrsRunId,
|
|---|
| 60 | DeltaT/3600e0 AS DeltaT
|
|---|
| 61 | FROM
|
|---|
| 62 | Table1
|
|---|
| 63 | WHERE
|
|---|
| 64 | -- Select only the rows with the smallest DeltaT
|
|---|
| 65 | RowNumber=1
|
|---|
| 66 | ) |
|---|
