https://www.umh.app logo
Join Discord
Powered by
I gave up on this approach. ChatGPT didn't quite g...
# general
r
I gave up on this approach. ChatGPT didn't quite get me there but I figured out the logic enough to come up with this:
Copy code
sql
-- 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;
u
I'd need to dabble with the queries to know if they work out the same vs which is better, but... when I've had to do something like this I used the 
row_number
window function to reduce the data set down to just the state changes. In your case I think I'd do 
PARTITION BY asset, state ORDER BY time
. Then 
where
the 
row_number
column is 
1
and you've got just the state changes. Calculate the duration with 
coalesce(lead(time over...), now()) - time
d
You want to read the time between states? Do you get only 1 stopped between start or can you have several stop after each other? If only 1 You could calculate time between timestamps and name it as timeOn if state=stopped and vice versa
u
I interpreted that the data format meant the rows could look like:
Copy code
running
running
stopped
running
stopped
stopped
stopped
stopped
running
running
So could be any number of rows between the current state and the next state change. I'm basing that off the 
case
statement that's trying to check the previous and next states though.
d
Count the values using DISTINCT could work
r
More or less. I'm polling the equipment at 1Hz so I can have many repeated states.
I like the idea and I can visualize partitioning by one column, but not two... Wouldn't that group all the "running" and "stopped" rows into separate logical units?
Well, I was hoping it was going to be easy. I worked with ChatGPT to come up with this but it's at 90s and still going. The first version only took 18 seconds for about 8MM records.
Copy code
sql
WITH subquery AS (
  SELECT
    time,
    asset,
    state,
    ROW_NUMBER() OVER (PARTITION BY asset ORDER BY time) AS row_num
  FROM
    public.plates_makino_mills_combined
)
SELECT
  time,
  asset,
  state,
  EXTRACT(EPOCH FROM (LEAD(time) OVER (PARTITION BY asset ORDER BY time) - time)) AS duration
FROM
  subquery
WHERE
  state <> (
    SELECT state
    FROM subquery AS inner_subquery
    WHERE inner_subquery.row_num = subquery.row_num - 1
    LIMIT 1
  ) OR subquery.row_num = 1;
d
What if you select only the first distinct values and if the timestamp for state stopped is newer then you can calculate the running time and if state running is newer you can calculate the time stopped
would this work ?:
Copy code
SELECT asset, SUM(EXTRACT(epoch FROM end_time - start_time)) AS running_time
FROM (
    SELECT asset, timems AS start_time, LEAD(timems) OVER (PARTITION BY asset ORDER BY timems) AS end_time
    FROM plates_makino_mills_combined
    WHERE state = 'running'
) AS subquery
WHERE end_time IS NOT NULL
GROUP BY asset;
u
Why do you need that subquery in the 
where
clause at the end?
r
ChatGPT came up with it, I was trying to efficiently jump between state changes instead of evaluating every row, but it never worked.
Perhaps, I may try it out, but I would like to classify all states. Currently only 
running
, 
stopped
, and `offline `are generated but long term I want to create a more generalized query. Some equipment may have an `idle `state, and our ovens have a 
recovering
state getting back up to temp after opening.
j
maybe as one idea: what about using for this type of reporting the analytics features? you would need to use the same data scheme as UMH (should be easy to do), but you would then get all the analytics features such as OEE, production speed, etc. incl. micro stop detection and other stuff. https://umh.docs.umh.app/docs/features/analytics/ I know from experience that it can get complicated very quickly as more and more edge cases appear and requests like (pls add the shifts, pls add a featrue that takes out obvious shifts where nothing was produced, etc.)
and it is not so far off from what you have at the moment: https://umh.docs.umh.app/docs/architecture/datamodel/database/statetable/
we bascially already handle all of these states, and you can move them into whatever OEE bucket you have. maybe "recovering" could be a "SettingUp" state (in EUROMAP its called "PREPARING"): https://umh.docs.umh.app/docs/architecture/datamodel/states/process/
also @_glenngillen: welcome to the community 😄
u
I think that whole nested part of the where clause is redundant, and likely the cause of why it's soooooo slow. You should only need the row_num = 1 bit. Either way, I've been sufficiently nerd-sniped at this point 😉 If you're able to DM me a dump of a sample of the data to make sure I've 100% got the right schema and values, I'll load it up locally and take a pass at it.
r
I make take you up on that... I'm still trying to wrap my head around partitioning over 
asset
and 
state
, but if I do that, I won't know exactly when it changed state because I don't have the actual row where the state changed. It would be close, but not exactly right (within a secondish).
Timescale has a 
state_agg()
function, but it's only in the toolkit version which is only available in the high availability docker image and I loaded the standard one before I knew the difference.
j
100% the above, use 
state_agg
, that's what it's for. It is blazing fast b/c it's written in Rust
s
Hi @Jermuk, can you share any example OEE dashboards/ panels on how to configure kpis like availability, performance, stop time and listing of products? Especially from umh-v2 data source
j
otherwise, there are some example on learn and in the documentation. basically everywhere where we create grafana dashboards
2 Views
PreviousNext
Powered by