MySQL 实现累加、累乘、累减、累除
table: TEST
+-----------+---------+---------+
| PARENT_ID | PART_ID | QUALITY |
+-----------+---------+---------+
| 1 | 1 | 2 |
| 1 | 2 | 3 |
| 1 | 3 | 2 |
| 1 | 4 | 5 |
| 2 | 2 | 3 |
| 2 | 3 | 5 |
| 2 | 4 | 7 |
+-----------+---------+---------+累加
最简略，用 sum+over 即可
SELECT
T.*,
SUM(T.QUALITY) OVER (PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RUNNING_QUALITY
FROM
TEST T;累乘
应用 EXP(SUM(LN(字段)) 实现累乘，其余的和累加一样！
SELECT
T.*,
ROUND(EXP(SUM(LN(T.QUALITY)) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID)),0) RUNNING_PROD
FROM
TEST T;累减
累减巧用负号！
（1）先用 row_number 排序
SELECT
T.*,
ROW_NUMBER() OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RN
FROM TEST T+-----------+---------+---------+----+
| PARENT_ID | PART_ID | QUALITY | RN |
+-----------+---------+---------+----+
| 1 | 1 | 2 | 1 |
| 1 | 2 | 3 | 2 |
| 1 | 3 | 2 | 3 |
| 1 | 4 | 5 | 4 |
| 2 | 2 | 3 | 1 |
| 2 | 3 | 5 | 2 |
| 2 | 4 | 7 | 3 |
+-----------+---------+---------+----+（2）当 RN= 1 时，保留第一位的数字，其余的全副取负号，这样就能够实现累减了！
SELECT
T.PARENT_ID,
T.PART_ID,
T.QUALITY,
SUM(if(T.RN=1,T.QUALITY,-T.QUALITY)) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RUNNING_PROD
FROM
(
SELECT
T.*,
ROW_NUMBER() OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RN
FROM
TEST T) T;累除
和累减相似，只不过这里要把非第一位的数字，取对数后再取负号，这样通过 sum 和 exp 就相当于做除法啦！
SELECT
T.PARENT_ID,
T.PART_ID,
T.QUALITY,
round(EXP(SUM(if(RN=1,LN(T.QUALITY),-LN(T.QUALITY))) OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID)),2) RUNNING_PROD
FROM
(
SELECT
T.*,
ROW_NUMBER() OVER(PARTITION BY T.PARENT_ID ORDER BY T.PART_ID) RN
FROM
TEST T)T;- 小红书：鲸鲸说数据
- 公众号：鲸析
- 网站：https://zg104.github.io/
本文由 mdnice 多平台公布