mysql - 使用mysql计算两个值的差异并根据结果进行排序

Question

  game table:
 | id | group_id | user_id | last_update         | bonus |
 | 1  | 4        | 1       | 2017-01-22 00:06:10 |    0  |
 | 2  | 4        | 1       | 2017-01-12 00:11:34 |  300  |
 | 3  | 4        | 1       | 2017-01-02 00:30:44 | -111  |
 | 3  | 4        | 1       | 2017-02-02 00:21:44 | 4330  |
 | 3  | 4        | 6       | 2017-01-02 01:02:27 | 30    |
 | 3  | 4        | 6       | 2017-01-07 11:22:37 | 40    |
 | 3  | 4        | 6       | 2017-03-04 11:22:37 |  0    |

我想计算给定组中每个用户当前日期的奖金减去当月第一天的奖金。

想要的输出:

| user_id | january (last day bonus - first day bonus) |
| 5       | 1400                                     |
| 19      | 1377                                     |
| 1       | 806                                      |
| 14      | 140                                      |
| 50      | 14                                       |

目前，我正在获得给定月份的奖金(1 个查询)，计算最后一个和第一个查询之间的差异。我有 4000 个用户，因此我要执行 4000 个查询来完成我想要的操作，但速度太慢了。

是否可以仅使用 mysql 来做到这一点？

Answer 1

也许“最正确”的方法是使用变量或复杂的子查询。然而，另一种可行的方法是使用group_concat()/substring_index():

select user_id, date_format(last_update, '%Y-%m') as yyyymm,
       substring_index(group_concat(bonus order by last_update desc), ',', 1) as last_bonus,
       substring_index(group_concat(bonus order by last_update asc), ',', 1) as first_bonus,
       (substring_index(group_concat(bonus order by last_update desc), ',', 1) -
        substring_index(group_concat(bonus order by last_update asc), ',', 1)
       ) as bonus_diff
from t
group by user_id, yyyymm;

请注意，这会将奖金转换为字符串，然后再转换回数字以进行计算。这就是为什么我可能称之为“快速而肮脏”或“黑客”。但是，它应该可以工作并且转换是安全的，因为值是以数字开始的。

其次，group_concat() 的默认限制为 1024 字节。对于这些聚合来说，这不应该成为问题——除非您在一个月内为用户提供了数百行数据。