mysql - 使用 MySQL 创建排行榜

Question

我想根据交易表找出谁的利润最多。 (排行榜)

Table: Transactions    +--------------+    | id           |    +--------------+    | buy_user_id  |    +--------------+    | sell_user_id |    +--------------+    | amount       |    +--------------+    | price        |    +--------------+

This table holds information about transactions which occurred durning an exchange between two individuals. "buy_user_id" is the buyer while "sell_user_id" is the seller.

"amount" is how many stocks was bought, price is at what price (in USD). The turnover is then (amount*price).

I would like to figure out which users had the most profit. If you take the total profit minus the total losses, the end result should be 0.

e.g.:

    +---+---------+--------+    | # | User ID | Profit |    +---+---------+--------+    | 1 | 13      | +1200  |    +---+---------+--------+    | 2 | 52      | +300   |    +---+---------+--------+    | 3 | 29      | -500   |    +---+---------+--------+    | 4 | 72      | -1000  |    +---+---------+--------+

Suggestions?

Here is a full example, with transaction table first:

    +----+-------------+--------------+--------+-------+    | id | buy_user_id | sell_user_id | amount | price |    +----+-------------+--------------+--------+-------+     | 1  | 13          | 72           | 1000   | 0.01  | $10 paid by 13 for 1000 stocks   (now 13 has $10 loss while 72 has $10 profit)    +----+-------------+--------------+--------+-------+    | 2  | 72          | 13           | 1000   | 1.01  | $1010 paid by 72 for 1000 stocks (now 72 has $1000 loss while 13 has $1000 profit)    +----+-------------+--------------+--------+-------+    | 3  | 13          | 72           | 500    | 0.02  | $10 paid by 13 for 500 stocks    (now 72 has $990 loss while 13 has $990 profit)    +----+-------------+--------------+--------+-------+    | 4  | 72          | 13           | 100    | 5.10  | $510 paid by 72 for 100 stocks   (now 72 has $1500 loss while 13 has $1500 profit)    +----+-------------+--------------+--------+-------+    | 5  | 29          | 13           | 400    | 1.25  | $500 paid by 29 for 400 stocks   (now 29 has $500 loss while 13 has $2000 profit)

this example should generate this result:

    +---+---------+--------+    | # | User ID | Profit |    +---+---------+--------+    | 1 | 13      | +2000  |    +---+---------+--------+    | 2 | 72      | -1500  |    +---+---------+--------+    | 3 | 29      | -500   |    +---+---------+--------+

What would be the best way to accomplish this? And is this making sense?

My attempt:

SELECT sell_user_id as user_id, SUM(amount*price) as amount, 'sell' as type 
FROM exchange_transactions 
GROUP BY sell_user_id 
UNION 
SELECT buy_user_id as user_id, SUM(amount*price) as amount, 'buy' as type 
FROM exchange_transactions 
GROUP BY buy_user_id

Answer 1

SELECT user_id
     , SUM(profit) AS profit
     , SUM(stock_balance) AS stock_balance
FROM
    ( SELECT sell_user_id AS user_id
           , +SUM(amount*price) AS profit
           , -SUM(amount) AS stock_balance
      FROM exchange_transactions 
      GROUP BY sell_user_id
    UNION ALL
      SELECT buy_user_id
          , -SUM(amount*price)
          , +SUM(amount) 
      FROM exchange_transactions 
      GROUP BY buy_user_id
    ) AS tmp
GROUP BY user_id
ORDER BY profit DESC