sql server 怎么算数据的百分比 sql server 显示百分比

转载

IT剑客行 2023-08-31 13:15:15

文章标签 sql 数据库后端四舍五入数据 文章分类 SQL Server 数据库

概要

我们在进行业务系统开发时候，经常会为用户开发业务报表，而百分比计算在业务报表中非常普遍。本文以一个销售部门季度报表为例，介绍一下本人在SQL Server中常用的百分比计算方法以及误差处理的方法。

知识准备

首先我们看下面的例子的计算结果：

select convert(decimal(18,0),(1.0/3.0)*100) as LT5
select convert(decimal(18,0),(2.0/3.0)*100) as GT5

第一个的计算结果事33，第二个是67。原因如下：

计算完除法，结果应该是0.333333333和0.666666666；
乘以100后，变为33.333333300，66.666666600
通过convert函数，转换为没有小数位的deciaml，会进行四舍五入，因此结果是33和67。

根据MS官方文档，Decimal类型不存在精度损失。因此推荐在报表计算中，尤其涉及小数的取舍时候，最好把涉及的栏位先转换位Decimal或者在表定义的时候直接使用Decimal类型。

业务数据

数据表t_sales记录了每个月每个部门的销售额。具体包括：

自增Id
销售金额
部门Id
销售日期

create table t_sales (
	Id int primary key identity(1,1),
	Sale decimal(18,0) not null,
	DeptId int not null,
	RequiredDate datetime2 not null
)

具体业务数据初始化详见附录。

实现目标

计算每个销售组每个季度的销售额，以及该销售额在全年销售额中的比例。

代码实现

实际中，改代码是定义在存储过程中，为了更直观，笔者略去了其他不相关的数据。

declare @tabReport table(
	deptId int,
	Q1 decimal(18,0) default 0,
	Q1Rate decimal(18,0) default 0,
	Q2 decimal(18,0) default 0,
	Q2Rate decimal(18,0) default 0,
	Q3 decimal(18,0) default 0,
	Q3Rate decimal(18,0) default 0,
	Q4 decimal(18,0) default 0,
	Q4Rate decimal(18,0) default 0,
	Total decimal(18,0) 
)

;with CTE_MONTH as(
	select t.Id, t.Sale,t.DeptId, convert(int,FORMAT(t.RequiredDate,'MM'))MonthDate  from  t_sales t
), 
CTE_GROUPBY_DEPTID as(
select m.DeptId,
	sum(case  when MonthDate in (1,2,3) then m.Sale else 0 end ) Q1,
	sum(case  when MonthDate in (4,5,6) then m.Sale else 0 end ) Q2,
	sum(case  when MonthDate in (7,8,9) then m.Sale else 0 end ) Q3,
	sum(case  when MonthDate in (10,11,12) then m.Sale else 0 end ) Q4,
	sum(m.sale) Total
from CTE_MONTH m
group by m.DeptId
)

insert into @tabReport(DeptId, Q1, Q2, Q3, Q4, Total) select * from CTE_GROUPBY_DEPTID

update @tabReport 
set Q1Rate = convert(decimal(18,0), (Q1/(Total)*100) ),
	Q2Rate = convert(decimal(18,0), (Q2/(Total)*100) ),
	Q3Rate = convert(decimal(18,0), (Q3/(Total)*100) )
	
update @tabReport 
set Q4Rate = 100 - Q1Rate - Q2Rate - Q3Rate

select 
	Q1,  CAST(Q1Rate as nvarchar(2)) + '%' as Q1Rate,
	Q2,  CAST(Q2Rate as nvarchar(2)) + '%' as Q2Rate,
	Q2,  CAST(Q3Rate as nvarchar(2)) + '%' as Q3Rate,
	Q2,  CAST(Q4Rate as nvarchar(2)) + '%' as Q4Rate,
	Total
 from @tabReport