MySQL分析函式實現

沃趣科技發表於2018-10-29
MySql函式

| MySQL分析函式實現

還好MySQL8.0已經實現了與Oracle相同的分析函式。

1. 實現rownum 

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

或者寫成： 

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;


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中也是這麼實現的： 

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;


2.2 row_number () 實現 

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

我們的預設規則是在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;


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

2.3 求每個人員佔他所在部門總工資的百分比

在Oracle中實現： 

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


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;

2.4 求各個部門的總共工資

Oracle: 

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

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;

子查詢的功能實現如下：

下面是這個語句的結果

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;


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;


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;


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;


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



| 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



|  作者簡介

姚崇·沃趣科技高階資料庫技術專家

熟悉Oracle、MySQL資料庫內部機制，豐富的Oracle、MySQL故障診斷、效能調優、資料庫備份恢復、複製、高可用方案及遷移經驗。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2217845/，如需轉載，請註明出處，否則將追究法律責任。

相關文章