MySQL分析函数实现

ゝ一世哀愁。 2022-05-14 18:13 435阅读 0赞

## **| MySQL分析函数实现** ##

还好MySQL8.0已经实现了与Oracle相同的分析函数。

### **1. 实现rownum** ###

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70][]

或者写成：

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,*

在Oracle中分页语句的原始语句如下：

SELECT E.*, SUM(SAL) OVER(PARTITION BY DEPTNO) AS COUNTOVER FROM EMP E;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 1][]

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;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 2][]

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;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 3][]

**2.2 row\_number () 实现**

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 4][]

我们的默认规则是在from后初始化变量。

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;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 5][]

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;

*这个语句首先执行order by*

*![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 6][]*

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

在Oracle中实现：

SELECT E.*,
       TRUNC(SAL / SUM(SAL) OVER(PARTITION BY DEPTNO), 3) AS SALPERCENT
    FROM EMP E
    ORDER BY DEPTNO;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 7][]

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;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 8][]

**2.4 求各个部门的总共工资**

Oracle:

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 9][]

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;

子查询的功能实现如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 10][]

下面是这个语句的结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 11][]

**2.5 拿部门第二的工资的人**

首先我们拿第二名的，用Oracle很好实现，不论是第一还是第二。

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 12][]

Mysql中第一这么实现：

在5.6版本，sql\_mode非only\_full\_group\_by的情况，我们可以使用如下方式实现

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;

在SQL\_MODE非only\_full\_group\_by时，MySQL中的group by是只取第一行的，下面我们看取第二行的SQL。

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;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 13][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 14][]

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;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 15][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 16][]

# #

## **| 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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 17][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 18][]

## **|  作者简介** ##

**姚崇·沃趣科技高级数据库技术专家**

熟悉Oracle、MySQL数据库内部机制，丰富的Oracle、MySQL故障诊断、性能调优、数据库备份恢复、复制、高可用方案及迁移经验。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70]: /images/20220503/7cb8d4ebe6f04ec599e30610fddd232c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 1]: /images/20220503/5040b437daf9403db2ce9bd580fc99d4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 2]: /images/20220503/86eb830b987347f9ae97257418456fd6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 3]: /images/20220503/db332d2b4a1143bca0cb59329be31047.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 4]: /images/20220503/8a430797c49a418588568dfecc1b1d2f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 5]: /images/20220503/31853da5f66245eabcaa668b2608aab1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 6]: /images/20220503/64b99ccb8b774173a099e89d78fc03c6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 7]: /images/20220503/8f06bc04dba34e719983b0727fa4cb94.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 8]: /images/20220503/1d52ce348a9d44f39303104176838af0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 9]: /images/20220503/ba5acf9d8f2e4974b214b8272b8cd721.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 10]: /images/20220503/dba09dc674204f88b39c2c5879dae8aa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 11]: /images/20220503/ffda3ba3eae44813a06cd4116991a8c9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 12]: /images/20220503/4b243aaae1b04648b98b7b4456cd16ac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 13]: /images/20220503/068783be50084f559cef290011fc0226.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 14]: /images/20220503/5b3cc4986d1e40d797518996e2978fd3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 15]: /images/20220503/05e9a9271122482ca53bac8d085fad3f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 16]: /images/20220503/7c8ce5de5d0f4ee198c46d56e48187e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 17]: /images/20220503/f73d5450f5b74585a2d5fe32edb1a9cd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_27_color_FFFFFF_t_70 18]: /images/20220503/55b8085ce1a94f5082b398d1f8175768.png