Re: [SQL] 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 ) 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
Re: [SQL] Help needed with Window function
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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Help needed with Window function
> 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 (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 = 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 | 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
accumulated qty value for that date is a nett qty which you want.
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
