个人中心
文章发布
退出
作者热门文章
- Java锁的逻辑（结合对象头和ObjectMonitor）
- 还在用饼状图？来瞧瞧这些炫酷的百分比可视化新图形（附代码实现）⛵
- 自动注册实体类到EntityFrameworkCore上下文，并适配ABP及ABPVNext
- 基于Sklearn机器学习代码实战
28
4
说明 。
中位数、环比、同比概念请自行百度，本文求 字段A中位数、根据字段B分组后字段A中位数、字段A环比、字段A同比、字段A中位数的环比、字段A中位数的同比.
可替换部分标黄 。
1、表结构如下图 。
。
查询条件为 capital_name in （'金融机构1','金融机构2'），以下查询的中位数、环比等都基于此条件； 。
。
2、求【最终金额】的【中位数】 。
中位数主要是利用临时变量查询，且一个sql只能查询一个字段的中位数，下面的sql对中位数做保留2位小数点处理 。
1
SELECT
2
@max_row_number
:
=
max
( row_number ),
3
ROUND
( (
CASE
MOD (
@max_row_number
,
2
)
4
WHEN
0
THEN
(
sum
(
IF
( row_number
=
FLOOR
(
@max_row_number
/
2
)
OR
row_number
=
FLOOR
(
@max_row_number
/
2
)
+
1
, final_app_amount,
0
))
/
2
)
5
WHEN
1
THEN
SUM
(
IF
( row_number
=
FLOOR
(
@max_row_number
/
2
)
+
1
, final_app_amount,
0
))
END
6
),
2
)
AS
final_app_amount_median
7
FROM
8
(
9
SELECT
10
final_app_amount
,
11
@rank AS row_number,
12
@rank
:
=
@rank
+
1
13
FROM
repay_customer
AS
t1,
14
(
SELECT
@rank
:
=
1
) t2
15
WHERE
16
1
=
1
AND
capital_name
IN
(
'金融机构1
'
,
'金融机构2
'
)
17
ORDER
BY
final_app_amount
18
) t3,
19
(
SELECT
@max_row_number
:
=
0
) t4
。
3、求【最终金额】的【分组中位数】 。
即根据时间，计算每月的最终金额的中位数，对结果做保留2位小数处理 。
1
SELECT
2
group_index,
3
loan_time_credit,
4
CASE
MOD (
count
(
*
),
2
)
5
WHEN
0
THEN
ROUND
( (
sum
(
IF
( rank
=
FLOOR
( group_count
/
2
)
OR
rank
=
FLOOR
( group_count
/
2
)
+
1
, final_app_amount,
0
))
/
2
),
2
)
6
WHEN
1
THEN
ROUND
( (
SUM
(
IF
( rank
=
FLOOR
( group_count
/
2
)
+
1
, final_app_amount,
0
) ) ),
2
)
7
END
AS
final_app_amount_median
8
FROM
9
(
10
SELECT
11
t3.
*
,
12
@group_count
:
=
CASE
WHEN
@last_group_index
=
group_index
THEN
@group_count
ELSE
rank
END
AS
group_count,
13
@last_group_index
:
=
group_index
14
FROM
15
(
16
SELECT
17
CONCAT( DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
) )
AS
group_index,
18
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
AS
loan_time_credit,
19
final_app_amount
AS
final_app_amount,
20
@rank
:
=
CASE
WHEN
@last_group
=
CONCAT( DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
) )
THEN
@rank
+
1
ELSE
1
END
AS
rank,
21
@last_group
:
=
CONCAT( DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
))
22
FROM
23
repay_customer
AS
t1,
24
(
SELECT
@group_count
:
=
0
,
@rank
:
=
0
) t2
25
WHERE
26
1
=
1
AND
capital_name
IN
(
'
金融机构1
'
,
'
金融机构2
'
)
27
ORDER
BY
28
loan_time_credit,
29
final_app_amount
30
) t3,
31
(
SELECT
@group_count
:
=
0
,
@last_group_index
:
=
0
) t4
32
ORDER
BY
33
group_index,
34
rank
DESC
35
) t5
36
GROUP
BY
37
group_index
。
。
4、求【最终金额】和【合同金额】的环比 。
。
环比一般以月为分组条件，求环比的分组字段必须为时间字段，且只有一个时间字段； 。
以下sql求每月 “最终金额“ 的“和“ 的环比增长量、增长率， 和 每月 “合同金额“ 的 “平均值” 的环比增长量、增长率； 。
【注】此sql中计算了sum的环比和avg的环比，同理可换成 min、max，count 等； 。
注意 ：此sql思路为根据查询条件计算出目标数据的最小时间和最大时间，罗列此区间内的所有月，再匹配分组后结果，例如， 2021-11 月里没有 金融机构1和金融机构2的数据，但结果中依然后 2021-11 这一行，目的是为了更明显的与上个月做对比； 。
对结果做保留2位小数点处理； 。
1
SELECT
2
t3.group_index,
3
t3.group_index
AS
loan_time_credit,
4
ROUND
( ( ( t3.final_app_amount_sum_growth
-
last_final_app_amount_sum_growth )
/
last_final_app_amount_sum_growth ),
2
)
AS
final_app_amount_sum_rises,
5
ROUND
( ( ( t3.contract_amount_avg_growth
-
last_contract_amount_avg_growth )
/
last_contract_amount_avg_growth ),
2
)
AS
contract_amount_avg_rises,
6
ROUND
( ( t3.final_app_amount_sum_growth
-
t3.last_final_app_amount_sum_growth ),
2
)
AS
final_app_amount_sum_growth,
7
ROUND
( ( t3.contract_amount_avg_growth
-
t3.last_contract_amount_avg_growth ),
2
)
AS
contract_amount_avg_growth
8
FROM
9
(
10
SELECT
11
12
@last_final_app_amount_sum_growth
:
=
CASE
WHEN
@last_group_index
!=
group_index
THEN
@last_final_app_amount_sum_growth
ELSE
t1.final_app_amount_sum_growth
END
AS
last_final_app_amount_sum_growth,
13
@last_contract_amount_avg_growth
:
=
CASE
WHEN
@last_group_index
!=
group_index
THEN
@last_contract_amount_avg_growth
ELSE
t1.contract_amount_avg_growth
END
AS
last_contract_amount_avg_growth,
14
t1.
*
,
15
@last_group_index
:
=
group_index,
16
@last_final_app_amount_sum_growth
:
=
t1.final_app_amount_sum_growth,
17
@last_contract_amount_avg_growth
:
=
t1.contract_amount_avg_growth
18
FROM
19
(
select
@start_date
:
=
(
select
min
(loan_time_credit)
from
repay_customer
where
1
=
1
and
capital_name
IN
(
'
金融机构1
'
,
'
金融机构2
'
)
),
20
@end_date
:
=
(
select
max
(loan_time_credit)
from
repay_customer
where
1
=
1
and
capital_name
IN
(
'
金融机构1
'
,
'
金融机构2
'
))
) t4 ,
21
(
22
SELECT
23
group_index,
24
final_app_amount_sum_growth,
25
contract_amount_avg_growth
26
FROM
27
(
28
SELECT
29
DATE_FORMAT( date_sub(
@start_date
, INTERVAL (
@i
:
=
@i
-
1
)
MONTH
),
'
%Y-%m
'
)
AS
group_index
30
FROM
31
mysql.help_topic
32
JOIN
(
SELECT
@i
:
=
1
) c
33
WHERE
34
help_topic_id
<=
(
35
TIMESTAMPDIFF(
MONTH
,
@start_date
,
@end_date
))
36
) dateI
37
LEFT
JOIN
(
38
SELECT
39
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
AS
loan_time_credit,
40
sum
(
final_app_amount
)
AS
final_app_amount_sum_growth,
41
avg
(
contract_amount
)
AS
contract_amount_avg_growth
42
FROM
43
repay_customer
44
WHERE
45
1
=
1
46
AND
capital_name
IN
(
'
金融机构1
'
,
'
金融机构2
'
)
47
GROUP
BY
48
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)) dataA
ON
dateI.group_index
=
dataA.loan_time_credit
49
) t1,(
50
SELECT
51
@last_group_index
:
=
0
,
52
@last_final_app_amount_sum_growth
:
=
0
,
53
@last_contract_amount_avg_growth
:
=
0
54
) t2
55
) t3
。
。
。
5、求【最终金额】和【合同金额】的同比 。
。
同比一般与上一年比较，求同比的分组字段必须为时间字段，且只有一个时间字段； 。
以下sql求每月 “最终金额“ 的“和“ 的同比增长量、增长率， 和 每月 “合同金额“ 的 “平均值” 的同比增长量、增长率； 。
【注】此sql中计算了sum的同比和avg的同比，同理可换成 min、max，count 等； 。
注意 ：此sql思路为根据查询条件计算出目标数据的最小时间和最大时间，罗列此区间内的所有月，再匹配分组后结果，例如， 2021-11 月里没有 金融机构1和金融机构2的数据，但结果中依然后 2021-11 这一行，目的是为了更明显的与上个月做对比； 。
对结果做保留2位小数点处理； 。
1
SELECT
2
t1.group_index,
3
t1.group_index
AS
loan_time_credit,
4
ROUND
( ( ( t2.final_app_amount_sum_growth
-
t3.final_app_amount_sum_growth )
/
t3.final_app_amount_sum_growth ),
2
)
AS
final_app_amount_sum_rises,
5
ROUND
( ( ( t2.contract_amount_avg_growth
-
t3.contract_amount_avg_growth )
/
t3.contract_amount_avg_growth ),
2
)
AS
contract_amount_avg_rises,
6
t2.final_app_amount_sum_growth
-
t3.final_app_amount_sum_growth
AS
final_app_amount_sum_growth,
7
t2.contract_amount_avg_growth
-
t3.contract_amount_avg_growth
AS
contract_amount_avg_growth
8
FROM
9
(
select
@start_date
:
=
(
select
min
(
loan_time_credit
)
from
repay_customer
where
1
=
1
and
capital_name
IN
(
'
金融机构1
'
,
'金融机构2
'
)
),
10
@end_date
:
=
(
select
max
(
loan_time_credit
)
from
repay_customer
where
1
=
1
and
capital_name
IN
(
'
金融机构1
'
,
'金融机构2
'
)
)) t4 ,
11
(
12
SELECT
13
DATE_FORMAT( date_sub(
@start_date
, INTERVAL (
@i
:
=
@i
-
1
)
MONTH
),
'
%Y-%m
'
)
AS
group_index
14
FROM
15
mysql.help_topic
16
JOIN
(
SELECT
@i
:
=
1
) c
17
WHERE
18
help_topic_id
<=
(
19
TIMESTAMPDIFF(
MONTH
,
@start_date
,
@end_date
) )
20
) t1
21
LEFT
JOIN
(
22
SELECT
23
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
AS
group_index,
24
DATE_FORMAT( DATE_ADD(
loan_time_credit
, INTERVAL
-
1
YEAR
),
'
%Y-%m
'
)
AS
last_group_index,
25
sum
(
final_app_amount
)
AS
final_app_amount_sum_growth,
26
avg
(
contract_amount
)
AS
contract_amount_avg_growth
27
FROM
28
repay_customer
29
WHERE
30
1
=
1
31
AND
capital_name
IN
(
'
华夏银行
'
,
'
蓝海银行
'
,
'
中金租
'
)
32
GROUP
BY
33
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
34
) t2
ON
t1.group_index
=
t2.group_index
35
LEFT
JOIN
(
36
SELECT
37
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
AS
group_index,
38
sum
(
final_app_amount
)
AS
final_app_amount_sum_growth,
39
avg
(
contract_amount
)
AS
contract_amount_avg_growth
40
FROM
41
repay_customer
42
WHERE
43
1
=
1
44
AND
capital_name
IN
(
'金融机构1
'
,
'金融机构2
'
)
45
AND
loan_time_credit
>=
DATE_ADD(
@start_date
, INTERVAL
-
1
YEAR
)
46
AND
loan_time_credit
<=
DATE_ADD(
@end_date
, INTERVAL
-
1
YEAR
)
47
GROUP
BY
48
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
49
) t3
ON
t2.last_group_index
=
t3.group_index
。
。
。
6、求【最终金额】中位数的环比 。
分组字段只能为时间且只有一个； 。
一个sql只能查一个字段的中位数； 。
对结果做保留2位小数点处理； 。
1
SELECT
2
t3.group_index,
3
t3.group_index
AS
loan_time_credit,
4
ROUND
( ( t3.final_app_amount
-
t3.last_final_app_amount ),
2
)
AS
final_app_amount_median_growth,
5
ROUND
( ( ( t3.final_app_amount
-
last_final_app_amount )
/
last_final_app_amount ),
2
)
AS
final_app_amount_median_rises
6
FROM
7
(
8
SELECT
9
@last_final_app_amount
:
=
CASE
WHEN
@last_group_index
!=
group_index
THEN
@last_final_app_amount
ELSE
t1.final_app_amount
END
AS
last_final_app_amount,
10
t1.
*
,
11
@last_group_index
:
=
group_index,
12
@last_final_app_amount
:
=
t1.final_app_amount
13
FROM
14
(
15
SELECT
16
dateI.group_index,
17
final_app_amount
18
FROM
19
(
select
@start_date
:
=
(
select
min
(
loan_time_credit
)
from
repay_customer
where
1
=
1
and
capital_name
IN
(
'
金融机构1
'
,
'
金融机构2
'
)
),
20
@end_date
:
=
(
select
max
(
loan_time_credit
)
from
repay_customer
where
1
=
1
and
capital_name
IN
(
'
金融机构1
'
,
'
金融机构2
'
)
)) t4 ,
21
(
22
SELECT
23
DATE_FORMAT( date_sub(
@start_date
, INTERVAL (
@i
:
=
@i
-
1
)
MONTH
),
'
%Y-%m
'
)
AS
group_index
24
FROM
25
mysql.help_topic
26
JOIN
(
SELECT
@i
:
=
1
) c
27
WHERE
28
help_topic_id
<=
(
29
TIMESTAMPDIFF(
MONTH
,
@start_date
,
@end_date
))
30
) dateI
31
LEFT
JOIN
(
32
SELECT
33
group_index,
34
CASE
35
MOD (
count
(
*
),
2
)
36
WHEN
0
THEN
37
(
38
sum
(
39
IF
40
( rank
=
FLOOR
( group_count
/
2
)
OR
rank
=
FLOOR
( group_count
/
2
)
+
1
, final_app_amount,
0
))
/
2
41
)
42
WHEN
1
THEN
43
SUM
(
44
IF
45
( rank
=
FLOOR
( group_count
/
2
)
+
1
, final_app_amount,
0
))
46
END
AS
final_app_amount
47
FROM
48
(
49
SELECT
50
t3.
*
,
51
@group_count
:
=
52
CASE
53
54
WHEN
@last_group_index
=
group_index
THEN
55
@group_count
ELSE
rank
56
END
AS
group_count,
57
@last_group_index
:
=
group_index
58
FROM
59
(
60
SELECT
61
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
AS
group_index,
62
final_app_amount
AS
final_app_amount,
63
@rank
:
=
64
CASE
65
66
WHEN
@last_group
=
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
THEN
67
@rank
+
1
ELSE
1
68
END
AS
rank,
69
@last_group
:
=
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
70
FROM
71
repay_customer
AS
t1,
72
(
SELECT
@group_count
:
=
0
,
@rank
:
=
0
) t2
73
WHERE
74
1
=
1
AND
capital_name
IN
(
'
金融机构1
'
,
'
金融机构2
'
)
75
ORDER
BY
76
loan_time_credit
,
77
final_app_amount
78
) t3,
79
(
SELECT
@group_count
:
=
0
,
@last_group_index
:
=
0
) t4
80
ORDER
BY
81
group_index,
82
rank
DESC
83
) t5
84
GROUP
BY
85
group_index
86
) dataA
ON
dateI.group_index
=
dataA.group_index
87
) t1,(
88
SELECT
89
@last_group_index
:
=
0
,
90
@last_final_app_amount
:
=
0
91
) t2
92
) t3
。
7、求【最终金额】中位数的同比 。
分组字段只能为时间且只有一个； 。
一个sql只能查一个字段的中位数； 。
对结果做保留2位小数点处理； 。
1
SELECT
2
t1.group_index,
3
t1.group_index
AS
loan_time_credit,
4
ROUND
( ( t2.final_app_amount
-
t3.final_app_amount ),
2
)
AS
final_app_amount_median_growth,
5
ROUND
( ( ( t2.final_app_amount
-
t3.final_app_amount )
/
t3.final_app_amount ),
2
)
AS
final_app_amount_median_rises
6
FROM
7
(
select
@start_date
:
=
(
select
min
(
loan_time_credit
)
from
repay_customer
where
1
=
1
and
capital_name
IN
(
'
金融机构1
'
,
'
金融机构2
'
)
),
8
@end_date
:
=
(
select
max
(
loan_time_credit
)
from
repay_customer
where
1
=
1
and
capital_name
IN
(
'
金融机构1
'
,
'
金融机构2
'
)
)) t4 ,
9
(
10
SELECT
11
DATE_FORMAT( date_sub(
@start_date
, INTERVAL (
@i
:
=
@i
-
1
)
YEAR
),
'
%Y-%m
'
)
AS
group_index
12
FROM
13
mysql.help_topic
14
JOIN
(
SELECT
@i
:
=
1
) c
15
WHERE
16
help_topic_id
<=
(
17
TIMESTAMPDIFF(
MONTH
,
@start_date
,
@end_date
))
18
) t1
19
LEFT
JOIN
(
20
SELECT
21
group_index,
22
last_year_group_index,
23
CASE
MOD (
count
(
*
),
2
)
WHEN
0
THEN
sum
(
IF
( rank
=
FLOOR
( group_count
/
2
)
OR
rank
=
FLOOR
( group_count
/
2
)
+
1
, final_app_amount,
0
))
/
2
24
WHEN
1
THEN
SUM
(
IF
( rank
=
FLOOR
( group_count
/
2
)
+
1
, final_app_amount,
0
))
END
AS
final_app_amount
25
FROM
26
(
27
SELECT
28
t3.
*
,
29
@group_count
:
=
30
CASE
31
32
WHEN
@last_group_index
=
group_index
THEN
33
@group_count
ELSE
rank
34
END
AS
group_count,
35
@last_group_index
:
=
group_index
36
FROM
37
(
38
SELECT
39
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
AS
group_index,
40
DATE_FORMAT( DATE_ADD(
loan_time_credit
, INTERVAL
-
1
MONTH
),
'
%Y-%m
'
)
AS
last_year_group_index,
41
final_app_amount
,
42
@rank
:
=
CASE
WHEN
@last_group
=
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
THEN
@rank
+
1
ELSE
1
END
AS
rank,
43
@last_group
:
=
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
44
FROM
45
repay_customer
AS
t1,
46
(
SELECT
@group_count
:
=
0
,
@rank
:
=
0
) t2
47
WHERE
48
1
=
1
AND
capital_name
IN
(
'
金融机构1
'
,
'
金融机构2
'
)
49
ORDER
BY
50
loan_time_credit
,
51
final_app_amount
52
) t3,
53
(
SELECT
@group_count
:
=
0
,
@last_group_index
:
=
0
) t4
54
ORDER
BY
55
group_index,
56
rank
DESC
57
) t5
58
GROUP
BY
59
group_index
60
) t2
ON
t1.group_index
=
t2.group_index
61
LEFT
JOIN
(
62
SELECT
63
group_index,
64
CASE
MOD (
count
(
*
),
2
)
65
WHEN
0
THEN
sum
(
IF
( rank
=
FLOOR
( group_count
/
2
)
OR
rank
=
FLOOR
( group_count
/
2
)
+
1
, final_app_amount,
0
))
/
2
66
WHEN
1
THEN
SUM
(
IF
( rank
=
FLOOR
( group_count
/
2
)
+
1
, final_app_amount,
0
))
67
END
AS
final_app_amount
68
FROM
69
(
70
SELECT
71
t3.
*
,
72
@group_count
:
=
CASE
WHEN
@last_group_index
=
group_index
THEN
@group_count
ELSE
rank
END
AS
group_count,
73
@last_group_index
:
=
group_index
74
FROM
75
(
76
SELECT
77
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
AS
group_index,
78
final_app_amount
,
79
@rank
:
=
CASE
WHEN
@last_group
=
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
THEN
@rank
+
1
ELSE
1
END
AS
rank,
80
@last_group
:
=
DATE_FORMAT(
loan_time_credit
,
'
%Y-%m
'
)
81
FROM
82
repay_customer
AS
t1,
83
(
SELECT
@group_count
:
=
0
,
@rank
:
=
0
) t2
84
WHERE
85
1
=
1
AND
capital_name
IN
(
'
金融机构1
'
,
'
金融机构2
'
)
86
AND
loan_time_credit
>=
DATE_ADD(
@start_date
, INTERVAL
-
1
YEAR
)
87
AND
loan_time_credit
<=
DATE_ADD(
@end_date
, INTERVAL
-
1
YEAR
)
88
ORDER
BY
89
loan_time_credit
,
90
final_app_amount
91
) t3,
92
(
SELECT
@group_count
:
=
0
,
@last_group_index
:
=
0
) t4
93
ORDER
BY
94
group_index,
95
rank
DESC
96
) t5
97
GROUP
BY
98
group_index
99
) t3
ON
t2.last_year_group_index
=
t3.group_index
。
。
八 完 。
太不容易了我！ 。
。
最后此篇关于mysql求分组中位数、环比、同比、中位数的环比、同比的文章就讲到这里了,如果你想了解更多关于mysql求分组中位数、环比、同比、中位数的环比、同比的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。
28
4
0
说明 中位数、环比、同比概念请自行百度，本文求 字段A中位数、根据字段B分组后字段A中位数、字段A环比、字段A同比、字段A中位数的环比、字段A中位数的同比。 可替换部分标
我是一名优秀的程序员,十分优秀！