agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRe: Help needed with Window function
3+ messages / 3 participants
[nested] [flat]
* Re: Help needed with Window function
@ 2013-10-03 01:06 David Johnston <polobo@yahoo.com>
2013-10-03 05:10 ` Re: Help needed with Window function gmb <gmbouwer@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: David Johnston @ 2013-10-03 01:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help needed with Window function
2013-10-03 01:06 Re: Help needed with Window function David Johnston <polobo@yahoo.com>
@ 2013-10-03 05:10 ` gmb <gmbouwer@gmail.com>
2013-10-04 03:08 ` Re: Help needed with Window function Akihiro Okuno <choplin.choplin@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: gmb @ 2013-10-03 05:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help needed with Window function
2013-10-03 01:06 Re: Help needed with Window function David Johnston <polobo@yahoo.com>
2013-10-03 05:10 ` Re: Help needed with Window function gmb <gmbouwer@gmail.com>
@ 2013-10-04 03:08 ` Akihiro Okuno <choplin.choplin@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Akihiro Okuno @ 2013-10-04 03:08 UTC (permalink / raw)
To: gmb <gmbouwer@gmail.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-10-04 03:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-10-03 01:06 Re: Help needed with Window function David Johnston <polobo@yahoo.com>
2013-10-03 05:10 ` gmb <gmbouwer@gmail.com>
2013-10-04 03:08 ` Akihiro Okuno <choplin.choplin@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox