精华内容
下载资源
问答
  • oracle同比和环比
    2021-03-23 14:58:16

    SELECT curmonth MONTH,
           ((curmonth_sum - lastyear_sum) / lastyear_sum * 100) identical,
           ((curmonth_sum - lastmonth_sum) / lastmonth_sum * 100) annulus,
      FROM (select t.SALARYMONTH as curmonth
          ,t.SALARY      as curmonth_sum
          ,LAG(t.SALARY, 12, NULL) OVER (ORDER BY t.SALARYMONTH) AS lastyear_sum
          ,LAG(t.SALARY, 1, NULL) OVER (ORDER BY t.SALARYMONTH)  AS lastmonth_sum
    from (select SALARYMONTH  as SALARYMONTH
        ,INDEXNAME    as INDEXNAME
        ,sum(SALARY)  as SALARY
          from d_month_salary 
          group by SALARYMONTH,
           INDEXNAME ) t
    where t.INDEXNAME = 'id' )

    更多相关内容
  • 本篇文章是对oracle同比,环比函数(LAG与LEAD)进行了详细的分析介绍,需要的朋友参考下
  • oracle 同期 同比 环比有现成sqlserver的存储过程来实现,但是贴到oracle里面完全出错了,oracle里面如何写?这是sql的:DECLARE@var_sql1VARCHAR(max),@var_sql2VARCHAR(max),@var_sql3VARCHAR(max),@var_sql4...

    求助 。。。oracle 同期 同比 环比

    有现成  sql server 的存储过程来实现,但是贴到oracle里面完全出错了,

    oracle 里面 如何写 ?

    这是 sql的:

    DECLARE @var_sql1 VARCHAR(max),@var_sql2 VARCHAR(max),@var_sql3 VARCHAR(max),@var_sql4 VARCHAR(max),@var_group VARCHAR(200) ,

    @var_parm1 VARCHAR(max),@var_parm2 VARCHAR(max),

    @var_parm3 VARCHAR(max),@var_group1 VARCHAR(200),@date_lyearbegin DATETIME,

    @date_lyearend DATETIME,@date_lmonthbegin DATETIME,@date_lmonthend DATETIME

    --参数解析

    --同期的时间

    SET @date_lyearbegin = DATEADD(year,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+20,10) AS DATETIME))

    SET @date_lyearend = DATEADD(year,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+20,10) AS DATETIME))

    --环比的时间

    SET @date_lmonthbegin = DATEADD(month,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+20,10) AS DATETIME))

    SET @date_lmonthend = DATEADD(month,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+20,10) AS DATETIME))

    --同期的参数

    SET @var_parm2 =SUBSTRING(@var_parm,0,charindex('a.dsate_out,120) >= ',@var_parm)+20) +convert(varchar(10),@date_lyearbegin,120)+''' '

    + SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+32,LEN(' and convert(varchar(10),a.dsate_out,120) <= ')+1) +convert(varchar(10),@date_lyearend,120) + ''' ' + SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+32,999)

    --环比的参数

    SET @var_parm3 =SUBSTRING(@var_parm,0,charindex('a.dsate_out,120) >= ',@var_parm)+20) +convert(varchar(10),@date_lmonthbegin,120) +''' '

    + SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+32,LEN(' and convert(varchar(10),a.dsate_out,120) <= ')+1) +convert(varchar(10),@date_lmonthend,120) + ''' ' + SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+32,999)

    ------解决方案--------------------

    通过当前lag() over()函数 来实现当前值与一值的比较

    同期lag() over(order by item)

    环比lag() over(partition by 分组字段 order by item)

    ------解决方案--------------------

    写一个简单的例子  大致就这样了

    with t1 as

    (

    select '001' item,2 qty,date'2012-01-01' dt from dual union all

    select '002' item,2 qty,date'2012-02-01' dt from dual union all

    select '003' item,3 qty,date'2012-03-01' dt from dual union all

    select '001' item,5 qty,date'2013-01-01' dt from dual union all

    select '002' item,4 qty,date'2013-02-01' dt from dual union all

    select '003' item,6 qty,date'2013-03-01' dt from dual union all

    select '001' item,1 qty,date'2014-01-01' dt from dual

    )

    select item,qty,

    lag(qty) over(order by dt) s_qty,

    lag(qty) over(partition by item,to_char(dt,'mm') order by to_char(dt,'yyyy')) h_qty

    from t1

    item    qty  s_qty  h_qty

    ----------------------------------------

    10012

    200222

    300332

    4001532

    5002452

    6003643

    7001165

    展开全文
  • ORACLE计算同比环比

    千次阅读 多人点赞 2020-07-06 14:19:18
    同比环比概念公式 同比: 同比一般情况下是今年第n月与去年第n月比 环比: 表示连续2个统计周期(比如连续两月)内的量的变化比。 同比增长率=(本期数-同期数)/|同期数|×100% 环比增长率=(本期数-上期数)/上期...

    同比环比概念和公式

    同比: 同比一般情况下是今年第n月与去年第n月比

    环比: 表示连续2个统计周期(比如连续两月)内的量的变化比。

    同比增长率=(本期数-同期数)/|同期数|×100%

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

    需求:

    计算2020年01月到2020年2月, 每个月的本期值和环比和同比率

    数据库 表和数据准备

    CREATE TABLE test (
      "ID" NUMBER NOT NULL ,
      "VALUE" NUMBER ,
      "YEARMONTH" VARCHAR2(6 CHAR) ,
      CONSTRAINT "SYS_C0086930" PRIMARY KEY ("ID")
    )
    
    
    INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('1', '100', '202002');
    INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('2', '80', '202001');
    INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('3', '120', '201912');
    INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('4', '142', '201911');
    INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('5', '70', '201910');
    INSERT INTO "test"("ID", "VALUE", "YEARMONTH") VALUES ('6', '51', '201901');
    
    

    oracle 需要用到的函数简绍:

    NVL(expr1, expr2)

    expr1为空时, 给默认值expr2
    

    TO_CHAR(number,‘格式’)

      格式化函数 如:FM99990.00 
    	9代表此位为0或没有值时,不显示. 0代表此位为0或者没有值时补0. 
        小数点前是99990五位,代表数字的整数位只能有5位,小数位00表示,保留2位小数
    

    ROUND(number[,decimals])

    number 待做截取处理的数值
    decimals 指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分,并四舍五入。
    如果为负数则表示从小数点开始左边的位数,相应整数数字用0填充,小数被去掉. 
    对截取的数字要四舍五入。
    

    DECODE(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

    该函数的含义如下:
    
    
    IF 条件=值1 THEN
        RETURN(返回值1)
    ELSIF 条件=值2 THEN
        RETURN(返回值2)
        ......
    ELSIF 条件=值n THEN
        RETURN(返回值n)
    ELSE
        RETURN(缺省值)
    END IF
    

    计算同比和环比最重要的函数

    LAG(exp_str,offset,default) OVER(partition by expr2 order by expr3)

    LEAD(exp_str,offset,default) OVER(partition by expr2 order by expr3)

    exp_str 要做对比的字段
    offset 偏移量
    default 默认值
    
    partition by expr2 根据expr2分组
    order by expr3 根据expr3排序
    
    LAG函数: 向上取offset行的数据中exp_str字段,如果没有取default
    LEAD函数: 向下取offset行的数据种exp_str字段,如果没有取default
    OVER函数: 先对数据先进行分组和排序. 如: 这次测试,我们用日期进行排序,保证了LAG函数向上取offset行,能取到
    对应的环比和同比的日期的值
    
    
    

    再简绍一下,如何获取一个时间段内连续的日期

    这样保证了如果数据库中有些日期没有数据时,可以给一个默认值,使LAG和LEAD函数可以获取到正确的行数

    SELECT AA.monthlist, NVL(BB.CAP,0) CAP FROM -- 获取需要查询的时间范围内 连续月份的 数据值
    (SELECT
      TO_CHAR( ADD_MONTHS( TO_DATE( '201901', 'yyyyMM' ), ROWNUM - 1 ), 'yyyyMM' ) AS monthlist 
    FROM
      DUAL CONNECT BY ROWNUM <= months_between(
        to_date( '202001', 'yyyyMM' ),
        to_date( '201901', 'yyyyMM' )) + 1
    

    思路:

    第一步: 将需要的业务数据计算出来. 也就是将我们数据库中有的数据,按照日期分组.

    注意: 在这里我们从业务数据表中获取数据的时间范围,并不是我们需要求的2020年01月到2020年2月的数据,我们想要计算得到同比值那就必须得到最早开始时间(2020年1月)向前推12月的日期,即2019年1月. 这样我们才能

    同比增长率=(本期数2020年1月 - 同期数2019年1月 )/ 同期数2019年1月 ×100% .

    环比值所需要的数据(2019年12月)已经在范围内

    SELECT YEARMONTH, NVL(SUM(VALUE), 0) VALUE FROM  "test" 
    WHERE YEARMONTH >=  201901 AND YEARMONTH <= 202002 
    GROUP BY YEARMONTH ORDER BY YEARMONTH
    

    结果:

    YEARMONTH	VALUE
    201901		 51
    201910		 70
    201911		 142
    201912		 120
    202001		 80
    202002		 100
    

    第二步: 用我们上面简绍的获取连续月份的sql和上一步我们计算出来的业务数据连表, 并与连续月份的临时表为主表.

    ​ 这样我们就可以得到,我们需要的时间范围只能的所有日期和它的值

    ​ 注意: ①获取连续日期的sql的时间范围和上一步的相同,都必须包含到同比值

    SELECT AA.YEARMONTH, NVL(BB.VALUE, 0) VALUE FROM 
    	(SELECT
    	TO_CHAR( ADD_MONTHS( TO_DATE( '201901', 'yyyyMM' ), ROWNUM - 1 ), 'yyyyMM' ) AS YEARMONTH 
    	FROM DUAL 
    	CONNECT BY ROWNUM <= months_between( to_date( '202002', 'yyyyMM' ), to_date( '201901', 'yyyyMM' )) + 1) AA
    
    LEFT JOIN
    	(
    		SELECT YEARMONTH, NVL(SUM(VALUE), 0) VALUE FROM  "test" 
    		WHERE YEARMONTH >=  201901 AND YEARMONTH <= 202002 
    		GROUP BY YEARMONTH ORDER BY YEARMONTH
    	) BB 
    ON AA.YEARMONTH = BB.YEARMONTH	ORDER BY  AA.YEARMONTH
    

    结果:

    YEARMONTH	VALUE
    201901		51
    201902		0
    201903		0
    201904		0
    201905		0
    201906		0
    201907		0
    201908		0
    201909		0
    201910		70
    201911		142
    201912		120
    202001		80
    202002		100
    

    第三步: 使用LAG或LEAD函数,使现值,环比值(month-on-month)和同比值(year-on-year)成为一行数据.

    注意: 我们这里使用的LAG**(exp_str,offset,default)**函数, 它向上取offset行的值exp_str,如果没有就是default.

    SELECT
    CC.YEARMONTH,
    CC.VALUE,
    LAG ( CC.VALUE, 1, 0 ) OVER (  ORDER BY CC.YEARMONTH ) AS monthOnMonth,
    LAG ( CC.VALUE, 12, 0 ) OVER (  ORDER BY CC.YEARMONTH ) AS yearOnYear
    FROM (
    	SELECT AA.YEARMONTH, NVL(BB.VALUE, 0) VALUE FROM 
    		(SELECT
    		TO_CHAR( ADD_MONTHS( TO_DATE( '201901', 'yyyyMM' ), ROWNUM - 1 ), 'yyyyMM' ) AS YEARMONTH 
    		FROM DUAL 
    		CONNECT BY ROWNUM <= months_between( to_date( '202002', 'yyyyMM' ), to_date( '201901', 'yyyyMM' )) + 1) AA
    
    	LEFT JOIN
    		(
    			SELECT YEARMONTH, NVL(SUM(VALUE), 0) VALUE FROM  "test" 
    			WHERE YEARMONTH >=  201901 AND YEARMONTH <= 202002 
    			GROUP BY YEARMONTH ORDER BY YEARMONTH
    		) BB 
    	ON AA.YEARMONTH = BB.YEARMONTH	ORDER BY  AA.YEARMONTH
    
    ) CC
    

    结果: 我们可以观察结果,发现月份都获取到了正确的环比和同比值

    YEARMONTH	VALUE	MONTHONMONTH	YEARONYEAR
    201901		 51			 0				0
    201902		 0			 51				0
    201903		 0			 0				0
    201904		 0			 0				0
    201905		 0			 0				0
    201906		 0			 0				0
    201907		 0			 0				0
    201908		 0			 0				0
    201909		 0			 0				0
    201910   	 70	         0				0
    201911		 142	     70				0
    201912		 120		 142			0
    202001		80			 120			51
    202002		100			 80				0
    

    第四步: 计算出环比和同比的增长率

    按照公式

    同比增长率=(本期数-同期数)/|同期数|×100%

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

    注意: ① 我们在上一步中可以看到,我们的环比和同比值有可能为0, 但是根据公式,环比和同比值需要做被除数,而被除数又不能等于0,所以我们需要使用DECODE函数,如何值为0,环比和同比的增长率就为0

    ​ ② 在整个sql的最后面我们要加一个时间范围条件. 因为我们之前计算的都是根据最大日期范围计算的. 但是我们所需要的仅仅是 2020年1月到2020年2月的数据,所以加上时间范围条件.只获取我们所需的数据.

    SELECT 
    DD.YEARMONTH,
    DD.VALUE,
    TO_CHAR(NVL(ROUND(DECODE(DD.monthOnMonth,0,100,(DD.VALUE - DD.monthOnMonth )/DD.monthOnMonth * 100),2), 0), 'fm9999999990.00') || '%' 环比增长率,
    TO_CHAR(NVL(ROUND(DECODE(DD.yearOnYear,0,100,(DD.VALUE - DD.yearOnYear)/DD.yearOnYear * 100),2), 0),'fm9999999990.00')  || '%' 同比增长率
    FROM (
    	SELECT
    	CC.YEARMONTH,
    	CC.VALUE,
    	LAG ( CC.VALUE, 1, 0 ) OVER (  ORDER BY CC.YEARMONTH ) AS monthOnMonth,
    	LAG ( CC.VALUE, 12, 0 ) OVER (  ORDER BY CC.YEARMONTH ) AS yearOnYear
    	FROM (
    		SELECT AA.YEARMONTH, NVL(BB.VALUE, 0) VALUE FROM 
    			(SELECT
    			TO_CHAR( ADD_MONTHS( TO_DATE( '201901', 'yyyyMM' ), ROWNUM - 1 ), 'yyyyMM' ) AS YEARMONTH 
    			FROM DUAL 
    			CONNECT BY ROWNUM <= months_between( to_date( '202002', 'yyyyMM' ), to_date( '201901', 'yyyyMM' )) + 1) AA
    
    		LEFT JOIN
    			(
    				SELECT YEARMONTH, NVL(SUM(VALUE), 0) VALUE FROM  "test" 
    				WHERE YEARMONTH >=  201901 AND YEARMONTH <= 202002 
    				GROUP BY YEARMONTH ORDER BY YEARMONTH
    			) BB 
    		ON AA.YEARMONTH = BB.YEARMONTH	ORDER BY  AA.YEARMONTH
    
    	) CC
    ) DD WHERE DD.YEARMONTH >= 202001 AND DD.YEARMONTH <=202002
    
    

    👆 就是最终的推导出的sql,该sql还可以优化和简化, sql优化和简化不是本篇的重点,如有需要请根据实际情况处理.

    展开全文
  • Oracle 同比环比

    2022-05-17 20:58:20
    oracle 同比环比增长

    环比:与上个统计周期相比的变化比。 例如,2021年1月份与2020年12月份相比较称其为环比。
    同比:与去年同时期相比的变化比。 例如,2021年1月份与2020年1月份相比较称其为同比。

    同比增长率=(本期数-同期数)/同期数100% 指和去年同期相比较的增长率。
    环比增长率=(本期数-上期数)/上期数100% 反映本期比上期增长了多少。

    除:注意分母不能为零。避免会出错
    参考文章:
    https://www.oracletutorial.com/oracle-analytic-functions/oracle-lag/
    http://www.voycn.com/article/oracledelagheleadfenxihanshu
    http://www.xiayou.net/oracle/4616.html

    1:
    select lag(money, 1, 1) over(partition by t.dept order by t.month ) last_money ,
    round(money /
    (lag(money, 1, 1)
    over(partition by t.dept order by t.month )) * 100 - 100,
    0) last_money

    from t_table t
    2:
    /* select

    (select decode(a.last_month ,0,0,round( (a.money- a.last_month ) / a.last_month ,2)) hunabilv
    from (select id, money, lag(money , 1, 0) over(partition by b.dept order by b.month ) last_month from dept_table b ) a where a. id = t.id) last_month_aomunt_confirmed

    from t_table t;
    */

    展开全文
  • 计算同比环比是数据分析工作中常见的工作,虽然理论简单,但是sql计算还是有点繁琐,先把我提炼出来的模板总结如下,方便日后直接使用。 select b1.唯一标识, b1.目标年月, b1.变量, round(b1.变量 - b3.变量, ...
  • 基于Oracle同比环比数据统计分析研究.pdf
  • sumcase when的结合使用 废话不多说,我们直接进行测试。 1.学生表数据 2.将学生按照性别区分 我们一般的写法就是使用group by进行分组。 select t.ssex, count(t.sname) from STUDENT t group by t.ssex;...
  • 关于oracle计算同比环比。还要考虑是不是为0或者为空,为0为null做除法更麻烦了。
  • AS PREV_SAL -- 环比分析,与上个月份进行比较 ,LAG(SALARY, 12, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_12_SAL -- 同比分析,与上个年度相同月份进行比较 ,SUM(SALARY) OVER(PARTITION BY ...
  • sumcase when的结合使用废话不多说,我们直接进行测试。1.学生表数据 2.将学生按照性别区分我们一般的写法就是使用group by进行分组。select t.ssex, count(t.sname) from STUDENT t group by t.ssex;如果我们需要...
  • 1.普及一下概念环比 = 2018年10月/2018年09月(同一时期内不同时间段的比较)同比 = 2018年10月/2017年10月(不同时期内相同时间段的比较)环比增长率 =(2018年10月-2018年09月)/2018年09月同比增长率 =(2018年10月-2017...
  • Oracle_管道函数求同比和环比(非连续时段) 数据准备 --测试数据准备 CREATE TABLE salaryByMonth( employeeNo varchar2(20), yearMonth varchar2(6), salary number ) ; insert into SALARYBYMONTH ...
  • 可以利用此分析函数来秒求环比和同比的值,但这其中也有些问题。LAG分析函数语法结构如下: LAG(EXPRESSION,[OFFSET],[DEFAULT]) OVER([PATITION BY COLUMN1...] ORDER BY COLUMN1...); 其中offset是偏移量,指的是...
  • 数据表 create table d_month_salary( id number(2) primary key, salaryMonth varchar2(10), salary number(10)); 查询数据如下: [img=...请高手们指点指点,感激不尽撒...
  • AS PREV_SAL -- 环比分析,与上个月份进行比较 ,LAG(SALARY, 12, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_12_SAL -- 同比分析,与上个年度相同月份进行比较 ,SUM(SALARY) OVER(PARTITION BY...
  • /// ///生成计算同比环比查询语句///table:表名称;statColumns:要统计的值字段;yearColumn:年份字段名;monthColumn:月份字段名;joinColumns:除年月外的连接条件///--上期无值或0本期有值不为0:1///--上期有值...
  • -- Create table create table T_REGISTER_REPORT ( ID VARCHAR2(16) not null, CITY VARCHAR2(6), TOTAL NUMBER(10), ... BUSINESS_TYPE VARCHAR2(4), ...这些数据我要实现同比和环比 怎么写啊
  • Oracle同比环比和累计

    千次阅读 2012-05-19 14:01:55
     ,LAG(SALARY, 12, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_12_SAL -- 同比分析,与上个年度相同月份进行比较   ,SUM(SALARY) OVER(PARTITION BY EMPLOYEENO, SUBSTR(YEARMONTH, 1, 4) ...
  • 用SQL语句表示同比和环比,并对其中涉及到的部分SQL函数作出解释。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 685
精华内容 274
关键字:

oracle同比和环比