mysql统计 同比 环比

我们来看下计算公式：

1、同比环比计算规则

月度环比=（本月-上月）/上月*100%

月度同比=（当年本月-上年本月）/上年本月*100%

年度同比=（今年-去年）/去年*100%

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for finance
-- ----------------------------
DROP TABLE IF EXISTS `finance`;
CREATE TABLE `finance`  (
  `id` int(12) NOT NULL,
  `amount` decimal(6, 0) NULL DEFAULT NULL,
  `dt` timestamp(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of finance
-- ----------------------------
INSERT INTO `finance` VALUES (1, 22, '2021-01-01 15:54:05');
INSERT INTO `finance` VALUES (2, 11, '2021-01-03 15:55:13');
INSERT INTO `finance` VALUES (3, 55, '2021-02-11 15:56:52');
INSERT INTO `finance` VALUES (4, 55, '2021-03-19 15:58:14');
INSERT INTO `finance` VALUES (5, 33, '2021-04-22 15:58:57');
INSERT INTO `finance` VALUES (6, 22, '2022-01-01 16:09:08');
INSERT INTO `finance` VALUES (7, 23, '2022-02-01 16:09:35');
INSERT INTO `finance` VALUES (8, 33, '2022-03-01 16:09:58');

SET FOREIGN_KEY_CHECKS = 1;

上面是收入表：

我要统计年度同比：（今年-去年）/去年*100%

	select 
	 t1.dtYear,
	 t1.sumAmount,
	 t2.latSumAmount, 
	     CASE
    WHEN latSumAmount IS NULL
     OR latSumAmount = 0 THEN
     0 ELSE ( CONVERT ( ( ( sumAmount - latSumAmount ) / latSumAmount ) * 100, DECIMAL ( 10, 2 ) ) )
      END hb
	 
	  from 
  (SELECT
	   date_format(f.dt,'%Y') dtYear,
		 SUM(f.amount) as sumAmount
 FROM
	   finance f 
		 GROUP BY dtYear) t1
		 
		 left join 
		   (SELECT
	   date_format(f.dt,'%Y') dtYear,
		 SUM(f.amount) as latSumAmount
 FROM
	   finance f 
		 GROUP BY dtYear) t2 on t1.dtYear = t2.dtYear + 1
		 

月度环比：月度环比=（本月-上月）/上月*100%

	select 
        t1.dtMonth,
        t1.sumAmount,
        t2.lastMonthsumAmount,
			    CASE
    WHEN lastMonthsumAmount IS NULL
     OR lastMonthsumAmount = 0 THEN
     0 ELSE ( CONVERT ( ( ( sumAmount - lastMonthsumAmount ) / lastMonthsumAmount ) * 100, DECIMAL ( 10, 2 ) ) )
      END monthhb
				
       from  
     
		 (SELECT
        date_format( f.dt, '%Y-%m' ) dtMonth,
        SUM( f.amount ) AS sumAmount 
      FROM
        finance f 
      GROUP BY
         dtMonth)t1 left join 
          		
				(SELECT 
        DATE_FORMAT(DATE_ADD(f.dt,INTERVAL 1 MONTH),'%Y-%m') as lastMonth,
        SUM( f.amount ) AS lastMonthsumAmount 
      FROM
        finance f 
      GROUP BY
         lastMonth) t2 on t1.dtMonth = t2.lastMonth

月度同比=（当年本月-上年本月）/上年本月*100%

   
		select 
        t1.dtMonth,
        t1.sumAmount,
        t2.lastYearMonthsumAmount,
			    CASE
    WHEN lastYearMonthsumAmount IS NULL
     OR lastYearMonthsumAmount = 0 THEN
     0 ELSE ( CONVERT ( ( ( sumAmount - lastYearMonthsumAmount ) / lastYearMonthsumAmount ) * 100, DECIMAL ( 10, 2 ) ) )
      END monthhb
				
       from  
     
		 (SELECT
		    date_format( f.dt, '%Y' ) dtYear,
        date_format( f.dt, '%Y-%m' ) dtMonth,
        SUM( f.amount ) AS sumAmount 
      FROM
        finance f 
      GROUP BY
         dtYear,dtMonth)t1 left join   		
				(SELECT 
				 date_format( f.dt, '%Y' ) dtYear,
        date_format( f.dt, '%Y-%m' ) dtMonth,
        DATE_FORMAT(DATE_ADD(f.dt,INTERVAL 1 YEAR),'%Y-%m') as lastYearMonth,
        SUM( f.amount ) AS lastYearMonthsumAmount 
      FROM
        finance f 
      GROUP BY
         dtYear,dtMonth,lastYearMonth) t2 on t1.dtMonth = t2.lastYearMonth