Date: Wed, 2 Oct 2013 18:06:08 -0700 (PDT)
From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Message-ID: <1380762368253-5773171.post@n5.nabble.com>
In-Reply-To: <1380751468173-5773160.post@n5.nabble.com>
References: <1380751468173-5773160.post@n5.nabble.com>
Subject: Re: Help needed with Window function
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

gmb wrote
>  item_code | _date            |  qty      | max
>  ---------------------------------------------------------
>  ABC       | 2013-04-05       |  10.00    | 2013-04-05    
>  ABC       | 2013-04-06       |  10.00    | 2013-04-06    
>  ABC       | 2013-04-06       |  -2.00    | 2013-04-06    
>  ABC       | 2013-04-07       |  10.00    | 2013-04-07    
>  ABC       | 2013-04-08       |  -2.00    | 2013-04-07    << last date
> where a positive qty was posted
>  ABC       | 2013-04-09       |  -1.00    | 2013-04-07    << last date
> where a positive qty was posted

Brute force approach; tweak if performance dictates:

WITH vals (id, amt, tag) AS ( VALUES (1, 10, '1'), (2, -2, '2'), (3, -3,
'3'), (4, 5, '4'), (5, -1, '5'), (6, 6, '6') )
SELECT *
, array_agg(CASE WHEN amt < 0 THEN NULL ELSE tag END) OVER (ORDER BY id)
, array_last_nonnull(array_agg(CASE WHEN amt < 0 THEN NULL ELSE tag END)
OVER (ORDER BY id))
FROM vals;

CREATE OR REPLACE FUNCTION array_last_nonnull(in_array anyarray) 
RETURNS anyelement
AS $$

	SELECT unnest FROM (
	SELECT unnest, row_number() OVER () AS array_index FROM (
	SELECT unnest($1)
	) explode ) filter
	WHERE unnest IS NOT NULL 
	ORDER BY array_index DESC
	LIMIT 1;	

$$
LANGUAGE sql
STRICT
IMMUTABLE
;

Basic idea: use ORDER BY in the window to auto-define a range-preceding
frame.  Create an array of all dates (tags in the example) that match with
positive amounts.  Negative amounts get their matching tag added to the
array as NULL.  The provided function looks into the generated array and
returns the last (closest to the current row in the frame) non-null date/tag
in the array which ends up being the date/tag matching the last positive
amount in the frame.

David J.


--
View this message in context: http://postgresql.1045698.n5.nabble.com/Help-needed-with-Window-function-tp5773160p5773171.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql