• 5星
4.85MB m0_52957036 2020-11-28 21:46:04
• 5星
6KB qq_38412739 2021-04-13 11:41:58
• 5星
222KB weixin_39709134 2021-08-03 16:04:18
• ## HiveSql计算占比、同比、占环比 sql hive

相关公式如下: 同比增长率计算公式 (当年值-上年值)/上年值x100% 环比增长率计算公式 (当月值-上月值)/上月值x100% 实现： select now_month, now_num, last_num, round( (now_num-last_num) / last_num, 2 ) as ...
一、数据准备
-- 创建表并插入数据
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;

写法二：通用方法
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;


展开全文
msWeiwenbo 2021-10-06 20:36:31
• ## 计算占比并保证百分比和为1 javascript 可视化

计算一组数据的百分比占比，并保证百分比总和为1


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

常用场景
用于动态计算页面元素的百分比占比可视化中扇形统计图的显示数值修正
代码
     /**
* 计算数组中各项的占比，保证百分比和等于1
* @param arr 需要计算百分比的数组,传入时必须降序排序
* @param precision  精度 比如 4   0.34567 =》 34.56
*
* arr降序排序，避免较小的值因为顺位补值导致大于原本比他大的值，
* 至于原本相等的值因为顺位补值变得不相等则不考虑，
* 比如[33,33,33]  => [34,33,33]，将数值精确后势必三个百分比会不相等，
* 【总和等于1】与【相等的值百分比也相等】在这里只保证第一种情况，
* 毕竟百分比是看的与1的关系
*
*/
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 += '';
return parseFloat(per.replace(/(\d{2})/, '\$1.')) + '%'
});
return result
}

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

展开全文
weixin_43954962 2020-12-24 11:39:48
• ## 计算占比总和为100整 javascript

(data[key].toString().split('.')[1]).substring(0,2) : 0 total += parseInt(data[key]) temp.push({ name: key, value: parseInt(data[key]), dec: Number(num), }) } //计算差值 let ...
const calcPercentage = ((data) => {
let total = 0
let temp = []
let obj = {}
for (let key in data) {
let num = !!data[key] ? (data[key].toString().split('.')[1]).substring(0,2) : 0
total += parseInt(data[key])
temp.push({
name: key,
value: parseInt(data[key]),
dec: Number(num),
})
}
//计算差值
let dif = 100 - total;
//根据判断小数点排序
let newArr = temp.sort(function(a,b){
return b.dec - a.dec
})

//补齐总和100
for(let i = 0; i<newArr.length; i++){
if(i < dif){
newArr[i].value = newArr[i].value + 1
}
}
newArr.forEach(item => {
for(let key in data){
if(key == item.name){
obj[key] = item.value
}
}
})
return obj
})

展开全文
weixin_39410229 2021-03-10 13:49:43
• 原标题：Power BI计算占比问题，你还需要掌握这个函数！ 关于占比，之前有篇文章(利用ALL和ALLSELECTED灵活计算占比)详细介绍了各种情况下占比的度量值。如果你没有看过这篇文章，建议去我的微信公众号中找来先读...

原标题：Power BI计算占比问题，你还需要掌握这个函数！

关于占比，之前有篇文章(利用ALL和ALLSELECTED灵活计算占比)详细介绍了各种情况下占比的度量值。如果你没有看过这篇文章，建议去我的微信公众号中找来先读一下。
经星友咨询，还有一种情况没有介绍，实际工作中会经常用到的情形是：父级字段显示该层级值占总体的比例，而打开该层级，显示子级各明细项占该层级值的占比。
仍然用占比文章中的示例，直接来看一下效果。
当处于产品类别层级，显示类别占在总体的比例，

而打开类别，产品明细显示占该类别的比例，

