# MySQL分析函数实现

## | MySQL分析函数实现

1. 实现rownum

```SET @rn:=0;
SELECT @rn:=@rn+1 AS rownum ,e.* FROM emp e;```

`SELECT @rn:=@rn + 1 AS rownum ,e.* FROM emp e ,(SELECT @rn:=0) c`

2. 各种分析函数写法 (MySQL实现分析语句时可能遇到的各种计算问题)

2.1 sum() 实现

--SQL 执行顺序 ,FROM ,JOIN ,WHERE ,GROUP BY,HAVING ,ORDER BY ,SELECT,

`SELECT E.*, SUM(SAL) OVER(PARTITION BY DEPTNO) AS COUNTOVER FROM EMP E;`
```SELECT E.*,
(SELECT SUMOVER
FROM (SELECT DEPTNO, SUM(SAL) AS SUMOVER
FROM EMP E1
GROUP BY DEPTNO) X
WHERE X.DEPTNO = E.DEPTNO) AS COUNTOVER
FROM EMP E
ORDER BY DEPTNO;```

Mysql中也是这么实现的：

```SELECT E.*,
(SELECT SUMOVER
FROM (SELECT DEPTNO, SUM(SAL) AS SUMOVER
FROM emp E1
GROUP BY DEPTNO) X
WHERE X.DEPTNO = E.DEPTNO) AS COUNTOVER
FROM emp E
ORDER BY DEPTNO;```

2.2 row_number () 实现

```select
e.* ,row_number() over(partition by deptno order by empno) as ROW_NUMBER from emp e;```

```SELECT E.*,
IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS ROW_NUMBER,
@DEPTNO := DEPTNO AS VAR1
FROM EMP E, (SELECT @DEPTNO := '', @RN := 0) C
ORDER BY DEPTNO;```
```SELECT E.*,
IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS ROW_NUMBER,
@DEPTNO := DEPTNO AS VAR1
FROM EMP E, (SELECT @DEPTNO := '', @RN := 0) C
ORDER BY DEPTNO;```

2.3 求每个人员占他所在部门总工资的百分比

```SELECT E.*,
TRUNC(SAL / SUM(SAL) OVER(PARTITION BY DEPTNO), 3) AS SALPERCENT
FROM EMP E
ORDER BY DEPTNO;```
```SELECT E.*,
SAL / (SELECT SUMOVER
FROM (SELECT DEPTNO, SUM(SAL) AS SUMOVER
FROM emp E1
GROUP BY DEPTNO) X
WHERE X.DEPTNO = E.DEPTNO) AS SalPercent
FROM emp E
ORDER BY DEPTNO;```

2.4 求各个部门的总共工资

Oracle:

`SELECT e.* ,SUM(sal) OVER(PARTITION BY deptno) FROM emp e;`

MySQL:

```SELECT A.*,
ROUND(CAST(IF(@DEPTNO = DEPTNO, @MAX := @MAX, @MAX := SUMOVER) AS CHAR ),0) AS SUMOVER2,
@DEPTNO := DEPTNO AS VAR2
FROM (SELECT E.*,
IF(@DEPTNO = DEPTNO, @SUM := @SUM + SAL, @SUM := SAL) AS SUMOVER,
@DEPTNO := DEPTNO AS VAR1
FROM emp E, (SELECT @DEPTNO := '', @SUM := 0, @MAX := 0) C
ORDER BY DEPTNO) A
ORDER BY DEPTNO, SUMOVER DESC;```

2.5 拿部门第二的工资的人

```SELECT *
FROM (SELECT E.*,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RN
FROM EMP E)
WHERE RN = 2;```

Mysql中第一这么实现：

`set global sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';`
`SELECT * FROM (SELECT e.* FROM emp e ORDER BY deptno,sal ) a GROUP BY deptno;`

```SELECT *
FROM (SELECT E.*,
IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS RN,
@DEPTNO := DEPTNO
FROM EMP E, (SELECT @RN := 0, @DEPTNO := 0) C
ORDER BY DEPTNO, SAL DESC) X
WHERE X.RN = 2;```

2.6 dense_rank()

dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都一样。

```SELECT empno,
ename,
sal,
deptno,
rank() OVER(PARTITION BY deptno ORDER BY sal desc) as rank,
dense_rank() OVER(PARTITION BY deptno ORDER BY sal desc) as dense_rank
FROM emp e;```

MySQL的写法：

