Сумма last_value каждого раздела в SQL с оконными функциями

Question

У меня есть таблица, в которой хранится общий объем используемого диска в любой момент времени для каждого объекта. Я хочу найти максимальный диск, использованный за определенный период времени. Например, данные выглядят примерно так

Примечание: метка времени - это фактическая метка времени с точностью до секунды, я установил ее на 10 утра и т. Д. Для краткости.

timestamp | entity_id | disk_used
---------------------------------
    9am   |         1 |  10
   10am   |         2 |  20
   11am   |         2 |  15
   12am   |         1 |  12
     

В этом примере максимальный размер используемого диска равен 30 (10 для объекта 1 и 20 для объекта 2).

Я пробовал несколько подходов.

1. Сумма (макс. Для каждой сущности) не работает, потому что она даст результат 20 + 12 = 32. Но до того, как сущность 1 увеличила свой размер, сущность 2 уменьшила размер, поэтому пиковое использование диска было 30.
2. Я попытался использовать оконную функцию, чтобы найти сумму last_value каждой сущности

select timestamp, entity_id,
    disk_used, 
    sum(last_value(disk_used) over(
        partition by entity_id order by timestamp)
    ) sum_of_last

пытаюсь сгенерировать, поэтому я могу максимизировать его,

timestamp | entity_id | disk_used | sum_of_last
-----------------------------------------------
    9am   |         1 |  10       |   10
   10am   |         2 |  20       |   30
   11am   |         2 |  15       |   25       // (10 + 15)
   12am   |         1 |  12       |   27       // (12 + 15)
     

однако этот запрос не работает, потому что мы не можем агрегировать оконную функцию в стандарте ISO Standard SQL 2003. Я использую базу данных Amazon timestream. Механизм запросов совместим со стандартом ISO Standard SQL 2003.

- Перефразируя тот же вопрос, на каждой временной метке у нас есть точка данных для всего диска, используемого в данный момент. Чтобы найти общий объем используемого диска в данный момент, просуммируйте последнее значение каждого объекта.

Есть ли эффективный способ вычислить это?

Answer 1

Я хочу найти максимальный диск, использованный за период времени

Вы можете использовать два уровня агрегирования:

select max(sum_disk_used)
from (
    select time, sum(disk_used) as sum_disk_used
    from mytable
    group by time
) t

Подзапрос вычисляет общую disk_used в каждый момент времени внешний запрос получает только пиковое значение.

Если ваша база данных поддерживает какой-то limit предложение, это можно упростить:

select time, sum(disk_used) as sum_disk_used
from mytable
group by time
order by sum_disk_used limit 1

Для фильтрации по заданному периоду вы обычно добавляете where предложение к подзапросу.

Answer 2

Если у вас всего две сущности, вы можете:

select t.*,
       (last_value(case when entity_id = 1 then disk_used end ignore nulls) over (order by time) +
        last_value(case when entity_id = 2 then disk_used end ignore nulls) over (order by time)
       ) as total        
from t;

Один из способов обобщить это для всех сущностей - каждый раз генерировать строку для каждой сущности, приписывать значение и агрегировать:

select ti.time, e.entity_id,
       last_value(disk_used ignore nulls) over (partition by e.entity_id order by t.time) as imputed_disk_used
from (select distinct time from t) ti cross join
     (select distinct entity_id from t) e left join
     t
     on ti.time = t.time and e.entity_id = t.entity_id;

Затем вы можете агрегировать:

select time, sum(imputed_disk_used)
from (select ti.time, e.entity_id,
             last_value(disk_used ignore nulls) over (partition by e.entity_id order by t.time) as imputed_disk_used
      from (select distinct time from t) ti cross join
           (select distinct entity_id from t) e left join
           t
           on ti.time = t.time and e.entity_id = t.entity_id
     ) te
group by time;

Однако это дает значение за время, а не за время, и entity_id.