Скользящая сумма до достижения определенного значения плюс рассчитанная продолжительность

У меня есть требование, когда мне нужно знать, когда sum(value) достигает определенной точки, и рассчитывать продолжительность. Ниже приведен образец таблицы.

create table sample (dt timestamp, value real);

insert into sample values
     ('2019-01-20 00:29:43 ',0.29)
    ,('2019-01-20 00:35:06 ',0.31)
    ,('2019-01-20 00:35:50 ',0.41)
    ,('2019-01-20 00:36:32 ',0.26)
    ,('2019-01-20 00:37:20 ',0.33)
    ,('2019-01-20 00:41:30 ',0.42)
    ,('2019-01-20 00:42:28 ',0.35)
    ,('2019-01-20 00:43:14 ',0.52)
    ,('2019-01-20 00:44:18 ',0.25);

Теперь мне нужно рассчитать совокупную сумму следующих строк, чтобы увидеть, когда sum(value) достигнет значения выше 1,0. Для этого может потребоваться всего 1 строка или n строк. Как только эта строка будет достигнута, мне нужно рассчитать разницу во времени между текущей строкой и строкой, где sum(value) достигает значения выше 1.0.

По сути, мой желаемый результат находится в формате ниже.
Для 1-й строки совокупный sum(value) достигается в 3-й строке.
Для 2-й строки совокупный sum(value) достигается в 4-й строке и т. Д.

         dt         | value | sum(value)| time_at_sum(value)_1| Duration
---------------------+--------+------------------------------------------
 2019-01-20 00:29:43| 0.29  |   1.01    | 2019-01-20 00:35:50 | 00:06:07
 2019-01-20 00:35:06| 0.31  |   1.31    | 2019-01-20 00:37:20 | 00:02:14 
 2019-01-20 00:35:50| 0.41  |   1.00    | 2019-01-20 00:37:20 | 00:01:30 
 2019-01-20 00:36:32| 0.26  |   1.01    | 2019-01-20 00:41:30 | 00:04:58 
 2019-01-20 00:37:20| 0.33  |   1.10    | 2019-01-20 00:42:28 | 00:05:08 
 2019-01-20 00:41:30| 0.42  |   1.29    | 2019-01-20 00:43:14 | 00:01:44 
 2019-01-20 00:42:28| 0.35  |   1.12    | 2019-01-20 00:44:18 | 00:01:50 
 2019-01-20 00:43:14| 0.52  |   NULL    |  -                  | -
 2019-01-20 00:44:18| 0.25  |   NULL    |  -                  | -

У кого-нибудь есть идеи или советы о том, как справиться с вышеуказанным требованием?


postgresql sql cumulative-sum rolling-computation sqlcommand
person acul    schedule 28.02.2019    source источник
comment
И при чем здесь sum(value) данные?   -  person Gordon Linoff    schedule 28.02.2019
comment
Столбец с кумулятивной суммой значений, пока он не достигнет 1,0. Отображается только ожидаемый результат.   -  person acul    schedule 28.02.2019
comment
Данные, которые вы показали для value, явно не суммируются с sum(value) примерами. Можете ли вы отредактировать описание, чтобы показать фактическое вычисление, которое ведет от value к sum(value) в вашем примере?   -  person bignose    schedule 28.02.2019
comment
Привет @bignose, я отредактировал свой запрос. Я вручную заполнил желаемый выходной столбец. Обычно Sum (значение) - это кумулятивная сумма следующих n записей, пока она не достигнет значения 1.   -  person acul    schedule 28.02.2019
comment
1.00 для sum(value) в вашем результате нарушает ваше условие when the sum(value) reaches above 1.0, которое переводится в > 1.0, а не в >= 1.0.   -  person Erwin Brandstetter    schedule 28.02.2019

Ответы (2)


arrow_upward
2
arrow_downward

Способ решить эту эффективно - это процедурное решение с двумя курсорами: Один явный курсор и еще один неявный курсор FOR цикла:

CREATE OR REPLACE FUNCTION foo()
  RETURNS TABLE (dt timestamp
               , val real
               , sum_value real
               , time_at_sum timestamp
               , duration interval) AS
$func$
DECLARE
   _bound real := 1.0;          -- your bound here
   cur CURSOR FOR SELECT * FROM sample s ORDER BY s.dt; -- in chronological order
   s sample;                    -- cursor row 
