mysql 求分组中位数、环比、同比、中位数的环比、同比
说明
中位数、环比、同比概念请自行百度，本文求 字段A中位数、根据字段B分组后字段A中位数、字段A环比、字段A同比、字段A中位数的环比、字段A中位数的同比。
可替换部分标黄
一、表结构如下图
查询条件为 capital_name in （'金融机构1','金融机构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 ) ASfinal_app_amount_median7 FROM 8 (9 SELECT 10final_app_amount,11 @rank AS row_number,12 @rank := @rank + 1 13 FROM repay_customer ASt1,14 ( SELECT @rank := 1) t215 WHERE 16 1 = 1 AND capital_name IN ( '金融机构1', '金融机构2')17 ORDER BYfinal_app_amount18 ) t3,19 ( SELECT @max_row_number := 0 ) t4
三、求【最终金额】的【分组中位数】
即根据时间，计算每月的最终金额的中位数，对结果做保留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 ASfinal_app_amount_median8 FROM 9 (10 SELECT 11 t3.*,12 @group_count := CASE WHEN @last_group_index = group_index THEN @group_count ELSE rank END ASgroup_count,13 @last_group_index :=group_index14 FROM 15 (16 SELECT 17 CONCAT( DATE_FORMAT( loan_time_credit, '%Y-%m' ) ) ASgroup_index,18 DATE_FORMAT( loan_time_credit, '%Y-%m' ) ASloan_time_credit,19 final_app_amount ASfinal_app_amount,20 @rank := CASE WHEN @last_group = CONCAT( DATE_FORMAT( loan_time_credit, '%Y-%m' ) ) THEN @rank + 1 ELSE 1 END ASrank,21 @last_group := CONCAT( DATE_FORMAT( loan_time_credit, '%Y-%m'))22 FROM 23 repay_customer ASt1,24 ( SELECT @group_count := 0, @rank := 0) t225 WHERE 26 1 = 1 AND capital_name IN ( '金融机构1', '金融机构2')27 ORDER BY
28 loan_time_credit,29final_app_amount30 ) t3,31 ( SELECT @group_count := 0, @last_group_index := 0) t432 ORDER BY 33 group_index,34 rank DESC 35 ) t536 GROUP BY 37 group_index
四、求【最终金额】和【合同金额】的环比
环比一般以月为分组条件，求环比的分组字段必须为时间字段，且只有一个时间字段；
以下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 ASloan_time_credit,4 ROUND( ( ( t3.final_app_amount_sum_growth - last_final_app_amount_sum_growth )/ last_final_app_amount_sum_growth ), 2 ) ASfinal_app_amount_sum_rises,5 ROUND( ( ( t3.contract_amount_avg_growth - last_contract_amount_avg_growth )/ last_contract_amount_avg_growth ), 2 ) AScontract_amount_avg_rises,6 ROUND( ( t3.final_app_amount_sum_growth - t3.last_final_app_amount_sum_growth ), 2 ) ASfinal_app_amount_sum_growth,7 ROUND( ( t3.contract_amount_avg_growth - t3.last_contract_amount_avg_growth ), 2 ) AScontract_amount_avg_growth8 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 ASlast_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 ASlast_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_growth18 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_growth26 FROM 27 (28 SELECT 29 DATE_FORMAT( date_sub( @start_date, INTERVAL ( @i := @i - 1 ) MONTH ), '%Y-%m' ) ASgroup_index30 FROM 31 mysql.help_topic32 JOIN ( SELECT @i := 1) c33 WHERE 34 help_topic_id <=(35 TIMESTAMPDIFF( MONTH, @start_date,@end_date))36 ) dateI37 LEFT JOIN(38 SELECT 39 DATE_FORMAT( loan_time_credit, '%Y-%m' ) ASloan_time_credit,40 sum( final_app_amount ) ASfinal_app_amount_sum_growth,41 avg( contract_amount ) AScontract_amount_avg_growth42 FROM 43 repay_customer44 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_credit49 ) t1,(50 SELECT 51 @last_group_index := 0,52 @last_final_app_amount_sum_growth := 0,53 @last_contract_amount_avg_growth := 0 54 ) t255 ) t3
五、求【最终金额】和【合同金额】的同比
同比一般与上一年比较，求同比的分组字段必须为时间字段，且只有一个时间字段；
以下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 ASloan_time_credit,4 ROUND( ( ( t2.final_app_amount_sum_growth - t3.final_app_amount_sum_growth )/ t3.final_app_amount_sum_growth ), 2 ) ASfinal_app_amount_sum_rises,5 ROUND( ( ( t2.contract_amount_avg_growth - t3.contract_amount_avg_growth )/ t3.contract_amount_avg_growth ), 2 ) AScontract_amount_avg_rises,6 t2.final_app_amount_sum_growth - t3.final_app_amount_sum_growth ASfinal_app_amount_sum_growth,7 t2.contract_amount_avg_growth - t3.contract_amount_avg_growth AScontract_amount_avg_growth8 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=1and capital_name IN ( '金融机构1', '金融机构2'))) t4 ,11 (12 SELECT 13 DATE_FORMAT( date_sub( @start_date, INTERVAL ( @i := @i - 1 ) MONTH ), '%Y-%m' ) ASgroup_index14 FROM 15 mysql.help_topic16 JOIN ( SELECT @i := 1) c17 WHERE 18 help_topic_id <=(19 TIMESTAMPDIFF( MONTH, @start_date, @end_date) )20 ) t121 LEFT JOIN(22 SELECT 23 DATE_FORMAT( loan_time_credit, '%Y-%m' ) ASgroup_index,24 DATE_FORMAT( DATE_ADD( loan_time_credit, INTERVAL - 1 YEAR ), '%Y-%m' ) ASlast_group_index,25 sum( final_app_amount ) ASfinal_app_amount_sum_growth,26 avg( contract_amount ) AScontract_amount_avg_growth27 FROM 28 repay_customer29 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_index35 LEFT JOIN(36 SELECT 37 DATE_FORMAT( loan_time_credit, '%Y-%m' ) ASgroup_index,38 sum( final_app_amount ) ASfinal_app_amount_sum_growth,39 avg( contract_amount ) AScontract_amount_avg_growth40 FROM 41 repay_customer42 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
六、求【最终金额】中位数的环比
分组字段只能为时间且只有一个；
一个sql只能查一个字段的中位数；
对结果做保留2位小数点处理；
1 SELECT 2 t3.group_index,3 t3.group_index ASloan_time_credit,4 ROUND( ( t3.final_app_amount - t3.last_final_app_amount ), 2 ) ASfinal_app_amount_median_growth,5 ROUND( ( ( t3.final_app_amount - last_final_app_amount )/ last_final_app_amount ), 2 ) ASfinal_app_amount_median_rises6 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 ASlast_final_app_amount,10 t1.*,11 @last_group_index :=group_index,12 @last_final_app_amount :=t1.final_app_amount13 FROM 14 (15 SELECT 16 dateI.group_index,17 final_app_amount18 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' ) ASgroup_index24 FROM 25 mysql.help_topic26 JOIN ( SELECT @i := 1) c27 WHERE 28 help_topic_id <=(29 TIMESTAMPDIFF( MONTH, @start_date, @end_date))30 ) dateI31 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 ASfinal_app_amount47 FROM 48 (49 SELECT 50 t3.*,51 @group_count := 52 CASE 53 54 WHEN @last_group_index = group_index THEN 55 @group_count ELSErank56 END ASgroup_count,57 @last_group_index :=group_index58 FROM 59 (60 SELECT 61 DATE_FORMAT( loan_time_credit, '%Y-%m' ) ASgroup_index,62 final_app_amount ASfinal_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 ASrank,69 @last_group := DATE_FORMAT( loan_time_credit, '%Y-%m')70 FROM 71 repay_customer ASt1,72 ( SELECT @group_count := 0, @rank := 0) t273 WHERE 74 1 = 1 AND capital_name IN ( '金融机构1', '金融机构2')75 ORDER BY 76 loan_time_credit,77 final_app_amount78 ) t3,79 ( SELECT @group_count := 0, @last_group_index := 0) t480 ORDER BY 81 group_index,82 rank DESC 83 ) t584 GROUP BY 85 group_index86 ) dataA ON dateI.group_index =dataA.group_index87 ) t1,(88 SELECT 89 @last_group_index := 0,90 @last_final_app_amount := 0 91 ) t292 ) t3
七、求【最终金额】中位数的同比
分组字段只能为时间且只有一个；
一个sql只能查一个字段的中位数；
对结果做保留2位小数点处理；
1 SELECT 2 t1.group_index,3 t1.group_index ASloan_time_credit,4 ROUND( ( t2.final_app_amount - t3.final_app_amount ), 2 ) ASfinal_app_amount_median_growth,5 ROUND( ( ( t2.final_app_amount - t3.final_app_amount )/ t3.final_app_amount ), 2 ) ASfinal_app_amount_median_rises6 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' ) ASgroup_index12 FROM 13 mysql.help_topic14 JOIN ( SELECT @i := 1) c15 WHERE 16 help_topic_id <=(17 TIMESTAMPDIFF( MONTH, @start_date, @end_date))18 ) t119 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 ASfinal_app_amount25 FROM 26 (27 SELECT 28 t3.*,29 @group_count := 30 CASE 31 32 WHEN @last_group_index = group_index THEN 33 @group_count ELSErank34 END ASgroup_count,35 @last_group_index :=group_index36 FROM 37 (38 SELECT 39 DATE_FORMAT( loan_time_credit, '%Y-%m' ) ASgroup_index,40 DATE_FORMAT( DATE_ADD( loan_time_credit, INTERVAL - 1 MONTH ), '%Y-%m' ) ASlast_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 ASrank,43 @last_group := DATE_FORMAT( loan_time_credit, '%Y-%m')44 FROM 45 repay_customer ASt1,46 ( SELECT @group_count := 0, @rank := 0) t247 WHERE 48 1 = 1 AND capital_name IN ( '金融机构1', '金融机构2')49 ORDER BY 50 loan_time_credit,51 final_app_amount52 ) t3,53 ( SELECT @group_count := 0, @last_group_index := 0) t454 ORDER BY 55 group_index,56 rank DESC 57 ) t558 GROUP BY 59 group_index60 ) t2 ON t1.group_index =t2.group_index61 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 ASfinal_app_amount68 FROM 69 (70 SELECT 71 t3.*,72 @group_count := CASE WHEN @last_group_index = group_index THEN @group_count ELSE rank END ASgroup_count,73 @last_group_index :=group_index74 FROM 75 (76 SELECT 77 DATE_FORMAT( loan_time_credit, '%Y-%m' ) ASgroup_index,78 final_app_amount,79 @rank := CASE WHEN @last_group = DATE_FORMAT( loan_time_credit, '%Y-%m' ) THEN @rank + 1 ELSE 1 END ASrank,80 @last_group := DATE_FORMAT( loan_time_credit, '%Y-%m')81 FROM 82 repay_customer ASt1,83 ( SELECT @group_count := 0, @rank := 0) t284 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_amount91 ) t3,92 ( SELECT @group_count := 0, @last_group_index := 0) t493 ORDER BY 94 group_index,95 rank DESC 96 ) t597 GROUP BY 98 group_index99 ) t3 ON t2.last_year_group_index = t3.group_index
八 完
太不容易了我！