agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Akihiro Okuno <choplin.choplin@gmail.com>
To: gmb <gmbouwer@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help needed with Window function
Date: Fri, 4 Oct 2013 12:08:05 +0900
Message-ID: <DB0DF543-E608-487F-A120-75F398EA3D38@gmail.com> (raw)
In-Reply-To: <1380777056063-5773196.post@n5.nabble.com>
References: <1380751468173-5773160.post@n5.nabble.com>
<1380762368253-5773171.post@n5.nabble.com>
<1380777056063-5773196.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
view thread (3+ messages)
Message-ID: <DB0DF543-E608-487F-A120-75F398EA3D38@gmail.com>
Permalink: ../DB0DF543-E608-487F-A120-75F398EA3D38@gmail.com/
Also on: postgresql.org/message-id/DB0DF543-E608-487F-A120-75F398EA3D38@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: choplin.choplin@gmail.com, gmbouwer@gmail.com
Subject: Re: Help needed with Window function
In-Reply-To: <DB0DF543-E608-487F-A120-75F398EA3D38@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox