mysql 求分組中位數、環比、同比、中位數的環比、同比

hsql發表於2023-04-07

說明

中位數、環比、同比概念請自行百度,本文求  欄位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

 

 

八 完

太不容易了我!

 

相關文章