I have a working query but I’m wondering if it could be optimized. I’m not sure if state_agg() would be of benefit or not since I don’t have the toolkit available.
I am polling CNC equipment at approximately 1Hz and piping the data into a hypertable.
The current query uses a simple LEAD() call to calculate the duration for each row by partitioning by asset and it works fine but I wonder if it can be optimized.
Is there an SQL way to say, “Take the current row and look forward until the state column changes values”?
It may be more intensive than calculating all the tiny durations but wanted to know if there was a more efficient way of doing this.
Below is the current query:
-- Calculate utilization
SELECT
hour_bucket,
asset,
coalesce(running_duration / (running_duration + COALESCE(stopped_duration, 0)), 0) AS utilization
FROM (
SELECT
time_bucket('1 hour', time) as hour_bucket,
asset,
SUM(case when state = 'running' then duration else 0 end) AS running_duration,
SUM(CASE WHEN state = 'stopped' THEN duration ELSE 0 END) AS stopped_duration
FROM (
SELECT
time,
asset,
state,
EXTRACT(EPOCH FROM (LEAD(time) OVER (PARTITION BY asset ORDER BY time) - time)) AS duration
FROM
public.plates_makino_mills_combined
) subquery
GROUP BY
hour_bucket,
asset
) subquery2
ORDER BY
hour_bucket DESC,
asset;