postgresql – 对表的每3行求和
发布时间:2020-12-13 16:16:38 所属栏目:百科 来源:网络整理
导读:我有以下查询来每分钟计算所有数据. $sql= "SELECT COUNT(*) AS count,date_trunc('minute',date) AS momentoFROM p WHERE fk_id_b=$id_b GROUP BY date_trunc('minute',date) ORDER BY momento ASC"; 我需要做的是获得每行的计数总和以及过去2分钟的计数. F
我有以下查询来每分钟计算所有数据.
$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. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |