mysql数据分析函数 mysql分析函数有哪些

窗口函数是用与当前行有关的数据行参与计算。

窗口函数主要分为两大类：
排序类：row_number、rank、dense_rank等，percent_rank、cume_dist 等分布排序类

相对引用类：lag、lead、first_value、last_value、nth_value 等

除了这两类专用窗口函数之外，还有广义的聚合函数也可配套窗口函数使用：sum、avg、max、min等。

SELECT ( @i := @i + 1 ) num, t1.*
FROM emp t1,( SELECT @i := 0 ) t2

-- 查询出员工的姓名、工资、所在部门的平均工资，要求显示出来的是大于部门平均工资的员工。
-- 发一
SELECT e.empno, e.ename, e.sal, ROUND(d.avg_sal, 2) avg_sal
FROM emp e, (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) d
WHERE e.deptno = d.deptno AND e.sal > d.avg_sal
ORDER BY e.empno ASC;

-- 法二
SELECT t.empno, t.ename, t.sal, t.deptno, ROUND(t.avg_dept_sal,2) avg_sal
FROM (SELECT empno,
             ename,
             sal,
             deptno,
             AVG(sal) over(PARTITION BY deptno) avg_dept_sal
      FROM emp) t
WHERE t.sal > t.avg_dept_sal
ORDER BY t.empno ASC;

注：分析函数可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。
普通聚合函数用 group by 分组，每个分组返回一个统计值；
分析函数采用 partition by 分组，每组每行都可以返回一个统计值。
1、AVG是函数名
2、(sal)是分析函数的参数，每个函数有0~3个参数，参数可以是表达式，例：SUM(sal+comm)
3、over是一个关键字，用于标识分析函数，否则查询分析器不能区别avg()聚合函数和avg()分析函数 4、PARTITION BY deptno是可选的分区字句，如果不存在分区字句，则全部的结果集可看做一个单一的大分区。

分析函数之等级函数
查看emp员工表的工资数据，按降序排序：

SELECT empno, ename, sal 
FROM emp 
ORDER BY sal DESC;

-- Oracle中的分析函数
SELECT
	empno,
	ename,
	RANK() OVER ( ORDER BY sal DESC ) rank, -- 
	DENSE_RANK() OVER ( ORDER BY sal DESC ) dense_rank,
	ROW_NUMBER() OVER ( ORDER BY sal DESC ) row_number 
FROM
	emp

-- Oracle中的分析函数
SELECT
	id,
	SUM( id ) OVER ( ORDER BY id ) default_sum,
	SUM( id ) OVER ( ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) range_unbounded_sum,
	SUM( id ) OVER ( ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) rows_unbounded_sum,
	SUM( id ) OVER ( ORDER BY id RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING ) range_sum,
	SUM( id ) OVER ( ORDER BY id ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING ) rows_sum 
FROM
	t
-- range 是逻辑范围
-- rows 是物理范围

CREATE TABLE dept ( 
	deptno BIGINT ( 20 ) PRIMARY KEY auto_increment, 
	dname VARCHAR ( 14 ), 
	loc VARCHAR ( 13 ) 
);
INSERT INTO dept VALUES ( 10, 'accounting', 'new york' );
INSERT INTO dept VALUES ( 20, 'research', 'dallas' );
INSERT INTO dept VALUES ( 30, 'sales', 'chicago' );
INSERT INTO dept VALUES ( 40, 'operations', 'boston' );

CREATE TABLE emp (
	empno BIGINT ( 20 ) PRIMARY KEY auto_increment,
	ename VARCHAR ( 10 ),
	job VARCHAR ( 9 ),
	mgr INT,
	hiredate DATE,
	sal DECIMAL ( 10, 2 ),
	comm DECIMAL ( 10, 2 ),
	deptno BIGINT ( 20 ),
	CONSTRAINT fk_deptno FOREIGN KEY ( deptno ) REFERENCES dept ( deptno ) 
);
INSERT INTO emp VALUES ( 7369, 'smith', 'clerk', 7902, str_to_date( '1980-12-17 00:00:00', '%Y-%m-%d %H:%i:%s' ), 800, NULL, 20 );
INSERT INTO emp VALUES ( 7499, 'allen', 'salesman', 7698, str_to_date( '1981-02-20 00:00:00', '%Y-%m-%d %H:%i:%s' ), 1600, 300, 30 );
INSERT INTO emp VALUES ( 7521, 'ward', 'salesman', 7698, str_to_date( '1981-02-22 00:00:00', '%Y-%m-%d %H:%i:%s' ), 1250, 500, 30 );
INSERT INTO emp VALUES ( 7566, 'jones', 'manager', 7839, str_to_date( '1981-04-02 00:00:00', '%Y-%m-%d %H:%i:%s' ), 2975, NULL, 20 );
INSERT INTO emp VALUES ( 7654, 'martin', 'salesman', 7698, str_to_date( '1981-09-28 00:00:00', '%Y-%m-%d %H:%i:%s' ), 1250, 1400, 30 );
INSERT INTO emp VALUES ( 7698, 'blake', 'manager', 7839, str_to_date( '1981-05-01 00:00:00', '%Y-%m-%d %H:%i:%s' ), 2850, NULL, 30 );
INSERT INTO emp VALUES ( 7782, 'clark', 'manager', 7839, str_to_date( '1981-06-09 00:00:00', '%Y-%m-%d %H:%i:%s' ), 2450, NULL, 10 );
INSERT INTO emp VALUES ( 7788, 'scott', 'analyst', 7566, str_to_date( '1987-07-13 00:00:00', '%Y-%m-%d %H:%i:%s' ), 3000, NULL, 20 );
INSERT INTO emp VALUES ( 7839, 'king', 'president', NULL, str_to_date( '1981-11-17 00:00:00', '%Y-%m-%d %H:%i:%s' ), 5000, NULL, 10 );
INSERT INTO emp VALUES ( 7844, 'turner', 'salesman', 7698, str_to_date( '1981-09-08 00:00:00', '%Y-%m-%d %H:%i:%s' ), 1500, 0, 30 );
INSERT INTO emp VALUES ( 7876, 'adams', 'clerk', 7788, str_to_date( '1987-07-13 00:00:00', '%Y-%m-%d %H:%i:%s' ), 1100, NULL, 20 );
INSERT INTO emp VALUES ( 7900, 'james', 'clerk', 7698, str_to_date( '1981-12-03 00:00:00', '%Y-%m-%d %H:%i:%s' ), 950, NULL, 30 );
INSERT INTO emp VALUES ( 7902, 'ford', 'analyst', 7566, str_to_date( '1981-12-03 00:00:00', '%Y-%m-%d %H:%i:%s' ), 3000, NULL, 20 );
INSERT INTO emp VALUES ( 7934, 'miller', 'clerk', 7782, str_to_date( '1982-01-23 00:00:00', '%Y-%m-%d %H:%i:%s' ), 1300, NULL, 10 );

CREATE TABLE salgrade ( 
	grade INT, 
	losal INT, 
	hisal INT 
);
INSERT INTO salgrade VALUES ( 1, 700, 1200 );
INSERT INTO salgrade VALUES ( 2, 1201, 1400 );
INSERT INTO salgrade VALUES ( 3, 1401, 2000 );
INSERT INTO salgrade VALUES ( 4, 2001, 3000 );
INSERT INTO salgrade VALUES ( 5, 3001, 9999 );

CREATE TABLE bonus ( 
	ename VARCHAR ( 10 ), 
	job VARCHAR ( 9 ), 
	sal DECIMAL ( 10, 2 ), 
	comm DECIMAL ( 10, 2 ) 
);