php - MySQL 增长变化之和与增长顺序

Question

您好，我有两个表用于投资组合和股票数据详细信息。我想知道价格变化导致的用户组合总增长有多少。例如:

用户A:

股票A -4.41

股票B -1.49

库存C 0.38

库存 D 1.43

用户B

股票A -2.05

库存 B .05

我想显示如下表格:用户增长

                        A      -4.09

                        B      -2.1

这是我的两个表:一个投资组合，其中所有买卖信息均按 user_id 保存用户

 +-------------------+---------------+------+-----+---------+----------------+
 | Field             | Type          | Null | Key | Default | Extra          |
 +-------------------+---------------+------+-----+---------+----------------+
 | portfolio_ID      | int(11)       | NO   | PRI | NULL    | auto_increment |
 | user_id           | int(11)       | NO   |     | NULL    |                |
 | contest_id        | int(11)       | NO   |     | NULL    |                |
 | company_id        | int(11)       | NO   |     | NULL    |                |
 | share_amount      | int(11)       | NO   |     | NULL    |                |
 | buy_price         | decimal(9,2)  | NO   |     | NULL    |                |
 | total_buy_price   | decimal(10,4) | NO   |     | NULL    |                |
 | buy_date          | date          | NO   |     | NULL    |                |
 | commision         | decimal(9,2)  | NO   |     | NULL    |                |
 | sell_share_amount | int(11)       | NO   |     | NULL    |                |
 | sell_price        | decimal(10,2) | NO   |     | NULL    |                |
 | total_sell_price  | decimal(16,2) | NO   |     | NULL    |                |
 | sell_date         | date          | NO   |     | NULL    |                |
 | sell_commision    | decimal(9,2)  | NO   |     | NULL    |                |
 +-------------------+---------------+------+-----+---------+----------------+

接下来是每日库存表:

 +-----------------+------------------+------+-----+---------+-------+
 | Field           | Type             | Null | Key | Default | Extra |
 +-----------------+------------------+------+-----+---------+-------+
 | company_id      | int(11)          | NO   | PRI | NULL    |       |
 | entry_date      | date             | NO   | PRI | NULL    |       |
 | entry_timestamp | int(10) unsigned | NO   |     | NULL    |       |
 | open            | decimal(16,2)    | NO   |     | NULL    |       |
 | high            | decimal(16,2)    | NO   |     | NULL    |       |
 | low             | decimal(16,2)    | NO   |     | NULL    |       |
 | ltp             | decimal(16,2)    | NO   |     | NULL    |       |
 | ycp             | decimal(16,2)    | NO   |     | NULL    |       |
 | cse_price       | decimal(9,2)     | NO   |     | NULL    |       |
 | cse_volume      | decimal(18,2)    | NO   |     | NULL    |       |
 | total_trade     | int(30)          | NO   |     | NULL    |       |
 | total_volume    | int(30)          | NO   |     | NULL    |       |
 | total_value     | decimal(18,4)    | NO   |     | NULL    |       |
 | changes         | float(10,2)      | NO   |     | NULL    |       |
 | floating_cap    | float(16,2)      | NO   |     | NULL    |       |
 +-----------------+------------------+------+-----+---------+-------+

这是我正在使用的sql:

 SELECT
 po.user_id,
 ((e.ltp-po.buy_price)/po.buy_price) AS growth
 FROM eod_stock AS e
 LEFT OUTER JOIN portfolio AS po
 ON e.company_id = po.company_id
 WHERE po.contest_id = 2
 GROUP BY po.user_id;

但它只返回第一个变化而不是增长变化的总和，然后我像这样修改它:

 SELECT
 po.user_id,
 SUM((e.ltp-po.buy_price)/po.buy_price) AS growth
 FROM eod_stock AS e
 LEFT OUTER JOIN portfolio AS po
 ON e.company_id = po.company_id
 WHERE po.contest_id = 2
 GROUP BY growth
 ORDER BY growth DESC;

但它会生成错误代码 1056，无法对增长进行分组

这是sql fiddle : http://sqlfiddle.com/#!2/b3a83/2

感谢您的阅读。

Answer 1

您不想按增长进行分组。为什么要更改分组依据？

SELECT po.user_id, SUM((e.ltp-po.buy_price)/po.buy_price) AS growth
FROM eod_stock e LEFT OUTER JOIN
     portfolio po
     ON e.company_id = po.company_id
WHERE po.contest_id = 2
GROUP BY po.user_id
ORDER BY growth DESC;

但是，我怀疑您实际上可能想要最大值减去最小值:

SELECT po.user_id,
       (MAX(e.ltp-po.buy_price) - MIN(e.ltp-po.buy_price))/po.buy_price) AS growth
FROM eod_stock e LEFT OUTER JOIN
     portfolio po
     ON e.company_id = po.company_id
WHERE po.contest_id = 2
GROUP BY po.user_id
ORDER BY growth DESC;

这提供了一段时间内的总体 View 。但是，它没有显示方向。因此，从 50 到 100 的值与从 100 到 50 的值具有相同的值。如果这是您真正想要的，则可以修复此问题。