用mysql实现oracle的分析函数

最近数据中心环境切换，源数据保存在oracle11g里，新环境要mysql5.7， 由于很多的sql语句用到了oracle的分析函数，而mysql对此暂无很好的支持，所以做了以下的转化，作为标记，有错误的地方，希望大家指正。

一。准备工作

1.数据表准备

oracle的scott用户下，有emp表，我这里基于它进行验证

（1）mysql建表语句

CREATE TABLE `emp` (
`empno` DECIMAL(4,0) NOT NULL,
`ename` VARCHAR(10) NULL DEFAULT NULL,
`job` VARCHAR(9) NULL DEFAULT NULL,
`mgr` DECIMAL(4,0) NULL DEFAULT NULL,
`hiredate` DATE NULL DEFAULT NULL,
`sal` DECIMAL(7,2) NULL DEFAULT NULL,
`comm` DECIMAL(7,2) NULL DEFAULT NULL,
`deptno` DECIMAL(2,0) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

（2）数据准备

INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, 0.00, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, 0.00, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, 0.00, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, 0.00, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, 0.00, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', 0, '1981-11-17', 5000.00, 0.00, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, 0.00, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, 0.00, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, 0.00, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, 0.00, 10);

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

1.oracle  row_number over-》mysql自定义变量
（1） oracle

select deptno,sal,row_number() over(partition by deptno order by sal) rownumber from scott.emp

（2） mysql
select 
tmp.deptno,
tmp.sal,
IF (@deptno = tmp.deptno,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownumber,
@deptno := tmp.deptno as dtno
FROM
(
SELECT deptno,sal FROM emp
ORDER BY deptno,sal
) tmp,

(SELECT @deptno := NULL,@rownumber:= 0) a

-------------------------------------------------------------------
2.oracle first_value ->mysql自定义变量
(1)oracle
SELECT DEPTNO,
       JOB,
       SAL,
       FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) firstvalue

  FROM scott.EMP

(2)mysql
select a.DEPTNO,a.JOB,a.SAL,m.sal
from emp a left outer join 
(
SELECT DEPTNO,JOB,SAL
from 
(
SELECT DEPTNO,
       JOB,
       SAL,
       @num := if(@DEPTNO = DEPTNO, @num + 1, 1) as row_number,
@DEPTNO := DEPTNO as dummy
  FROM emp
  order by DEPTNO,SAL
  ) x
  where x.row_number=1
  order by DEPTNO,SAL
  ) m

  on a.deptno = m.deptno

-------------------------------------------------------------------
3.ratio_to_report的oracle-》mysql
(1)oracle
SELECT  
empno,ename,sal,deptno,  
   ratio_to_report(sal) over () as pct1l,    
     ratio_to_report(sal) over (partition by deptno) as pct2  

    FROM scott.emp; 

(2)mysql
select a.DEPTNO,a.JOB,a.SAL,a.sal/m.s
from emp a left outer join 
(
select sal,deptno,sum(sal) as s
from emp 
group by DEPTNO
) m
  on a.deptno = m.deptno

  order by a.DEPTNO

-------------------------------------------------------------------
4.max,min,avg,count over -> mysql
(1)oracle
SELECT  deptno,
     max(sal) over (partition by deptno) as maxsal,
     min(sal) over (partition by deptno) as minsal,
     avg(sal) over (partition by deptno) as avgsal,
      count(sal) over (partition by deptno) as maxsal  

    FROM scott.emp; 

(2)mysql
SELECT a.deptno,m.maxsal,m.minsal,m.avgsal,m.countsal
FROM emp a
LEFT OUTER
JOIN (
SELECT deptno,MAX(sal) maxsal,MIN(sal) minsal,AVG(sal) avgsal,COUNT(sal) countsal
FROM emp
GROUP BY deptno
) m ON a.deptno = m.deptno

order by a.deptno

5.用row_number实现oracle的lag函数

（1）oracle

select deptno,sal,lag(sal,1) over(partition by deptno order by sal) from scott.emp

（2）mysql

select main.deptno,main.rownumber,main.sal,ano.sal as lagsal,ano.rownumber
from
(
select
tmp.deptno,
tmp.sal,
IF (@deptno = tmp.deptno,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownumber,
@deptno := tmp.deptno as dtno
FROM
(
SELECT deptno,sal FROM emp
ORDER BY deptno,sal
) tmp,
(SELECT @deptno := NULL,@rownumber:= 0) a
) main left join 
(
select
tmp.deptno,
tmp.sal,
IF (@deptno = tmp.deptno,@rownumber:=@rownumber+ 1,@rownumber:= 1) AS rownumber,
@deptno := tmp.deptno as dtno
FROM
(
SELECT deptno,sal FROM emp
ORDER BY deptno,sal
) tmp,
(SELECT @deptno := NULL,@rownumber:= 0) a
) ano
on main.deptno=ano.deptno and ano.rownumber=main.rownumber-1

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