BEGIN
   OPEN cur;
   FETCH cur INTO time_at_sum, sum_value; -- fetch first row into target

   FOR dt, val IN  -- primary pass over table
      SELECT x.dt, x.value FROM sample x ORDER BY s.dt
   LOOP
      WHILE sum_value <= _bound LOOP
         FETCH cur INTO s;
         IF NOT FOUND THEN  -- end of table
            sum_value := NULL; time_at_sum := NULL;
            EXIT;           -- exits inner loop
         END IF;
         sum_value := sum_value + s.value; 
      END LOOP;
      IF sum_value > _bound THEN  -- to catch end-of-table
         time_at_sum := s.dt;
      END IF;   
      duration := time_at_sum - dt;
      RETURN NEXT;
      sum_value := sum_value - val;  -- subtract previous row before moving on
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Вызов:

SELECT * FROM foo();

db ‹> скрипт здесь

Должен работать хорошо, так как для этого требуется всего 2 сканирования стола.

Обратите внимание, что я реализовал > _bound, как того требует ваше описание, а не >= _bound, как указывает ваш результат. В любом случае легко изменить.

Предполагает, что столбец значений равен NOT NULL.

Связанный:

person Erwin Brandstetter    schedule 28.02.2019
comment
@acul: Мне было бы очень интересно узнать фактическое время выполнения функции по сравнению с запросом unutbu (вместе с количеством строк в вашей таблице и приблизительным средним числом строк для агрегирования на sum_value). - person Erwin Brandstetter; 01.03.2019
comment
Привет, @Erwin Brandstetter: Я получаю сообщение об ошибке ОШИБКА: значение вне допустимого диапазона: переполнение в обоих решениях .. - person acul; 04.03.2019
comment
Действительно ли ваш столбец value определяется как real? Каково истинное значение _bound в вашем случае ошибки (1.0 в примере) и каковы минимальные и максимальные значения? У вас должны быть огромные значения для value, диапазон для real обычно составляет как минимум от 1E-37 до 1E + 37. (Или вы как-то представили независимую проблему.) Может быть, начнете новый вопрос со всеми соответствующими деталями. - person Erwin Brandstetter; 04.03.2019
comment
Привет, @Erwin Brandstetter :, да, ты прав. Мое плохое, не смотрел диапазон значений при копировании из родительской таблицы. Я исправил эту проблему. Когда я запускаю таблицу с ~ 55 КБ записей, ваше решение работает ~ 4 секунды, тогда как SQL выполняется ~ 6 минут. Это большая разница, и разница во времени резко увеличивается с увеличением размера стола. Спасибо, Эрвин. Ясно, куда идти. - person acul; 05.03.2019

arrow_upward
4
arrow_downward 

WITH tmp AS (
    SELECT *
        , sum(value) OVER (ORDER BY dt rows between current row and unbounded following) as forward_sum
    FROM sample
    ORDER BY dt)
SELECT t1.dt, t1.value
    , (t2.value + t1.forward_sum - t2.forward_sum) as "sum(value)"
    , t2.dt as "time_at_sum(value)_1" 
    , t2.dt - t1.dt as "Duration"
FROM tmp t1
LEFT JOIN LATERAL (
    SELECT * 
    FROM tmp t
    WHERE t1.forward_sum - t.forward_sum < 1
        AND (t.value + t1.forward_sum - t.forward_sum) >= 0.999
    ORDER BY dt DESC 
    LIMIT 1
    ) t2
ON TRUE

дает

| dt                  | value | sum(value) | time_at_sum(value)_1 | Duration |
|---------------------+-------+------------+----------------------+----------|
| 2019-01-20 00:29:43 |  0.29 |       1.01 | 2019-01-20 00:35:50  | 00:06:07 |
| 2019-01-20 00:35:06 |  0.31 |       1.31 | 2019-01-20 00:37:20  | 00:02:14 |
| 2019-01-20 00:35:50 |  0.41 |          1 | 2019-01-20 00:37:20  | 00:01:30 |
| 2019-01-20 00:36:32 |  0.26 |       1.01 | 2019-01-20 00:41:30  | 00:04:58 |
| 2019-01-20 00:37:20 |  0.33 |        1.1 | 2019-01-20 00:42:28  | 00:05:08 |
| 2019-01-20 00:41:30 |  0.42 |       1.29 | 2019-01-20 00:43:14  | 00:01:44 |
| 2019-01-20 00:42:28 |  0.35 |       1.12 | 2019-01-20 00:44:18  | 00:01:50 |
| 2019-01-20 00:43:14 |  0.52 |            |                      |          |
| 2019-01-20 00:44:18 |  0.25 |            |                      |          |