```select
empno,ename,sal,deptno,
if(@deptno = deptno,if(@sal=sal,@rn:=@rn,@rn3:=@rn3+1),@rn:=1) as "RANK() OVER",
if(@sal =sal,@rn2:=@rn2 ,if(@deptno = deptno,@rn2:=@rn2+1,@rn2:=1)) as "DENSE_RANK() OVER",
if(@deptno = deptno,@rn:=@rn+1,@rn:=1) as "ROW_NUMBER() OVER"
, @deptno:=deptno,@sal:=sal
from
(select empno,ename,sal,deptno from emp a ,(select @rn:=1,@deptno:=0,@rn2:=0,@rn3:=0,@sal:=0,@i:=0) b order by deptno,sal desc) c;```

2.7 连续获得冠军的有哪些

--请写出一条SQL语句，查询出在此期间连续获得冠军的有哪些，其连续的年份的起止时间是多少，结果如下：

```create table  nba as
SELECT '公牛' AS TEAM, '1991' AS Y FROM DUAL UNION ALL
SELECT '公牛' AS TEAM, '1992' AS Y FROM DUAL UNION ALL
SELECT '公牛' AS TEAM, '1993' AS Y FROM DUAL UNION ALL
SELECT '活塞' AS TEAM, '1990' AS Y FROM DUAL UNION ALL
SELECT '火箭' AS TEAM, '1994' AS Y FROM DUAL UNION ALL
SELECT '火箭' AS TEAM, '1995' AS Y FROM DUAL UNION ALL
SELECT '公牛' AS TEAM, '1996' AS Y FROM DUAL UNION ALL
SELECT '公牛' AS TEAM, '1997' AS Y FROM DUAL UNION ALL
SELECT '公牛' AS TEAM, '1998' AS Y FROM DUAL UNION ALL
SELECT '马刺' AS TEAM, '1999' AS Y FROM DUAL UNION ALL
SELECT '湖人' AS TEAM, '2000' AS Y FROM DUAL UNION ALL
SELECT '湖人' AS TEAM, '2001' AS Y FROM DUAL UNION ALL
SELECT '湖人' AS TEAM, '2002' AS Y FROM DUAL UNION ALL
SELECT '马刺' AS TEAM, '2003' AS Y FROM DUAL UNION ALL
SELECT '活塞' AS TEAM, '2004' AS Y FROM DUAL UNION ALL
SELECT '马刺' AS TEAM, '2005' AS Y FROM DUAL UNION ALL
SELECT '热火' AS TEAM, '2006' AS Y FROM DUAL UNION ALL
SELECT '马刺' AS TEAM, '2007' AS Y FROM DUAL UNION ALL
SELECT '凯尔特人' AS TEAM, '2008' AS Y FROM DUAL UNION ALL
SELECT '湖人' AS TEAM, '2009' AS Y FROM DUAL UNION ALL
SELECT '湖人' AS TEAM, '2010' AS Y FROM DUAL;```

Oracle实现：

```SELECT TEAM, MIN(Y), MAX(Y)
FROM (SELECT E.*,
ROWNUM,
ROW_NUMBER() OVER(PARTITION BY TEAM ORDER BY Y) AS RN,
ROWNUM - ROW_NUMBER() OVER(PARTITION BY TEAM ORDER BY Y) AS DIFF
FROM NBA E
ORDER BY Y)
GROUP BY TEAM, DIFF
HAVING MIN(Y) != MAX(Y)
ORDER BY 2;```

MySQL实现：

```SELECT TEAM, MIN(Y), MAX(Y)
FROM (SELECT TEAM,
Y,
IF(@TEAM = TEAM, @RN := @RN + 1, @RN := 1) AS RWN,
@RN1 := @RN1 + 1 AS RN,
@TEAM := TEAM
FROM nba N, (SELECT @RN := 0, @TEAM := '', @RN1 := '') C) A
GROUP BY RN - RWN
HAVING MIN(Y) != MAX(Y)
ORDER BY 2```

## | UDF插件

Userdefined Function，用户定义函数。我们知道，MySQL本身支持很多内建的函数，此外还可以通过创建存储方法来定义函数。UDF为用户提供了一种更高效的方式来创建函数。

UDF与普通函数类似，有参数，也有输出。分为两种类型：单次调用型和聚集函数。前者能够针对每一行数据进行处理，后者则用于处理Group By这样的情况。

UDF自定义函数，在MySQL basedir/include

