MySQL: supplementary knowledge

MySQL supplementary knowledge

After learning the basic and improved contents of MySQL:

Basic knowledge notes:

Improve knowledge notes:

The main points not recorded in the above notes are

Import data into table

Method 1: insert into table

Method 2: import data into the table by loading files;

  1. The tables created are:
CREATE TABLE pet(
	NAME VARCHAR(20),
	OWNER VARCHAR(20),
	species VARCHAR(20),
	sex CHAR(1),
	birth DATE,
	death DATE
);
  1. Create a txt file (Note: each field is separated by tab key, and if there is no record of value in the field, replace it with \ N);
Fluffy	Harold	cat	f	1993‐02‐04
Claws	Gwen	cat	m	1994‐03‐17
Buffy	Harold	dog	f	1989‐05‐13
Fang	Benny	dog	m	1990‐08‐27
Bowser	Diane	dog	m	1979‐08‐31	1995‐07‐29
Chirpy	Gwen	bird	f	1998‐09‐11
Whistler	Gwen	bird	\N	1997‐12‐09	\N
Slim	Benny	snake	m	1996‐04‐29
  1. Load data
mysql> load data local infile 'txt File path' into table Table name

mysql> load data local infile 'C:\\Users\\ZhuCC\\Desktop\\pet.txt' into table pet;
Query OK, 8 rows affected, 7 warnings (0.00 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 7
  1. Verify whether the import is successful
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | 0000-00-00 |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
+----------+--------+---------+------+------------+------------+
8 rows in set (0.00 sec)

Total external connection

All external connections: the results of the left external connection and the right external connection are merged, but the duplicate records will be removed.

select * from Table 1 full outer join Table 2 on condition

However, mysql database does not support this syntax

IN and EXISTS

IN: explained IN MySQL foundation;

EXISTS: indicates existence. When the result of the sub query EXISTS, all the data in the main query will be displayed

SELECT * FROM tab_route r WHERE EXISTS (SELECT * FROM tab_category c WHERE c.`cid` = r.`cid` AND c.`cname`="Domestic tour");

reference resources: https://www.cnblogs.com/dreamtecher/p/5128668.html

SQL question in niuke.com: https://www.nowcoder.com/questionTerminal/c39cbfbd111a4d92b221acec1c7c1484

UNION and UNION ALL

UNION statement: used to display the data queried in the same column in different tables; (excluding duplicate data)

mysql> SELECT * FROM table_a;
+------+-------+
| id   | name  |
+------+-------+
|    1 | one   |
|    2 | two   |
|    3 | three |
|    4 | four  |
|    5 | five  |
+------+-------+

mysql> SELECT * FROM table_b;
+------+-------+
| id   | NAME  |
+------+-------+
|    4 | four  |
|    5 | five  |
|    6 | six   |
|    7 | seven |
|    8 | eight |
+------+-------+

mysql> SELECT * FROM table_a UNION SELECT * FROM table_b;
+------+-------+
| id   | name  |
+------+-------+
|    1 | one   |
|    2 | two   |
|    3 | three |
|    4 | four  |
|    5 | five  |
|    6 | six   |
|    7 | seven |
|    8 | eight |
+------+-------+

UNION ALL statement: used to display the data queried in the same column in different tables; (including duplicate data)

mysql> SELECT * FROM table_a UNION ALL SELECT * FROM table_b;
+------+-------+
| id   | name  |
+------+-------+
|    1 | one   |
|    2 | two   |
|    3 | three |
|    4 | four  |     -- Duplicate data
|    5 | five  |
|    4 | four  |     -- Duplicate data
|    5 | five  |
|    6 | six   |
|    7 | seven |
|    8 | eight |
+------+-------+

CASE WHEN statement

SELECT *,
	CASE <expression>
	WHEN <Expression value> THEN <sql Statement or result>
	WHEN <Expression value> THEN <sql Statement or result>
	...
	ELSE <By default sql Statement or result>
END

-- explain:
--    case Followed by the field to be judged
--    when Followed by judgment conditions
--    then Followed by results
--    else amount to default
--    end Is the end of the sentence

-- give an example:
SELECT *,
	CASE 
	WHEN a.`id`=1 THEN "1=one"
	WHEN a.`id`=2 THEN "2=two"
	WHEN a.`id`=3 THEN "3=three"
	WHEN a.`id`=4 THEN "4=four"
	ELSE "None" END AS "type"
FROM table_a AS a;

IF and CASE statements

MySQL's if can be used not only as an expression, but also as a process control statement in a stored procedure

-- expression:
IF(expr1, expr2, expr3)

-- If expr1 yes TRUE (expr1 <> 0 and expr1 <> NULL)，be IF()The return value of is expr2; 
-- Otherwise, the return value is expr3;
-- IF() The return value of is a numeric value or a string value, depending on the context.

-- Example:
select *, if(sva=1,"male", "female") as ssva from taname where sva != "";

The if of the expression can also be realized by CASE when:

select CASE sva WHEN 1 THEN 'male' ELSE 'female' END as ssva from taname where sva != '';

-- Among the above returned results
-- If there is no matching result value, the returned result is ELSE Results after
-- without ELSE Part, the return value is NULL
SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END as testCol;  -- output one
SELECT CASE 1 WHEN 2 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END as testCol;  -- output two
SELECT CASE 1 WHEN 3 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END as testCol;  -- output more
SELECT CASE 1 WHEN 3 THEN 'one' WHEN 2 THEN 'two' END as testCol;  -- output null

Get server metadata

command describe result
SELECT VERSION(); Server version information version()
5.5.40
SELECT DATABASE(); Current database name (or null) DATABASE()
db1
SELECT USER(); Current user name USER()
root@localhost
SHOW STATUS; Server status slightly
SHOW VARIABLES; Server configuration variables slightly

Built in function

String function

function describe example
ASCII(s) Returns the ASCII code of the first character of string s SELECT ASCII("abc");
CHAR_LENGTH(s) Returns the number of characters in the string s SELECT CHAR_LENGTH("abc")
CHARACTER_LENGTH(s) Returns the number of characters in the string s SELECT CONCAT("a", "b", "c");
CONCAT(s1,s2...sn) String s1,s2 and other strings are combined into one string SELECT CONCAT("a", "b", "c");
CONCAT_WS(x, s1,s2...sn) Same as CO NCAT(s1,s2,...) Function, but each string needs to be directly added with x, which can be a separator SELECT CONCAT_WS("," , "a", "b", "c");
FIELD(s,s1,s2...) Returns the first string s in the string list (s1,s2...) Location in SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2) Returns the position of the string that matches s1 in string s2 SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n) The function can format the number x "#, ####. ##", keep x to n digits after the decimal point, and round the last digit SELECT FORMAT(250500.5634, 3);
INSERT(s1,x,len,s2) The string s2 replaces the string whose starting length is len at the x position of s1 SELECT INSERT("google.com", 1, 6, "runnob");
LOCATE(s1,s) Get the start position of s1 from the string s SELECT INSTR('abc','b');
LCASE(s) Turns all letters of string s into lowercase letters SELECT LOWER('ABC');
LEFT(s,n) Returns the first n characters of string s SELECT LEFT('hello',2);
LOCATE(s1,s) Get the start position of s1 from the string s SELECT LOCATE('b', 'abc');
LOWER(s) Turns all letters of string s into lowercase letters SELECT LOWER('ABC1234');
LPAD(s1,len,s2) Fill the string s2 at the beginning of the string s1 so that the length of the string reaches len SELECT LPAD('abc',10,'xx');
LTRIM(s) Remove the space at the beginning of the string s SELECT LTRIM(" hello");
MID(s,n,len) Intercept the substring with length from the n position of string s, the same as SUBSTRING(s,n,len) SELECT MID("zhuchengchao", 4, 5);
SELECT SUBSTRING("zhuchengchao", 4, 5);
POSITION(s1 IN s) Get the start position of s1 from the string s SELECT POSITION('b' IN 'abc');
REPEAT(s,n) Repeat string s n times SELECT REPEAT('hello',3);
REPLACE(s,s1,s2) Replace string s1 in string s with string s2 SELECT REPLACE('abcabc','a','x');
REVERSE(s) Reverse the order of string s SELECT REVERSE('abc');
RIGHT(s,n) Returns the last n characters of string s SELECT RIGHT('hello',2);
RPAD(s1,len,s2) Add string s2 at the end of string s1 to make the length of string len SELECT RPAD('abc',10,'xx');
RTRIM(s) Remove the space at the end of the string s SELECT RTRIM("hello ");
SPACE(n) Return n spaces SELECT CHAR_LENGTH(SPACE(10));
STRCMP(s1,s2) Compare the strings s1 and s2, return 0 if s1 is equal to s2, return 1 if s1 > s2, and return - 1 if s1 < s2 SELECT STRCMP("Hello", "hello");
SUBSTR(s, start, length) Intercept the substring with length from the start position of string s SELECT SUBSTR("Hello", 2, 3);
SUBSTRING(s, start, length) Intercept the substring with length from the start position of string s SELECT SUBSTRING("zhuchengchao", 4, 5);
SUBSTRING_INDEX(s, delimiter, number) Returns the substring after the delimiter from the number of string s
If number is a positive number, returns the string to the left of character number
If number is negative, returns the string to the right of the (absolute value of number (from the right)) character		 SELECT SUBSTRING_INDEX('a*b*c','*', 2);
SELECT SUBSTRING_INDEX('a*b*c','*', -2);
TRIM(s) Remove the spaces at the beginning and end of the string s SELECT TRIM(' hello ');
UCASE(s) Convert string to uppercase SELECT UCASE("hello");
UPPER(s) Convert string to uppercase SELECT UPPER("hello");

