Hi, I have a database that contains the number of registers stored on each device. These devices are identified by the tag ID.
I already have a table that shows the evolution of the information of each device over time:
Where each value of the legend is diferent device ID.
Now I would like to plot the evolution of the total registers over time. This should be easy (making a subquery that sums al registers). But there’s a catch: the devices don’t publish the information consistently. Maybe one day the update the information 20 times and some day they don’t.
However, we can asume that for a given day the amount of registers stored is the last published information of each device.
How could I program this “intelligence”?
Until now I have tried the easy query:
SELECT SUM(lv) FROM (SELECT LAST("regs") as lv FROM /^$Ciutat$/ WHERE ID=~/^$IDs$/ GROUP BY time(24h), "ID") GROUP BY time(24h) fill(linear)
But this doesn’t take into acount that if for a given ID there is not info in the 24h analized it should pick the last data:
You can see that for the last day the number of registers decreasses because some devices haven’t registered information that day.
In my mind the query should sound like: "For each period of 24h pick the last information (or regs) of each ID device, no matter the day"
