SQL语句ROI计算错误修正请求：按Profit/Bet计算收益率

问题原因 #

原ROI计算错误是因为SQL运算符优先级：除法(/)优先级高于减法(-)，你的原公式实际执行顺序是：

SUM(...) OVER () - (COUNT(*) OVER () / COUNT(*) OVER ())

也就是 Collect - 1，完全偏离了Profit / Bet的预期逻辑。

修正后的SQL语句 #

直接将Profit的完整表达式括起来，再除以投注数Bet，同时用ROUND函数保留两位小数：

SELECT 
  Bet = COUNT(*) OVER (), 
  Collect = SUM(CASE WHEN Result = 1 THEN (([BetfairWinSP] - 1) * .95 + 1) END) OVER (), 
  Profit = SUM(CASE WHEN Result = 1 THEN (([BetfairWinSP] - 1) * .95 + 1) END) OVER () - COUNT(*) OVER (), 
  Roi = ROUND(
    (SUM(CASE WHEN Result = 1 THEN (([BetfairWinSP] - 1) * .95 + 1) END) OVER () - COUNT(*) OVER ()) 
    / COUNT(*) OVER (), 
    2
  )

简化优化写法（可选） #

如果数据库支持，用CTE提前计算核心指标，让代码更清晰易维护：

WITH CoreMetrics AS (
  SELECT 
    TotalBets = COUNT(*) OVER (),
    TotalCollect = SUM(CASE WHEN Result = 1 THEN (([BetfairWinSP] - 1) * .95 + 1) END) OVER ()
  FROM YourTableName -- 替换为你的实际表名
)
SELECT
  Bet = TotalBets,
  Collect = TotalCollect,
  Profit = TotalCollect - TotalBets,
  Roi = ROUND((TotalCollect - TotalBets) / TotalBets, 2)
FROM CoreMetrics

内容的提问来源于stack exchange，提问作者racepro