在做数据分析的时候,我们经常会听到同比和环比计算的概念。企业和组织在发布统计数据时,通常喜欢用与以往历史数据的同比、环比对比来说明数据的变化。例如,统计局公布2022年1月CPI同比上涨0.9%,环比上涨0.6%。在实践中,在基于数据库的数据分析场景中,环比和同比是典型的复杂计算场景之一,尤其是在Oracle等商业数据库的分析功能出现之前。以MySQL为例,8.0版本引入了Lag和Lead功能。这两个函数结合窗函数可以有效提高同比、环比等复杂计算的执行效率。在5.x系列版本中,MySQL需要依赖多次嵌套子查询和自关联来实现这样的计算。下面举个简单的例子单独来看。MySql5.x和8.0实现了同比和环比的计算。示例数据见表:CREATETABLEsales(`ProductID`varchar(20),`Salesquantity`int(20),`Salestime`timestamp(6)NULLDEFAULTNULL)INSERTINTOsalesVALUES('C1001',15,'2020-06-0110:10:12');INSERTINTOsalesVALUES('C1002',26,'2020-05-020:10:12');INSERTINTOsalesVALUES('C1003',21,'2020-04-030:10:12');INSERTINTOsalesVALUES('C1003',23,'2020-04-040:10:12');INSERTINTOsalesVALUES('C1003',0,'2020-03-050:10:12');INSERTINTOsalesVALUES('C1001',16,'2020-02-063:0:12');INSERTINTOsalesVALUES('C1002',32,'2020-01-070:10:12');INSERTINTOsalesVALUES('C1001',16,'2019-12-080:12:24');INSERTINTOsalesVALUES('C1001',32,'2019-06-090:12:24');INSERTINTOsalesVALUES('C1002',17,'2019-05-090:12:24');1.MySQL5.x:通过子查询和关联实现的同比和比例计算,根据不同年份按年按月统计总销售额,计算环比(总销售额比较与上期)和同比(总销售额与上年同期相比)为例。样例表结构和数据通过SQL按季环比和同比计算:selectyear(c.salestime)yy,month(c.salestime)mm,concat(ifnull(abs(round((sum(c.salesquantity)-ss1)/ss1*100,2)),0),'%')同比,concat(ifnull(abs(round((sum(c.salesquantity)-ss2)/ss2*100,2)),0),'%')fromsalescleftjoin(selectmonth(a.salestime)mm1,year(a.salestime)yy1,sum(a.salesquantity)ss1fromsalesaGROUPBYmm1,yy1)aonmonth(c.salestime)=a.mm1anda.yy1=year(c.salestime)-1leftjoin(selectmonth(a.salestime)mm2,year(a.salestime)yy2,sum(a.salesquantity)ss2fromsalesaGROUPBYmm2,yy2)bon(b.yy2=year(c.salestime)andb.mm2+1=month(c.salestime)OR(yy2=year(c.salestime)-1ANDb.mm2=12ANDmonth(c.Salestime)=1))groupbyyy,mmorderbyyy,mmasc计算结果:2。MySQL8.0:通过分析函数计算同比和比例**MySql8.0支持Lead和Lag分析函数。虽然可以大大提高同年和环月计算的效率,但仍然需要编写SQL语句进行处理。2.1计算同比selectt2.year,t2.month,concat(round((t2.quantity-t1.quantity)/t1.quantity,2)*100,'%')asyear-on-yearfrom(SELECTyear(salestime)asyear,month(salestime)asmonth,sum(salesquantity)asquantityfromsalesgroupbyyear(salestime),month(salestime)orderbyyear(salestime)desc,month(salestime)desc)t1,(SELECTyear(salestime)asyear,month(salestime)asmonth,sum(salesquantity)asquantityfromsalesgroupbyyear(salestime),month(salestime)orderbyyear(salestime)desc,month(salestime)desc)t2wheret1.Year=t2.Year-1andt1.Month=t2.Month2,2计算链比SELECTmm,CONCAT(ROUND(IFNULL((xl-first_xl)/first_xl*100,2),0),'%')ASringFROM(SELECTmm,xl,lead(xl,1)over(ORDERBYmmDESC)ASfirst_xlFROM(SELECTDATE_FORMAT(销售时间,'%Y-%m')ASmm,sum(销售数量)ASxlFROMsalesGROUPBYDATE_FORMAT(销售时间,'%Y-%m'))t)aSqlServer2008R2和Oracle10g之后,都提供了Lag和Lead分析功能。具体计算逻辑和用法与上述MySQL8.0类似。3、使用BI工具的计算引擎对于这种复杂的计算场景,商业智能BI数据分析工具提供了更高效的解决方案。以WynEnterprise嵌入式商业智能软件为例,其内置的wax分析表达式和快速计算引擎提供了直接实现同比、环比等复杂计算的能力,无需编写复杂而冗长的SQL。3.1使用内置的同比、环比速算功能**同比、环比计算一般是BI工具的标准功能,我们可以直接实现通过设置。3.2使用数据分析表达式如果内置的快速计算不能满足要求,也可以通过分析表达式实现更复杂的计算。分析表达式是一种更灵活、更强大的数据计算方法。通过丰富的函数,用户可以像Excel公式一样自由组合,实现更强大的分析能力。分析表达式根据数据模型进行业务计算,通过一些定义明确的函数,使用正确的语法来完成一个复杂的业务逻辑计算。这样,用户就可以更灵活地使用数据,最大限度地利用数据。各位老板们,通过比较SQL和BI数据分析工具在处理同比、环比等复杂计算时的差异,我们可以发现,在数据计算和处理能力上,专业的工具更加方便。以后如果工作中有类似的分析计算需求,选择BI分析工具来处理比较合适。
