MySQL8.0新特性-CTE,generated column,window function



CTE-公用表表达式


CTE包括递归和非递归两种

公用表达式允许使用命令的临时结果集,这是通过允许在SELECT语句和某些其他语句前面使用WITH子句来实现的

为什么需要CTE
不能在同一查询中两次引用派生表,因为那样的话,查询会根据派生表的引用次数计算两次或多次,这会引发严重的性能问题。 使用CTE后,子查询只会计算一次

非递归CTE

公用表表达式( CTE)与派生表类似,但它的声明会放在查询块之前,而不是FROM
子句中。
派生表
SELECT ... FROM  (subquery)  AS derived,  tl
CTE
SELECT ... WI TH derived AS  (subquery)  SELECT ... FROM  deived, tl 


派生查询不能引用其他派生查询:
SELECT
FROM  (SELECT  .  .  .  FROM  . . . )  AS dl,  (SELECT  . . .  FROM  dl . .. )  AS d2
ERROR:  1146 (42S02):  Table 、db. dl’ doesn’ t exist

CTE可以引用其他CTE:
WITH dl AS  (SELECT ... FROM  . . . ) ,  d2 AS  (SELECT  . . .  FROM  dl . .. )
SELECT 
        FROM d1,d2...

递归CTE
是一种特殊的CTE,其子查询会引用自己的名字。 WITH子句必须以WITH RECURSIVE开头。递归CTE子查询包括两部分:seed查询和recursive查询,由UNION[ALL]或UNIONDISTINCT分隔
 
mysql> with recursive cte(n) as ( select 1 union all select n + 1 from cte where n<5)  select * from cte;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

假设要执行分层数据遍历,以便为每个员工生成一个组织结构图(即从CEO到每个员工的路径),也可以使用递归CTE!
mysql> create table employees_mgr(
    -> id int primary key not null,
    -> name varchar(100) not null,
    -> manager_id int null,
    -> index(manager_id),
    -> foreign key(manager_id) references employees_mgr(id));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into employees_mgr values
    -> (333,"Yasmina",NULL),
    -> (198,"john",333),
    -> (692,"Tarek",333),
    -> (29,"Pedro",198),
    -> (4610,"Sarah",29),
    -> (72,"Pierre",29),
    -> (123,"Adil",692);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from employees_mgr;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | john    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+
7 rows in set (0.00 sec)

mysql> with recursive employee_paths(id,name,path) as
    -> (
    -> select id,name,cast(id as char(200))
    -> from employees_mgr
    -> where manager_id is null
    -> union all
    -> select e.id,e.name,concat(ep.path,',',e.id)
    -> from employee_paths as ep join employees_mgr as e
    -> on ep.id=e.manager_id) select * from employee_paths order by path;
+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | john    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+
7 rows in set (0.00 sec)

mysql> explain with recursive employee_paths(id,name,path) as
    -> (
    -> select id,name,cast(id as char(200))
    -> from employees_mgr
    -> where manager_id is null
    -> union all
    -> select e.id,e.name,concat(ep.path,',',e.id)
    -> from employee_paths as ep join employees_mgr as e
    -> on ep.id=e.manager_id) select * from employee_paths order by path;
+----+-------------+---------------+------------+------+---------------+------------+---------+-------+------+----------+------------------------+
| id | select_type | table         | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                  |
+----+-------------+---------------+------------+------+---------------+------------+---------+-------+------+----------+------------------------+
|  1 | PRIMARY     | <derived2>    | NULL       | ALL  | NULL          | NULL       | NULL    | NULL  |    4 |   100.00 | Using filesort         |
|  2 | DERIVED     | employees_mgr | NULL       | ref  | manager_id    | manager_id | 5       | const |    1 |   100.00 | Using index condition  |
|  3 | UNION       | ep            | NULL       | ALL  | NULL          | NULL       | NULL    | NULL  |    2 |   100.00 | Recursive; Using where |
|  3 | UNION       | e             | NULL       | ref  | manager_id    | manager_id | 5       | ep.id |    1 |   100.00 | NULL                   |
+----+-------------+---------------+------------+------+---------------+------------+---------+-------+------+----------+------------------------+
4 rows in set, 1 warning (0.00 sec)


生成列(generated column):

应用场景:
1. 创建表时
2. 已有表时

生成列(generated column)的值是根据列定义中包含的表达式计算得出的。 生成列包含下面两种类型:

virtual(虚拟):当从表中读取记录时,将动态计算该列。
stored(存储):当向表中写入新记录时,将计算该列并将其存储为常规列。
        virtual生成列比stored生成列更有用,因为一个虚拟的列不占用任何存储空间。你可以使用触发器模拟stored生成列的行为。


1. 创建表时
        假设你的应用程序从t_employees_generated表中检索数据时,使用full_name表示concat(first_name, ' ', last_name),而不是使用表达式来表示,从而实现虚拟列实时计算full_name。

