mysql 求分組中位數、環比、同比、中位數的環比、同比
2023-04-07 21:00:33
說明
中位數、環比、同比概念請自行百度，本文求 欄位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 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
三、求【最終金額】的【分組中位數】
即根據時間，計算每月的最終金額的中位數，對結果做保留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
四、求【最終金額】和【合同金額】的環比
環比一般以月為分組條件，求環比的分組欄位必須為時間欄位，且只有一個時間欄位；
以下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
五、求【最終金額】和【合同金額】的同比
同比一般與上一年比較，求同比的分組欄位必須為時間欄位，且只有一個時間欄位；
以下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
六、求【最終金額】中位數的環比
分組欄位只能為時間且只有一個；
一個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
七、求【最終金額】中位數的同比
分組欄位只能為時間且只有一個；
一個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
八 完
太不容易了我！