```[root@test12c include]# pwd
/usr/local/mysql/include
[root@test12c include]# cat rownum.c
#include <my_global.h>
#include <my_sys.h>

#if defined(MYSQL_SERVER)
#include <m_string.h>        /* To get strmov() */
#else
/* when compiled as standalone */
#include <string.h>
#define strmov(a,b) stpcpy(a,b)
#endif

#include <mysql.h>
#include <ctype.h>

/*
gcc -fPIC -Wall -I/usr/local/mysql/include -I. -shared rownum.c -o rownum.so
DROP FUNCTION IF EXISTS rownum;
CREATE FUNCTION rownum RETURNS INTEGER SONAME 'rownum.so';
*/

C_MODE_START;

my_bool rownum_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void rownum_deinit(UDF_INIT *initid);
chong rownum(UDF_INIT *initid, UDF_ARGS *args, char *is_null,char *error);

C_MODE_END;

/*
Simple example of how to get a sequences starting from the first argument
or 1 if no arguments have been given
*/

my_bool rownum_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
if (args->arg_count > 1)
{
strmov(message,"This function takes none or 1 argument");
return 1;
}
if (args->arg_count)
args->arg_type[0]= INT_RESULT;        /* Force argument to int */

if (!(initid->ptr=(char*) malloc(sizeof(chong))))
{
strmov(message,"Couldn't allocate memory");
return 1;
}
memset(initid->ptr, 0, sizeof(chong));
initid->const_item=0;
return 0;
}

void rownum_deinit(UDF_INIT *initid)
{
if (initid->ptr)
free(initid->ptr);
}

chong rownum(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,char *is_null __attribute__((unused)),char *error __attribute__((unused)))
{
uchong val=0;
if (args->arg_count)
val= *((chong*) args->args[0]);
return ++*((chong*) initid->ptr) + val;
}```

`gcc rownum.c -fPIC -shared -o ../lib/plugin/rownum.so`

## | 作者简介

0 条评论

• ### 给二三线城市的技术爱好者的几点建议

最近有很多朋友问我一些学习上的想法，最开始我是本着高大上的思维来考虑的，但是经过了解发现有不少的朋友有此疑问，而且很多是来自于二三线城市。突然我意识到我...

• ### 每周分享第 24 期

以前的 3D 打印，一般都使用塑料。今年，3D 金属打印机问世了，可以用金属打印零件，生成更轻、更坚固、更复杂的形状，而且成本更低、速度更快。这为复杂的金属模具...

• ### 怎么才算一个合格的程序员？【大牛经验】

产品经理经常改需求这是程序员最头疼的事情，作为程序员应该也站在PM的角度思考，帮助PM分析出本质的需求，这也许可以减少需求的变更。当然，前提是得干一行爱一行，...

• ### 微服务实战(六)：落地微服务架构到直销系统(事件存储)

在CQRS架构中，一个比较重要的内容就是当命令处理器从命令队列中接收到相关的命令数据后，通过调用领域对象逻辑，然后将当前事件的对象数据持久化到事件存储中。主要的...

• ### 使用 Ray 用 15 行 Python 代码实现一个参数服务器

参数服务器是很多机器学习应用的核心部分。其核心作用是存放机器学习模型的参数（如，神经网络的权重）和提供服务将参数传给客户端（客户端通常是处理数据和计算参数更新的...

• ### 区块链 价值互联网的基石

这是我很久之前看的一本书，对区块链的概念解释简单易懂，适合入门， 好久没有写区块链的开发，所以现在重拾起。这本书也推荐给想要入门的朋友。

• ### 局域网中连接windows环境下的oracle数据库

将该TNS信息配置到同事本地的tnsnames.ora文件，使用pl/sql developer无法连接，报错TNS-12535: TNS操作超时1。

• ### MySQL命令，一篇文章替你全部搞定

MySQL的基本操作可以包括两个方面：MySQL常用语句如高频率使用的增删改查（CRUD）语句和MySQL高级功能，如存储过程，触发器，事务处理等。而这两个方面...

• ### 使用预先训练好的单词向量识别影评的正负能量

上一节我们讨论路单词向量化的算法原理。算法的实现需要有大量的数据，一般而言你要收集到单词量在四十亿左右的文本数据才能通过上一节的算法训练处精准的单词向量，问题在...

• ### 运维建设的方向和思路

今天和同事聊需求的时候，突然发现目前我们在做的一些系统，其实他感觉有些迷茫，主要就是一个建设的思路和方向这一块，我想了下，也确实，目前来看，其实系统的功能初期避...