There is a table of initial data with the following column structure (N-number of the measurement from the beginning of the year + 300 columns of the results of measurement).
For it in the future, it is required to repeatedly receive statistical data for a certain interval of measurements. For example, to determine how much for the interval from 10 to 472 measurements for each of the data columns were unique values, average, maximum minimum value, etc.
To optimize the acquisition (in order not to calculate the values ​​each time), a КэшСтатистики table was КэшСтатистики with the columns КэшСтатистики + Статистические_данные out of range.
When receiving data from 10 to 472 measurements, they are immediately taken from the КэшСтатистики table, and if you need values, for example, from 8 to 478, you can measure them directly from the source data table, or take the calculated results from the КэшСтатистики for the range 10..472 and aggregate with the remaining Measurements 8, 9, 473..478.

The question is how to build a query that would give the maximum combination of calculated intervals for a given range of measurements from the КэшСтатистики table КэшСтатистики order to less calculate the source data

    1 answer 1

    In general, as something like this:

     select col1, col2, ... from Кэш where "НачальныйЗамер">=$start and "КонечныйЗамер" <=$end union all select col1, col2, ... from "Данные" left join ( select "НачальныйЗамер", "КонечныйЗамер" from Кэш where "НачальныйЗамер">=$start and "КонечныйЗамер" <=$end ) A on "Данные".N between A."НачальныйЗамер" and A."КонечныйЗамер" where "Данные".N between $start and $end and "Данные".N and A."НачальныйЗамер" is null

    $start and $end range for which data is needed. Only no one said that it would be faster than work on the source data. Try it, see the execution plans. For different DBMS, it may be possible to come up with more optimal options.

    And it is also interesting what is "and so on" in the enumeration of "maximum, minimum, average". Not all indicators can be calculated from already collected data. What is the weighted average cache you do not get.