EcalCondDB/sql/cms_payload_mv.sql

0001 CREATE MATERIALIZED VIEW ECALPEDESTALS
0002 AS
0003 /* query to fill ECALPEDESTALS table */
0004   SELECT
0005     miov.iov_id iov_value_id,
0006     riov.run_num time
0007   FROM
0008     /* inline view selects the mon_run_iov with the greatest subrun_num */
0009     (SELECT iov_id, run_iov_id,
0010        MAX(subrun_num) KEEP (DENSE_RANK FIRST ORDER BY subrun_num ASC)
0011        FROM mon_run_iov@cmsomds GROUP BY iov_id, run_iov_id) miov
0012     JOIN run_iov@cmsomds riov ON miov.run_iov_id = riov.iov_id
0013     JOIN run_tag@cmsomds rtag ON riov.tag_id = rtag.tag_id
0014     JOIN run_type_def@cmsomds rdef ON rtag.run_type_id = rdef.def_id
0015     JOIN location_def@cmsomds ldef ON rtag.location_id = ldef.def_id
0016   WHERE
0017       ldef.location='H4'
0018   AND rdef.run_type='PEDESTAL'
0019   AND rdef.config_tag='PEDESTAL-STD'
0020   AND rdef.config_ver=1
0021 ;
0022
0023 /* query to fill ECALPEDESTALS_ITEM table */
0024 CREATE MATERIALIZED VIEW ECALPEDESTALS_ITEM
0025 AS
0026   SELECT
0027     dat.iov_id iov_value_id,
0028     /* Creates a column of row numbers ordered by logic_id, for std::vec */
0029     ROW_NUMBER() OVER (PARTITION BY dat.iov_id ORDER BY dat.logic_id ASC) pos,
0030     cv.id1 det_id,
0031     dat.ped_mean_g12 mean_x12,
0032     dat.ped_rms_g12 rms_x12,
0033     dat.ped_mean_g6 mean_x6,
0034     dat.ped_rms_g6 rms_x6,
0035     dat.ped_mean_g1 mean_x1,
0036     dat.ped_rms_g1 rms_x1
0037   FROM
0038     /* inline view selects the mon_run_iov with the greatest subrun_num */
0039     (SELECT iov_id, run_iov_id,
0040        MAX(subrun_num) KEEP (DENSE_RANK FIRST ORDER BY subrun_num ASC)
0041        FROM mon_run_iov@cmsomds GROUP BY iov_id, run_iov_id) miov
0042     JOIN run_iov@cmsomds riov ON miov.run_iov_id = riov.iov_id
0043     JOIN run_tag@cmsomds rtag ON riov.tag_id = rtag.tag_id
0044     JOIN run_type_def@cmsomds rdef ON rtag.run_type_id = rdef.def_id
0045     JOIN location_def@cmsomds ldef ON rtag.location_id = ldef.def_id
0046     JOIN mon_pedestals_dat@cmsomds dat ON dat.iov_id = miov.iov_id
0047     JOIN channelview@cmsomds cv ON cv.logic_id = dat.logic_id
0048   WHERE
0049         cv.name='Offline_det_id'
0050     AND ldef.location='H4'
0051     AND rdef.run_type='PEDESTAL'
0052     AND rdef.config_tag='PEDESTAL-STD'
0053     AND rdef.config_ver=1
0054 ;