SQL EXPLANATION

This SQL query returns the run, subrun, SM number, crystal number, and
the number of events over the low and hi threshold as written by the
DQM, given a minum number of events over the low and high thresholds.

SELECT riov.run_num run, miov.subrun_num subrun, rdat.id1 SM, cv.id2 crystal, 
       occ.events_over_low_threshold n_lo, occ.events_over_high_threshold n_hi
  FROM run_iov riov
  JOIN run_tag rtag ON rtag.tag_id = riov.tag_id
  JOIN location_def ldef ON ldef.def_id = rtag.location_id
  JOIN (SELECT iov_id, cv.id1 FROM run_dat rdat 
          JOIN channelview cv ON cv.logic_id = rdat.logic_id AND cv.name = cv.maps_to) rdat
            ON rdat.iov_id = riov.iov_id
  JOIN mon_run_iov miov ON miov.run_iov_id = riov.iov_id
  JOIN mon_occupancy_dat occ ON occ.iov_id = miov.iov_id
  JOIN channelview cv ON cv.logic_id = occ.logic_id AND cv.name = cv.maps_to
 WHERE ldef.location = 'H4B'
   AND occ.events_over_low_threshold >= ?
   AND occ.events_over_high_threshold >= ?
 ORDER BY run, SM, crystal

Items in the SELECT clause are the columns that are output.  They
are defined using the data sources in the FROM clause.  The formal
DB name is written first, then an alias: e.g. riov is an alias for
run_iov, run is an alias for riov.run_num.  The FROM clause lists
data sources most of which are tables that are joined together by
the join clause following ON.  One of the data sources is a
subquery, which is just another query in parentheses.  This subquery
was required in order to provide the correct SM number.  After the
FROM clause is the WHERE clause where the results are filtered by
logical expressions.  Here we filter out by the location 'H4B', for
H4 beam.  Also we filter out by the mon_occupancy_dat's threshold
fields.  The question marks are bind parameters, they are variables
that are entered in when the statement is executed.  This technique
provides a huge performance benefit over writing the SQL string with
different variables each time.  Finally, the ORDER BY clause sorts
our results.


This next SQL query returns the SM number, the crystal number, ieta
and iphi, and the sum of the events over the low and high thresholds,
given a range of runs and the minimum sums you would like displayed.

SELECT rdat.id1 SM, cry.id2 crystal, ang.id1 ieta, ang.id2 iphi,
       sum(occ.events_over_low_threshold) sum_events_over_lo, sum(occ.events_over_high_threshold) sum_events_over_hi
  FROM run_iov riov
  JOIN run_tag rtag ON rtag.tag_id = riov.tag_id
  JOIN location_def ldef ON ldef.def_id = rtag.location_id
  JOIN (SELECT iov_id, cv.id1 FROM run_dat rdat 
          JOIN channelview cv ON cv.logic_id = rdat.logic_id AND cv.name = cv.maps_to) rdat
        ON rdat.iov_id = riov.iov_id
  JOIN mon_run_iov miov ON miov.run_iov_id = riov.iov_id
  JOIN mon_occupancy_dat occ ON occ.iov_id = miov.iov_id
  JOIN channelview cry ON cry.logic_id = occ.logic_id AND cry.name = 'EB_crystal_number'
  JOIN channelview ang ON ang.logic_id = cry.logic_id AND ang.name = 'EB_crystal_angle'
 WHERE ldef.location = 'H4B'
   AND riov.run_num >= ?
   AND riov.run_num <= ?
 GROUP BY rdat.id1, cry.id2, ang.id1, ang.id2
HAVING sum(occ.events_over_low_threshold) >= ?
   AND sum(occ.events_over_high_threshold) >= ?
 ORDER BY SM, crystal

This query is very similar to the last one, however the SUM() function
requires one to GROUP the output which you would like to sum over.
Since we are summing the number of events per channel, we GROUP BY all
the the parameters in SELECT that are related to the channel:  SM,
crystal, ieta, and iphi.  The requirement "only show results where the
sum is greater than x" goes in the HAVING clause.  The filters in
the HAVING clause are done after the SUM() is performed, while those
in the WHERE are done before.  Naturally, you can only impose
requirements on the sum after it is done!

Another feature of this query is using the CHANNELVIEW table twice in
one query:  this is called a self join.  We fetch two channel mappings
from the CHANNELVIEW table, one for crystal numbering (SM, crystal),
and one for angle numbering (ieta, iphi).



ADDITIONAL INFO
To see what is available in the DB, please see the PDFs in
OnlineDB/EcalCondDB/doc in CMSSW CVS.

For more on SQL, I reccomend these sites:
http://w3schools.org/sql
http://google.com/search?q=SQL+tutorial

For info on perl DBI, enter 'perldoc DBI' at the prompt.

For help, contact:

Ricky Egeland
[email protected]