sumover mysql_沃趣科技

原创 于 2021-01-30 10:15:08 发布 · 414 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sumover mysql

本文介绍了MySQL8.0中实现的分析函数，包括如何模拟Oracle的rownum、sum()、row_number()、求员工工资占比和部门总工资、获取部门第二高工资等，并给出了与Oracle的实现方式对比。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

|MySQL分析函数实现

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

1.实现rownum

SET @rn:=0;

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

1702a51a7b9dbcecce52e8db5a9499fa.png

或者写成：

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;

34dbc60865edbad75b0a253a66afc88e.png

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;

edfec8446394abcf71dc540d893698ed.png

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;

678ac6db711f16251ab87a0131a061cf.png

2.2 row_number()实现

select

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

9c1f94823627684b45dff9be5fd13077.png

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

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;

897efc0c97ef6dca4685e49727babec8.png

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

676ac0adcc110d503e120f8e8d4cf5ff.png

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

在Oracle中实现：

SELECT E.*,

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

FROM EMP E

ORDER BY DEPTNO;

78dab742640b8dff5c638896fca791f0.png

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;

472ae52a99e354b8e577092f009e0865.png

2.4 求各个部门的总共工资

Oracle:

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

1109a4b9df97f1731f4c99f15594b59e.png

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;

子查询的功能实现如下：

a1c0efe8a2c195d60a1401ee5e236ddd.png

下面是这个语句的结果

59c7f6a1ec640153e0a277ad72d00513.png

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;

564750b467f31eb44e4d66577504ade6.png

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;

e48129f7477e9b6119792c10a6b6bf97.png

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;

2c09664a8fad579a19d1d03770946d5f.png

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;

f38c41559940b46a7e20f667d8fcae21.png

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

5d5fa804418e2f3ea43a75335c71cd3e.png

| 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 #include #if defined(MYSQL_SERVER)

#include /* To get strmov() */

#else

/* when compiled as standalone */

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

#endif

#include #include /*

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

b4b7b1680d7919937de3ef9929a10e56.png

e77536ba3f178c66f821d78bfdd08a84.png

|  作者简介

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

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

摸鱼肥宅
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值