//2、 计算占比
			int num = Integer.parseInt(uintCardre.getUintPersonSum());
			int allNum = Integer.parseInt(uintCardre.getUintAllPersonSum());
			// 创建一个数值格式化对象
			NumberFormat numberFormat = NumberFormat.getInstance();
			// 设置精确到小数点后2位
			numberFormat.setMaximumFractionDigits(2);
			String result = numberFormat.format((float)num/(float)allNum*100) + "%";
			uintCardre.setProportion(result);//占比赋值

 
背景：某总公司旗下有若干子公司
 分别位于上海（SH）、北京（BJ）、成都（CD）、广州（GZ）等
 
每个子公司每年都会发生若干消费项目
 如：Information Technology、Travel and Entertainment、Forms and Supplies 等
 
下面介绍如何计算总体占比、分类占比、按筛选的总体占比、按筛选的分类占比
 
首先Power BI链接数据源（子公司的费用表）， 做一个矩阵视图：
 
 1、计算总体占比：
 
总体占比 = DIVIDE(SUM('费用表'[cost]),CALCULATE(SUM('费用表'[cost]),ALL('费用表')))
 

一、数据准备
 
-- 创建表并插入数据
CREATE TABLE `saleorder`  (
  `order_id` int ,
  `order_time` date ,
  `order_num` int
) 

INSERT INTO `saleorder` VALUES 
(1, '2020-04-20', 420),
(2, '2020-04-04', 800),
(3, '2020-03-28', 500),
(4, '2020-03-13', 100),
(5, '2020-02-27', 300),
(6, '2020-01-07', 450),
(7, '2019-04-07', 800),
(8, '2019-03-15', 1200),
(9, '2019-02-17', 200),
(10, '2019-02-07', 600),
(11, '2019-01-13', 300);
 

查看表信息
 
select * from saleorder;
 
二、占比
 
写法一：
 
基本思路：用式内连接，外加嵌套找出分子分母，相除（最后要分组）
 
SELECT
	order_month,
	num,
	total,
	round( num / total, 2 ) AS ratio 
FROM
(
	-- 月统计
    SELECT 
	    DATE_FORMAT(order_time,"yyyy-MM") AS order_month,
	    sum( order_num ) AS num
    FROM    
	saleorder 
    GROUP BY
		DATE_FORMAT(order_time,"yyyy-MM")
)t1,
(
	-- 年统计
    SELECT 
	    year(order_time) AS order_year,
	    sum( order_num ) AS total
    FROM 
	saleorder 
    GROUP BY
		year(order_time)
)t2 
where substr(t1.order_month,1,4) = t2.order_year;
 
写法二：显示内连接
 
基本思路：显示内联接，先分组、汇总–>笛卡尔积连接–>相除
 提示： 时间处理的时候除了用date_formate（）也可以用substr()函数来截取年月日格式
 
SELECT
	order_month,
	num,
	total,
	round(num/total,2) as ratio
FROM
(
    SELECT
	    substr( order_time, 1, 7 ) AS order_month,
	    sum( order_num ) AS num 
    FROM
	    saleorder 
    GROUP BY
	    substr( order_time, 1, 7 ) 
) t1
INNER JOIN 
(
    SELECT
	    substr( order_time, 1, 4 ) AS order_year,
	    sum( order_num ) AS total 
    FROM
	    saleorder
    GROUP BY
	    substr( order_time, 1, 4 ) 
) t2 
ON substr( order_month, 1, 4 ) = t2.order_year ;
 
写法三：开窗函数
 
SELECT 
	order_month,
	num,
	total,
	round( num / total, 2 ) AS ratio 
FROM 
(
    select 
        substr(order_time, 1, 7) as order_month, 
        sum(order_num) over (partition by substr(order_time, 1, 7)) as num,
        sum( order_num ) over ( PARTITION BY substr( order_time, 1, 4 ) ) total,
        row_number() over (partition by substr(order_time, 1, 7)) as rk 
    from saleorder
) temp 
where rk = 1;
 
注意：
 
（1） 时间处理的时候除了用date_formate（）也可以用substr()函数来截取年月日格式
（2）当我们求的占比分子分母没有时间维度只有数量的时候，我们可以采用on 1=1 来进行关联，构造成笛卡尔积
 
三、环比
 
与上年度数据对比称"同比"，与上月数据对比称"环比"。
 相关公式如下:
 
同比增长率计算公式
(当年值-上年值)/上年值x100% 

环比增长率计算公式
(当月值-上月值)/上月值x100% 
 
实现：
 
select 
    now_month,
    now_num,
    last_num,
    round( (now_num-last_num) / last_num, 2 ) as ratio
FROM 
(
    select 
        now_month,
        now_num, 
        lag( t1.now_num, 1 ) over (order by t1.now_month ) as last_num 
    from 
    (
        select 
            substr(order_time, 1, 7) as now_month, 
            sum(order_num) as now_num 
        from saleorder 
        group by 
            substr(order_time, 1, 7) 
    ) t1
) t2;
 
结果优化一下：
 
select 
    now_month,
    now_num,
    last_num,
    -- round( (now_num-last_num) / last_num, 2 ) as ratio
    concat( nvl ( round( ( now_num - last_num ) / last_num * 100, 2 ), 0 ), "%" ) 
FROM 
(
    select 
        now_month,
        now_num, 
        lag( t1.now_num, 1 ) over (order by t1.now_month ) as last_num 
    from 
    (
        select 
            substr(order_time, 1, 7) as now_month, 
            sum(order_num) as now_num 
        from saleorder 
        group by 
            substr(order_time, 1, 7) 
    ) t1
) t2;
 
四、同比
 
与上年度数据对比称"同比"，与上月数据对比称"环比"。
 相关公式如下:
 
