mysql 求分组中位数、环比、同比、中位数的环比、同比

技术分享 3年前 (2023-04-08) 0 999+

关注

说明

中位数、环比、同比概念请自行百度，本文求字段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 等；

对结果做保留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