Сначала вычислите совокупную сумму по столбцу value:

SELECT *
    , sum(value) OVER (ORDER BY dt rows between current row and unbounded following) as forward_sum
FROM sample
ORDER BY dt

который дает

| dt                  | value | forward_sum |
|---------------------+-------+-------------|
| 2019-01-20 00:29:43 |  0.29 |        3.14 |
| 2019-01-20 00:35:06 |  0.31 |        2.85 |
| 2019-01-20 00:35:50 |  0.41 |        2.54 |
| 2019-01-20 00:36:32 |  0.26 |        2.13 |
| 2019-01-20 00:37:20 |  0.33 |        1.87 |
| 2019-01-20 00:41:30 |  0.42 |        1.54 |
| 2019-01-20 00:42:28 |  0.35 |        1.12 |
| 2019-01-20 00:43:14 |  0.52 |        0.77 |
| 2019-01-20 00:44:18 |  0.25 |        0.25 |

Обратите внимание, что вычитание двух значений из forward_sum соответствует частичной сумме более values. Например,

0.29 + 0.31 + 0.41 = 3.14 - 2.13

Таким образом, различие forward_sums будет играть важную роль, и мы хотим сравнить эти различия с 1. Нам нужно объединить эту таблицу с самим собой, используя такое условие соединения, как:

t1.forward_sum - t.forward_sum < 1

Посмотрим, что произойдет, если мы воспользуемся LEFT JOIN LATERAL. Важнейшая вещь, которую нужно знать о LEFT JOIN LATERAL, заключается в том, что подзапрос справа от LATERAL join должен оцениваться один раз для каждой строки в таблица слева:

WITH tmp AS (
    SELECT *
        , sum(value) OVER (ORDER BY dt rows between current row and unbounded following) as forward_sum
    FROM sample
    ORDER BY dt)
SELECT t1.*, t2.*
FROM tmp t1
LEFT JOIN LATERAL (
    SELECT * 
    FROM tmp t
    WHERE t1.forward_sum - t.forward_sum < 1
    ORDER BY dt DESC 
    LIMIT 1
    ) t2
ON TRUE

дает

| dt                  | value | forward_sum | dt                  | value | forward_sum |
|---------------------+-------+-------------+---------------------+-------+-------------|
| 2019-01-20 00:29:43 |  0.29 |        3.14 | 2019-01-20 00:35:50 |  0.41 |        2.54 |
| 2019-01-20 00:35:06 |  0.31 |        2.85 | 2019-01-20 00:37:20 |  0.33 |        1.87 |
| 2019-01-20 00:35:50 |  0.41 |        2.54 | 2019-01-20 00:37:20 |  0.33 |        1.87 |
| 2019-01-20 00:36:32 |  0.26 |        2.13 | 2019-01-20 00:41:30 |  0.42 |        1.54 |
| 2019-01-20 00:37:20 |  0.33 |        1.87 | 2019-01-20 00:42:28 |  0.35 |        1.12 |
| 2019-01-20 00:41:30 |  0.42 |        1.54 | 2019-01-20 00:43:14 |  0.52 |        0.77 |
| 2019-01-20 00:42:28 |  0.35 |        1.12 | 2019-01-20 00:44:18 |  0.25 |        0.25 |
| 2019-01-20 00:43:14 |  0.52 |        0.77 | 2019-01-20 00:44:18 |  0.25 |        0.25 |
| 2019-01-20 00:44:18 |  0.25 |        0.25 | 2019-01-20 00:44:18 |  0.25 |        0.25 |

Обратите внимание, что мы угадали путь к условию соединения, которое соответствует желаемым датам. Теперь осталось просто составить правильные выражения значений для получения нужных столбцов, sum(value), time_at_sum(value)_1.

person unutbu    schedule 28.02.2019
comment
В мою скрипку добавлен ваш умный запрос - для удобства тестирования: dbfiddle.uk/ - person Erwin Brandstetter; 28.02.2019
comment
Отлично. Большое спасибо за вашу помощь Unutbu. Пару дней ломал голову над этой проблемой. - person acul; 28.02.2019