Combining delta and snapshot metrics

I have a quandary, but I’ll start off by saying that I know that I could achieve this by a CQ or even just feeding an extra series with the computed values, but I’d like to avoid doing so if there’s a more elegant way of achieving it.

I have two sets of data, I’m simplifying here but it’s the same flavour of problem:

  1. OccupantCountByRoom… periodically count the number of people in the room and log it here.
  2. MovementsByRoom… logs the number of people entering or exiting the room as delta events, positive for entering and negative for leaving.

If you take the whole history, cumulative_sum(MovementsByRoom) should pass through every point in OccupantCountByRoom, you can use one to check the validity of the other. My issue is that this only works if the time period examined has OccupantCountByRoom as 0 at the beginning, otherwise there will be a constant offset and your sets will never touch.

You could feed in a manual offset but you’d have to do it every time you change the period you’re looking at.

In generic software you could take the difference between first snapshot metric and the cumulative to get the offset to apply to the series, that approach doesn’t exactly lend itself to a query language though.

Ideally I’d like to plot a cumulative_since_last_snapshot function, but I’m not sure how that might be achieved within the constraints of InfluxQL.