SQL: Calculating a new column in Postgres that refers to its own rows

Question

Let' say I have the following table where percent_leave is the percentage of people from the previous period that leave in the current period:

| Period | percent_leave  |
----------------------
| 1      | 0.05           |
| 2      | 0.05           | 
| 3      | 0.05           |
| 4      | 0.05           |

I want to calculate a new column that will contain the percentage of people left at the end of that period. For example, if we start with 100 people, 5 people leave in the first period, therefore we are left with 95. 5% of 95 would leave in the second period, which leaves us with 90.25 people, and so forth. Then the table would look like:

| Period | percent_leave  | percent_remaining
-----------------------------------------
| 1      | 0.05           | 0.95
| 2      | 0.05           | 0.9025
| 3      | 0.05           | 0.857375
| 4      | 0.05           | 0.81450625

As you can see, the calculation of a new row in percent_remaining refers to the previous row in percent_remaining. Normally I would export this raw data into Excel and do this calculation there, but I would like to automate this task in SQL so I need to figure out how to do this in Postgres.

Any ideas?

Gordon Linoff · Accepted Answer · 2017-03-24 20:05:52Z

5

You can do this with a cumulative sum . . . and some arithmetic:

select t.*,
       exp(sum(ln(1-percent_leave)) over (order by period))
from t;

This is essentially implementing product() as a window function. This assumes that percent_leave is always less than 1 and greater than or equal to 0.

answered Mar 24, 2017 at 20:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4505419 Over a year ago

Ooh very nice. Thanks!

Vamsi Prabhala · Accepted Answer · 2017-03-25 00:00:26Z

1

You can also simply use the pow function.

select period,percent_leave,pow((1-percent_leave),period) as percent_remaining
from t

If period values are not consecutive, use row_number as the second argument to pow.

select period,percent_leave
,pow((1-percent_leave),row_number() over(order by period)) as percent_remaining
from t

answered Mar 25, 2017 at 0:00

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Collectives™ on Stack Overflow

SQL: Calculating a new column in Postgres that refers to its own rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related