Mathematical function

Function name describe example
ABS(x) Returns the absolute value of x SELECT ABS(-1);
ACOS(x) Find the inverse cosine of x (the parameter is radian) SELECT ACOS(0.25);
ASIN(x) Find the inverse sine value (the parameter is radian) SELECT ASIN(0.25);
ATAN(x) Find the arctangent value (the parameter is radian) SELECT ATAN(2.5);
ATAN2(n, m) Find the arctangent value (the parameter is radian) SELECT ATAN2(-0.8, 2);
AVG(expression) Returns the average value of an expression. Expression is a field SELECT AVG(Price) FROM Products;
CEIL(x) Returns the smallest integer greater than or equal to x SELECT CEIL(1.5)
CEILING(x) Returns the smallest integer greater than or equal to x SELECT CEIL(1.5)
COS(x) Find the cosine value (the parameter is radian) SELECT COS(2);
COT(x) Find the cotangent value (the parameter is radian) SELECT COT(6);
COUNT(expression) Returns the total number of records queried. The expression parameter is a field or * sign SELECT COUNT(ProductID) FROM Products;
DEGREES(x) Convert radians to angles SELECT DEGREES(3.1415926535898);
n DIV m Integer division, n is the divisor and m is the divisor SELECT 10 DIV 5;
EXP(x) Returns the x power of e SELECT EXP(3)
FLOOR(x) Returns the largest integer less than or equal to x SELECT FLOOR(1.5)
GREATEST(expr1, expr2, expr3, ...) Returns the maximum value in the list SELECT GREATEST(3, 12, 34, 8, 25);
LEAST(expr1, expr2, expr3, ...) Returns the minimum value in the list SELECT LEAST("apple1", "apple2");
LN Returns the natural logarithm of a number SELECT LN(2);
LOG(x) Returns the natural logarithm (base e logarithm) SELECT LOG(20.085536923188)
LOG10(x) Returns the base 10 logarithm SELECT LOG10(100)
LOG2(x) Returns the base 2 logarithm SELECT LOG2(6);
MAX(expression) Returns the maximum value in the field expression SELECT MAX(Price) FROM Products;
MIN(expression) Returns the minimum value in the field expression SELECT MIN(Price) FROM Products;
MOD(x,y) Returns the remainder of x divided by y SELECT MOD(5,2)
PI() Return PI (3.141593) SELECT PI()
POW(x,y) Returns the y power of x SELECT POW(2,3)
POWER(x,y) Returns the y-th power of x SELECT POWER(2,3)
RADIANS(x) Convert angles to radians SELECT RADIANS(180)
RAND() Returns a random number from 0 to 1 SELECT RAND()
ROUND(x) Returns the integer nearest to x, rounded SELECT ROUND(1.23456)
SIGN(x) Returns the sign of X, where x is a negative number, 0 and positive number, and returns - 1, 0 and 1 respectively SIGN(‐10)
SIN(x) Find the sine value (the parameter is radian) SELECT SIN(RADIANS(30))
SQRT(x) Returns the square root of x SELECT SQRT(25)
SUM(expression) Returns the sum of the specified fields SELECT SUM(Quantity) FROM OrderDetails;
TAN(x) Find the tangent value (the parameter is radian) SELECT TAN(1.75);
TRUNCATE(x,y) Returns the value of x to the y digits after the decimal point (the biggest difference from ROUND is that it will not be rounded) SELECT TRUNCATE(1.23456,3)

