I have a database that rates outfits. I want to take the value of predicted rating when predicted month = base month and fill it into rows with the same base month, shirt, pants, and shoes in a new column called actual rating, as shown below.
| predicted month | base month | shirt | pants | shoes | predicted rating |
|---|---|---|---|---|---|
| 4 | 0 | 1 | 1 | 1 | 0.1 |
| 3 | 0 | 1 | 1 | 1 | 0.2 |
| 0 | 0 | 1 | 1 | 1 | 0.5 |
| 1 | 1 | 2 | 2 | 1 | 0.6 |
| 5 | 1 | 2 | 2 | 1 | 0.3 |
| 4 | 1 | 2 | 2 | 1 | 0.1 |
I bolded the rows which predicted month = base month.
Eg. Since the first 2 rows have the same base month, shirt, pants, shoes as the third row, whose predicted month = base month, I want to take the third row’s predicted rating and fill it in to actual ratings of rows 1-3. How can I do this?
Eg. I want to take the 4th row’s predicted rating and fill it in to actual ratings of rows 5-6
| predicted month | base month | shirt | pants | shoes | predicted rating | Actual rating |
|---|---|---|---|---|---|---|
| 4 | 0 | 1 | 1 | 1 | 0.1 | 0.5 |
| 3 | 0 | 1 | 1 | 1 | 0.2 | 0.5 |
| 0 | 0 | 1 | 1 | 1 | 0.5 | 0.5 |
| 1 | 1 | 2 | 2 | 1 | 0.6 | 0.6 |
| 5 | 1 | 2 | 2 | 1 | 0.3 | 0.6 |
| 4 | 1 | 2 | 2 | 1 | 0.1 | 0.6 |
There is no order of which the database is ordered. The last row could be the first.
I’ve tried partition by and looked for ways to do this still don’t know how. Is this possible with SQL? Thanks in advance.
Advertisement
Answer
It looks like you can use First_Value() for this:
select *, First_Value(predictedRating) over(partition by baseMonth order by predictedMmonth) as ActualRating from t;