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.

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

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.

CodePudding user response：

We can use ROW_NUMBER combined with conditional aggregation. This answer assumes that the "third" row per group is characterized by having the earliest predicted month value.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY [base month], shirt, pants, shoes
                                 ORDER BY [predicted month]) rn
    FROM yourTable
)

SELECT [predicted month], [base month], shirt, pants, shoes, [predicted rating],
       MAX(CASE WHEN rn = 1 THEN [predicted rating] END)
           OVER (PARTITION BY [base month], shirt, pants, shoes) AS [Actual rating]
FROM cte
ORDER BY [base month], shirt, pants, shoes, [predicted month] DESC;

CodePudding user response：

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;