MySQL中怎么实现一个分析函数

发布时间:2021-07-13 16:40:20 作者:Leah
来源:亿速云 阅读:242

本篇文章为大家展示了MySQL中怎么实现一个分析函数,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。

1. 实现rownum

1

2

SET @rn:=0;

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

MySQL中怎么实现一个分析函数

或者写成:

1

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中分页语句的原始语句如下:

1

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

MySQL中怎么实现一个分析函数

1

2

3

4

5

6

7

8

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中怎么实现一个分析函数

Mysql中也是这么实现的:

1

2

3

4

5

6

7

8

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中怎么实现一个分析函数

2.2 row_number () 实现

1

2

select 

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

MySQL中怎么实现一个分析函数

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

1

2

3

4

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;

MySQL中怎么实现一个分析函数

1

2

3

4

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 

MySQL中怎么实现一个分析函数

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

在Oracle中实现:

1

2

3

4

SELECT E.*,

   TRUNC(SAL / SUM(SAL) OVER(PARTITION BY DEPTNO), 3) AS SALPERCENT

FROM EMP E

ORDER BY DEPTNO;

MySQL中怎么实现一个分析函数

1

2

3

4

5

6

7

8

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;

MySQL中怎么实现一个分析函数

2.4 求各个部门的总共工资

Oracle:

1

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

MySQL中怎么实现一个分析函数

MySQL: 

1

2

3

4

5

6

7

8

9

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;

子查询的功能实现如下: 

MySQL中怎么实现一个分析函数

下面是这个语句的结果

MySQL中怎么实现一个分析函数

2.5 拿部门第二的工资的人

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

1

2

3

4

5

SELECT *

FROM (SELECT E.*,

           ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESCAS RN

      FROM EMP E)

WHERE RN = 2;

MySQL中怎么实现一个分析函数

Mysql中第一这么实现:

在5.6版本,sql_mode非only_full_group_by的情况,我们可以使用如下方式实现

1

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

1

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。 

1

2

3

4

5

6

7

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;

MySQL中怎么实现一个分析函数

2.6 dense_rank()

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

1

2

3

4

5

6

7

SELECT empno,

ename,

sal,

deptno,

rank() OVER(PARTITION BY deptno ORDER BY sal descas rank,

dense_rank() OVER(PARTITION BY deptno ORDER BY sal descas dense_rank

FROM emp e;

MySQL中怎么实现一个分析函数

MySQL的写法:

1

2

3

4

5

6

7

8

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;

MySQL中怎么实现一个分析函数

2.7 连续获得冠军的有哪些

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

create table  nba as 

SELECT '公牛' AS TEAM, '1991' AS FROM DUAL UNION ALL

SELECT '公牛' AS TEAM, '1992' AS FROM DUAL UNION ALL

SELECT '公牛' AS TEAM, '1993' AS FROM DUAL UNION ALL

SELECT '活塞' AS TEAM, '1990' AS FROM DUAL UNION ALL

SELECT '火箭' AS TEAM, '1994' AS FROM DUAL UNION ALL

SELECT '火箭' AS TEAM, '1995' AS FROM DUAL UNION ALL

SELECT '公牛' AS TEAM, '1996' AS FROM DUAL UNION ALL

SELECT '公牛' AS TEAM, '1997' AS FROM DUAL UNION ALL

SELECT '公牛' AS TEAM, '1998' AS FROM DUAL UNION ALL

SELECT '马刺' AS TEAM, '1999' AS FROM DUAL UNION ALL

SELECT '湖人' AS TEAM, '2000' AS FROM DUAL UNION ALL

SELECT '湖人' AS TEAM, '2001' AS FROM DUAL UNION ALL

SELECT '湖人' AS TEAM, '2002' AS FROM DUAL UNION ALL

SELECT '马刺' AS TEAM, '2003' AS FROM DUAL UNION ALL

SELECT '活塞' AS TEAM, '2004' AS FROM DUAL UNION ALL

SELECT '马刺' AS TEAM, '2005' AS FROM DUAL UNION ALL

SELECT '热火' AS TEAM, '2006' AS FROM DUAL UNION ALL

SELECT '马刺' AS TEAM, '2007' AS FROM DUAL UNION ALL

SELECT '凯尔特人' AS TEAM, '2008' AS FROM DUAL UNION ALL

SELECT '湖人' AS TEAM, '2009' AS FROM DUAL UNION ALL

SELECT '湖人' AS TEAM, '2010' AS FROM DUAL;

Oracle实现:

1

2

3

4

5

6

7

8

9

10

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实现: 

1

2

3

4

5

6

7

8

9

10

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

MySQL中怎么实现一个分析函数

| UDF插件

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

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

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

}

生成动态链接库

1

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

MySQL中怎么实现一个分析函数

MySQL中怎么实现一个分析函数

上述内容就是MySQL中怎么实现一个分析函数,你们学到知识或技能了吗?如果还想学到更多技能或者丰富自己的知识储备,欢迎关注亿速云行业资讯频道。

推荐阅读:
  1. Oracle分析函数
  2. 分析函数

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:mysql中怎么配置半同步复制

下一篇:mysql 中怎么定时添加删除历史分区

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》