关于一个 sql 优化问题

[MrGoooo]

有这样一个表,表里记录的是用户获得金币的记录表,每一条记录都详细的说明了这一条记录的金币是怎么得到的(或者用掉的),表结构大概如下:

id,user_id,count(得到 /消费的金币数量),change_type(得到 /消费的类型),create_time

现在有个需求:得到用户每一条记录创建时的剩余金币数,

得到的结果大概为:

id	user_id	count	change_type	create_time	remain
1	1	10	1	2020-10-10	10
2	1	-2	2	2020-10-10	8
3	1	15	3	2020-10-10	23
4	1	-14	4	2020-10-10	9

其实就是要这个 remain 字段。

建表语句:
CREATE TABLE `coin_record` (
`id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`user_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`count` int(11) NULL DEFAULT NULL,
`change_type` tinyint(3) NULL DEFAULT NULL,
`create_time` datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

感谢大家的回答,我得到了想要的结果。就是用开窗函数。
最终结果:
SELECT
*,
sum( count ) over (ORDER BY id ) AS remain
FROM
coin_record
WHERE user_id = 'XXXXX'

注意:一定要是'ORDER BY id',不能是'ORDER BY create_time',因为时间可能相同,导致结果错误。

了解一下开窗函数,explain 后发现会导致 filesort,好像用不了索引,不知道为啥

使用临时变量不会导致 filesort,效率应该比开窗函数高
SELECT
coin_record.*,
( @last_sum := @last_sum + count ) total
FROM
coin_record,
( SELECT @last_sum := 0 ) r
WHERE
user_id = '1'

分页之后,临时变量的赋值流程在 limit 语句执行之后,会导致得到的结果是错误的。
分页语句 0(得到结果不正确):
SELECT
coin_record.*,
( @last_sum := @last_sum + count ) total
FROM
coin_record,
( SELECT @last_sum := 0 ) r
WHERE
user_id = '1'
LIMIT 1,2;

但是使用开窗函数辅助一下,临时变量的赋值流程会变为在 limit 语句执行之前,会导致得到的结果是正确的。
分页语句 1(得到结果正确):
SELECT
coin_record.*,
row_number() over(), -- 这条语句很流弊,将临时变量的赋值流程提前了
( @last_sum := @last_sum + count ) total
FROM
coin_record,
( SELECT @last_sum := 0 ) r
WHERE
user_id = '1'
LIMIT 1,2;

[MrGoooo]

| id | user_id | count | change_type | create_time | remain |
| ---- | ----| ---- | ----| ---- | ----|
| 1 | 1 | 10 | 1 | 2020-10-10 | 10 |
| 2 | 1 | -2 | 2 | 2020-10-10 | 8 |
| 3 | 1 | 15 | 3 | 2020-10-10 | 23 |
| 4 | 1 |-14 | 4 | 2020-10-10 | 9 |

[gtchan13579]

remain 这个字段我认为不应该跟这个记录一起存储吧
何况这个字段其实不需要存储，查询一下 userid 然后统计一下 count 就出来了吧

[MrGoooo]

@gtchan13579 就是现在表里没有存 remain,需要把这个字段查出来,想对这个查询过程优化

[shyrock]

remain 要存储当时的剩余金币值？你这相当于找回历史记录啊。写个脚本对每个用户追溯一次记录呗。

[AngryPanda]

SELECT id, score, (SELECT SUM(score) FROM score WHERE id <= s.id) as remain FROM `score` s

[zhaokun]

SQL 都没有，何来优化之说？？

[sadfQED2]

建表语录，select 语录你发出来啊，不然优化空气啊

[MrGoooo]

@zhaokun 5 楼的就是

[MrGoooo]

@sadfQED2 5 楼的就是,建表语句已补充

[MrGoooo]

@AngryPanda 我就是你这么写的,不过要加上用户 id 判断,
SELECT id, count, (SELECT SUM(count) FROM coin_record WHERE id <= s.id and user_id = '1') as remain FROM `coin_record ` s where user_id = '1'

[0o0o0o0]

sum(count) over ( order by id ) as remain

[Egfly]

SELECT
s.id,
SUM(s2.count) as remain
FROM
coin_record as s
INNER JOIN coin_record as s2 ON s.user_id = s2.user_id AND s2.id <= s.id
WHERE
s.user_id = 1
GROUP BY s.id

[Egfly]

我觉得还是加一个 remain 字段，然后写个脚本维护一下历史数据比较好。适当的冗余一些字段，可以省很多事情

[c6h6benzene]

就是按行算 sum 吧。rows unbounded preceding

[IvanLi127]

如果能把余额也记录在表里的话，每次变化时查出上次余额后计算成当前余额一起写入，应该就不需要想怎么优化了。毕竟数据写入后不太可能变化，挺适合冗余的。

[justfindu]

你这个得每行进行重新计算. 一条 sql 看起来是没啥戏的

[dodosh]

开窗函数。

[Jface]

感觉开窗函数可以搞定。sum(count ) over(partition by user_id order by create_time ) as remain

[sakasaka]

可取方案不是优化 SQL，而是优化存储结构，这样的 SQL 写起来蠢蠢的

[brookepe]

```
select *, (uremain - IFNULL(sremain, 0)) as remain from (
select
coin_record.*,
users.remain as uremain,
(select sum(count) from coin_record cr1 where cr1.id > coin_record.id) sremain
from coin_record left join users on users.id = coin_record.user_id
) as coin_record

```

[MrGoooo]

了解一下开窗函数,explain 后发现会导致 filesort,好像用不了索引,不知道为啥