# 创建测试表
mysql> create table if not exists t_employees_generated (
  emp_no int(11) not null,
  birth_date date not null,
  first_name varchar(14) not null,
  last_name varchar(16) not null,
  gender enum('M', 'F') not null,
  hire_date date not null,
 full_name varchar(30) as (concat(first_name, ' ', last_name)),
  primary key (emp_no),
  key name (first_name, last_name)
  ) engine=innodb default charset=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.04 sec)
 
 请注意,应该根据虚拟列修改插入语句。你可以这样使用full insert:

# 执行插入
mysql> insert into  t_employees_generated  (emp_no, birth_date, first_name, last_name, gender, hire_date) values (123456, '1987-10-02', 'ABC', 'XYZ', 'F', '2008-07-28');
Query OK, 1 row affected (0.11 sec)


# 验证数据
mysql> select * from t_employees_generated where emp_no = '123456';
+--------+------------+------------+-----------+--------+------------+-----------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | full_name |
+--------+------------+------------+-----------+--------+------------+-----------+
| 123456 | 1987-10-02 | ABC        | XYZ       | F      | 2008-07-28 | ABC XYZ   |
+--------+------------+------------+-----------+--------+------------+-----------+
1 row in set (0.00 sec)
 
如果要在INSERT语句中包含full_name,就只能将其指定为DEFAULT

# 执行插入
mysql> insert into t_employees_generated  (emp_no, birth_date, first_name, last_name, gender, hire_date, full_name)  values  (123457, '1987-10-02', 'ABC', 'XYZ', 'F', '2008-07-28', DEFAULT);
Query OK, 1 row affected (0.01 sec)

# 验证数据
mysql> select * from t_employees_generated where emp_no = '123457';
+--------+------------+------------+-----------+--------+------------+-----------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | full_name |
+--------+------------+------------+-----------+--------+------------+-----------+
| 123457 | 1987-10-02 | ABC        | XYZ       | F      | 2008-07-28 | ABC XYZ   |
+--------+------------+------------+-----------+--------+------------+-----------+
1 row in set (0.00 sec)
 
 其他值都会引发ERROR 3105 (HY000):错误,不允许在t_employees_generated表中为生成的列full_name指定值:


mysql> insert into  t_employees_generated  (emp_no, birth_date, first_name, last_name, gender, hire_date, full_name)  values (123458, '1987-10-02', 'ABC', 'XYZ', 'F', '2008-07-28', 'TEST');
ERROR 3105 (HY000): The value specified for generated column 'full_name' in table 't_employees_generated' is not allowed.
 
2. 已有表时
        如果你已经创建了表并希望添加新的生成列,请执行ALTER TABLE语句
# 修改表结构 - 新增生成列
mysql> alter table t_employees_generated add hire_date_year year as (year(hire_date)) virtual;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0


# 验证数据
mysql> select * from t_employees_generated where emp_no = '123456';
+--------+------------+------------+-----------+--------+------------+-----------+----------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | full_name | hire_date_year |
+--------+------------+------------+-----------+--------+------------+-----------+----------------+
| 123456 | 1987-10-02 | ABC        | XYZ       | F      | 2008-07-28 | ABC XYZ   |           2008 |
+--------+------------+------------+-----------+--------+------------+-----------+----------------+
1 row in set (0.00 sec)


# 查看表结构
mysql> desc employees.t_employees_generated;
+----------------+---------------+------+-----+---------+-------------------+
| Field          | Type          | Null | Key | Default | Extra             |
+----------------+---------------+------+-----+---------+-------------------+
| emp_no         | int(11)       | NO   | PRI | NULL    |                   |
| birth_date     | date          | NO   |     | NULL    |                   |
| first_name     | varchar(14)   | NO   | MUL | NULL    |                   |
| last_name      | varchar(16)   | NO   |     | NULL    |                   |
| gender         | enum('M','F') | NO   |     | NULL    |                   |
| hire_date      | date          | NO   |     | NULL    |                   |
| full_name      | varchar(30)   | YES  |     | NULL    | VIRTUAL GENERATED |
| hire_date_year | year(4)       | YES  |     | NULL    | VIRTUAL GENERATED |
+----------------+---------------+------+-----+---------+-------------------+
8 rows in set (0.00 sec)


窗口函数:
对于查询中的每一行,可以使用窗口函数,利用与该行相关的行执行计算。 这是通过使用OVER和WINDOW子句来完成的。
        以下是可以执行计算的函数。

ROW_NUMBER():分区内当前行的编号。
RANK():分区中当前行的等级(有间隔)。
DENSE_RANK():分区内当前行的等级(无间隔)。
PERCENT_RANK():百分比排名值。
FIRST_VALUE():窗口帧中第一行的参数值。
LAST_VALUE():窗口帧中最末行的参数值。
LEAD():领先于分区内当前行的那一行的参数值。
LAG():落后于分区内当前行的那一行的参数值。
NTH_VALUE():窗口帧中的第 n 行的参数值。
NTILE():分区内当前行的桶的编号。
COME_DIST():累积分布值。

准备工作
        为了使示例有效,请先添加hire_date_year、full_name虚拟列
参考:MySQL - MySQL 8.0进阶操作:生成列(generated column)

