Sum every 3 rows of the table

Question

I have the following query to count all the data every minute.

$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"; 

What I need to do is get the count amount for each row with the score for the last few minutes.

 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 

Answer 1

This is not so difficult with the lag() window function (also in SQL Fiddle ):

 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; 

• I have double quotes for date and count , since these are reserved words;
• lag(field, distance) gives me the field column value of the distance column from the current, so the first function gives the previous row value, and the second call gives the value from the previous one;
• coalesce() is required to avoid the NULL result from the lag() function (for the first line in your query there is no "previous", so it is NULL ), otherwise total will also be NULL .

Answer 2

Here's a more general solution for the sum of the values ​​from the current and N previous lines (N = 2 in your case).

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

You can change N between 0 and "No limit". This approach gives you the option to have a last minute count option in your application. In addition, there is no need to handle default values ​​if they are out of bounds.

This can be found in PostgreSQL docs ( 4.2.8. Windows function calls )

Answer 3

@vyegorov's answer covers it mostly. But I have more problems than in the comments.

• Do not use reserved words such as date and count as identifiers at all. PostgreSQL allows these two keywords as an identifier - except for every SQL standard. But this is still bad practice. The fact that you can use anything inside double quotes as an identifier, even "; DELETE FROM tbl;" doesn't make him a good idea. The name "date" for a timestamp is misleading.

• Invalid data type. The example displays timestamp , not timestamptz . It does not matter here, but is still misleading.

• You do not need COALESCE() . Using the lag() and lead() functions, you can specify the default value as the third parameter:

Based on this setting:

 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); 

Query:

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

---

Or better : use one sum() as a window aggregation function with a custom window frame :

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

The same result.
Connected:

• Group by the end of the period instead of the start date