Count and group by measurements based on the last value from another sensr

Telegraf
,
1

Hi all,

We are working in discrete manufacturing field and using InfluxDB in combination with Grafana to store & visualize sensor measurements. For the issue that I am going to elaborate below you need to know two things.

  • Recipe: In the discrete manufacturing there are large numbers of recipes for each product based on which machine operates.

  • Quality sensors: Every part which is produced would have an associated quality parameter such as Good Part and Bad Part

Now lets assume that I have the following measurements (TABLE 1) :

> SELECT * FROM "Value" WHERE sensor_id  = 'GoodPart' OR sensor_id  = 'Recipe_ID_sensor' 

name: Value
--------------
time                   sensor_id          value   Recipe_ID
2015-09-18T21:35:00Z   Recipe_ID_sensor   LTE21   null
2015-09-18T21:36:00Z   GoodPart           1       LTE21
2015-09-18T21:37:00Z   GoodPart           0       LTE21
2015-09-18T21:38:00Z   GoodPart           1       LTE21
2015-09-18T21:39:00Z   GoodPart           1       LTE21
2015-09-18T21:39:00Z   GoodPart           1       LTE21
2015-09-18T21:40:00Z   GoodPart           1       LTE21

In the table above GoodPart measurements are for the quality state of produced parts and Recipe_ID is the corresponding recipe for these. Please be aware that I am using another measurement called Recipe_ID_sensor to tag the GoodPart measurement.

Now lets assume the machine started to run at 21:41:00 with another recipe (TABLE 2):

> SELECT * FROM "Value" WHERE sensor_id  = 'GoodPart'

name: Value
--------------
time                   sensor_id   value   Recipe_ID
2015-09-18T21:36:00Z   GoodPart    1       LTE21
2015-09-18T21:37:00Z   GoodPart    0       LTE21
2015-09-18T21:38:00Z   GoodPart    1       LTE21
2015-09-18T21:39:00Z   GoodPart    1       LTE21
2015-09-18T21:39:00Z   GoodPart    1       LTE21
2015-09-18T21:40:00Z   GoodPart    1       LTE21
2015-09-18T21:41:00Z   GoodPart    1       3G214
2015-09-18T21:42:00Z   GoodPart    0       3G214
2015-09-18T21:43:00Z   GoodPart    1       3G214

Now, what I want to achieve is the following:
If I run a magical query (imaginary) before the recipe was changed, so around 21:40:10, the query should count the number of good parts (value = 1) for where Recipe_ID equals to LTE21

> SELECT COUNT(value), Recipe_ID FROM "Value" WHERE sensor_id  = 'GoodPart' AND value = 1 AND Recipe_ID = (SELECT LAST(value) FROM Value WHERE sensor_id = 'Recipe_ID_sensor')

name: Value
--------------
2015-09-18T21:36:00Z   COUNT   5

If I run the same query at 21:43:10 the query should return the number of good parts with Recipe_ID equals to 3G214 (latest value of ‘Recipe_ID_sensor’)

> SELECT COUNT(value), Recipe_ID FROM "Value" WHERE sensor_id  = 'GoodPart' AND value = 1 AND Recipe_ID = (SELECT LAST(value) FROM Value WHERE sensor_id = 'Recipe_ID_sensor')

name: Value
--------------
2015-09-18T21:36:00Z   COUNT   2     3G214

Is there any way to achieve this with InfluxDB? Continous queries or FLUX?

Any help is appreciated.