Date function

Function name describe example
ADDDATE(d,n) Calculate the actual date d plus n days SELECT ADDDATE("2020-06-19", 10);
ADDTIME(t,n) Time t plus n seconds SELECT ADDTIME(NOW(), 100);
CURDATE() Returns the current date slightly
CURRENT_DATE() Returns the current date slightly
CURRENT_TIME Returns the current time SELECT CURRENT_TIME;
CURRENT_TIMESTAMP() Returns the current date and time SELECT CURRENT_TIMESTAMP;
CURTIME() Returns the current time slightly
DATE() Extract a date value from a date or datetime expression SELECT DATE("2020,6,19");
DATEDIFF(d1,d2) Days between calculation dates D1 - > D2 SELECT DATEDIFF("2020-06-19","2020-06-29")
DATE_ADD(d，INTERVAL expr type) Calculate the date after the start date d plus a time period SELECT DATE_ADD("2020-06-19", INTERVAL 2 DAY);
DATE_FORMAT(d,f) Display the date d as required by expression f SELECT DATE_FORMAT("2020-06-19","%y+%M+%d %r");
DATE_SUB(date,INTERVAL expr type) Function subtracts the specified time interval from the date. slightly
DAY(d) Returns the date part of the date value d SELECT DAY("2020-06-19");
DAYNAME(d) The return date d is the day of the week, such as Monday and Tuesday SELECT DAYNAME("2020-06-19");
DAYOFMONTH(d) The calculation date d is the day of the month SELECT DAYOFMONTH("2020-06-19");
DAYOFWEEK(d) Date d what day is today, 1 Sunday, 2 Monday, and so on SELECT DAYOFWEEK("2020-06-19");
DAYOFYEAR(d) The calculation date d is the day of the year SELECT DAYOFYEAR("2020-06-19");
EXTRACT(type FROM d) Get the specified value from date d, and type specifies the returned value. SELECT EXTRACT(YEAR FROM "2020-06-19");
ROM_DAYS(n) Calculate the date n days from January 1, 0000 SELECT FROM_DAYS(737960);
HOUR(t) Return value in t hours SELECT HOUR('10:05:03');
LAST_DAY(d) Returns the last day of the month for a given date SELECT LAST_DAY("2020-06-19")
LOCALTIME() Returns the current date and time slightly
LOCALTIMESTAMP() Returns the current date and time slightly
MAKEDATE(year, day-of year) Day of year returns a date based on the given parameter year and the number of days in the year SELECT MAKEDATE(2020,31)
MAKETIME(hour, minute, second) Combination time. The parameters are hour, minute and second respectively SELECT MAKETIME(12,15,30);
MICROSECOND(date) Returns the number of milliseconds corresponding to the date parameter SELECT MICROSECOND('12:00:00.12');
MINUTE(t) Returns the minute value in t SELECT MINUTE('12:13:14');
MONTHNAME(d) Returns the name of the month in the date, such as Janyary SELECT MONTHNAME("2020-06-19");
MONTH(d) Returns the month value in date d, from 1 to 12 SELECT MONTH("2020-06-19");
NOW() Returns the current date and time SELECT NOW();
PERIOD_ADD(period, number) Add a period for the year month combination date SELECT PERIOD_ADD(199801,2);
PERIOD_DIFF(period1, period2) Returns the month difference between two periods SELECT PERIOD_DIFF(9802,199703);
QUARTER(d) What season is the return date d? Return 1 to 4 SELECT QUARTER("2020-06-19");
SECOND(t) Returns the second value in t SELECT SECOND('12:13:14');
SEC_TO_TIME(s) Converts the time s in seconds to minutes and seconds SELECT SEC_TO_TIME('54925');
STR_TO_DATE(string, format_mask) Convert string to date SELECT STR_TO_DATE('06/19/2020', '%m/%d/%Y');
SUBDATE(d,n) Date d minus n days SELECT SUBDATE("2020-06-19", 1);
SUBTIME(t,n) Time t minus n seconds SELECT SUBTIME('12:13:14', 14);
SYSDATE() Returns the current date and time slightly
TIME(expression) Extract the time portion of the incoming expression SELECT TIME("19:30:10");
TIME_FORMAT(t,f) Display the time t as required by expression f SELECT TIME_FORMAT(NOW(), '%l:%i %p');
TIME_TO_SEC(t) Convert time t to seconds SELECT TIME_TO_SEC(NOW());
TIMEDIFF(time1, time2) Calculate time difference SELECT TIMEDIFF('12:00','10:00');
TIMESTAMP(expression, interval) For a single parameter, the function returns a date or date time expression; When there are 2 parameters, add the parameters SELECT TIMESTAMP("2020-06-19");
SELECT TIMESTAMP("2020-06-19", "01:00:00");
TO_DAYS(d) The number of days from the calculation date d to January 1, 0000 SELECT TO_DAYS("2020-06-19");
WEEK(d) The calculation date d is the week ordinal of the year, ranging from 0 to 53 SELECT WEEK("2020-06-19");
WEEKDAY(d) Date d is the day of the week. 0 means Monday and 1 means Tuesday SELECT WEEKDAY("2020-06-19");
WEEKOFYEAR(d) The calculation date d is the week ordinal of the year, ranging from 0 to 53 SELECT WEEKOFYEAR("2020-06-19");
YEAR(d) Return year SELECT YEAR("2020-06-19");
YEARWEEK(date, mode) Returns the year and the week (0 to 53). In mode, 0 means Sunday, 1 means Monday, and so on SELECT YEARWEEK("2020-06-19", 0);

