广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MySQL中怎么实现一个分析函数
  • 265

0
分享到

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

2024-04-02 19:04:59 265人浏览 薄情痞子
摘要

本篇文章为大家展示了Mysql中怎么实现一个分析函数，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。1. 实现rownum12SET @rn:=0;SELE

本篇文章为大家展示了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 DESC) AS 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 desc) as rank,

dense_rank() OVER(PARTITION BY deptno ORDER BY sal desc) as 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 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实现：

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>        

#else

#include <string.h>

#define strmov(a,b) stpcpy(a,b)

#endif

#include <mysql.h>

#include <ctype.h>

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;

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;        

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中怎么实现一个分析函数，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注编程网数据库频道。

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: MySQL中怎么实现一个分析函数

本文链接: https://www.lsjlt.com/news/56345.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • MySQL中怎么实现一个分析函数
    本篇文章为大家展示了MySQL中怎么实现一个分析函数，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。1. 实现rownum12SET @rn:=0;SELE...
    99+
    2022-10-18
  • PHP中怎么实现一个分页函数
    PHP中怎么实现一个分页函数，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。写列表性质的页面，几乎都要写上一段分页的程序。最近小阳终于决心一劳永逸&mdash;&am...
    99+
    2023-06-17
  • MySQL中怎么实现一个截取字符串函数
    MySQL中怎么实现一个截取字符串函数，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。　　函数：　　1、从左开始截取字符串　　left...
    99+
    2022-10-18
  • vbs中怎么实现一个字符串分割函数
    这篇文章给大家介绍vbs中怎么实现一个字符串分割函数，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。Function CutAndJoin(sSource, iLong, sJoiner)&...
    99+
    2023-06-08
  • 怎么协助MySQL实现Oracle高级分析函数
    小编给大家分享一下怎么协助MySQL实现Oracle高级分析函数，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！Oracle 支持一些独特的语法和函数，在移植到 M...
    99+
    2023-06-01
  • MongoDB中怎么实现一个更新函数
    MongoDB中怎么实现一个更新函数，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。在MongoDB里面对于数据的更新操作提供了...
    99+
    2022-10-18
  • C#中怎么实现一个取整函数
    本篇文章为大家展示了C#中怎么实现一个取整函数，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。C#取整函数使用实例：Math.Round是"就近舍入"，当要舍入的是5时与"...
    99+
    2023-06-17
  • C#中怎么实现一个回调函数
    C#中怎么实现一个回调函数，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。什么是C#回调函数C#回调函数是应用程序提供给Windows系统DLL或其它DLL调用的函数，一般用于截...
    99+
    2023-06-18
  • 怎么在MySQL数据库中实现一个分布式锁
    这篇文章将为大家详细讲解有关怎么在MySQL数据库中实现一个分布式锁，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。建表CREATE TABLE&n...
    99+
    2022-10-18
  • Python中怎么实现一个生成器函数
    Python中怎么实现一个生成器函数，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。1.生成器在 Python 中，一边循环一边计算的机制，称为生成器（Generator）；生成...
    99+
    2023-06-02
  • Java8中怎么实现一个函数式接口
    这篇文章给大家介绍Java8中怎么实现一个函数式接口，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。以下我们继续深入Java8函数式编程模型public class Test1 { ...
    99+
    2023-05-30
    java8
  • Golang中怎么实现一个不可重入函数
    Golang中怎么实现一个不可重入函数，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。场景用例某个服务是对某些条件进行轮询，每秒监视一些状态。我们希望每个状态都可以独立地检查，而...
    99+
    2023-06-04
  • 怎么在Shell脚本中实现一个ftok函数
    怎么在Shell脚本中实现一个ftok函数？针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。#!/bin/shlet key=0function ftok(...
    99+
    2023-06-09
  • Oracle 中怎么实现一个大小写转换函数
    本篇文章给大家分享的是有关Oracle 中怎么实现一个大小写转换函数，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。Oracle 大小写转换函数...
    99+
    2022-10-18
  • C#中怎么实现一个截取字符串函数
    C#中怎么实现一个截取字符串函数，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。C#截取字符串函数实例：//C#截取字符串函数  public...
    99+
    2023-06-17
  • C++中怎么实现一个字符串替换函数
    这篇文章将为大家详细讲解有关C++中怎么实现一个字符串替换函数，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。C++字符串替换函数代码如下：@brief 实现字符串替换 &...
    99+
    2023-06-17
  • 利用Java怎么实现一个同步函数
    本篇文章为大家展示了利用Java怎么实现一个同步函数，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。具体方法如下：//同步函数的锁是this锁，this是一个引用，this指向的对象就是锁//下面证明...
    99+
    2023-05-31
    java ava
  • 怎么在Python中使用pandas函数实现数据分析
    本篇文章给大家分享的是有关怎么在Python中使用pandas函数实现数据分析，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。python有哪些常用库python常用的库：1.r...
    99+
    2023-06-14
  • 使用java怎么实现一个mysql分表操作
    使用java怎么实现一个mysql分表操作？很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。设置项目气动执行次方法(每天检查一次表记录)public cl...
    99+
    2023-06-06
  • 分析MySQL中FTWRL一个奇怪的堵塞现象
    本篇内容介绍了“分析MySQL中FTWRL一个奇怪的堵塞现象”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！...
    99+
    2022-10-18
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作