Re: Help needed with Window function - Mailing list pgsql-sql
| From | Akihiro Okuno |
|---|---|
| Subject | Re: Help needed with Window function |
| Date | |
| Msg-id | DB0DF543-E608-487F-A120-75F398EA3D38@gmail.com Whole thread Raw |
| In response to | Re: Help needed with Window function (gmb <gmbouwer@gmail.com>) |
| List | 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
(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