其实单独计算产品明细占类别的比例，以及类别占总体的比例，在占比那篇文章都介绍过，现在只是需要把这两种情况合并在一起就行了。
合并在一起，主要是需要判断当前上下文是类别还是产品名称，这就要用到一个新函数：ISINSCOPE。
这个函数直译过来的意思就是：否在范围内，官方释义为：当指定的列是级别层次结构中的级别时，返回true。
直接给出实现上述效果的DAX写法：
占比 层级 =
SWITCH (TRUE (),
ISINSCOPE ('产品'[产品名称]), DIVIDE([销售额],CALCULATE([销售额],ALLSELECTED('产品'[产品名称]))),
ISINSCOPE ('产品'[产品类别]), DIVIDE([销售额],CALCULATE([销售额],ALLSELECTED('产品'[产品类别]))),
DIVIDE([销售额],CALCULATE([销售额],ALLSELECTED('产品'[产品名称])))
)
通过ISINSCOPE来判断当前上下文是产品名称还是产品类别，分别计算不同的占比，并利用SWITCH函数区分不同的情况返回对应的数据。
因为利用了ALLSELECTED函数，这个度量值也是可以根据外部的筛选情况灵活返回对应的占比数据，

本质上依然是占比文章中利用ALL和ALLSELECTED函数和参数的变化来计算占比，只是多了一个层级的判断而已。
这两篇文章结合起来，细细把这几个度量值琢磨透彻，基本没有什么占比可以难倒你了。
如果你刚开始接触PowerBI，可在微信公众号“PowerBI星球”，回复"PowerBI"，获取《七天入门Power BI》电子书，帮你快速提升工作效率。
如果文章对你有帮助，别忘了点赞转发哦。 返回搜狐，查看更多
责任编辑：

展开全文
weixin_39627481 2020-12-20 02:47:07
• weixin_33305182 2021-01-21 11:22:59
• ## Echarts 计算每份占比（等分） echarts 前端 javascript

weixin_39428080 2021-11-02 19:36:30
• ## 窗口分析函数_9_计算组内总和的占比 数据分析 数据库

shenliang1985 2021-04-06 16:23:25
• ## 窗口分析函数_10_计算组总和的占比 数据分析 数据库

shenliang1985 2021-04-06 16:25:54
• ## sql中对某个字段进行占比计算 sql

q1923408717 2021-03-31 14:04:51
• ## C#控制台-计算当前时间占比和获取时间 c#

chinaherolts2008 2021-01-11 22:12:38
• ## ArcGIS中使用模型构建器快速计算图斑面积占比 数据库 python 人工智能

qq_43173805 2021-10-06 16:58:48
• ## pandas计算一个维度中的所有数值占总价值的占比 python

weixin_35757704 2021-03-18 14:11:07
• ## 合工大-python作业-图像识别细胞质和细胞核占比计算 python 开发语言 opencv

qq_58754996 2021-11-03 21:43:47
• weixin_39524574 2021-05-08 04:29:26
• weixin_42502345 2021-08-03 01:32:34
• ## Gartner：全行业投入人工智能，计算机视觉占比最高 人工智能 自然语言处理 big data

FL63Zv9Zou86950w 2021-09-30 13:42:33
• weixin_39575170 2020-12-20 11:31:09
• weixin_39579127 2021-06-20 03:09:55
• ## Ureport做占比报表 java 数据分析

szw906689771 2021-01-27 11:27:45
• ## python – 分组,计算和计算熊猫的比例？ python计算比例

weixin_42501904 2020-12-30 11:15:50
• ## 如何根据Java 8 Stream过滤器的输出计算百分比 java输出百分制

weixin_29513663 2021-04-06 08:23:47
• ## pandas Dataframe统计缺失值占比 python

weixin_35757704 2021-06-15 16:19:50
• ## presto sql 求占比--开窗函数解法 sql

BD_fuhong 2021-07-14 08:43:30
• ## MySQL通过分组计算百分比 mysql 占比函数

weixin_39824020 2020-12-20 10:38:36
• weixin_39753397 2021-06-27 11:29:43
• ## oracle计算比例，某字段占总数的百分比 数据库 oracle sql

lixingecho 2021-08-09 11:51:07
• ## MYSQL常见运算符和函数【重要】 mysql 占比函数

weixin_39521835 2020-12-20 10:37:59
• weixin_30174223 2021-07-26 02:30:05
• weixin_35867994 2021-07-25 03:00:49
• weixin_34766547 2021-05-20 10:23:16

...