Advanced function

Function name describe example
BIN(x) Returns the binary encoding of x SELECT BIN(15);
BINARY(s) Convert string s to binary string SELECT BINARY("itcast");
CAST(x AS type) shifting clause SELECT CAST("2017-08-29" AS DATE);
COALESCE(expr1, expr2, ...., expr_n) Returns the first non empty expression in the parameter (from left to right) SELECT COALESCE(NULL, NULL, 'a', NULL, 'b');
CONNECTION_ID() Returns the number of connections to the server SELECT CONNECTION_ID();
CONV(x,f1,f2) Returns a binary number from f1 to f2 SELECT CONV(15, 10, 2);
CONVERT(s USING cs) The function changes the character set of the string s to cs SELECT CHARSET('ABC');
SELECT CHARSET(CONVERT('ABC' USING gbk));
CURRENT_USER() Current user return SELECT CURRENT_USER();
DATABASE() Returns the current database name SELECT DATABASE();
IF(expr,v1,v2) If the expression expr holds, the result v1 is returned; Otherwise, the result v2 is returned. Select if (1 > 0, 'correct', 'error');
IFNULL(v1,v2) If the value of v1 is not NULL, v1 is returned, otherwise v2 is returned. SELECT IFNULL(NULL,'Hello Word')
ISNULL(expression) Judge whether the expression is empty SELECT ISNULL(NULL);
LAST_INSERT_ID() Returns the most recently generated AUTO_INCREMENT value SELECT LAST_INSERT_ID();
NULLIF(expr1, expr2) Compare two strings. If the strings expr1 and expr2 are equal, NULL is returned. Otherwise, expr1 is returned SELECT NULLIF("258", "248");
SESSION_USER() Returns the current user slightly
SYSTEM_USER() Returns the current user slightly
USER() Return current user lue slightly
VERSION() Returns the version number of the database slightly

