
鲸鲸说数据
V1
2023/03/09阅读:17主题:全栈蓝
MySQL实现累加、累乘、累减、累除
「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/
作者介绍

鲸鲸说数据
V1
💓公众号:鲸析 💓小红书:鲸鲸说数据