SQL百分比计算和自动生成年月日期

需求

需求是：1、计算历年上月和当月的增长比例。
2、以及自动SQL 补充年月日期（可以看具体是xml SQL 文件代码）

<!--  年度捐款情况分析 按照月去分析  -->
<select id="analysisofMonthdonations" resultType="org.jeecg.modules.demo.invoice.entity.CsMoneyMonthdonationsVo">
  select date AS yearmonth,IF(money,money,0) AS summoney,IF(count,count,0) AS sumcount from
  (
  SELECT  DATE_FORMAT(@cdate := date_add(@cdate,interval - 1 month) ,'%Y-%m') as date
  from (SELECT DATE_FORMAT(paydate,'%Y-%m') as date,@cdate :=date_add(CURDATE(),interval + 1 month) from cs_invoice_info a)  t1
  where @cdate > CONCAT(YEAR(CURRENT_DATE), '-02') order by @cdate

  ) a left join ( select DATE_FORMAT( paydate, '%Y-%m' ) AS years ,IF( sum( money ), sum( money ), 0 ) AS money,count(*) as count
  from cs_invoice_info GROUP BY
  DATE_FORMAT( paydate, '%Y-%m' )
  ) b on a.date = b.years
</select>


<!-- 历年捐赠情况分析 -->
<select id="analysisofYeardonations" parameterType="string" resultType="org.jeecg.modules.demo.invoice.entity.CsMoneyMonthdonationsVo">
  select date AS yearmonth,IF(money,money,0) AS summoney,IF(count,count,0) AS sumcount from
  (
  SELECT  DATE_FORMAT(@cdate := date_add(@cdate,interval - 1 YEAR) ,'%Y') as date
  from (SELECT DATE_FORMAT(paydate,'%Y') as date,@cdate :=date_add(CURDATE(),interval + 1 YEAR) from cs_invoice_info a)  t1
  where @cdate >   DATE_SUB( CURDATE(), INTERVAL #{years}-1 YEAR ) order by @cdate

  ) a left join ( select DATE_FORMAT( paydate, '%Y' ) AS years ,IF( sum( money ), sum( money ), 0 ) AS money,count(*) as count
  from cs_invoice_info GROUP BY
  DATE_FORMAT( paydate, '%Y' )
  ) b on a.date = b.years
</select>

关键业务代码：
调用calculateGrowthRate（）需要传入当前这月汇总年份，和上月的数据 进行增长比计算。

/**
 * @param current 增长比
 * @param last
 * @return
 */
private static String calculateGrowthRate(BigDecimal current, BigDecimal last) {
   if (last.compareTo(BigDecimal.ZERO) == 0) {
      return "100";
   }
   BigDecimal increaseAmount = current.subtract(last);
   BigDecimal growthRate = increaseAmount.divide(last, 4, BigDecimal.ROUND_HALF_UP)
         .multiply(new BigDecimal(100)).setScale(2);;
   return growthRate.stripTrailingZeros().toPlainString();
}

前端 Echarts

    // 年度情况分析----月
    monthechartsz() {
      //获取Dom节点
      let chartDomone = this.$refs.Domtyear
      //初始化echarts实例
      let myChartone = this.$echarts.init(chartDomone)
      //绘制图标
      let option
      option = {
        title: {
          text: '年度情况分析',
        },
        tooltip: {
          // 鼠标显示
          trigger: 'axis',
          formatter: function (params) {
            var result = ''
            result += params[0].name + '月' + '<br />'
                for (var i = 0; i < params.length; i++) {
              result += params[i].seriesName+": ";
              if (params[i].seriesName === '较上月') {
                result +=  params[i].value + '%' + '<br />'
              }else{
                   result +=  params[i].value  + '<br />'
              }
            }
            return result
          },
          backgroundColor: '#fff', // 悬浮框背景色
          borderColor: '#000', // 悬浮框边框颜色
          borderWidth: 1, // 悬浮框边框宽度
          textStyle: {
            // 悬浮框文字样式
            color: '#000',
            fontSize: 13,
          },
        },
        legend: {},
        toolbox: {
          show: true,
          feature: {
            dataZoom: {
              yAxisIndex: 'none',
            },
            dataView: { readOnly: false },
            magicType: { type: ['line', 'bar'] },
            restore: {},
            saveAsImage: {},
          },
        },
        xAxis: {
          type: 'category',
          boundaryGap: false,
          data: [],
        },
        yAxis: [
         {
            type: 'value',
            name: '金额',
            nameTextStyle: {
              color: '#8695A1',
            },
          },
          {
            type: 'value',
            name: '次数',
            nameTextStyle: {
              color: '#8695A1',
            },
            splitLine: {
              show: false,
            },
          },
        ],
        series: [
          {
            name: '金额',
            type: 'line',
            data: [],
          },

          {
            name: '次数',
            type: 'line',
            data: [],
            yAxisIndex: 1,
            axisLabel: {
              formatter: '{value}',
            },
          },
          {
            name: '较上月',
            type: 'line',
            data: [],
          },
        ],
      }
      let httpurl = ''
      httpurl += this.url.monthecharts
      //   console.log(this.years)
      diffinvoicebatchNODel(httpurl).then((res) => {
        if (res.result) {
          console.log(res.result)
          res.result.forEach((item) => {
            option.series[0].data.push(item.summoney) 
            option.xAxis.data.push(item.yearmonth) //
            option.series[1].data.push(item.sumcount) 
            option.series[2].data.push(item.percentage) //百分比
          })
          option && myChartone.setOption(option)
        } else {
          this.$message.warning('暂无数据!')
        }
      })
      myChartone.on('mouseover', function (params) {
        console.log(params)
      })
    },
  },
}

效果图展示