Блог

Я работаю над реализацией Java для временной агрегации с использованием базы данных PostgreSQL.

Моя таблица выглядит следующим образом

 Value | Start      | Stop
(int) | (Date)     | (Date)
-------------------------------
1     | 2004-01-01 | 2010-01-01
4     | 2000-01-01 | 2008-01-01
  

Итак, чтобы визуализировать эти периоды:

                       ------------------------------
  ----------------------------------------
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010
  [        4         ][       5=4 1      ][    1   ]
  

Теперь мой алгоритм вычисляет временные агрегации данных, например, SUM():

 Value | Start      | Stop
-------------------------------
4     | 2000-01-01 | 2004-01-01
5     | 2004-01-01 | 2008-01-01
1     | 2008-01-01 | 2010-01-01
  

Для того, чтобы протестировать полученные результаты, я теперь хотел бы запросить данные напрямую, используя PostgreSQL. Я знаю, что пока нет простого способа решения этой проблемы. Однако, несомненно, есть способ получить те же результаты. Должно поддерживаться количество агрегаций, Max, Min, Sum и среднее значение. Я не возражаю против плохого или медленного решения, оно просто должно работать.

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

 select count(*), ts, te
from ( checkout a normalize checkout b using() ) checkoutNorm
group by ts, te;
  

Мое принятие выглядит следующим образом:

 select count(*), start, stop
from ( myTable a normalize myTable b using() ) myTableNorm
group by start, stop;
  

Однако было сообщено об ошибке ERROR: syntax error at or near "normalize" -- LINE 2: from ( ndbs_10 a normalize ndbs_10 b using() ) ndbsNorm .

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

Ваш вопрос был действительно труден для понимания. Но я думаю, что я понял это.
Вы хотите получить текущую сумму value . Значения применимы только между start и stop определенного периода времени. Таким образом, они должны быть добавлены в начале этого периода и вычтены в конце.
Кроме того, вам нужно указать начало и конец результирующего периода, для которого действительна сумма.
Этого должно хватить:

 -- DROP SCHEMA x CASCADE;
CREATE SCHEMA x;
CREATE TABLE x.tbl(val int, start date, stop date);
INSERT INTO x.tbl VALUES
 (4 ,'2000-01-01' ,'2008-01-01')
,(7 ,'2001-01-01' ,'2009-01-01')
,(1 ,'2004-01-01' ,'2010-01-01')
,(2 ,'2005-01-01' ,'2006-01-01');

WITH a AS (
    SELECT start as ts, val FROM x.tbl
    UNION  ALL
    SELECT stop, val * (-1) FROM x.tbl
    ORDER  BY 1, 2)
SELECT sum(val) OVER w AS val_sum
      ,ts AS start
      ,lead(ts) OVER w AS stop
FROM   a
WINDOW w AS (ORDER BY ts)
ORDER  BY ts;

val_sum |   start    |    stop
-------- ------------ ------------
      4 | 2000-01-01 | 2001-01-01
     11 | 2001-01-01 | 2004-01-01
     12 | 2004-01-01 | 2005-01-01
     14 | 2005-01-01 | 2006-01-01
     12 | 2006-01-01 | 2008-01-01
      8 | 2008-01-01 | 2009-01-01
      1 | 2009-01-01 | 2010-01-01
      0 | 2010-01-01 |
  

Редактировать после запроса

Для всех запрошенных агрегатных функций:

 SELECT period
      ,val_sum
      ,val_count
      ,val_sum::float /val_count AS val_avg
      ,(SELECT min(val) FROM x.tbl WHERE start < y.stop AND stop > y.start) AS val_min
      ,(SELECT max(val) FROM x.tbl WHERE start < y.stop AND stop > y.start) AS val_max
      ,start
      ,stop
FROM   (
    WITH a AS (
         SELECT start as ts, val, 1 AS c FROM x.tbl
         UNION  ALL
         SELECT stop, val, -1 FROM x.tbl
         ORDER  BY 1, 2)
    SELECT count(*) OVER w AS period
          ,sum(val*c) OVER w AS val_sum
          ,sum(c) OVER w AS val_count
          ,ts AS start
          ,lead(ts) OVER w AS stop
    FROM   a
    WINDOW w AS (ORDER BY ts)
    ORDER  BY ts
    ) y
WHERE stop IS NOT NULL;

 period | val_sum | val_count | val_avg | val_min | val_max |   start    |    stop
-------- --------- ----------- --------- --------- --------- ------------ ------------
      1 |       4 |         1 |       4 |       4 |       4 | 2000-01-01 | 2001-01-01
      2 |      11 |         2 |     5.5 |       4 |       7 | 2001-01-01 | 2004-01-01
      3 |      12 |         3 |       4 |       1 |       7 | 2004-01-01 | 2005-01-01
      4 |      14 |         4 |     3.5 |       1 |       7 | 2005-01-01 | 2006-01-01
      5 |      12 |         3 |       4 |       1 |       7 | 2006-01-01 | 2008-01-01
      6 |       8 |         2 |       4 |       1 |       7 | 2008-01-01 | 2009-01-01
      7 |       1 |         1 |       1 |       1 |       1 | 2009-01-01 | 2010-01-01
  

min() и max , возможно, можно было бы оптимизировать, но этого должно быть достаточно.
CTE ( WITH предложение) и подзапросы и, как вы можете видеть, могут быть заменены.