同比增长率计算公式
(当年值-上年值)/上年值x100% 

环比增长率计算公式
(当月值-上月值)/上月值x100% 
 
同比的话，如果每个月都齐全，都有数据lag(num,12)就ok.。我们的例子中只有19年和20年1-4月份的数据。这种特殊情况应该如何处理？
 
写法一：本案例进行单独处理
 
有4个月数据，我就lag(num,4)
 
select 
    now_month,
    now_num,
    last_num,
    round( (now_num-last_num) / last_num, 2 ) as ratio 
FROM 
(
    select 
        now_month,
        now_num, 
        lag( t1.now_num, 4 ) over (order by t1.now_month ) as last_num 
    from 
    (
        select 
            substr(order_time, 1, 7) as now_month, 
            sum(order_num) as now_num 
        from saleorder 
        group by 
            substr(order_time, 1, 7) 
    ) t1
) t2;
 
优化：
 对空值可以做一下优化处理，用到nvl()函数和lag()函数的第三个参数。
 
select 
    now_month,
    now_num,
    last_num,
    nvl ( round( ( now_num - last_num ) / last_num, 2 ), 0 ) AS ratio 
FROM 
(
    select 
        now_month,
        now_num, 
        lag( t1.now_num, 4, 0 ) over (order by t1.now_month ) as last_num 
    from 
    (
        select 
            substr(order_time, 1, 7) as now_month, 
            sum(order_num) as now_num 
        from saleorder 
        group by 
            substr(order_time, 1, 7) 
    ) t1
) t2;
 
写法二：通用方法
 
基本思路：利用date_add()生成跨年时间
 
SELECT
	t1.now_month,
    CASE WHEN now_num IS NULL OR now_num = 0 
	    THEN 0 ELSE now_num 
        END now_num,
    CASE WHEN last_num IS NULL OR last_num = 0 
        THEN 0 ELSE last_num 
		END last_num,
    CASE WHEN last_num IS NULL OR last_num = 0 
        THEN 0 ELSE round( ( now_num - last_num ) / last_num, 2 ) 
		END ratio 
FROM
(
	SELECT
		DATE_FORMAT( order_time, 'yyyy-MM' ) AS now_month,
		sum( order_num ) AS now_num 
	FROM
		saleorder 
	GROUP BY
		DATE_FORMAT( order_time, 'yyyy-MM' ) 
) t1
LEFT JOIN 
(
	SELECT
		DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) AS now_month,
		sum( order_num ) AS last_num 
	FROM
		saleorder 
	GROUP BY
	DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) 
) AS t2 ON t1.now_month = t2.now_month;
 
优化：
 用nvl()代替case when
 
SELECT
	t1.now_month,
    nvl ( now_num, 0 ) AS now_num,
	nvl ( last_num, 0 ) AS last_num,
	nvl ( round( ( now_num - last_num ) / last_num, 2 ), 0 ) AS ratio 
FROM
(
	SELECT
		DATE_FORMAT( order_time, 'yyyy-MM' ) AS now_month,
		sum( order_num ) AS now_num 
	FROM
		saleorder 
	GROUP BY
		DATE_FORMAT( order_time, 'yyyy-MM' ) 
) t1
LEFT JOIN 
(
	SELECT
		DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) AS now_month,
		sum( order_num ) AS last_num 
	FROM
		saleorder 
	GROUP BY
	DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) 
) AS t2 ON t1.now_month = t2.now_month;
 

 
计算占比并保证百分比和为1
 
常用场景
代码

 
常用场景
 
用于动态计算页面元素的百分比占比
可视化中扇形统计图的显示数值修正
 
代码
 
     /**
     * 计算数组中各项的占比，保证百分比和等于1
     * @param arr 需要计算百分比的数组,传入时必须降序排序
     * @param precision  精度 比如 4   0.34567 =》 34.56
     *
     * arr降序排序，避免较小的值因为顺位补值导致大于原本比他大的值，
     * 至于原本相等的值因为顺位补值变得不相等则不考虑，
     * 比如[33,33,33]  => [34,33,33]，将数值精确后势必三个百分比会不相等，
     * 【总和等于1】与【相等的值百分比也相等】在这里只保证第一种情况，
     * 毕竟百分比是看的与1的关系
     *
     * padEnd 属于es6 可能需要自己改写  '0'.padEnd(3,'-') => '0--'
     */
    function getPercent(arr, precision) {
        let total = 1,//总和
            percentArr,//每个模块的宽度百分比数组 精确到.00
            percentTotal = 0,//第一次计算百分比后，百分比总和
            dif,//第一次计算百分比总和后与实际和的差值,因为第一次计算百比试向下精确，所以和总会比实际和小
            len = arr.length,
            i = 0,
            result;

        //计算总和
        total = arr.reduce(function (a, b) {
            return a + b;
        });
        //简单过滤总和为零的情况
        if(total == 0){
            return
        }
        //计算百分比数组
        percentArr = arr.map(function (d, index) {
            let r = (d / total + '').substring(2, 2 + precision);//0.23456 => 2345
            r = +r.padEnd(precision, '0');//0.23 => 2300
            percentTotal += r;//累计百分比
            return +r;// 2345
        });
        //计算差值
        dif = Math.pow(10, precision) - percentTotal;

        //分配差值
        while (dif-- > 0) {
            percentArr[i++] += 1;
            i = i % len;
        }
        //转换为百分比字符串
        result = percentArr.map(function (per) {
            per += '';
            per=per.padStart(precision, '0');
            return parseFloat(per.replace(/(\d{2})/, '$1.')) + '%'
        });
        return result
    }

    console.log(getPercent([33, 33, 33], 4));