如何在SQL中对返回的列运行计算

Question

我有一个运行良好的SQL。它得到了人们下注的总金额和他们赢得的总金额。我想对这些值做一些数学运算，以返回它们的ROI。

SELECT
*,
SUM(CASE WHEN result = 'win' THEN amount * odds - (amount * bonus) END) AS amountwon,
SUM(CASE WHEN result <> 'pending' AND bonus = 0 THEN amount END) AS amountbet
FROM
bets 
INNER JOIN users ON bets.user = users.id
WHERE
club = 2 
GROUP BY
user
ORDER BY roi DESC

我试过了，但没有运气

SELECT
*,
SUM(CASE WHEN result = 'win' THEN amount * odds - (amount * bonus) END) AS amountwon,
SUM(CASE WHEN result <> 'pending' AND bonus = 0 THEN amount END) AS amountbet,
COALESCE(((SUM(amountwon) * 100.0) / NULLIF(SUM(amountbet), 0)),
100) AS roi
FROM
bets 
INNER JOIN users ON bets.user = users.id
WHERE
club = 2 
GROUP BY
user
ORDER BY roi DESC

如何在基于这些值的新列上使用这些返回值amountwon和amountbet？

感谢

Answer 1

不能对amountwon和amountbet使用聚合函数SUM，因为它在同一查询中。使用子查询代替

select *, COALESCE(((SUM(amountwon) * 100.0) / NULLIF(SUM(amountbet), 0)),
100) AS roi
from (
your first query
)

Answer 2

为了可读性，您可以使用cte首先计算您的值，比如：

with cte as (
select
Sum(case when B.result = 'win' then B.amount * B.odds - (B.amount * B.bonus) end) as amountwon,
Sum(case when B.result <> 'pending' and B.bonus = 0 then B.amount end) as amountbet
from bets b inner join users u on b.user = u.id
where u.club = 2 
group by b.user
)
select *,
Coalesce(((amountwon * 100.0) / NullIf(amountbet, 0)), 100) as roi
from cte
order by roi desc

还请注意，使用有意义的短别名可以更容易地读取

Answer 3

WITH子句可用于此目的

WITH amounts(user_id, won, bet) AS (
SELECT
user,
SUM(CASE WHEN result = 'win' THEN amount * odds - (amount * bonus) END),
SUM(CASE WHEN result <> 'pending' AND bonus = 0 THEN amount END)
FROM bets
GROUP BY user
)
SELECT *, COALESCE(won*100.0/NULLIF(bet, 0) as roi from amounts order by roi;

或者，您可以使用子查询：

SELECT *, COALESCE(won*100.0/NULLIF(bet, 0) as roi from (
SELECT
user,
SUM(CASE WHEN result = 'win' THEN amount * odds - (amount * bonus) END) as won,
SUM(CASE WHEN result <> 'pending' AND bonus = 0 THEN amount END) as bet
FROM bets
GROUP BY user
) as amounts;

在这两种情况下，您现在都可以轻松地将上面的临时amounts和user表连接起来，以获取更多信息。