postgresql – 对表的每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
这对 lag() window function(也是 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;

>我有双引号日期和计数列,因为这些是保留字;> lag(field,distance)给出了离开当前行的字段列距离行的值,因此第一个函数给出前一行的值,第二个调用给出前一行的值;> coalesce()需要避免来自lag()函数的NULL结果(对于查询中的第一行,没有“previous”,因此它是NULL),否则total也将为NULL.

相关文章
相关标签/搜索