Re: Help needed with Window function

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 ) filterWHERE
unnestIS NOT NULL ORDER BY array_index DESCLIMIT 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.