Thread: 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.
David Johnston wrote > 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. Hi David, Thanks for your reply. This is an approach I also considered, but hoped for a solution without the expense (albeit small) of having to create a function. Just wanted to confirm that I'm not missing a simpler solution (my knowledge in terms of window functions is rather limited). Until something better comes along, I'll implement the solution as suggested here. Regards GMB -- View this message in context: http://postgresql.1045698.n5.nabble.com/Help-needed-with-Window-function-tp5773160p5773196.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
> This is an approach I also considered, but hoped for a solution without the
> expense (albeit small) of having to create a function.
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
(PARTITIONBY 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 = t2.item_code AND t1._date >= t2._date
) t
WHERE accumulated_qty >= 0
GROUP BY item_code, _date ;
item_code | _date | max | nett_qty
-----------+------------+------------+----------ABC | 2013-04-05 | 2013-04-05 | 10ABC | 2013-04-06 |
2013-04-06| 8ABC | 2013-04-07 | 2013-04-07 | 10ABC | 2013-04-08 | 2013-04-07 | 8ABC
| 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| 10ABC | 2013-04-06 | 8 | ABC | 2013-04-06 | 8ABC | 2013-04-06 | 8
|ABC | 2013-04-05 | 10ABC | 2013-04-07 | 10 | ABC | 2013-04-07 | 10ABC |
2013-04-07| 10 | ABC | 2013-04-06 | 8ABC | 2013-04-07 | 10 | ABC | 2013-04-05 |
10ABC | 2013-04-08 | -2 | ABC | 2013-04-08 | -2ABC | 2013-04-08 | -2 | ABC |
2013-04-07| 10ABC | 2013-04-08 | -2 | ABC | 2013-04-06 | 8ABC | 2013-04-08 | -2
|ABC | 2013-04-05 | 10ABC | 2013-04-09 | -1 | ABC | 2013-04-09 | -1ABC |
2013-04-09| -1 | ABC | 2013-04-08 | -2ABC | 2013-04-09 | -1 | ABC | 2013-04-07 |
10ABC | 2013-04-09 | -1 | ABC | 2013-04-06 | 8ABC | 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 |
10ABC | 2013-04-06 | 2013-04-06 | 8 | 8ABC | 2013-04-06 | 2013-04-05 |
10 | 18ABC | 2013-04-07 | 2013-04-07 | 10 | 10ABC | 2013-04-07 |
2013-04-06 | 8 | 18ABC | 2013-04-07 | 2013-04-05 | 10 | 28ABC |
2013-04-08| 2013-04-08 | -2 | -2ABC | 2013-04-08 | 2013-04-07 | 10 |
8ABC | 2013-04-08 | 2013-04-06 | 8 | 16ABC | 2013-04-08 | 2013-04-05 | 10 |
26ABC | 2013-04-09 | 2013-04-09 | -1 | -1ABC | 2013-04-09 | 2013-04-08 |
-2 | -3ABC | 2013-04-09 | 2013-04-07 | 10 | 7ABC | 2013-04-09 |
2013-04-06 | 8 | 15ABC | 2013-04-09 | 2013-04-05 | 10 | 25
3. Select the max date which have a positive accumulated qty value. The accumulated qty value for that date is a nett
qtywhich you want.
item_code | _date | max | nett_qty
-----------+------------+------------+----------ABC | 2013-04-05 | 2013-04-05 | 10ABC | 2013-04-06 |
2013-04-06| 8ABC | 2013-04-07 | 2013-04-07 | 10ABC | 2013-04-08 | 2013-04-07 | 8ABC
| 2013-04-09 | 2013-04-07 | 7
Akihiro Okuno