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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 13、MySQL Case-MySQL分析函式實現MySql函式
- MySQL排名函式實現MySql函式
- MySQL空間函式實現位置打卡MySql函式
- MySQL自定義變數實現row_number分析函式的天坑MySql變數函式
- MySQL 函式索引功能終於可以實現了MySql函式索引
- mysql函式substring_index實現split切割效果MySql函式Index
- MySQL 函式MySql函式
- MySQL函式MySql函式
- fcntl函式實現dup函式
- MySQL(四)日期函式 NULL函式 字串函式MySql函式Null字串
- Mysql 常用函式(20)- ceiling 函式MySql函式
- Mysql 常用函式(15)- upper 函式MySql函式
- JavaScript的迭代函式與迭代函式的實現JavaScript函式
- Zepto 原始碼分析 3 - qsa 實現與工具函式設計原始碼函式
- 去抖函式的實現函式
- bind 函式的實現原理函式
- 巧妙地實現 debugOnly 函式Go函式
- js實現函式過載JS函式
- async 函式的實現原理函式
- Golang實現PHP常用函式GolangPHP函式
- Go實現PHP常用函式GoPHP函式
- MySQL 常用函式MySql函式
- MySQL函式(一)MySql函式
- mysql函式大全MySql函式
- 14 mysql 函式MySql函式
- MySQL 常用函式。MySql函式
- MySQL函式大全(字串函式,數學函式,日期函式,系統級函式,聚合函式)MySql函式字串
- mysql FIND_IN_SET函式、INSTR函式MySql函式
- MySQL函式-條件判斷函式MySql函式
- MySQL函式學習(一)-----字串函式MySql函式字串
- 實現call函式,手寫Function.prototype.call函式函式Function
- mysql執行函式出現1418錯誤MySql函式
- MySQL資料庫分散式事務XA的實現原理分析MySql資料庫分散式
- Oracle分析函式與視窗函式Oracle函式
- Mysql 常用函式(1)- 常用函式彙總MySql函式
- Vue元件實現函式防抖Vue元件函式
- Vue實現函式防抖元件Vue函式元件
- 虛擬函式的實現原理函式