Warning, /OnlineDB/EcalCondDB/analysis/README is written in an unsupported language. File is not indexed.
0001 SQL EXPLANATION
0002
0003 This SQL query returns the run, subrun, SM number, crystal number, and
0004 the number of events over the low and hi threshold as written by the
0005 DQM, given a minum number of events over the low and high thresholds.
0006
0007 SELECT riov.run_num run, miov.subrun_num subrun, rdat.id1 SM, cv.id2 crystal,
0008 occ.events_over_low_threshold n_lo, occ.events_over_high_threshold n_hi
0009 FROM run_iov riov
0010 JOIN run_tag rtag ON rtag.tag_id = riov.tag_id
0011 JOIN location_def ldef ON ldef.def_id = rtag.location_id
0012 JOIN (SELECT iov_id, cv.id1 FROM run_dat rdat
0013 JOIN channelview cv ON cv.logic_id = rdat.logic_id AND cv.name = cv.maps_to) rdat
0014 ON rdat.iov_id = riov.iov_id
0015 JOIN mon_run_iov miov ON miov.run_iov_id = riov.iov_id
0016 JOIN mon_occupancy_dat occ ON occ.iov_id = miov.iov_id
0017 JOIN channelview cv ON cv.logic_id = occ.logic_id AND cv.name = cv.maps_to
0018 WHERE ldef.location = 'H4B'
0019 AND occ.events_over_low_threshold >= ?
0020 AND occ.events_over_high_threshold >= ?
0021 ORDER BY run, SM, crystal
0022
0023 Items in the SELECT clause are the columns that are output. They
0024 are defined using the data sources in the FROM clause. The formal
0025 DB name is written first, then an alias: e.g. riov is an alias for
0026 run_iov, run is an alias for riov.run_num. The FROM clause lists
0027 data sources most of which are tables that are joined together by
0028 the join clause following ON. One of the data sources is a
0029 subquery, which is just another query in parentheses. This subquery
0030 was required in order to provide the correct SM number. After the
0031 FROM clause is the WHERE clause where the results are filtered by
0032 logical expressions. Here we filter out by the location 'H4B', for
0033 H4 beam. Also we filter out by the mon_occupancy_dat's threshold
0034 fields. The question marks are bind parameters, they are variables
0035 that are entered in when the statement is executed. This technique
0036 provides a huge performance benefit over writing the SQL string with
0037 different variables each time. Finally, the ORDER BY clause sorts
0038 our results.
0039
0040
0041 This next SQL query returns the SM number, the crystal number, ieta
0042 and iphi, and the sum of the events over the low and high thresholds,
0043 given a range of runs and the minimum sums you would like displayed.
0044
0045 SELECT rdat.id1 SM, cry.id2 crystal, ang.id1 ieta, ang.id2 iphi,
0046 sum(occ.events_over_low_threshold) sum_events_over_lo, sum(occ.events_over_high_threshold) sum_events_over_hi
0047 FROM run_iov riov
0048 JOIN run_tag rtag ON rtag.tag_id = riov.tag_id
0049 JOIN location_def ldef ON ldef.def_id = rtag.location_id
0050 JOIN (SELECT iov_id, cv.id1 FROM run_dat rdat
0051 JOIN channelview cv ON cv.logic_id = rdat.logic_id AND cv.name = cv.maps_to) rdat
0052 ON rdat.iov_id = riov.iov_id
0053 JOIN mon_run_iov miov ON miov.run_iov_id = riov.iov_id
0054 JOIN mon_occupancy_dat occ ON occ.iov_id = miov.iov_id
0055 JOIN channelview cry ON cry.logic_id = occ.logic_id AND cry.name = 'EB_crystal_number'
0056 JOIN channelview ang ON ang.logic_id = cry.logic_id AND ang.name = 'EB_crystal_angle'
0057 WHERE ldef.location = 'H4B'
0058 AND riov.run_num >= ?
0059 AND riov.run_num <= ?
0060 GROUP BY rdat.id1, cry.id2, ang.id1, ang.id2
0061 HAVING sum(occ.events_over_low_threshold) >= ?
0062 AND sum(occ.events_over_high_threshold) >= ?
0063 ORDER BY SM, crystal
0064
0065 This query is very similar to the last one, however the SUM() function
0066 requires one to GROUP the output which you would like to sum over.
0067 Since we are summing the number of events per channel, we GROUP BY all
0068 the the parameters in SELECT that are related to the channel: SM,
0069 crystal, ieta, and iphi. The requirement "only show results where the
0070 sum is greater than x" goes in the HAVING clause. The filters in
0071 the HAVING clause are done after the SUM() is performed, while those
0072 in the WHERE are done before. Naturally, you can only impose
0073 requirements on the sum after it is done!
0074
0075 Another feature of this query is using the CHANNELVIEW table twice in
0076 one query: this is called a self join. We fetch two channel mappings
0077 from the CHANNELVIEW table, one for crystal numbering (SM, crystal),
0078 and one for angle numbering (ieta, iphi).
0079
0080
0081
0082 ADDITIONAL INFO
0083 To see what is available in the DB, please see the PDFs in
0084 OnlineDB/EcalCondDB/doc in CMSSW CVS.
0085
0086 For more on SQL, I reccomend these sites:
0087 http://w3schools.org/sql
0088 http://google.com/search?q=SQL+tutorial
0089
0090 For info on perl DBI, enter 'perldoc DBI' at the prompt.
0091
0092 For help, contact:
0093
0094 Ricky Egeland
0095 ricky.egeland@cern.ch
