mysql 求分组中位数、环比、同比、中位数的环比、同比
博客园 2023-04-07 18:26:19
说明
(相关资料图)
中位数、环比、同比概念请自行百度，本文求 字段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 ) AS final_app_amount_median 7 FROM 8 ( 9 SELECT10 final_app_amount,11 @rank AS row_number,12 @rank := @rank + 1 13 FROM repay_customer AS t1,14 ( SELECT @rank := 1 ) t2 15 WHERE16 1 = 1 AND capital_name IN ( "金融机构1", "金融机构2" ) 17 ORDER BY final_app_amount 18 ) 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 AS final_app_amount_median 8 FROM 9 (10 SELECT11 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 FROM15 (16 SELECT17 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 FROM23 repay_customer AS t1,24 ( SELECT @group_count := 0, @rank := 0 ) t2 25 WHERE26 1 = 1 AND capital_name IN ( "金融机构1", "金融机构2" ) 27 ORDER BY28 loan_time_credit,29 final_app_amount 30 ) t3,31 ( SELECT @group_count := 0, @last_group_index := 0 ) t4 32 ORDER BY33 group_index,34 rank DESC 35 ) t5 36 GROUP BY37 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 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 SELECT11 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 FROM19 (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 SELECT23 group_index,24 final_app_amount_sum_growth,25 contract_amount_avg_growth 26 FROM27 (28 SELECT29 DATE_FORMAT( date_sub( @start_date, INTERVAL ( @i := @i - 1 ) MONTH ), "%Y-%m" ) AS group_index 30 FROM31 mysql.help_topic32 JOIN ( SELECT @i := 1 ) c 33 WHERE34 help_topic_id <= (35 TIMESTAMPDIFF( MONTH, @start_date,@end_date))36 ) dateI37 LEFT JOIN (38 SELECT39 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 FROM43 repay_customer 44 WHERE45 1 = 1 46 AND capital_name IN ( "金融机构1", "金融机构2" ) 47 GROUP BY48 DATE_FORMAT( loan_time_credit, "%Y-%m" )) dataA ON dateI.group_index = dataA.loan_time_credit 49 ) t1,(50 SELECT51 @last_group_index := 0,52 @last_final_app_amount_sum_growth := 0,53 @last_contract_amount_avg_growth := 0 54 ) t2 55 ) 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 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 SELECT13 DATE_FORMAT( date_sub( @start_date, INTERVAL ( @i := @i - 1 ) MONTH ), "%Y-%m" ) AS group_index 14 FROM15 mysql.help_topic16 JOIN ( SELECT @i := 1 ) c 17 WHERE18 help_topic_id <= (19 TIMESTAMPDIFF( MONTH, @start_date, @end_date) )20 ) t121 LEFT JOIN (22 SELECT23 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 FROM28 repay_customer 29 WHERE30 1 = 1 31 AND capital_name IN ( "华夏银行", "蓝海银行", "中金租" ) 32 GROUP BY33 DATE_FORMAT( loan_time_credit, "%Y-%m" ) 34 ) t2 ON t1.group_index = t2.group_index35 LEFT JOIN (36 SELECT37 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 FROM41 repay_customer 42 WHERE43 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 BY48 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 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 FROM14 (15 SELECT16 dateI.group_index,17 final_app_amount 18 FROM19 (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 SELECT23 DATE_FORMAT( date_sub( @start_date, INTERVAL ( @i := @i - 1 ) MONTH ), "%Y-%m" ) AS group_index 24 FROM25 mysql.help_topic26 JOIN ( SELECT @i := 1 ) c 27 WHERE28 help_topic_id <= (29 TIMESTAMPDIFF( MONTH, @start_date, @end_date )) 30 ) dateI31 LEFT JOIN (32 SELECT33 group_index,34 CASE35 MOD ( count(*), 2 ) 36 WHEN 0 THEN37 (38 sum(39 IF40 ( rank = FLOOR( group_count / 2 ) OR rank = FLOOR( group_count / 2 ) + 1, final_app_amount, 0 )) / 2 41 ) 42 WHEN 1 THEN43 SUM(44 IF45 ( rank = FLOOR( group_count / 2 ) + 1, final_app_amount, 0 )) 46 END AS final_app_amount 47 FROM48 (49 SELECT50 t3.*,51 @group_count :=52 CASE53 54 WHEN @last_group_index = group_index THEN55 @group_count ELSE rank 56 END AS group_count,57 @last_group_index := group_index 58 FROM59 (60 SELECT61 DATE_FORMAT( loan_time_credit, "%Y-%m" ) AS group_index,62 final_app_amount AS final_app_amount,63 @rank :=64 CASE65 66 WHEN @last_group = DATE_FORMAT( loan_time_credit, "%Y-%m" ) THEN67 @rank + 1 ELSE 1 68 END AS rank,69 @last_group := DATE_FORMAT( loan_time_credit, "%Y-%m" ) 70 FROM71 repay_customer AS t1,72 ( SELECT @group_count := 0, @rank := 0 ) t2 73 WHERE74 1 = 1 AND capital_name IN ( "金融机构1", "金融机构2" ) 75 ORDER BY76 loan_time_credit,77 final_app_amount 78 ) t3,79 ( SELECT @group_count := 0, @last_group_index := 0 ) t4 80 ORDER BY81 group_index,82 rank DESC 83 ) t5 84 GROUP BY85 group_index 86 ) dataA ON dateI.group_index = dataA.group_index 87 ) t1,(88 SELECT89 @last_group_index := 0,90 @last_final_app_amount := 0 91 ) t2 92 ) t3
七、求【最终金额】中位数的同比
分组字段只能为时间且只有一个；
一个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 SELECT11 DATE_FORMAT( date_sub( @start_date, INTERVAL ( @i := @i - 1 ) YEAR ), "%Y-%m" ) AS group_index 12 FROM13 mysql.help_topic14 JOIN ( SELECT @i := 1 ) c 15 WHERE16 help_topic_id <= (17 TIMESTAMPDIFF( MONTH, @start_date, @end_date )) 18 ) t119 LEFT JOIN (20 SELECT21 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 FROM26 (27 SELECT28 t3.*,29 @group_count :=30 CASE31 32 WHEN @last_group_index = group_index THEN33 @group_count ELSE rank 34 END AS group_count,35 @last_group_index := group_index 36 FROM37 (38 SELECT39 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 FROM45 repay_customer AS t1,46 ( SELECT @group_count := 0, @rank := 0 ) t2 47 WHERE48 1 = 1 AND capital_name IN ( "金融机构1", "金融机构2" ) 49 ORDER BY50 loan_time_credit,51 final_app_amount 52 ) t3,53 ( SELECT @group_count := 0, @last_group_index := 0 ) t4 54 ORDER BY55 group_index,56 rank DESC 57 ) t5 58 GROUP BY59 group_index 60 ) t2 ON t1.group_index = t2.group_index61 LEFT JOIN (62 SELECT63 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 FROM69 (70 SELECT71 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 FROM75 (76 SELECT77 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 FROM82 repay_customer AS t1,83 ( SELECT @group_count := 0, @rank := 0 ) t2 84 WHERE85 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 BY89 loan_time_credit,90 final_app_amount 91 ) t3,92 ( SELECT @group_count := 0, @last_group_index := 0 ) t4 93 ORDER BY94 group_index,95 rank DESC 96 ) t5 97 GROUP BY98 group_index 99 ) t3 ON t2.last_year_group_index = t3.group_index八 完
太不容易了我！
