Empower Example SQL Queries
The following are sample SQL queries for viewing data.
/* Find the number of injections that has manually processed results for a given project */ SELECT COUNT(DISTINCT(i.uuid)) FROM "lcuv_empower_v4_injection" i LEFT JOIN "lcuv_empower_v4_results" r ON i.uuid = r.parent_uuid WHERE r.processing_method_manual = true AND i.project='EDSL__Waters__Data__Acquity__Multi__inj_Aug20_FAT';
/* Count the total number of peaks in a particular project */ SELECT count(*) FROM "lcuv_empower_v4_results_peaks" p LEFT JOIN "lcuv_empower_v4_results" r ON p.parent_uuid=r.uuid LEFT JOIN "lcuv_empower_v4_injection" i ON r.parent_uuid=i.uuid WHERE i.project_name='EDSL Waters Data\Acquity Multi inj_Aug20_FAT';
/* It is important to understand System Utilization as it pertains to Maintenance and Suitability. The following query will order by the systems with the longest runtimes. This query shows data in the last 6 months. */ select i1.system_name, sum(i1.run_time_value) as total_time, i1.run_time_unit from "lcuv_empower_v3_injection" i1 left join "lcuv_empower_v3_injection" i2 on i2.uuid=i1.uuid where from_iso8601_timestamp(i2.time_acquisition) > date_add('month', -6, current_date) group by i1.system_name, i1.run_time_unit order by total_time desc /* This insight could potentially help the lab balance their runs better to gain greater utilization across all instruments. We could even go deeper and look at the column utilization to better predict when maintenance will be due. */
Updated 11 months ago