sql - 如果不同的列为真，则替换列的值

Question

此时在我的查询中，我的结果大致如下:

id     isCorrect    value
1       0            100
1       0            101
2       1            80
2       0            100
2       0            120
3       1            1
3       1            1

我的结果需要是这样的:

id     isCorrect    value
1        0           100.5
1        0           100.5
2        1           80
2        0           80
2        0           80
3        1           1
3        1           1

在英语中，对于给定的 ID，我希望 isCorrect = 1 的值替换该 ID 的其他值。如果没有 isCorrect 行，我想对它们进行平均。

据我所知，我正在执行一个 rank() 函数，为我将使用的行提供 1，例如:

SELECT id, isCorrect, value, rank() over (partition by id order by isCorrect DESC) as correctRank from foo

哪个会给我

id     isCorrect     value    correctRank
1       0             100      1
1       0             101      1
2       1             80       1
2       0             100      3
2       0             120      3
3       1             1        1
3       1             1        1

我遇到了从那里去哪里的路障。

Answer 1

您可以使用窗口函数获得所需的结果:

SELECT id, isCorrect,          
       CASE 
          WHEN MAX(isCorrect) OVER (PARTITION BY id ORDER BY isCorrect DESC) = 1
             THEN FIRST_VALUE([value]) OVER (PARTITION BY id ORDER BY isCorrect DESC)
          ELSE AVG([value]*1.0) OVER (PARTITION BY id)      
       END AS [value]
FROM mytable

Demo here