精华内容
下载资源
问答
  • 年份的同比
    千次阅读
    2021-11-02 16:12:25

    我们来看下计算公式:

    1、同比环比计算规则

    月度环比=(本月-上月)/上月*100%

    月度同比=(当年本月-上年本月)/上年本月*100%

    年度同比=(今年-去年)/去年*100%

    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for finance
    -- ----------------------------
    DROP TABLE IF EXISTS `finance`;
    CREATE TABLE `finance`  (
      `id` int(12) NOT NULL,
      `amount` decimal(6, 0) NULL DEFAULT NULL,
      `dt` timestamp(0) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of finance
    -- ----------------------------
    INSERT INTO `finance` VALUES (1, 22, '2021-01-01 15:54:05');
    INSERT INTO `finance` VALUES (2, 11, '2021-01-03 15:55:13');
    INSERT INTO `finance` VALUES (3, 55, '2021-02-11 15:56:52');
    INSERT INTO `finance` VALUES (4, 55, '2021-03-19 15:58:14');
    INSERT INTO `finance` VALUES (5, 33, '2021-04-22 15:58:57');
    INSERT INTO `finance` VALUES (6, 22, '2022-01-01 16:09:08');
    INSERT INTO `finance` VALUES (7, 23, '2022-02-01 16:09:35');
    INSERT INTO `finance` VALUES (8, 33, '2022-03-01 16:09:58');
    
    SET FOREIGN_KEY_CHECKS = 1;
    

    上面是收入表:

    我要统计年度同比:(今年-去年)/去年*100%

    	select 
    	 t1.dtYear,
    	 t1.sumAmount,
    	 t2.latSumAmount, 
    	     CASE
        WHEN latSumAmount IS NULL
         OR latSumAmount = 0 THEN
         0 ELSE ( CONVERT ( ( ( sumAmount - latSumAmount ) / latSumAmount ) * 100, DECIMAL ( 10, 2 ) ) )
          END hb
    	 
    	  from 
      (SELECT
    	   date_format(f.dt,'%Y') dtYear,
    		 SUM(f.amount) as sumAmount
     FROM
    	   finance f 
    		 GROUP BY dtYear) t1
    		 
    		 left join 
    		   (SELECT
    	   date_format(f.dt,'%Y') dtYear,
    		 SUM(f.amount) as latSumAmount
     FROM
    	   finance f 
    		 GROUP BY dtYear) t2 on t1.dtYear = t2.dtYear + 1
    		 

    月度环比:月度环比=(本月-上月)/上月*100%

    	select 
            t1.dtMonth,
            t1.sumAmount,
            t2.lastMonthsumAmount,
    			    CASE
        WHEN lastMonthsumAmount IS NULL
         OR lastMonthsumAmount = 0 THEN
         0 ELSE ( CONVERT ( ( ( sumAmount - lastMonthsumAmount ) / lastMonthsumAmount ) * 100, DECIMAL ( 10, 2 ) ) )
          END monthhb
    				
           from  
         
    		 (SELECT
            date_format( f.dt, '%Y-%m' ) dtMonth,
            SUM( f.amount ) AS sumAmount 
          FROM
            finance f 
          GROUP BY
             dtMonth)t1 left join 
              		
    				(SELECT 
            DATE_FORMAT(DATE_ADD(f.dt,INTERVAL 1 MONTH),'%Y-%m') as lastMonth,
            SUM( f.amount ) AS lastMonthsumAmount 
          FROM
            finance f 
          GROUP BY
             lastMonth) t2 on t1.dtMonth = t2.lastMonth

    月度同比=(当年本月-上年本月)/上年本月*100%

       
    		select 
            t1.dtMonth,
            t1.sumAmount,
            t2.lastYearMonthsumAmount,
    			    CASE
        WHEN lastYearMonthsumAmount IS NULL
         OR lastYearMonthsumAmount = 0 THEN
         0 ELSE ( CONVERT ( ( ( sumAmount - lastYearMonthsumAmount ) / lastYearMonthsumAmount ) * 100, DECIMAL ( 10, 2 ) ) )
          END monthhb
    				
           from  
         
    		 (SELECT
    		    date_format( f.dt, '%Y' ) dtYear,
            date_format( f.dt, '%Y-%m' ) dtMonth,
            SUM( f.amount ) AS sumAmount 
          FROM
            finance f 
          GROUP BY
             dtYear,dtMonth)t1 left join   		
    				(SELECT 
    				 date_format( f.dt, '%Y' ) dtYear,
            date_format( f.dt, '%Y-%m' ) dtMonth,
            DATE_FORMAT(DATE_ADD(f.dt,INTERVAL 1 YEAR),'%Y-%m') as lastYearMonth,
            SUM( f.amount ) AS lastYearMonthsumAmount 
          FROM
            finance f 
          GROUP BY
             dtYear,dtMonth,lastYearMonth) t2 on t1.dtMonth = t2.lastYearMonth

    更多相关内容
  • MySQL计算同比和环比

    千次阅读 2022-03-17 15:43:50
    今天在做数据建模的时候,ads层的需求中有个叫同比和环比的指标,这两指标的计算之前都没有接触过,经过我两三个小时的研究,终于搞明白什么是同比,什么是环比,如何使用Mysql来计算同比和环比。 我们先来看看什么...

    MySQL计算同比和环比

    今天在做数据建模的时候,ads层的需求中有个叫同比和环比的指标,这两指标的计算之前都没有接触过,经过我两三个小时的研究,终于搞明白什么是同比,什么是环比,如何使用Mysql来计算同比和环比。

    我们先来看看什么是同比,什么是环比:

    • 同比:通常是指今年第n月与去年第n月相比。同比发展速度主要是为了消除季节变动的影响,用以说明本期发展水平与去年同期发展水平对比而达到的相对发展速度。

    • 环比:通常是指表示连续2个单位周期(比如连续两月)内的量的变化比。环比包括两种:环比增长速度和环比发展速度。

    那同比增长率和环比增长率又如何计算呢:

    同比增长率:
    同 比 增 长 率 = ( 本 期 数 − 同 期 数 ) / 同 期 数 ∗ 100 % 同比增长率=(本期数-同期数)/同期数*100 \% =()/100%

    环比增长率:
    环 比 增 长 率 = ( 本 期 数 − 上 期 数 ) / 上 期 数 × 100 % 环比增长率=(本期数-上期数)/上期数×100\% =()/×100%

    在MySQL中如何计算同比和环比

    数据准备

    创建商品表并向商品表中添加数据

    CREATE TABLE product  (
      `产品ID` varchar(20) NOT NULL,
    	  `产品名称` varchar(20) ,
      `产品单价` int (10)
    )
    INSERT INTO product VALUES ('C1001','产品A',45);
    INSERT INTO product VALUES ('C1002','产品B',52);
    INSERT INTO product VALUES ('C1003','产品C',39);
    

    image-20220317095350813

    创建订单明细表并向订单明细表中添加数据

    CREATE TABLE sales  (
        `订单ID` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
      `产品ID` varchar(25) NOT NULL ,
    	  `销售数量` int(20) ,
      `销售时间` timestamp(6) NULL DEFAULT NULL
    );
    INSERT INTO sales(`产品ID`,`销售数量`,`销售时间`) VALUES ('C1001', 15, '2020-06-01 10:10:12');
    INSERT INTO sales(`产品ID`,`销售数量`,`销售时间`)  VALUES ('C1002',26, '2020-05-02 0:10:12');
    INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1003', 21, '2020-04-03 0:10:12');
    INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1003', 23, '2020-04-04 0:10:12');
    INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1003', 0, '2020-03-05 0:10:12');
    INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1001', 16, '2020-02-06 3:0:12');
    INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1002', 32, '2020-01-07 0:10:12');
    INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1001', 16, '2019-12-08 0:12:24');
    INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1001', 32, '2019-06-09  0:12:24');
    INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1002', 17, '2019-05-09 0:12:24');
    

    image-20220317121540101

    计算同比和环比

    select  year(c.销售时间) yy,month(c.销售时间) mm,     
           sum(c.销售数量*d.产品单价) ss,
    concat(ifnull(abs(round((sum(c.销售数量*d.产品单价)-ss1)/ss1*100,2)),0),'%') 同比,
    concat(ifnull(abs(round((sum(c.销售数量*d.产品单价)-ss2)/ss2*100,2)),0),'%')  环比
    from sales c
    left join product d on c.产品ID=d.产品ID
    left join (select month(a.销售时间) mm1,
                        year(a.销售时间) yy1,
                        sum(a.销售数量*d.产品单价) ss1
              from sales a
              left join product d on a.产品ID=d.产品ID
              GROUP BY mm1,yy1) a
              on month(c.销售时间) = a.mm1 
              and a.yy1 = year(c.销售时间)-1	 
     left join  (select month(a.销售时间) mm2,
                        year(a.销售时间) yy2,
                        sum(a.销售数量*d.产品单价) ss2
                 from sales a
    		     left join product d on a.产品ID=d.产品ID
                  GROUP BY mm2,yy2) b
    on (b.yy2 = year(c.销售时间) and b.mm2+1 = month(c.销售时间) OR (yy2=year(c.销售时间)-1 
    AND b.mm2 = 12 AND month(c.销售时间) = 1))
     group by yy, mm
     order by yy,mm asc
    

    sql解析

    select  year(c.销售时间) yy,month(c.销售时间) mm,     
           sum(c.销售数量*d.产品单价) ss,
     
    # concat函数,mysql字符串拼接,因为同比和环比都是百分数
    # ifnull函数,mysql判断字段是否为空,为空则为0
    # abs函数,mysql取绝对值,因为我这里取的都是正数
    # round函数,mysql保留几位小数
    concat(ifnull(abs(round((sum(c.销售数量*d.产品单价)-ss1)/ss1*100,2)),0),'%') 同比,
    concat(ifnull(abs(round((sum(c.销售数量*d.产品单价)-ss2)/ss2*100,2)),0),'%')  环比
    from sales c
    left join product d on c.产品ID=d.产品ID
     
    # 上一年销售额
    left join (select month(a.销售时间) mm1,
                        year(a.销售时间) yy1,
                        sum(a.销售数量*d.产品单价) ss1
              from sales a
              left join product d on a.产品ID=d.产品ID
              GROUP BY mm1,yy1) a
              # 同比月份相同,年份减1
              on month(c.销售时间) = a.mm1   
              and a.yy1 = year(c.销售时间)-1	
     
    # 今年销售额 
     left join  (select month(a.销售时间) mm2,
                        year(a.销售时间) yy2,
                        sum(a.销售数量*d.产品单价) ss2
                 from sales a
    		     left join product d on a.产品ID=d.产品ID
                  GROUP BY mm2,yy2) b
              # 环比取数考虑到为一月的情况
    on (b.yy2 = year(c.销售时间) and b.mm2+1 = month(c.销售时间) OR (yy2=year(c.销售时间)-1 
    AND b.mm2 = 12 AND month(c.销售时间) = 1))
     group by yy, mm
     order by yy,mm asc
    

    结果:

    image-20220317121641787

    除此之外,还想要计算累计销售的话,可以这样写:

    select year(销售时间) yy,month(销售时间) mm,
    sum(销售数量*b.产品单价) over(order by year(销售时间) ,month(销售时间) ) 累计数量 
    from sales a
    left join product b on a.产品ID=b.产品ID
    order by  yy,mm
    

    同比和环比这块的确不好计算

    展开全文
  • Tableau 日&月环比&同比

    千次阅读 2021-11-03 14:59:17
    )), -1)) 简易版: zn(SUM([Sales]))/LOOKUP(zn(SUM([Sales])),-1)-1 方法2:筛选年份同比上一年 步骤一:创建参数,选择int格式的。 步骤二:创建一个筛选字段,并拖到筛选器,选择真,此处的select year是步骤一的...
    1. 最新日期的日环比

    我们知道环比公式是:今天销售额/昨天销售额-1

    一般数据源刷新方式是T+1刷新,所以数据源中的最近日期是相对日期的昨天,创建一个计算字段【日环比】:

    sum(if [日期]=dateadd('day',-1,today()) then [销售额] end )/

    sum(if [日期]=dateadd('day',-2,today()) then [销售额] end )-1

    将创建好的计算字段【日环比】拖拽至文本上,会显示对应的环比,如图所示:

    2.最近的月环比

    同样的方法,创建计算字段【月环比】:

    sum(if [日期]= datetrunc('month',dateadd('month',-1,today())) then [销售额] end )/

    sum(if [日期]=datetrunc('month',dateadd('month',-2,today())) then [销售额] end )-1

    3.月同比

    创建一个计算字段【月同比】:

    sum(if [日期]=datetrunc('month',dateadd('month',-1,today())) then [销售额] end)/

    sum(if [日期]=datetrunc('month',dateadd('month',-1,dateadd('year',-1,today()))) then [销售额] end )-1

    4.上月对比上上月的环比

    创建一个计算字段【月汇总环比】

    COUNT(if YEAR([浏览日期])=YEAR(dateadd('month',-1,today())) and MONTH([浏览日期])=MONTH(dateadd('month',-1,today()))  then [浏览主键] end )/
    COUNT(if YEAR([浏览日期])=YEAR(dateadd('month',-2,today())) and MONTH([浏览日期])=MONTH(dateadd('month',-2,today()))  then [浏览主键] end )-1

    解释:

    YEAR([浏览日期])=YEAR(dateadd('month',-1,today())) :统计数据的年=今日的上个月的年份 ,

    MONTH([浏览日期])=MONTH(dateadd('month',-1,today())):统计数据的月=今日的上个月的月份

    (另一种相同写法:COUNTD(if YEAR([日期])=YEAR(datetrunc('month',dateadd('month',-1,today()))) and MONTH([日期])=MONTH(datetrunc('month',dateadd('month',-1,today())))  then [知识点ID] end )/
    COUNTD(if YEAR([日期])=YEAR(datetrunc('month',dateadd('month',-2,today()))) and MONTH([日期])=MONTH(datetrunc('month',dateadd('month',-2,today())))  then [知识点ID] end )-1)

    5.上周对比上上周的环比

    创建一个计算字段【周汇总环比】

    sum(if YEAR([日期])=YEAR(dateadd('week',-1,today())) and DATEPART('week',[日期w])=DATEPART('week',dateadd('week',-1,today()))   then [更新量] end )/
    sum(if YEAR([日期])=YEAR(dateadd('week',-2,today())) and DATEPART('week',[日期w])=DATEPART('week',dateadd('week',-2,today()))  then [更新量] end )-1

    6.计算同比和环比任意天数据

    网上看了很多帖子,要实现同比和环比都是用lookup函数,但是如果数据并没有顺序或者一个月有很多行数据操作起来就比较麻烦了。

    现在用一个比较简单的fixed函数来实现这个功能。看看fixed函数的用法:

    这是个超级强大的函数,可以跨层级固定住指定的维度,甚至做得比较SQL还简便,例如超市里面想看顾客买牛奶的同时又购买了什么,对于结构化数据如果写SQL需要先订单出订单号再查看订单明细一共需要扫描两次,这只是看一个产品而已,如果看N个产品呢?计算量是非常大的,但是用fixed函数一次就可以同时查看N个产品的情况。

    ,再例如想看到明细的同时又看到汇总信息 ,会员的RFM求出每个会员最后消费日期等等,更多fixed函数的应用推荐这个链接,里面有各种LOD表达式的用法。

    下面举例,要看某城市昨天相比上周同一天的销售增长。

    首先,计算昨天的销售额,我把门店固定住,找昨天的销售额,创建计算函数

    {FIXED [StoreID] : SUM(if [SalesDate]=TODAY()-1 then [netsales] end)}

    这里利用tableau实现了sumif效果

    然后把门店固定住,计算上周同一天的销售额

    最后计算环比上周,搞定

    我们可以把负增长的数据表示出来

    效果如下:

    Tableau同比或环比计算方法

    方法1:官方提供的快速表计算:年度同比增长
    计算字段:
    (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

    简易版:
    zn(SUM([Sales]))/LOOKUP(zn(SUM([Sales])),-1)-1

    方法2:筛选年份同比上一年

    步骤一:创建参数,选择int格式的。

    步骤二:创建一个筛选字段,并拖到筛选器,选择真,此处的select year是步骤一的参数字段。

    int(DATEPART(‘year’,[Order Date]))= int([select year])

    步骤三:重新创建一个订单日期字段,用途是应用步骤一的参数,使其生效。

    if int(DATEPART(‘year’,[Order Date])) = int([select year]) then [Order Date] END

    步骤四:计算本年sales。

    { FIXED DATEPART(‘month’, [Order Date]):sum(
    IF int(datepart(‘year’,[Order Date])) = int([select year]) then [Sales] END)}

    步骤五:计算去年sales。

    { FIXED DATEPART(‘month’, [Order Date]):sum(
    IF int(datepart(‘year’,[Order Date])) = int([select year])-1 then [Sales] END)}

    步骤六:计算同比

    即: 步骤四的sales/步骤五的sales-1

    步骤七:计算一个颜色字段,此处的year over year sales=步骤六的同比

    IF SUM([year over year sales])>0 then ‘green’

    步骤八:将相关字段拖到行列位置:

    将步骤三的日期拖到列,选择月份;
    将步骤六的同比拖到行,显示标签
    将步骤七的颜色字段拖到颜色。


    方法3:通过设定日期月度范围来比较销售额

    步骤一:这里重点是筛选器日期设置,创建一个计算字段,将此字段先拖到行或者列,由离散字段转换为连续字段,再拖到筛选器,选择你需要的日期范围

    LOOKUP(MIN([Order Date]),0)

    步骤二:将order date 字段拖到列,选择离散字段的year,然后再下钻到月份。

    步骤三:将sales拖到行,然后选择快速表计算的同比【year over year growth】,添加标签。

    方法4:与去年、2年前、3年前对比

    步骤一:创建一个计算字段表当前年份,【current year 】
    { max(YEAR([Order Date]))}

    步骤二:创建一个参数int类型,【select comparison time】
    -1,显示为去年
    -2,显示为2年前
    -3,显示为3年前

    步骤三:创建一个字段计算为对比年【comparison year】,current year 为步骤一的计算字段,select
    comparison time是步骤二的参数

    [current year ]+[select comparison time ]

    步骤四:创建当前年份的sales【current year sales 】,current year是步骤一的当前年份

    if YEAR([Order Date])=[current year ] then [Sales] END

    步骤五:对比年的销售额【comparison year sales】

    if YEAR([Order Date])=[comparison year ] then [Sales] END

    步骤六:计算同比【current vs Comparison】

    sum([current year sales ])/sum([comparison year sales])-1

    步骤七:创建正数对比和负数对比

    正数对比:IF [current vs Comparison]>=0 then [current vs Comparison] END
    负数对比:IF [current vs Comparison]<0 then [current vs Comparison] END

    步骤八:将步骤四、步骤二、步骤七的内容都放到标记的【标签】内,调整顺序

    tableau 日周月筛选器_Tableau技巧——快速计算同比和环比(及其衍生)

    同比和环比是统计分析中常见的功能,在Tableau中,通过表计算,我们不用写一行代码,就可以快速实现同比和环比。

    环比

    根据百度得到,环比增长率=(本期数-上期数)/上期数×100%。

    以2016年10月销售额为例,环比增长率=(2016年10月销售额-2016年9月销售额)/2016年9月销售额*100。那么在Tableau中,怎么才能不写代码就得到环比增长率呢?不写代码?我书读的少,不要忽悠我哦!

    以10.2版本为例,数据源使用自带的《示例 - 超市》,读取数据源后,将订单日期拖到行功能区,右键选择下面的“月”格式,并设置为离散,效果如下:

    然后复制度量窗格的“销售额”,将“度量名称”拖到列功能区,按住ctrl键将“度量名称”从列功能区拖到筛选器区,并选择“销售额”、“销售额(复制)”两个度量字段:

    将“度量”窗格的“度量值”拖到“标记”——“文本”:

    这样我们就得到了一个按年月升序排列的销售额数据,接下来右键“标记”区的“总计(销售额(复制))”字段,选择“快速表计算”——“百分比差异”,就得到了我们想要的环比数据啦:

    设置下字体格式,编辑下字段别名:

    你没有看错,就是辣么简单!就是不写一行代码!就是辣么任性!环比就搞定了!!!

    先不要高兴的太早,老话说,知其然,还要知其所以然。为什么Tableau计算环比这么简单,我们来看看原因在哪里。

    在前面的操作中,我们使用了Tableau一个强大的功能,表计算。这里的表,并不是数据源对应的那个表,而是一个虚拟表,是在工作表开发界面由选择的维度和度量构成的一张虚拟表。在环比这个例子中,虚拟表是由“订单日期”按照年月格式显示的一个维度字段,和“销售额”、“销售额(复制)”两个度量字段,按照“订单日期”升序排列而创建。升序排列让Tableau知道该如何选择“本期”和“上期”,两个度量字段用于实际计算环比,但是最关键一步,还是在于选择的表计算类型“百分比差异“。让我们右键“总计(销售额(复制))”字段,选择“编辑表计算”,一探“百分比差异”的神奇之处:

    这里有几个关键之处,首先我们看到“计算依据”选择了“表(向下)”,由于虚拟表是按照订单日期升序排列,因此“表(向下)”告诉了Tableau你计算“百分比差异”的顺序是往下,另外下面可以看到“相对于”——“上一”,这就是告诉Tableau,“上期”的位置是“当期”的上面一个数值。翻译成人话就是,从2013年1月开始计算,往下一直到2016年12月。第一个当期是2013年1月销售额,上期就是上一行,也就是2012年12月销售额,但是虚拟表中没有2012年12月销售额,因此2013年1月的环比为空。然后往下到第二行,2013年2月销售额为当期,那么上期就是上一行,也就是2013年1月销售额,这时候按照内置的计算逻辑:(2013年2月销售额-2013年1月销售额)/2013年1月销售额*100,就得到了2013年2月环比增长率,后面以此类推,直接2016年12月销售额计算完毕,整个表计算也就结束。

    有同学会问,如果不是升序排列,而是降序呢?那么只要在“相对于”这里,选择“下一”就可以了:

    再提高一点,除了通过可视化了解环比,还可以通过Tableau自动生成的代码来学习环比计算,我们可以双击“标记”区的“总计(销售额(复制))”字段,将里面的代码拷贝出来,贴到一个文本文件:

    是不是看了有点晕,没关系,我们先解释下几个函数:

    ZN函数是指如果括号里的表达式结果不为NULL,则返回它,否则返回0。用这个函数主要是处理NULL值。

    ABS函数是取括号里表达式的绝对值。

    重点来了,赶紧记赶紧记,LOOKUP函数是这段代码的灵魂,括号内分两部分,逗号前面是当前值,后面的-1表示上一个值,翻译成人话就是根据当前值,找它的上一个值。

    三个函数明白了以后,这段代码的含义就可以理解为:

    (当前值 - 根据当前值找到它的上一个值)/根据当前值找到它的上一个值的绝对值。虽然有点绕口,但这就是“表计算——百分比差异”在Tableau中的语法。

    掌握了这个公式,其实我们就可以直接用代码来快速实现同比了。

    同比

    根据百度得到,同比增长率=(本期数-同期数)/同期数×100%。

    以2016年10月销售额为例,环比增长率=(2016年10月销售额-2015年10月销售额)/2015年10月销售额*100。下面我们看下如何快速实现同比。我们复制刚才的“环比”工作表,重命名为“同比”:

    然后我们双击“标记”——“总计(销售额(复制))”,将公式修改如下:

    (ZN(SUM([销售额 (复制)])) - LOOKUP(ZN(SUM([销售额 (复制)])),-12)) / ABS(LOOKUP(ZN(SUM([销售额 (复制)])),-12))

    想一下,为什么这里不是-1,而是-12了?

    因为同比,只要日期中间没有中断,那么上一个值就应该是去年同月,自然等于当前年月-12了。

    我们来看下结果:

    2013年的12个月份,由于没有对应的2012年数据,因此同比也没有数据,第一个应该是从2014年1月开始。有兴趣同学可以逐一核对。

    如果日期按降序排列,那么代码中只需要将-12替换成12就可以了,是不是非常的简单方便!

    掌握了这个计算公式后,不论同比、环比,还是当前值与之前、之后的任一值进行比较,我们都可以快速通过修改代码,迅速得到正确结果,就是辣么爽!

    注意事项:

    1.日期序列中间不能有缺失,比如2016年12个月,如果没有5月份,那就变成6月-4月,这是不对的。因此需要对数据源做出处理,人为添加5月份,值为0即可。

    2.由于这个方法与日期字段的排序有紧密关系,因此当用户修改了排序顺序后,原来的计算公式就失效了,聪明的你,是否有更好的解决方案呢?
     

    展开全文
  • 实现同比、环比计算的N种姿势

    千次阅读 2022-03-09 13:41:51
    在做数据分析时,我们会经常听到同比、环比同比的概念。各个企业和组织在发布统计数据时,通常喜欢用同比、环比来和之前的历史数据进行比较,用来说明数据的变化情况。例如,统计局公布2022年1月份CPI同比增长0.9%,...

    在做数据分析时,我们会经常听到同比、环比同比的概念。各个企业和组织在发布统计数据时,通常喜欢用同比、环比来和之前的历史数据进行比较,用来说明数据的变化情况。例如,统计局公布2022年1月份CPI同比增长0.9%,环比增长0.6%。

    实际中,在基于数据库的数据分析场景中,环比和同比是典型的复杂计算场景之一,特别是在Oracle等商业数据库的分析函数出现之前。以MySQL为例,在8.0版本中才引入了Lag和Lead函数,这两个函数结合开窗函数有效的提高了同比、环比等复杂运算的实现效率。在5.x系列版本中,MySQL需要依赖多次嵌套子查询和自关联才能实现此类计算。

    我们以一个简单的例子,来分别看下,MySql 5.x和8.0是具体实现同比、环比计算的。

    示例数据见表:

    CREATE TABLE sales  (
      `产品ID` varchar(20),
    	  `销售数量` int(20) ,
      `销售时间` timestamp(6) NULL DEFAULT NULL
    )
    INSERT INTO sales VALUES ('C1001', 15, '2020-06-01 10:10:12');
    INSERT INTO sales VALUES ('C1002',26, '2020-05-02 0:10:12');
    INSERT INTO sales VALUES ('C1003', 21, '2020-04-03 0:10:12');
    INSERT INTO sales VALUES ('C1003', 23, '2020-04-04 0:10:12');
    INSERT INTO sales VALUES ('C1003', 0, '2020-03-05 0:10:12');
    INSERT INTO sales VALUES ('C1001', 16, '2020-02-06 3:0:12');
    INSERT INTO sales VALUES ('C1002', 32, '2020-01-07 0:10:12');
    INSERT INTO sales VALUES ('C1001', 16, '2019-12-08 0:12:24');
    INSERT INTO sales VALUES ('C1001', 32, '2019-06-09  0:12:24');
    INSERT INTO sales VALUES ('C1002', 17, '2019-05-09 0:12:24');
    
    

    1、MySQL 5.x :通过子查询和关联实现同比和占比计算

    以按年月统计不同年份的销售总值,并计算环比(销售总额同比上期)、同比(销售总额同比去年同期)为例。

    示例表结构和数据

    通过SQL计算环比和同比:

    select  year(c.销售时间) yy,month(c.销售时间) mm,     
    concat(ifnull(abs(round((sum(c.销售数量)-ss1)/ss1*100,2)),0),'%') 同比,
    concat(ifnull(abs(round((sum(c.销售数量)-ss2)/ss2*100,2)),0),'%')  环比
    from sales c
    left join (select month(a.销售时间) mm1,
                        year(a.销售时间) yy1,
                        sum(a.销售数量) ss1
              from sales a
              GROUP BY mm1,yy1) a
              on month(c.销售时间) = a.mm1 
              and a.yy1 = year(c.销售时间)-1	 
     left join  (select month(a.销售时间) mm2,
                        year(a.销售时间) yy2,
                        sum(a.销售数量) ss2
                 from sales a
    		   
                  GROUP BY mm2,yy2) b
    on (b.yy2 = year(c.销售时间) and b.mm2+1 = month(c.销售时间) OR (yy2=year(c.销售时间)-1 
    AND b.mm2 = 12 AND month(c.销售时间) = 1))
     group by yy, mm
     order by yy,mm asc
    
    

    计算结果:

    2、 MySQL 8.0 :通过分析函数实现同比和占比计算**

    MySql8.0支持了Lead和Lag分析函数,虽然可以大幅提高同、环比计算的效率,但仍然需要编写SQL语句处理。

    2、1 计算同比

    select t2.年份,t2.月份,concat(round((t2.数量-t1.数量)/t1.数量,2)*100,'%') as 同比 from (
    SELECT year(销售时间) as 年份,month(销售时间) as 月份,sum(销售数量) as 数量 from sales 
    group by year(销售时间),month(销售时间) order by year(销售时间) desc, month(销售时间) desc
    ) t1
    ,(
    SELECT year(销售时间) as 年份,month(销售时间) as 月份,sum(销售数量) as 数量 from sales 
    group by year(销售时间),month(销售时间) order by year(销售时间) desc, month(销售时间) desc
    ) t2 where t1.年份=t2.年份-1 and t1.月份=t2.月份
    
    

    2、2计算环比

    SELECT
    	mm,
    	CONCAT(
    		ROUND(
    			IFNULL(
    				(xl - first_xl) / first_xl * 100,
    				2
    			),
    			0
    		),
    		'%'
    	) AS 环比
    FROM
    	(
    		SELECT
    			mm,
    			xl,
    			lead (xl, 1) over (ORDER BY mm DESC) AS first_xl
    		FROM
    			(
    				SELECT
    					DATE_FORMAT(销售时间, '%Y-%m') AS mm,
    					sum(销售数量) AS xl
    				FROM
    					sales
    				GROUP BY
    					DATE_FORMAT(销售时间, '%Y-%m')
    			) t
    	) a
    
    

    在SqlServer2008R2和Oracle10g之后,都提供了Lag和Lead分析函数。具体的计算逻辑和用法与上述MySQL8.0类似。

    3、使用 BI工具的计算引擎

    针对此类复杂的计算场景,商业智能BI数据分析工具提供了更加高效的解决方案。以Wyn Enterprise嵌入式商业智能软件为例,其内置的wax分析表达式和快速计算引擎,提供直接实现同比、环比等复杂计算的能力,而不再需要写复杂冗长的SQL。

    3、1 使用内置的同比、环比快速计算功能**

    同比、环比等计算一般是BI工具的标准功能,我们可以直接通过设置实现。

    3、2 使用数据分析表达式

    如果内置的快速计算无法满足要求,还可以通过分析表达式实现更复杂的计算。分析表达式是一种更加灵活、强大的数据计算方式,通过丰富的函数,用户可以像Excel公式一样自由组合,实现更加强大的分析能力。分析表达式基于数据模型进行业务计算,以一些定义好的函数运用正确的语法来完成某个复杂的业务逻辑计算。这样可以使用户更灵活的地使用数据,最大限度的利用数据。

    各位老板们,通过对比SQL和BI数据分析工具在处理同比、环比等复杂计算中的差异,我们可以发现,还是专业的工具在数据计算和处理能力上要更加便捷。以后在工作中,如果有类似的分析计算需求,选择BI分析工具来处理就是再合适不过的了。

    展开全文
  • 同比就是个上一阶段相比环比就是。。。还是举例子吧, 今年6月和去年6月对比就是环比同比增长的意思:比如你有10000元,存进了银行,过了一段时间,他变成11000元,相比原来增长了10%,这就是同比增长。 同比增长...
  • Excel数据分析之环比和同比的计算

    千次阅读 2022-03-16 19:44:31
    年: ...3.例如2020年7月同比 =(2020年7月数据 / 2019年7月数据)- 1 日: 4.例如2020年7月1日的日环比 =(2020年7月1日数据 / 2020年6月31日数据)- 1 5.例如2020年7月1日的月环比 =(2020年7...
  • MySQL统计同比环比SQL

    2021-01-25 23:15:03
    大体思路:MySQL没有类似oracle方便的统计函数,只能靠自己去硬计算:通过时间字段直接增加年份、月份,然后通过left join关联时间字段去计算环比、同比公式即可原始表结构:求同比SQL:-- 按年同比SELECT t5.*, ...
  • 本帖最后由 Noah 于 2015-8-20 09:22 编辑同比(环比)在分析模型中的实现1) 案例说明本文整理用友医疗BQ应用开发过程中提出的部分关键问题及其解决方案。2)实际问题在数据统计分析项目中,经常会有指标需要做同比、...
  • 数据分析:同比-环比

    千次阅读 2021-11-24 22:57:37
    文章目录数据分析:同比-环比同比-环比同比环比日期获取:上月-本季-本年类设计CaseConsts:时间类型常量(本月、本季、本年)DateListBean:时间Bean方法设计getDateList:根据时间类型获取时间checkStartDate:...
  • Tableau同比或环比计算方法

    千次阅读 2020-08-27 09:26:32
    笔者借鉴学习了6种tableau同比或环比计算方法 方法1:官方提供的快速表计算:年度同比增长 方法2:筛选年份同比上一年 方法3:通过设定日期月度范围来比较销售额 方法4:
  • SQL 同比环比

    千次阅读 2022-01-04 14:08:24
    一、下拉框选择年份参数筛选 select distinct strftime('%Y',订购日期) as year from 订单 order by year asc 二、页面隔行变色 条件属性隔行变色 三、在SQL中计算环比,不在单元格内计算与过滤 select...
  • 首先同比是指 不同年份的相同月份相比较 难点是取到上一年的相同月数据 SELECT date_str, date_year, date_month, curr_month_num, last_year_num, ( curr_month_num - last_year_num ) / last_year_num AS...
  • MySQL计算环比、同比(年、月、季度)

    万次阅读 多人点赞 2019-08-06 12:50:46
    MySQL计算环比、同比(年、月、季度) 环比 所谓“环比”即连续2个单位周期(比如连续两月)内的量的变化比。如今年8月比今年7月。 当然这里的单位不一定是月,可以是任何时间单位。 环比增长率=(本期数-上期数)/...
  • java时间计算同比环比周的问题

    千次阅读 2020-03-18 17:16:23
    2、同时需要计算对应的去年同比时间和环比(就是上一个时间段,周的2020-03环比就是2020-04)时间, 3、同时还要获取周和月的开始时间和结束时间,比如周2020-02的开始是2020-01-06,结束时间是2020-01-12, 4、周...
  • 对于ElementUI中使用ECharts在上一篇文章中已经说明具体使用方法 一、效果图:(以每一个月的工业产值为例) 我们可以选择不同企业和不同的年份生成同比环比图 1、环比图 2、同比图 二、从Echart官网中导入我们需要...
  • oracle 同比增长率计算

    千次阅读 2020-03-19 11:01:49
    同比计算公式:同比增长率=(本年-上年)/上年*100% 例如计算某个东西的绝对量的年度同比增长率 NVL (ROUND (NORM_VLAUE / LAG (NORM_VLAUE) OVER (ORDER BY NORM_NAME, "YEAR") * 100,2) - 100,'0') 计算公式: ...
  • sql语句下的同比,环比操作

    万次阅读 多人点赞 2020-07-01 00:44:26
    sql语句下的同比,环比操作 题目:有两个表 表一:销售明细 表二:产品明细 需求:查询2020年,每月的销售额,以及同比环比,并按照年月进行升序排序 建数据库 建表 插入数据 偷个懒,这些语句就先省略了 给看个截图...
  • Mysql计算同比环比(超详细)

    千次阅读 热门讨论 2020-09-11 20:52:17
    产品ID GROUP BY mm1,yy1) a # 同比月份相同,年份减1 on month(c.销售时间) = a.mm1 and a.yy1 = year(c.销售时间)-1 # 今年销售额 left join (select month(a.销售时间) mm2, year(a.销售时间) yy2, sum(a.销售...
  • 同比与环比的区别

    万次阅读 2019-03-31 23:42:40
    区别: 1、同比和环比,这两者所反映的虽然都是变化速度,但由于采用基期的不同...2、同比:与历史同时期比较,就是与不同年份的同一时期作比较,例如2005年7月份与2004年7月份相比,叫同比。如图2012年5月与2013年...
  • 窗口函数:计算环比和同比

    千次阅读 2019-03-27 12:45:47
    0 前言 环比,表示连续2个单位周期(比如连续两月)内的量的... 同比一般情况下是今年第n月与去年第n月比,用以说明本期发展水平与去年同期发展水平对比而达到的相对发展速度,计算公式:同比增长速度=(本期发展...
  • SQL实现占比、同比、环比指标分析

    千次阅读 多人点赞 2019-09-27 14:29:15
    三、同比 首先理解同比的概念,同比就是与历史同时期比较,就是与不同年份的同一时期作比较,例如2019年7月份与2018年7月份相比,叫同比。如图2012年5月与2013年5月对比就叫做同比。 月订单量同比 -- 求每个月订单...
  • 哈喽,亲爱的小伙伴们,我们好久不见哦!今天我们来分享一下如何制作可以看到当年销售额和上年销售额对比情况,同时还能看到当年目标额完成情况的图表。如下动态图所示,灰色部分为去年各月销售额,蓝色部分为19年各...
  • mysql计算同比环比

    2020-05-21 20:50:11
    sql 计算同比环比 – 同比 SELECT t1.now_year AS 年份, t1.now_month AS 月份, CASE WHEN now_month_num IS NULL OR now_month_num = 0 THEN 0 ELSE now_month_num END 本月销售量, CASE WHEN last_month_num IS ...
  • dax-自定义周做同比和环比

    千次阅读 2018-07-26 11:21:24
    中国式报表:公司要指定财务月做月度周期,然后还要切分周,要做同比和环比。查了一些老师的文章和资料,发觉都没有现在要求的这么复杂。于是只好用很笨的办法去写了公式,至少看起来是需求的样子了,后续有没有bug...
  • 使用分析函数:偏移量函数 偏移量函数:Lag(exp,N,defval) over(PARTITION BY region order by year) Lag函数为Lag(exp,N,defval),...region 表示参照序列,只对一个region的值取上一个值。 你写的时候需要按p...
  • 就可以获取一只股票的总资产同比增长率 与 净资产同比增长率了。   import baostock as bs import pandas as pd   # 登陆系统 lg = bs.login() # 显示登陆返回信息 print('login respond error_code:'+lg.error_...
  • CDA数据分析师 出品认识环比增长率很多企业比较注重自己的业务增长情况,时常会需要计算同比增长率和环比增长率。从上学的时候就有很多小伙伴搞不清楚这两个增长率之间的区别,这里简单直白的解释一下:同比增长率从...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 24,552
精华内容 9,820
关键字:

年份的同比