Shulou(Shulou.com)05/31 Report--

This article shows you how to implement an analysis function in MySQL, which is concise and easy to understand, which can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. Implement rownum

one

two

SET @ rn:=0

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

Or write as follows:

one

SELECT @ rn:=@rn + 1 AS rownum, e.* FROM emp e, (SELECT @ rn:=0) c

two。 Various analysis function writing methods (various calculation problems that may be encountered when MySQL implements analysis statements) 2.1 sum () implementation

-- SQL execution order, FROM, JOIN, WHERE, GROUP BY,HAVING, ORDER BY, SELECT

The original statement for the paging statement in Oracle is as follows:

one

SELECT E. AS COUNTOVER FROM EMP E, SUM (SAL) OVER (PARTITION BY DEPTNO)

one

two

three

four

five

six

seven

eight

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

This is also done in Mysql:

one

two

three

four

five

six

seven

eight

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 () implementation

one

two

Select

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

Our default rule is to initialize variables after from.

one

two

three

four

five

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

one

two

three

four

five

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

This statement first executes order by

2.3 ask for the percentage of each person in the total salary of his department

Implement in Oracle:

one

two

three

four

SELECT E.*

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

FROM EMP E

ORDER BY DEPTNO

one

two

three

four

five

six

seven

eight

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 ask for the total salary of each department

Oracle:

one

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

MySQL:

one

two

three

four

five

six

seven

eight

nine

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

The functions of the subquery are as follows:

Here is the result of this statement

2.5 people who take the second highest salary in the department

First of all, we take the second place, which is easy to implement with Oracle, whether it is the first or the second.

one

two

three

four

five

SELECT *

FROM (SELECT E.*

ROW_NUMBER () OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RN

FROM EMP E)

WHERE RN = 2

The first implementation in Mysql is as follows:

In version 5.6, where sql_ mode is not only _ full_group_by, we can implement it in the following way

one

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'

one

SELECT * FROM (SELECT e.* FROM emp e ORDER BY deptno,sal) a GROUP BY deptno

When SQL_ mode is not only _ full_group_by, the group by in MySQL takes only the first row, so let's look at the SQL of the second line.

one

two

three

four

five

six

seven

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 ()

The dense_rank function returns a unique value, unless all the same data are ranked the same when the same data is encountered.

one

two

three

four

five

six

seven

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 is written as follows:

one

two

three

four

five

six

seven

eight

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 who have won the championship in a row?

-- Please write a SQL statement to find out which consecutive champions have won the championship during this period, and what is the start and end time of their consecutive years. The results are as follows:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

Create table nba as

SELECT 'Bull' AS TEAM, '1991' AS Y FROM DUAL UNION ALL

SELECT 'Bull' AS TEAM, '1992' AS Y FROM DUAL UNION ALL

SELECT 'Bull' AS TEAM, '1993' AS Y FROM DUAL UNION ALL

SELECT 'Piston' AS TEAM, '1990' AS Y FROM DUAL UNION ALL

SELECT 'Rocket' AS TEAM, '1994' AS Y FROM DUAL UNION ALL

SELECT 'Rocket' AS TEAM, '1995' AS Y FROM DUAL UNION ALL

SELECT 'Bull' AS TEAM, '1996' AS Y FROM DUAL UNION ALL

SELECT 'Bull' AS TEAM, '1997' AS Y FROM DUAL UNION ALL

SELECT 'Bull' AS TEAM, '1998' AS Y FROM DUAL UNION ALL

SELECT 'Spurs' AS TEAM, '1999' AS Y FROM DUAL UNION ALL

SELECT 'Lakers' AS TEAM, '2000' AS Y FROM DUAL UNION ALL

SELECT 'Lakers' AS TEAM, '2001' AS Y FROM DUAL UNION ALL

SELECT 'Lakers' AS TEAM, '2002' AS Y FROM DUAL UNION ALL

SELECT 'Spurs' AS TEAM, '2003' AS Y FROM DUAL UNION ALL

SELECT 'Piston' AS TEAM, '2004' AS Y FROM DUAL UNION ALL

SELECT 'Spurs' AS TEAM, '2005' AS Y FROM DUAL UNION ALL

SELECT 'Heat' AS TEAM, '2006' AS Y FROM DUAL UNION ALL

SELECT 'Spurs' AS TEAM, '2007' AS Y FROM DUAL UNION ALL

SELECT 'Celtic' AS TEAM, '2008' AS Y FROM DUAL UNION ALL

SELECT 'Lakers' AS TEAM, '2009' AS Y FROM DUAL UNION ALL

SELECT 'Lakers' AS TEAM, '2010' AS Y FROM DUAL

Oracle implementation:

one

two

three

four

five

six

seven

eight

nine

ten

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 implementation:

one

two

three

four

five

six

seven

eight

nine

ten

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 plug-in |

Userdefined Function, a user-defined function. We know that MySQL itself supports many built-in functions, and you can also define functions by creating storage methods. UDF provides a more efficient way for users to create functions.

UDF is similar to ordinary functions in that it has parameters and output. There are two types: single call type and aggregate function. The former can be processed for each row of data, while the latter is used to deal with situations such as Group By.

UDF custom function, in MySQL basedir/include

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

thirty

thirty-one

thirty-two

thirty-three

thirty-four

thirty-five

thirty-six

thirty-seven

thirty-eight

thirty-nine

forty

forty-one

forty-two

forty-three

forty-four

forty-five

forty-six

forty-seven

forty-eight

forty-nine

fifty

fifty-one

fifty-two

fifty-three

fifty-four

fifty-five

fifty-six

fifty-seven

fifty-eight

[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 (apene b) stpcpy (ameme 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

}

Generate dynamic link library

one

Gcc rownum.c-fPIC-shared-o.. / lib/plugin/rownum.so

The above is how to implement an analysis function in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.