# 新增虚拟列
mysql> alter table employees.employees add hire_date_year year as (year(hire_date)) virtual;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table employees.employees add full_name varchar(30) as (concat(first_name, ' ', last_name)) virtual;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employees.employees;
+----------------+---------------+------+-----+---------+-------------------+
| Field          | Type          | Null | Key | Default | Extra             |
+----------------+---------------+------+-----+---------+-------------------+
| emp_no         | int(11)       | NO   | PRI | NULL    |                   |
| birth_date     | date          | NO   |     | NULL    |                   |
| first_name     | varchar(14)   | NO   |     | NULL    |                   |
| last_name      | varchar(16)   | NO   |     | NULL    |                   |
| gender         | enum('M','F') | NO   |     | NULL    |                   |
| hire_date      | date          | NO   |     | NULL    |                   |
| hire_date_year | year(4)       | YES  |     | NULL    | VIRTUAL GENERATED |
| full_name      | varchar(30)   | YES  |     | NULL    | VIRTUAL GENERATED |
+----------------+---------------+------+-----+---------+-------------------+
8 rows in set (0.00 sec)
 

分割结果
可以在OVER子句中分割结果。 假设你想、找出每年的工资排列情况

partition by:分组
order by:排序


mysql> select full_name, gender,row_number()over(partition by gender order by hire_date_year asc) rn  from employees.employees limit 3;
+-------------------+--------+----+
| full_name         | gender | rn |
+-------------------+--------+----+
| Mizuhito Yamaashi | M      |  1 |
| Moon Muhlberg     | M      |  2 |
| Randi Tagansky    | M      |  3 |
+-------------------+--------+----+
3 rows in set (0.84 sec)

行号:
mysql> select concat(first_name," ",last_name) as full_name,salary,row_number() over (order by salary desc) as 'Rank' from employees join salaries on salaries.emp_no=employees.emp_no limit 10;
+-------------------+--------+------+
| full_name         | salary | Rank |
+-------------------+--------+------+
| Tokuyasu Pesch    | 158220 |    1 |
| Tokuyasu Pesch    | 157821 |    2 |
| Honesty Mukaidono | 156286 |    3 |
| Xiahua Whitcomb   | 155709 |    4 |
| Sanjai Luders     | 155513 |    5 |
| Tsutomu Alameldin | 155377 |    6 |
| Tsutomu Alameldin | 155190 |    7 |
| Tsutomu Alameldin | 154888 |    8 |
| Tsutomu Alameldin | 154885 |    9 |
| Willard Baca      | 154459 |   10 |
+-------------------+--------+------+
10 rows in set (9.96 sec)

命名窗口
可以根据需要对一个窗口命名,并多次使用它,元须每次都重新定义:
mysql> select hire_date_year,salary,rank() over w as 'Rank' from employees join salaries on salaries.emp_no=employees.emp_no window w as (partition by hire_date_year order by salary desc) order by salary desc limit 10;
+----------------+--------+------+
| hire_date_year | salary | Rank |
+----------------+--------+------+
|           1985 | 158220 |    1 |
|           1985 | 157821 |    2 |
|           1986 | 156286 |    1 |
|           1985 | 155709 |    3 |
|           1987 | 155513 |    1 |
|           1985 | 155377 |    4 |
|           1985 | 155190 |    5 |
|           1985 | 154888 |    6 |
|           1985 | 154885 |    7 |
|           1985 | 154459 |    8 |
+----------------+--------+------+
10 rows in set (10.27 sec)

第一个、最后一个和第n个值
你可以选择窗口结果中的第一个、最后一个和第n个值。如果该行不存在,则返回NULL。
假设你想从窗口中找到第一个、最后一个和第3个值,代码如下:
mysql> select hire_date_year,salary,rank() over w as 'Rank',
    -> first_value(salary) over w as 'first',
    -> NTH_VALUE(salary,3) over w as 'third',
    -> LAST_value(salary) over w as 'last'
    -> from employees join salaries on salaries.emp_no=employees.emp_no window w as (partition by hire_date_year order by  salary desc) order by salary desc limit 10;
+----------------+--------+------+--------+--------+--------+
| hire_date_year | salary | Rank | first  | third  | last   |
+----------------+--------+------+--------+--------+--------+
|           1985 | 158220 |    1 | 158220 |   NULL | 158220 |
|           1985 | 157821 |    2 | 158220 |   NULL | 157821 |
|           1986 | 156286 |    1 | 156286 |   NULL | 156286 |
|           1985 | 155709 |    3 | 158220 | 155709 | 155709 |
|           1987 | 155513 |    1 | 155513 |   NULL | 155513 |
|           1985 | 155377 |    4 | 158220 | 155709 | 155377 |
|           1985 | 155190 |    5 | 158220 | 155709 | 155190 |
|           1985 | 154888 |    6 | 158220 | 155709 | 154888 |
|           1985 | 154885 |    7 | 158220 | 155709 | 154885 |
|           1985 | 154459 |    8 | 158220 | 155709 | 154459 |
+----------------+--------+------+--------+--------+--------+
10 rows in set (19.89 sec)

分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS