analysis/SimpleDBTrackingSubsystemChecker/CheckTrkrClusterStatus.sql

0001 -- Step 1: Create a table of transferred runs
0002 CREATE TEMP TABLE transferred_runs (run INT);
0003 \COPY transferred_runs FROM 'transferred_runs.txt'
0004
0005 -- Step 2: Extract relevant DST_TRKR_CLUSTER segments
0006 CREATE TEMP TABLE trkr_cluster_segments AS
0007 SELECT
0008   ps.run,
0009   ps.segment,
0010   ps.status
0011 FROM production_status ps
0012 WHERE ps.dsttype = 'DST_TRKR_CLUSTER_run3auau'
0013   AND ps.run IN (SELECT run FROM transferred_runs);
0014
0015 -- Step 3: Get per-run segment counts and percentages
0016 CREATE TEMP TABLE trkr_cluster_summary AS
0017 SELECT
0018   r.run,
0019   COUNT(*) AS total_segments,
0020   COUNT(*) FILTER (WHERE ps.status = 'finished') * 100.0 / COUNT(*) AS percent_finished,
0021   COUNT(*) FILTER (WHERE ps.status = 'running')  * 100.0 / COUNT(*) AS percent_running,
0022   COUNT(*) FILTER (WHERE ps.status = 'failed')   * 100.0 / COUNT(*) AS percent_failed
0023 FROM transferred_runs r
0024 JOIN trkr_cluster_segments ps ON ps.run = r.run
0025
0026 GROUP BY r.run
0027 ORDER BY r.run DESC;
0028
0029 -- Step 4: Handle runs with no segments (fill 0%)
0030 -- This is automatically handled above using LEFT JOIN, NULLs become 0 in COUNT.
0031
0032 -- Step 5: Output the result
0033 \COPY (SELECT run, COALESCE(ROUND(percent_finished, 1), 0.0) AS finished_percent, COALESCE(ROUND(percent_running, 1), 0.0) AS running_percent, COALESCE(ROUND(percent_failed, 1), 0.0) AS failed_percent FROM trkr_cluster_summary ORDER BY run DESC) TO 'trkr_cluster_summary.txt' WITH DELIMITER ' ';
0034