Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VRvkR-0001KE-6B for pgsql-sql@arkaria.postgresql.org; Fri, 04 Oct 2013 03:08:11 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VRvkP-0001vK-Ui for pgsql-sql@arkaria.postgresql.org; Fri, 04 Oct 2013 03:08:09 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VRvkO-0001vC-Q7 for pgsql-sql@postgresql.org; Fri, 04 Oct 2013 03:08:08 +0000 Received: from mail-pb0-x231.google.com ([2607:f8b0:400e:c01::231]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VRvkH-0008TO-8f for pgsql-sql@postgresql.org; Fri, 04 Oct 2013 03:08:07 +0000 Received: by mail-pb0-f49.google.com with SMTP id xb4so3325396pbc.36 for ; Thu, 03 Oct 2013 20:07:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=content-type:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=mnk3UWlCmZ+ujy2T71VZnTFZpb94zj4sfszDtTyIyJM=; b=HFGdcpc9OYySXTyl7GSQ8/kT+tpj5o+i7b8XzoKnMJsRgyOnb6KlKEv/0fwvLyWMbt kJdFm0wp1SRlZ7FYIBwhgLYOL8ZfBoc/STAX83aRlLg5Yfv3aZZH8PhaIvaZAeqlvSP4 PXKrl3mV1eN7DypIbu9V8pr5xAo/fg5te0k+NoxtpsGsnRXK3PmphOJbNFHR1iZnszSR nZBxZdcmTxo7KH0d0CeGVuk1vjneNIV/FXeG97NzmHOrWWMRLAJgGoceF1gVLOok8vRW gflAbnxnFIIJ2U9IsI9kK18w4mwlIKH+osHgb5Xl7louXOTHRT00icgiueynrdtuVzOW iQ1A== X-Received: by 10.68.136.7 with SMTP id pw7mr3866071pbb.106.1380856079306; Thu, 03 Oct 2013 20:07:59 -0700 (PDT) Received: from [192.168.255.32] ([210.251.87.248]) by mx.google.com with ESMTPSA id fi4sm11424704pbc.28.1969.12.31.16.00.00 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 03 Oct 2013 20:07:58 -0700 (PDT) Content-Type: text/plain; charset=us-ascii Mime-Version: 1.0 (Mac OS X Mail 6.5 \(1508\)) Subject: Re: Help needed with Window function From: Akihiro Okuno In-Reply-To: <1380777056063-5773196.post@n5.nabble.com> Date: Fri, 4 Oct 2013 12:08:05 +0900 Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: <1380751468173-5773160.post@n5.nabble.com> <1380762368253-5773171.post@n5.nabble.com> <1380777056063-5773196.post@n5.nabble.com> To: gmb X-Mailer: Apple Mail (2.1508) X-Pg-Spam-Score: 0.7 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org > This is an approach I also considered, but hoped for a solution without t= he > expense (albeit small) of having to create a function.=20 How about this query? ---- CREATE TABLE transactions ( item_code text, _date date, qty double precision ) ; INSERT INTO transactions VALUES ('ABC','2013-04-05',10.00), ('ABC','2013-04-06',10.00), ('ABC','2013-04-06',-2.00), ('ABC','2013-04-07',10.00), ('ABC','2013-04-08',-2.00), ('ABC','2013-04-09',-1.00) ; WITH aggregated_transactions AS ( SELECT item_code, _date, sum(qty) AS sum_qty FROM transactions GROUP BY item_code, _date ) SELECT item_code, _date, max(nett_qty_date), (array_agg(accumulated_qty ORDER BY _date DESC))[1] AS nett_qty FROM ( SELECT t1.item_code, t1._date, t2._date AS nett_qty_date, sum(t2.sum_qty) OVER (PARTITION BY t1.item_code, t1._date ORDER BY = t2._date DESC) AS accumulated_qty FROM aggregated_transactions t1 INNER JOIN aggregated_transactions t2 ON t1.item_code =3D t2.item_code AND t1.= _date >=3D t2._date ) t WHERE accumulated_qty >=3D 0 GROUP BY item_code, _date ; item_code | _date | max | nett_qty -----------+------------+------------+---------- ABC | 2013-04-05 | 2013-04-05 | 10 ABC | 2013-04-06 | 2013-04-06 | 8 ABC | 2013-04-07 | 2013-04-07 | 10 ABC | 2013-04-08 | 2013-04-07 | 8 ABC | 2013-04-09 | 2013-04-07 | 7 ---- Rough explanation: 1. List the past date for each date using self join. item_code | _date | sum_qty | item_code | _date | sum_qty -----------+------------+---------+-----------+------------+--------- ABC | 2013-04-05 | 10 | ABC | 2013-04-05 | 10 ABC | 2013-04-06 | 8 | ABC | 2013-04-06 | 8 ABC | 2013-04-06 | 8 | ABC | 2013-04-05 | 10 ABC | 2013-04-07 | 10 | ABC | 2013-04-07 | 10 ABC | 2013-04-07 | 10 | ABC | 2013-04-06 | 8 ABC | 2013-04-07 | 10 | ABC | 2013-04-05 | 10 ABC | 2013-04-08 | -2 | ABC | 2013-04-08 | -2 ABC | 2013-04-08 | -2 | ABC | 2013-04-07 | 10 ABC | 2013-04-08 | -2 | ABC | 2013-04-06 | 8 ABC | 2013-04-08 | -2 | ABC | 2013-04-05 | 10 ABC | 2013-04-09 | -1 | ABC | 2013-04-09 | -1 ABC | 2013-04-09 | -1 | ABC | 2013-04-08 | -2 ABC | 2013-04-09 | -1 | ABC | 2013-04-07 | 10 ABC | 2013-04-09 | -1 | ABC | 2013-04-06 | 8 ABC | 2013-04-09 | -1 | ABC | 2013-04-05 | 10 2. Calculate an accumulated qty value using window function sorted by date = in descending order. item_code | _date | nett_qty_date | sum_qty | accumulated_qty -----------+------------+---------------+---------+----------------- ABC | 2013-04-05 | 2013-04-05 | 10 | 10 ABC | 2013-04-06 | 2013-04-06 | 8 | 8 ABC | 2013-04-06 | 2013-04-05 | 10 | 18 ABC | 2013-04-07 | 2013-04-07 | 10 | 10 ABC | 2013-04-07 | 2013-04-06 | 8 | 18 ABC | 2013-04-07 | 2013-04-05 | 10 | 28 ABC | 2013-04-08 | 2013-04-08 | -2 | -2 ABC | 2013-04-08 | 2013-04-07 | 10 | 8 ABC | 2013-04-08 | 2013-04-06 | 8 | 16 ABC | 2013-04-08 | 2013-04-05 | 10 | 26 ABC | 2013-04-09 | 2013-04-09 | -1 | -1 ABC | 2013-04-09 | 2013-04-08 | -2 | -3 ABC | 2013-04-09 | 2013-04-07 | 10 | 7 ABC | 2013-04-09 | 2013-04-06 | 8 | 15 ABC | 2013-04-09 | 2013-04-05 | 10 | 25 3. Select the max date which have a positive accumulated qty value. The acc= umulated qty value for that date is a nett qty which you want.=20 item_code | _date | max | nett_qty -----------+------------+------------+---------- ABC | 2013-04-05 | 2013-04-05 | 10 ABC | 2013-04-06 | 2013-04-06 | 8 ABC | 2013-04-07 | 2013-04-07 | 10 ABC | 2013-04-08 | 2013-04-07 | 8 ABC | 2013-04-09 | 2013-04-07 | 7 Akihiro Okuno --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql