querying a discrete state time series for intervals with the 'active state'

# Feature request: 'active state query'

## Use case
Besides time-series, which represent measurements of continuously changing values (e.g. CPU load, free disk space, measured temperature/humidity), we would also like to store time-series **representing a value out of a discrete set of values**. Consider the state of a light switch: 'on', 'off'. Or the enumeration value of the current weather condition (sunny, cloudy, rainy, snowing, ...).

A common query we have for these types of time series is to **list which state was 'active' for a given query time window**. The **response should contain a set of intervals with the active state within this interval**. The intervals should cover ('partition') the whole query interval.

## Proposal
I do not want to propose a concrete syntax for this type of query, nor for the response, but the following example covers the cases for the query.

Assume the following weather condition changes:

| time | weather |
|-------|---------|
| 6:00 | snowy |
| 8:00 | rainy |
| 12:00 | sunny |

When querying for the current weather condition **between 7:00 and 13:00**, I expect the following response:

| interval | weather |
|---------------| --------|
| 7:00 - 8:00 | snowy |
| 8:00 - 12:00 | rainy |
| 12:00 - 13:00 | sunny |

When querying for the current weather condition **between 9:00 and 11:00**, I expect the following response (single value active):

| interval | weather |
|---------------| --------|
| 9:00 - 11:00 | rainy |

**Though the weather state change at 6:00 is not within the query interval, the result should assume that this weather condition is still present at the beginning of the query interval (7:00) and thus determine the value of the first interval.**

This behavior would require InfluxDB to *'look back in time' until the last value before the interval is found*. The time window which is searched for the last value before the query start time ('retrospection window') could potentially be restricted in the query.

If no value can be found (within the retrospection window) the interval would be filled with `null`.
When querying for the current weather condition **between 4:00 and 7:00**, I thus expect the following response (single value active):

| interval | weather |
|---------------| --------|
| 4:00 - 6:00 | `null` |
| 6:00 - 7:00 | snowy |

## Current behavior
This query can be 'simulated' by the following:
1. query all points within the desired interval `... WHERE time >= start AND time < end`.
2. query for `LAST() ... WHERE time < start` for the value of the first interval.

Unfortunately, the `LAST()` query turns out to be very slow for our usage.

With the described type of query in mind, maybe a different index structure can better support the lookup of values before the query interval to fill the first response interval.

This issue supersedes #7029 (which was opened by @beckettsean for us). I wanted to clearly describe the use case again.

No implementation files, tests, or entry points are named. Start by tracing the existing interval query and LAST() behavior described in the issue, then determine how a lookback window could provide the state at the query start. Done means the query returns partitioned active-state intervals, including prior-state and null cases.

Written by the indexing model from the issue text.