Imagine I have a table like so:
account_id date value
1 1/1/2015 5
1 1/3/2015 7
1 1/7/2015 8
3 1/2/2015 4
What if I wanted to do an ORDER BY DATE and GROUP BY account_id and update each row with the value of the row before it?
So the end result should be:
account_id date value prev_value
1 1/1/2015 5 null
1 1/3/2015 7 5
1 1/7/2015 8 7
3 1/2/2015 4 null
Any good way to do that in a single query?
lag(value anyelement [, offset integer [, default anyelement ]]) window function will do it for you, which basically:
returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null
WITH t(account_id,date,value) AS ( VALUES
(1,'1/1/2015'::DATE,5),
(1,'1/3/2015'::DATE,7),
(1,'1/7/2015'::DATE,8),
(3,'1/2/2015'::DATE,4)
)
SELECT
*,
lag(value,1) OVER (PARTITION BY account_id) AS prev_value
FROM t
GROUP BY 1,2,3
ORDER BY 1,2,3;
Result:
account_id | date | value | prev_value
------------+----------+-------+------------
1 | 1/1/2015 | 5 |
1 | 1/3/2015 | 7 | 5
1 | 1/7/2015 | 8 | 7
3 | 1/2/2015 | 4 |
(4 rows)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments