溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
獲取短信驗證碼
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
登 錄 注冊有禮
云服務器 香港服務器 高防服務器
最新更新 網站標簽 地圖導航
產品

MySQL中怎么實現一個分析函數

發布時間：2021-07-13 16:40:20 來源：億速云 閱讀：280 作者：Leah 欄目：MySQL數據庫

本篇文章為大家展示了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>        /* 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中怎么實現一個分析函數，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。

向AI問一下細節
推薦閱讀：
  1. Oracle分析函數
  2. 分析函數

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

mysql

猜你喜歡

最新資訊
相關推薦

相關標簽

mysql8.0.20 安裝mysql mysql workbench mysqlsla mysql innodb mysql視頻教程 mysql優化篇 mysql數據備份 mysql 監控 mysqli擴展 mysql oracle mysql性能 windows下安裝多個mysql實例 mysql基礎語句 mysqldump備份 zabbix監控mysql mysql8.0.11 mysql-server mysql約束 mysqlcheck
AI
助
手
產品服務
地區劃分
專題活動
幫助支持
關于我們
售后咨詢
7*24小時在線電話：400-100-2938
7*24小時在線 QQ：800811969
關注億速云

億速云公眾號

手機網站二維碼

Copyright ? Yisu Cloud Ltd. All Rights Reserved. 2018 版權所有

廣州億速云計算有限公司粵ICP備17096448號-1 粵公網安備 44010402001142號增值電信業務經營許可證編號：B1-20181529

亚洲午夜精品一区二区_中文无码日韩欧免_久久香蕉精品视频_欧美主播一区二区三区美女