oracle同比和环比

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)的详解
2020-09-10 20:59:37

本篇文章是对oracle求同比,环比函数(LAG与LEAD)进行了详细的分析介绍，需要的朋友参考下

收起
。oracle 同期同比环比
2021-04-30 11:18:41

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;
*/

收起

展开全文
ORACLE 计算某个变量的同比环比
2021-02-09 15:38:52

计算同比、环比是数据分析工作中常见的工作，虽然理论简单，但是sql计算还是有点繁琐，先把我提炼出来的模板总结如下，方便日后直接使用。 select b1.唯一标识, b1.目标年月, b1.变量, round(b1.变量 - b3.变量, ...

收起

数据挖掘
基于Oracle的同比环比数据统计分析研究.pdf
2021-10-09 23:15:26

基于Oracle的同比环比数据统计分析研究.pdf

收起
oracle 中sql实现同比环比
2022-02-24 10:19:01

sum和case when的结合使用废话不多说，我们直接进行测试。 1.学生表数据 2.将学生按照性别区分我们一般的写法就是使用group by进行分组。 select t.ssex, count(t.sname) from STUDENT t group by t.ssex;...

收起
【Oracle SQL】计算同比与环比（列转行进行偏移）
2022-07-20 10:30:51

关于oracle计算同比环比。还要考虑是不是为0或者为空，为0为null做除法更麻烦了。

收起

sql
Oracle分析函数（同比，环比，累计，基比）
2021-05-08 17:26:09

AS PREV_SAL -- 环比分析，与上个月份进行比较 ,LAG(SALARY, 12, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_12_SAL -- 同比分析，与上个年度相同月份进行比较 ,SUM(SALARY) OVER(PARTITION BY ...

收起
oracle中sum和case when的结合使用（求同比和环比）
2021-05-01 04:01:52

sum和case when的结合使用废话不多说，我们直接进行测试。1.学生表数据 2.将学生按照性别区分我们一般的写法就是使用group by进行分组。select t.ssex, count(t.sname) from STUDENT t group by t.ssex;如果我们需要...

收起
用分析函数计算环比、同比oracle
2021-05-01 00:33:35

1.普及一下概念环比 = 2018年10月/2018年09月(同一时期内不同时间段的比较)同比 = 2018年10月/2017年10月(不同时期内相同时间段的比较)环比增长率 =(2018年10月-2018年09月)/2018年09月同比增长率 =(2018年10月-2017...

收起
Oracle_管道函数求同比和环比（非连续时段）_o011
2020-05-13 12:57:54

Oracle_管道函数求同比和环比（非连续时段）数据准备 --测试数据准备 CREATE TABLE salaryByMonth( employeeNo varchar2(20), yearMonth varchar2(6), salary number ) ; insert into SALARYBYMONTH ...

收起
利用Oracle分析函数LAG求时间段内的本期、同比、环比值
2021-05-02 01:43:57

可以利用此分析函数来秒求环比和同比的值，但这其中也有些问题。LAG分析函数语法结构如下： LAG(EXPRESSION,[OFFSET],[DEFAULT]) OVER([PATITION BY COLUMN1...] ORDER BY COLUMN1...)；其中offset是偏移量，指的是...

收起
新手求教oracle同比和环比sql语句
2013-08-02 13:19:37

数据表 create table d_month_salary( id number(2) primary key, salaryMonth varchar2(10), salary number(10)); 查询数据如下： [img=...请高手们指点指点，感激不尽撒...

收起
oracle求同比，环比函数 LAG和LEAD
2021-05-07 08:43:57

AS PREV_SAL -- 环比分析，与上个月份进行比较 ,LAG(SALARY, 12, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_12_SAL -- 同比分析，与上个年度相同月份进行比较 ,SUM(SALARY) OVER(PARTITION BY...

收起
oracle 拼同比环比查询sql
2020-12-21 10:54:36

/// ///生成计算同比环比查询语句///table:表名称；statColumns：要统计的值字段;yearColumn:年份字段名；monthColumn：月份字段名；joinColumns:除年月外的连接条件///--上期无值或0本期有值不为0：1///--上期有值...

收起
oracle 同比和环比 怎么写
2012-05-25 16:28:27

-- 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) ...

收起

insert table
【SQL】用SQL语句表示同比和环比
2022-07-18 17:03:20

用SQL语句表示同比和环比，并对其中涉及到的部分SQL函数作出解释。

收起

sql

oracle求同比,环比函数(LAG与LEAD)的详解

。oracle 同期同比环比

ORACLE计算同比环比

同比环比概念和公式

需求:

数据库表和数据准备

oracle 需要用到的函数简绍:

思路:

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

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

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

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

Oracle 同比环比

ORACLE 计算某个变量的同比环比

基于Oracle的同比环比数据统计分析研究.pdf

oracle 中sql实现同比环比

【Oracle SQL】计算同比与环比（列转行进行偏移）

Oracle分析函数（同比，环比，累计，基比）

oracle中sum和case when的结合使用（求同比和环比）

用分析函数计算环比、同比oracle

Oracle_管道函数求同比和环比（非连续时段）_o011

利用Oracle分析函数LAG求时间段内的本期、同比、环比值

新手求教oracle同比和环比sql语句

oracle求同比，环比函数 LAG和LEAD

oracle 拼同比环比查询sql