Other functions

  • group_concat

group_concat() implements grouping aggregation

Syntax:

group_concat([DISTINCT] Fields to connect [Order BY sort field ASC/DESC] [Separator 'Separator'])

First create a table and add data

-- Create table
CREATE TABLE goods(
	id INT NOT NULL,
	price INT NOT NULL
)

INSERT INTO goods (id, price) VALUES
(1, 10),(1,20),(1,30),(1,40),(1,40),
(2, 10),(2,20),(2,30),(2,30),
(3, 10),(3,20);

SELECT * FROM goods;
-- result:
-- id	price
-- 1	10
-- 1	20
-- 1	30
-- 1	40
-- 1	40
-- 2	10
-- 2	20
-- 2	30
-- 2	30
-- 3	10
-- 3	20

Use Demo:

-- with id Group, put price Field values are printed on the same line, separated by commas(default)
SELECT id, GROUP_CONCAT(price) FROM goods GROUP BY id;
-- result
-- id	group_concat(price)
-- 1	10,20,30,40,40
-- 2	10,20,30,30
-- 3	10,20

-- with id Group, put price The values of the fields are printed on one line, separated by semicolons
SELECT id, GROUP_CONCAT(price SEPARATOR ';') FROM goods GROUP BY id;
-- result
-- id	GROUP_CONCAT(price separator ';')
-- 1	10;20;30;40;40
-- 2	10;20;30;30
-- 3	10;20

