[esaggs] Compare against time offset like timelion

[wylieconlon]

One of the high priority items on the Lens roadmap is the ability to support month over month calculations, described in that issue in user-facing terms. The architectural requirements are described here, including some sample tables. This issue will focus on the way we want to implement this feature in esaggs for use by Lens.

Defining the goal

We want to implement time offsets in esaggs using a top-level Filters aggregation. One filter will contain the dashboard context for the "Current time," and the other filter will contain dashboard context with the "offset time". Esaggs should combine the two filters into a single table. The table should be constructed by matching buckets from the current and offset values: if any of the buckets contain timestamps, they need to be aligned using moment. Missing matches need to apply a fitting function.

For our purposes in Lens, we only want to compare metric values, not buckets. If the buckets don't match then we can't build a reasonable comparison.

When using a time offset for display, there are three possible values for each metric. For example, if the metric is "Count", these are the three values:

• The count within the current time
• The count at the offset
• The percentage difference of current / offset

For example, this visualization shows "count at current time" with "percentage difference":

Defining the API

There are two kinds of APIs that could be acceptable for this. I would like feedback about which of these APIs we should use:

a. Automatically generate additional columns for each metric and add to the table. For example, esaggs offset="-4w" aggConfigs="[{ id: 'a', type: 'count' }]" would generate a table with three columns: ['a', 'a-offset', 'a-offset-percent']
b. Add an additional parameter to each metric which allows you to select between the three possible metrics with offset. For example, esaggs offset="-4w" aggConfigs="[{ id: 'a', type: 'count', offset: 'percent' }]" would return a single column instead of three columns.

In both APIs the offset is at the top level, because there is only one offset allowed. It does not make sense to build multiple time comparisons into the API.

cc @elastic/kibana-app-arch @lukeelmers

[wylieconlon]

I'm actually going to remove this from the Lens and KibanaApp labels/projects because it's an enhancement for esaggs

[wylieconlon]

@lukeelmers We have a preference from the Lens side to define the API as option B, where each esaggs metric can have a time offset parameter defined separately. This is mostly because it simplifies the logic in Lens and Visualize, where we have forms built for each metric individually.

[flash1293]

In both APIs the offset is at the top level, because there is only one offset allowed. It does not make sense to build multiple time comparisons into the API.

@wylieconlon I took a look at this - based on our UI I'm not sure this would be a good approach. What if the user want's to compare two offsets? Especially in the formula I think it would be an awkward limitation. Making the offset a parameter of the metric instead gives more flexibility without considerably complicating the logic we need to implement.

[flash1293]

We met with the Elasticsearch team to discuss this feature and where the logic should be implemented.

Currently there are three approaches:

• Do multiple parallel requests in Kibana and merge together the results. This would produce confusing results for the terms aggregation, so it would likely be the best to not allow time offset together with terms
• Do a request for the current time frame, then calculate all buckets in the shifted time frame(s) based on the first response, then send out these requests in parallel and merge together the results in Kibana
• Implement this logic in Elasticsearch (for example like this: https://gist.github.com/flash1293/f56fb1fa437ce97abd3d0ce3c546d480 )

Each of these approaches has up and downsides. There's already a POC for approach 1 (#94783 ) - next steps are:

• Build a similar prototype for approach 2
• Collect other related use cases to see whether an Elasticsearch implementation is worthwhile

[flash1293]

Fixed by #98781