MySQL统计每月数量并计算同比增长率

您所在的位置：网站首页 › 去年是0今年是12如何计算增长率 › MySQL统计每月数量并计算同比增长率

MySQL统计每月数量并计算同比增长率

2024-07-12 03:24| 来源: 网络整理| 查看: 265

表结构

CREATE TABLE `production` ( `id` int(11) NOT NULL AUTO_INCREMENT, `year_month` varchar(20) DEFAULT NULL COMMENT '月份', `num` bigint(20) DEFAULT NULL COMMENT '产量', `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='产品产量表'; 统计近1年每月的产量和同比增长率

所谓“同比”即同期比较，可以是今年和去年比较，本月和去年同月比较，本季度和去年同季度比较。

同比增长率 =（本期数 - 同期数）/ |同期数|×100% ## 同期没有数据的，会显示为null，这是设置成0 ## 重点关注t4表，注意：我这里的year_month字段类型为varchar，所以需要转换 SELECT t1.datetime, IFNULL(t2.num, 0) AS total, ROUND(IFNULL((t2.num-t2.lastNum)/t2.lastNum*100, 0), 2) AS rate FROM ( SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS datetime ) t1 LEFT JOIN ( SELECT t3.`year_month` AS datetime, t3.num, IFNULL(t4.num, 0) AS lastNum FROM production t3 LEFT JOIN production t4 ON DATE_FORMAT((STR_TO_DATE(CONCAT(t3.`year_month`,'-01'), '%Y-%m-%d') - INTERVAL 12 MONTH), '%Y-%m') = t4.`year_month` ORDER BY t3.`year_month` DESC LIMIT 12 ) t2 ON t1.datetime = t2.datetime ORDER BY t1.datetime DESC

执行结果

统计近1年每月的产量和环比增长率

所谓“环比”即连续2个单位周期比较，可以是今年和去年比较，本月和上个月比较，本季度和上个季度比较。

环比增长率 =（本期数 - 上期数） / |上期数|×100% ## 同期没有数据的，会显示为null，这是设置成0 ## 重点关注t4表，注意：我这里的year_month字段类型为varchar，所以需要转换 ## 注意比较环比和同比SQL的差异，实际上区别仅在 INTERVAL 1 MONTH 上 SELECT t1.datetime, IFNULL(t2.num, 0) AS total, ROUND(IFNULL((t2.num-t2.lastNum)/t2.lastNum*100, 0), 2) AS rate FROM ( SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS datetime union all SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS datetime ) t1 LEFT JOIN ( SELECT t3.`year_month` AS datetime, t3.num, IFNULL(t4.num, 0) AS lastNum FROM production t3 LEFT JOIN production t4 ON DATE_FORMAT((STR_TO_DATE(CONCAT(t3.`year_month`,'-01'), '%Y-%m-%d') - INTERVAL 1 MONTH), '%Y-%m') = t4.`year_month` ORDER BY t3.`year_month` DESC LIMIT 12 ) t2 ON t1.datetime = t2.datetime ORDER BY t1.datetime DESC

执行结果

【本文地址】

MySQL统计每月数量并计算同比增长率

MySQL统计每月数量并计算同比增长率

今日新闻

推荐新闻