-- with id Grouping to remove duplication and redundancy price The values of the fields are printed on one line, separated by commas
SELECT id,GROUP_CONCAT(DISTINCT price) FROM goods GROUP BY id; 
-- result
-- id	group_concat(distinct price)
-- 1	10,20,30,40
-- 2	10,20,30
-- 3	10,20

-- with id Group, put price The value of the field is reprinted on one line, separated by commas, according to price Reverse order
SELECT id,GROUP_CONCAT(DISTINCT price ORDER BY price DESC) FROM goods GROUP BY id;  
-- result
-- id	group_concat(DISTINCT price order by price desc)
-- 1	40,30,20,10
-- 2	30,20,10
-- 3	20,10
  • substring_index

substring_index to achieve segmentation

Format:

substring_index(str, delim, count)

-- str:String to process
-- delim:Separator
-- count:count

use:

SELECT SUBSTRING_INDEX('www.wikibt.com', '.', 1);
-- The result is: www

SELECT SUBSTRING_INDEX('www.wikibt.com','.',2);
-- The result is: www.wikibt
-- That is, if count If it's a positive number, then it's from left to right N All contents to the left of the separator
-- On the contrary, if it is a negative number, it is the number from the right, the second N All contents to the right of a separator, such as:
SELECT SUBSTRING_INDEX('www.wikibt.com','.',-2)
-- The result is: wikibt.com

-- To take wikibt What should I do?
-- All from the right of the second separator in the right, and then from the left of the first separator in the left:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.wikibt.com','.',-2),'.',1);
-- The result is: wikibt

Window function

summary

Windows: record sets

Window function: a special function executed on a set of records that meet certain conditions. For each record, the function must be executed in this window.

  • Static window: some functions have a fixed window size with different records;
  • Sliding window: some functions are on the contrary. Different records correspond to different windows, which is called sliding window.

Difference between window function and ordinary aggregate function

  • The aggregation function is to aggregate multiple records into one; Window function is that each record will be executed. How many records have been executed;
  • Aggregate functions can also be used for window functions

Basic syntax: function name over clause

over: it is used to specify the window range of function execution. If nothing is written in the brackets, it means that the window contains all rows that meet the WHERE condition, and the window function calculates based on all rows; If it is not empty, the following four grammars are supported to set the window

  • window_name: assign an alias to the window;

    Assign alias to WINDOW: WINDOW alias AS (PARTITION BY field 1, order by field 2)

  • PARTITION BY clause: the fields by which windows are grouped, and the window functions are executed on different groups;

  • ORDER BY clause: which fields are sorted by, and the window function will be numbered according to the sorted record order;

  • FRAME clause: FRAME is a subset of the current partition. The clause is used to define the rules of the subset, which is usually used as a sliding window.

Ordinal function

Purpose: displays the current line number in the partition

ROW_NUMBER(): order - 1, 2, 3

RANK(): sort in parallel, skip repeated sequence numbers - 1, 1, 3

DENSE_RANK(): sort in parallel without skipping repeated sequence numbers - 1, 1, 2

give an example:

-- Query students' grades of each course and sort them
-- Create table
CREATE TABLE stu_score(
	stu_id INT,
	lesson_id VARCHAR(4),
	score INT
);

INSERT INTO stu_score VALUES
(1, 'L001', 90), (1, 'L002', 95), (1, 'L003', 91), (1, 'L004', 92),
(2, 'L001', 95), (2, 'L002', 90), (2, 'L003', 90), (2, 'L004', 90),
(3, 'L001', 85), (3, 'L002', 85), (3, 'L003', 95), (3, 'L004', 95),
(4, 'L001', 92), (4, 'L002', 92), (4, 'L003', 92), (4, 'L004', 92);

