сумма всех 3 строках таблицы

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

$sql= "SELECT COUNT(*) AS count, date_trunc('minute', date) AS momento
FROM p WHERE fk_id_b=$id_b GROUP BY date_trunc('minute', date) 
ORDER BY momento ASC";

Что мне нужно сделать, так это получить сумму подсчета для каждой строки с подсчетом 2 последних минут.

For example with the result of the $sql query above
|-------date---------|----count----|
|2012-06-21 05:20:00 |      12     |
|2012-06-21 05:21:00 |      14     |
|2012-06-21 05:22:00 |      10     |
|2012-06-21 05:23:00 |      20     |
|2012-06-21 05:24:00 |      25     |
|2012-06-21 05:25:00 |      30     |
|2012-06-21 05:26:00 |      10     |  

I want this result:

|-------date---------|----count----|
|2012-06-21 05:20:00 |      12     |   
|2012-06-21 05:21:00 |      26     |     12+14
|2012-06-21 05:22:00 |      36     |     12+14+10
|2012-06-21 05:23:00 |      44     |     14+10+20
|2012-06-21 05:24:00 |      55     |     10+20+25
|2012-06-21 05:25:00 |      75     |     20+25+30
|2012-06-21 05:26:00 |      65     |     25+30+10
3 7
postgresql window-functions group-by

3 ответа:

Это не так сложно с lag() функция окна (также на скрипке SQL):

CREATE TABLE t ("date" timestamptz, "count" int4);
INSERT INTO t VALUES
('2012-06-21 05:20:00',12),
('2012-06-21 05:21:00',14),
('2012-06-21 05:22:00',10),
('2012-06-21 05:23:00',20),
('2012-06-21 05:24:00',25),
('2012-06-21 05:25:00',30),
('2012-06-21 05:26:00',10);

SELECT *,
    "count"
    + coalesce(lag("count", 1) OVER (ORDER BY "date"), 0)
    + coalesce(lag("count", 2) OVER (ORDER BY "date"), 0) AS "total"
  FROM t;
  1. я дважды процитировал столбцы date и count, поскольку это зарезервированные слова;
  2. lag(field, distance) дает мне значение столбца field distance строк, удаленных от текущего, таким образом, первая функция дает значение предыдущей строки, а второй вызов дает значение из предыдущего;
  3. coalesce() требуется, чтобы избежать NULL результата от lag() функции (для первой строки в вашем запрос нет никакого "предыдущего", таким образом, это NULL), иначе total также будет NULL.
11

Вот более общее решение для суммы значений из текущей и N предыдущих строк (N=2 в вашем случае). 

SELECT "date", 
sum("count") OVER (order by "date" ROWS BETWEEN 2 preceding AND current row)
FROM t
ORDER BY "date";

Вы можете изменить N между 0 и"Unbounded". Этот подход дает вам возможность иметь параметр в вашем приложении "количество N последних минут". Кроме того, нет необходимости обрабатывать значения по умолчанию, если они выходят за пределы.

Подробнее об этом можно прочитать в PostgreSQL docs (4.2.8. Вызовы Функций Окна )
11

@ответ Егорова охватывает его в основном. Но у меня больше претензий, чем уместно в комментарии.

  1. Не используйте зарезервированные слова , такие как date и count в качестве идентификаторов вообще. PostgreSQL допускает эти два конкретных ключевых слова в качестве идентификатора-кроме каждого стандарта SQL. Но это все равно плохая практика. Тот факт, что вы можете использовать что-либо внутри двойных кавычек в качестве идентификатора, даже "; DELETE FROM tbl;", не делает это хорошей идеей. Имя "date" для a timestamp в довершение всего вводит в заблуждение.

  2. Неверный тип данных. Примеры дисплеев timestamp, не timestamptz. Это не имеет значения, но все равно вводит в заблуждение.

  3. Вам не нужно COALESCE(). С помощью функций окна lag() и lead() вы можете указать значение по умолчанию в качестве 3-го параметра:

Построение на этой установке: 

CREATE TABLE tbl (ts timestamp, ct int4);
INSERT INTO tbl VALUES
  ('2012-06-21 05:20:00', 12)
, ('2012-06-21 05:21:00', 14)
, ('2012-06-21 05:22:00', 10)
, ('2012-06-21 05:23:00', 20)
, ('2012-06-21 05:24:00', 25)
, ('2012-06-21 05:25:00', 30)
, ('2012-06-21 05:26:00', 10);

Запрос: 

SELECT ts, ct + lag(ct, 1, 0) OVER (ORDER BY ts)
              + lag(ct, 2, 0) OVER (ORDER BY ts) AS total
FROM   tbl;

Или еще лучше : используйте один sum() в качестве окна агрегатная функция с пользовательской рамкой окна : 

SELECT ts, sum(ct) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM   tbl;

Тот же результат.
Связанные:

9