c# - 在参赛者之间动态分配奖品

Question

这真的让我大吃一惊。我创建了一个联盟，在该联盟中，我根据 leauge 中参赛者人数的最小和最大位置定义了奖品，即:

LeagueSizeID    LeagueSizeMin    LeagueSizeMax
     1                1               24
     2                25              49
     3                500             999

这与定义每个联赛支出的表格有关，这是 leaguesizeid 3 的示例

    LeaguesizeID    PositionMin PositionMax WinPerc
       3                  1         1        22.00
       3                  2         2        10.00
       3                  3         3         7.00
       3                  4         4         6.00
       3                  5         5         5.00
       3                  6         6         4.00
       3                  7         7         3.50
       3                  8         8         3.00
       3                  9         9         2.50
       3                  10        10        2.00
       3                  11        20        1.00
       3                  21        30        0.50

我采用的方法是循环每个位置并获得与该位置匹配的参赛者。我的问题是可能会有共同的赢家。如果有联合获奖者，则需要计算二等奖。所以对于下面的结果:

UserID    Position    Prize
  1          1         22.0%
  2          2         10
  3          3         6.5   // joint here so takes position 3 and 4 
  4          3         6.5   // and splits prize
  5          4         5 % // this is now position 5

我正在使用 DenseRank() 是 SQL 来对位置进行排序，但也许我需要将其更改为 Rank() 除非有人能指出算法的正确方向以从数据库对象驱动来执行此操作？

提前致谢!

Answer 1

我希望创建两个中间结果集。首先是 rank_bounds。这具有用户 ID、排名和该排名的关系数。第二个是 exploded_pa​​yout。每个支出位置都有一行，而不是汇总到最小和最大位置。一旦你有了这个，你就可以很容易地得到支付百分比。

我使用通用表表达式将它们放在一起(并简化并填充了一些缺失的表详细信息):

with ranks as (
  select
    UserID,
    rank() over (order by Score desc) rk
  from
    UserGameScores
  where
    GameID = 1
), rank_bounds as (
  select
    UserID,
    rk Position,
    count(1) over (partition by rk) as TieCount
  from
    ranks
), n as (
  select
    row_number() over (order by number) rn
  from
    -- if you have lots of payout lines, you might need more numbers than this
    master..spt_values
), exploded_payout as (
  select
    n.rn as Position,
    p.WinPerc
  from
    Payout p
      inner join
    n
      on p.PositionMin <= n.rn and p.PositionMax >= n.rn
) select
  UserId,
  avg(winperc)
from
  exploded_payout xp
    inner join
  rank_bounds rb
    on xp.Position >= rb.Position and
       xp.Position < rb.Position + rb.TieCount
group by
  UserId;

如果您想要结果集中的排名，您需要将最后一位放入 CTE 并再次加入排名表。

虽然 SQL 有点难以理解。我不确定程序方法会简单得多。此外，SQL 有一个很好的特性，它可以很容易地同时扩展到所有游戏:)

Example SQLFiddle