MySQL8.0其他新特性
MySQL8.0新特性概述
MySQL8.0新增特性
MySQL8.0移除的舊特性
新特性1：視窗函式
視窗函式的分類
MySQL8.0版本開始支持視窗函式，視窗函式的作用類似于在查詢程序中對資料進行分組，不同的是，分組操作會把分組的結果聚合成一條記錄，而視窗函式是將結果置于每一條記錄中
視窗函式分為靜態和動態
- 靜態視窗函式：視窗大小是固定的，不會因記錄的不同而不同
- 動態視窗函式：視窗大小會隨著記錄的不同而變化
語法格式
函式 OVER (| PARTITION BY 欄位名 ORDER BY 欄位名 ASC | DESC);
或是
函式 OVER 視窗名 _WINDOW 視窗名 AS (| PARTITION BY 欄位名 ORDER BY 欄位名 ASC | DESC);
舉例說明
序號函式
- ROW_NUMBER()函式
能夠對資料的序號進行順序排序，在表中第一行添加一列欄位（從1—n）
#查詢每個商品分類下價格從高到低的商品資訊
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS ROW_NUM,
# 序號函式 分類 排序 添加欄位的別名
要查詢的欄位
from goods;#要查詢的表
#結果：將category_id相同的放在一起，并編號到1—n(每一類重新排序)
- RANK()函式
#查詢每個商品分類下價格從高到低的商品資訊
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS ROW_NUM,
要查詢的欄位
from goods;
#結果：將category_id相同的放在一起，并編號到1—n(每一類重新排序)
#但是當價格一樣時，序號會是一樣的，eg:1、2、2、4
- DENSE_RANK()函式
#查詢每個商品分類下價格從高到低的商品資訊
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS ROW_NUM,
要查詢的欄位
from goods;
#結果：將category_id相同的放在一起，并編號到1—n(每一類重新排序)
#但是當價格一樣時，序號會是一樣的，eg:1、2、2、3
分布函式
- PERCENT_RANK()函式
計算方式：(rank-1) / (rows-1)
#查詢表中category_id = 1的商品類別下的PERCENT_RANK的值
SELECT RANK() OVER w AS r,
PERCENT_RANK() OVER w AS pr,
要查詢的欄位
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC) AS pr;
#此題的比率是序號-1 / 總數-1；
- CUME_DIST()函式
主要是查詢小于或是等于某個數的值
#查詢goods資料表下小于或是等于當前價格的比例
SELECT CUME_DIST() OVER (PARTITION BY category_id ORDER BY price ASC) AS cd,
要查詢的欄位
FROM goods;
#當前資料小于、等于全部資料的的概率
前后函式
- LAG(expr,n)函式（前）
#查詢前一個商品的價格于當前價格的查值
SELECT 基本資訊，price - pre_price AS diff_price
FROM (SELECT 基本資訊，LAG(expr,1) OVER w AS pre_price#子查詢：多出一列是上一行的價格
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price ASC))t;
- LEAD(expr,n)函式（后）
和前面的幾乎一樣
首位函式
- FIRST_VALUE(expr)函式
FIRST_VALUE(expr)函式回傳第一個expr的值
#按照價格排序，查詢第一個商品的價格資訊
SELECT 基本資訊，FIRST_VALUE(price) OVER w AS first_price
FROM goods WINDOW w AS (ARTITION BY category_id ORDER BY price ASC)
#新增加的列是資料是該分類下的第一行價格資料
- LAST_VALUE(expr)函式
LAST_VALUE(expr)函式回傳最后一個expr的值
其他函式
- WTH_VALUE(expr,n)函式
WTH_VALUE(expr,n)回傳函式第n個expr的值
- NTILE(n)函式
NTILE(n)將每一類平均進行n組
小結
視窗函式可以對資料進行分組、排序，并且不會減少由表中的行數，對統計和排序非常有用
公用表運算式
普通公用表運算式
- 語法結果
WITH CTE名稱
AS(子查詢)
SELECT | DELETE | UPDATE 陳述句;
- 舉個例子
#查詢員工所在部門的詳細資訊
方式一：子查詢實作
SELECT *
FROM departments
WHERE department_id IN (SELECT DISTINCT department_id
FROM departments
);
方式二:CTE實作
WITH CIE_emp t2
AS (SELECT DISTINCT department_id FROM departments)#我感覺是創建了一個臨時的新表
SELECT *
FROM departments t1 JOIN CIE_emp t2#多表連接了
WHERE t1.department_id = t2.department_id;
遞回公用表運算式
遞回;自己呼叫自己
- 語法格式：
WITH RECURSIVE
CTE名稱 AS(子查詢)
SELECT | DELETE | UPDATE 陳述句;
- 舉個例子
#查詢所有的下下屬
WITH PECURSIVE cte
AS
(
SELECT 基本資訊，1 AS n FROM employees WHERE id = 100 #初始值
UNION ALL
SELECT 基本資訊，n+1 FROM employees AS a JOIN cte
ON a.id = cte.id#遞回呼叫
)
SELECT 基本資訊
FROM cte
WHERE n > 3;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/447140.html
標籤：MySQL