SELECT * FROM stu_score;
-- result
-- stu_id	lesson_id	score
-- 1	L001	90
-- 1	L002	95
-- 1	L003	91
-- 1	L004	92
-- 2	L001	95
-- 2	L002	90
-- 2	L003	90
-- 2	L004	90
-- 3	L001	85
-- 3	L002	85
-- 3	L003	95
-- 3	L004	95
-- 4	L001	92
-- 4	L002	92
-- 4	L003	92
-- 4	L004	92

ROW_NUMBER(): order - 1, 2, 3

SELECT
	stu_id, lesson_id, score, ROW_NUMBER() over (PARTITION BY stu_id ORDER BY score DESC) AS score_order
FROM
	stu_score;

-- result
-- stu_id	lesson_id	score	score_order
-- 1	     L002	       95	  1
-- 1	     L004	       92	  2
-- 1	     L003	       91	  3
-- 1	     L001	       90	  4
-- 2	     L001	       95	  1
-- 2	     L002	       90	  2
-- 2	     L003	       90	  3
-- 2	     L004	       90	  4
-- 3	     L003	       95	  1
-- 3	     L004	       95	  2
-- 3	     L001	       85	  3
-- 3	     L002	       85	  4
-- 4	     L001	       92	  1
-- 4	     L002	       92	  2
-- 4	     L003	       92	  3
-- 4	     L004	       92	  4

RANK(): sort in parallel, skipping repeated sequence numbers - 1, 1, 3

SELECT
	stu_id, lesson_id, score, RANK() over (PARTITION BY stu_id ORDER BY score DESC) AS score_order
FROM
	stu_score;

-- result
-- stu_id	lesson_id	score	score_order
-- 1	L002	95	1
-- 1	L004	92	2
-- 1	L003	91	3
-- 1	L001	90	4
-- 2	L001	95	1
-- 2	L002	90	2
-- 2	L003	90	2
-- 2	L004	90	2
-- 3	L003	95	1
-- 3	L004	95	1
-- 3	L001	85	3
-- 3	L002	85	3
-- 4	L001	92	1
-- 4	L002	92	1
-- 4	L003	92	1
-- 4	L004	92	1

DENSE_RANK(): sort in parallel without skipping repeated sequence numbers - 1, 1, 2

SELECT
	stu_id, lesson_id, score, DENSE_RANK() over (PARTITION BY stu_id ORDER BY score DESC) AS score_order
FROM
	stu_score;
	
-- result
-- stu_id	lesson_id	score	score_order
-- 1	L002	95	1
-- 1	L004	92	2
-- 1	L003	91	3
-- 1	L001	90	4
-- 2	L001	95	1
-- 2	L002	90	2
-- 2	L003	90	2
-- 2	L004	90	2
-- 3	L003	95	1
-- 3	L004	95	1
-- 3	L001	85	2
-- 3	L002	85	2
-- 4	L001	92	1
-- 4	L002	92	1
-- 4	L003	92	1
-- 4	L004	92	1

distribution function

PERCENT_RANK(),CUME_DIST()

PERCENT_RANK(): each row is calculated according to the formula (rank-1) / (rows-1). Where rank is the sequence number generated by RANK() function, and rows is the total number of records in the current window; That is, calculate the percentage grade value

Not commonly used

SELECT
	stu_id, lesson_id, score,
	rank() over w AS rk,
	percent_rank() over w AS prk
FROM stu_score
WHERE stu_id=1
window w AS (PARTITION BY stu_id ORDER BY score);  -- Alias

-- result:
-- stu_id	lesson_id	score	rk	prk
-- 1	L001	90	1	0
-- 1	L003	91	2	0.3333333333333333
-- 1	L004	92	3	0.6666666666666666
-- 1	L002	95	4	1

CUME_DIST(): the number of rows in the group less than or equal to the current rank value / the total number of rows in the group

-- Query less than or equal to the current score( score)Proportion of
SELECT
	stu_id, lesson_id, score,
	-- If there is no partition, all data is a group, and the total number of rows is 8
	cume_dist() over (ORDER BY score) AS cd1,	
	-- according to lesson_id Divided into two groups with 4 rows each		
	cume_dist() over (PARTITION BY lesson_id ORDER BY score) AS cd2  
FROM
	stu_score
WHERE
	lesson_id IN ('L001', 'L003');
	
-- result
-- stu_id	lesson_id	score	cd1	cd2
-- 3	L001	85	    0.125	0.25
-- 1	L001	90	    0.375	0.5
-- 4	L001	92	    0.75	0.75
-- 2	L001	95	    1	    1
-- 2	L003	90	    0.375	0.25
-- 1	L003	91	    0.5	    0.5
-- 4	L003	92	    0.75	0.75
-- 3	L003	95	    1	    1

