Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id ED9E11F02EF1 for ; Thu, 26 Apr 2012 06:23:12 -0300 (ADT) Received: from mail.scanlab.de ([62.138.55.241]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SNKuq-0003lZ-C6 for pgsql-sql@postgresql.org; Thu, 26 Apr 2012 09:23:11 +0000 Received: from unknown (HELO Exchange-Server.scanlab-intern.de) ([172.16.20.15]) by mail.scanlab-intern.de with ESMTP/TLS/AES128-SHA; 26 Apr 2012 11:22:45 +0200 Received: from Exchange-Server.scanlab-intern.de ([127.0.0.1]) by Exchange-Server ([127.0.0.1]) with mapi; Thu, 26 Apr 2012 11:22:45 +0200 From: Andreas Gaab To: "pgsql-sql@postgresql.org" Date: Thu, 26 Apr 2012 11:22:44 +0200 Subject: Running mean filtering using Window Functions? Thread-Topic: Running mean filtering using Window Functions? Thread-Index: Ac0jjiMLeez+CrpdR1CGlE58LvNenw== Message-ID: <48DA836F3865C54B8FBF424A3B775AF6674B452B86@Exchange-Server> Accept-Language: de-DE Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: de-DE Content-Type: multipart/alternative; boundary="_000_48DA836F3865C54B8FBF424A3B775AF6674B452B86ExchangeServe_" MIME-Version: 1.0 X-Pg-Spam-Score: -0.0 (/) X-Archive-Number: 201204/71 X-Sequence-Number: 36548 --_000_48DA836F3865C54B8FBF424A3B775AF6674B452B86ExchangeServe_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, can I realize a running mean filter using window functions? What I can think of is: SELECT avg(random) OVER (PARTITION BY floor(time_s / 60) ORDER BY floor(tim= e_s / 60)) FROM ( SELECT generate_series(0,600,20) time_s, random() ) as data; which averages all measurements in one minute, but not every line with its = following two lines. Any suggestions? Best regards Andreas ___________________________________________________________________________ SCANLAB AG Dr. Andreas Simon Gaab Entwicklung * R & D Siemensstr. 2a * 82178 Puchheim * Germany Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199 mailto:a.gaab@scanlab.de * www.scanlab.de Amtsgericht M=FCnchen: HRB 124707 * USt-IdNr.: DE 129 456 351 Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik Aufsichtsrat (Vorsitz): Dr. Hans J. Langer ___________________________________________________________________________ --_000_48DA836F3865C54B8FBF424A3B775AF6674B452B86ExchangeServe_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi,<= /o:p>

<= p class=3DMsoNormal>can I realize a running mean filter using wind= ow functions?

= ;

What I can think of is:

SELECT avg(random) OVER (PARTITION BY floor(= time_s / 60) ORDER BY floor(time_s / 60))

FROM (

) as data;

w= hich averages all measurements in one minute, but not every line with its f= ollowing two lines.

Any suggestions?=

Best regards

Andreas

mailto:a.gaab@scanlab.de • www.scanlab.de

Amtsgericht M=FCnchen: HRB&= nbsp;124707 • USt-IdNr.: DE 129 456 351

Vorstand: Geor= g Hofner (Sprecher), Christian Huttenloher, Norbert Pet= schik

Aufsichtsrat (Vorsitz): Dr. Hans J. Langer

______= _____________________________________________________________________<= /o:p>

= --_000_48DA836F3865C54B8FBF424A3B775AF6674B452B86ExchangeServe_--