How to add rows with previous values to a table in PostgeSQL-CodePudding

I have a table with current levels of our users in PostgreSQL. The problem is I only have current level for each user, when I need to have all previous level for each user for further table transformation. Now my table looks like:

user_id	current_level
1	3
2	2
3	4
4	1
5	2

When I need it to look like:

user_id	current_level
1	1
1	2
1	3
2	1
2	2
3	1
3	2
3	3
3	4
4	1
5	1
5	2

What is the method I can use here to get such result? I assume there should be some window function but not sure which one (all window functions I know are doing different things).

CodePudding user response：

Sample data:

CREATE TABLE aaa (
    user_id int4 NULL,
    current_level int4 NULL,
);

INSERT INTO aaa (user_id, current_level) VALUES(1, 3);
INSERT INTO aaa (user_id, current_level) VALUES(2, 2);
INSERT INTO aaa (user_id, current_level) VALUES(3, 4);
INSERT INTO aaa (user_id, current_level) VALUES(4, 1);
INSERT INTO aaa (user_id, current_level) VALUES(5, 2);

Your query:

select user_id, generate_series(1, current_level) from aaa

Result:

user_id generate_series
1       1
1       2
1       3
2       1
2       2
3       1
3       2
3       3
3       4
4       1
5       1
5       2