Before and after function

LAG(expr,n),LEAD(expr,n):

Returns the value of expr in the first n rows (LAG(expr,n)) or the next n rows (LEAD(expr,n)) of the current row

-- Query the difference between the score of the first student and that of the current student

-- 1. Check the results of the previous student first
SELECT
	stu_id, lesson_id, score,
	lag(score, 1) over w AS pre_score  -- Returns the previous row of the current row score
FROM 
	stu_score
WHERE
	lesson_id IN ('L001', 'L003')
window w AS (PARTITION BY lesson_id ORDER BY score);

-- 2. Recheck the difference
SELECT 
	stu_id, lesson_id, score, pre_score, IFNULL((score - pre_score), 0) AS diff
FROM
	(SELECT
		stu_id, lesson_id, score,
		lag(score, 1) over w AS pre_score  -- Returns the previous row of the current row score
	FROM 
		stu_score
	WHERE
		lesson_id IN ('L001', 'L003')
	window w AS (PARTITION BY lesson_id ORDER BY score)) AS t;

Head tail function

FIRST_VALUE(expr),LAST_VALUE(expr):

Returns the value of the first (FIRST_VALUE(expr)) or last (LAST_VALUE(expr)) expr

-- Find the first one and the last one in descending order of course grades score
SELECT
	stu_id, lesson_id, score,
	first_value(score) over w AS frist_score,
	last_value(score) over w AS last_score
FROM
	stu_score
WHERE
	lesson_id IN ('L001', 'L003')
window w AS (PARTITION BY lesson_id ORDER BY score DESC);

Other functions

NTH_VALUE(expr, n),NTILE(n)

NTH_VALUE(expr,n): returns the value of the nth expr in the window. Expr can be an expression or a column name

-- Up to the current grade, the scores of the second and third grades in each student's grade will be displayed
SELECT
	stu_id, lesson_id, score,
	nth_value(score, 2) over w AS second_score,
	nth_value(score, 3) over w AS third_score
FROM
	stu_score
WHERE
	stu_id IN (1, 3)
window w AS (PARTITION BY stu_id ORDER BY score);

-- result
-- stu_id	lesson_id	score	second_score	third_score
-- 1	L001	90	\N	\N
-- 1	L003	91	91	\N
-- 1	L004	92	91	92
-- 1	L002	95	91	92
-- 3	L001	85	85	\N
-- 3	L002	85	85	\N
-- 3	L003	95	85	95
-- 3	L004	95	85	95

NTILE(n): divide the ordered data in the partition into N levels and record the number of levels

-- Divide each course into 3 groups according to their grades
SELECT
	stu_id, lesson_id, score,
	ntile(3) over w AS nf
FROM
	stu_score
WHERE
	lesson_id IN ('L001', 'L003')
window w AS (PARTITION BY lesson_id ORDER BY score);

-- result:
-- stu_id	lesson_id	score	nf
-- 3	L001	85	1
-- 1	L001	90	1
-- 4	L001	92	2
-- 2	L001	95	3
-- 2	L003	90	1
-- 1	L003	91	1
-- 4	L003	92	2
-- 3	L003	95	3

NTILE(n) function is widely used in data analysis. For example, due to the large amount of data, it is necessary to evenly distribute the data to N parallel processes for calculation respectively. At this time, NTILE(n) can be used to group the data (because the number of records is not necessarily divided by N, the data may not be completely average), and then redistribute the data of different bucket numbers.

Aggregate function as window function

Aggregate functions (SUM(), AVG(), MAX(), MIN(), COUNT()) are dynamically applied to each record in the window to dynamically calculate various aggregate function values in the specified window

-- Query by current time stu_id=1 The cumulative scores of students, the subjects with the highest scores and the subjects with the lowest scores
SELECT
	stu_id, lesson_id, score,
	SUM(score) over w AS score_num,
	MAX(score) over w AS score_max,
	MIN(score) over w AS score_min
FROM
	stu_score
WHERE
	stu_id=1
window w AS (PARTITION BY stu_id);

Tags: MySQL Database

Posted by j115 on Thu, 14 Apr 2022 04:47:55 +0930

Popular Tags

©2023 Programs Wiki