CODEWITHSUNDEEP
sqlpostgresql
user4505419
user4505419

Reputation: 341

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

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?

Upvotes: 1

Views: 82

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 5

Related Questions