CTE查询

专栏内容

  • postgresql内核源码分析
  • 手写数据库toadb
  • 并发编程

开源贡献

  • toadb开源库

个人主页:我的主页
管理社区:开源数据库
座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物.

系列文章

  • 入门准备
  • postgrersql基础架构
  • 快速使用
  • 初始化集群
  • 数据库服务管理
  • psql客户端使用
  • pgAdmin图形化客户端
  • 数据库的使用
  • 创建数据库
  • 数据库操作
  • 表的使用
  • 表的创建
  • 表的操作
  • 数据查询
  • 数据查询
  • 多表联合查询
  • 数据操作
  • 插入数据的方式

文章目录

  • CTE查询
  • 系列文章
  • 前言
  • 概述
  • CTE语法介绍
  • 数据准备
  • 基本应用
  • 多个CTE
    • 并行的 CTE
    • 递进的 CTE
  • 递归查询
    • 简单递归查询
  • 总结
  • 结尾

前言


postgresql 数据库是一款通用的关系型数据,在开源数据库中能与商业数据媲美,在业界也越来越流行。

因为是开源数据库,不仅公开源码,还有很多使用案例,好用的插件,所以它的慢慢变成了数据库的先驱和标准,通过postgresql可以很好从使用到原理,彻底搞懂;

如果是学习编程,也可以学到丰富的编程知识,数据结构,编程技巧,它里面还有很多精妙的架构设计,分层思想,可以灵活定制的思想。

本专栏主要介绍postgresql 入门使用,数据库维护管理,通过这些使用来了解数据库原理,慢慢了解postgresql是什么样的数据库,能做那些事情,以及如何做好服务,最关键的是这些知识都是面试的必备项。

概述


在一些分析型业务中,通常会遇到一些非常复杂的SQL语句,多个子查询的联合,此时可以使用 common table expression (CTE) 方式的SQL写法,来让SQL更加简化和易于理解,本文就来分享一下postgresql中的CTE语言的写法,以及使用案例。

CTE语法介绍


common table expression (CTE), 翻译过来就是通用表表达式,它将子查询创建成临时表的形式,通过with子句嵌套在查询语句中,在主查询中引用这些临时表就可以,简化了普通SQL的写法,更加易于理解和维护。

CTE的SQL形式如下:

WITH cte_name (column1, column2, ...) AS (-- 临时表查询SELECT ...)-- 主查询,使用CTESELECT ...FROM cte_name;

它主要包括以下几部分:

  • with子句,该关键字指示CTE语句的开始;
  • CTE表达式名, 指定CTE名字,名字有效范围是整个语句,所以整个语句中不能重名;
  • CTE目标列名,这是可选项,不指定时,默认为cte查询语句的select目标列;
  • CTE查询语句,它包括在AS关键字对应的括号中;可以是任意的SQL语句;
  • 主查询语句,主查询语句得到整个SQL的最终结果,它可以像引用普通表一样来引用CTE表达式名,这样就可以将复杂SQL拆解成几个CTE表达式,然后再引用;

数据准备


下面针对CTE的应用场景进行举例说明,为了方便举例,先创建以下几张表。

  1. 产品表 (Products)

这个表用于存储产品的信息。

字段类型描述
product_idINT PRIMARY KEY产品的唯一标识符
product_nameVARCHAR产品的名称
priceDECIMAL产品的价格
categoryVARCHAR产品的类别
  1. 订单表 (Orders)

这个表用于存储订单的信息。

字段类型描述
order_idINT PRIMARY KEY订单的唯一标识符
product_idINT与产品表关联的外键
quantityINT订购的产品数量
regionVARCHAR订单所在的区域
order_dateDATE订单的日期
-- 创建产品表CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(255) NOT NULL,price DECIMAL(10, 2) NOT NULL,category VARCHAR(255));-- 创建订单表CREATE TABLE orders (order_id INT PRIMARY KEY,product_id INT,quantity INT NOT NULL,region VARCHAR(255) NOT NULL,order_date DATE NOT NULL,FOREIGN KEY (product_id) REFERENCES Products(product_id));

基本应用


当我们查询每个区域的销量时,可以使用如下CTE语句

with region_sales as (SELECT region, SUM(quantity) AS total_salesFROM ordersGROUP BY region)select * from region_sales;

在这个例子中,CTE查询语句从基本表orders中得到每个区域的销量,在主查询中就可以直接引用CTE临时表了。

多个CTE


可以包含多个CTE语句,也就是CTEs,而且CTE之间也可以进行引用,递进的进行查询。

并行的 CTE

当我们想要查询销量最好的区域和销量最好的产品,那么可以用下面的语句。

with region_sales as (SELECT region, SUM(quantity) AS total_salesFROM ordersGROUP BY region order by total_sales desc;),procduct_sales as (select products.product_name, sum(quantity) as total_sales from orders, products where orders.product_id = products.product_idgroup by orders.product_id,products.product_nameorder by total_sales desc;)select (selectregion from region_sales limit 1) as top_region,(select product_name from procduct_sales limit 1) as top_product ;
  • 分别用两个CTE查询得到区域销量的排名和产品销量的排名;
  • 在主查询中,得到区域销量第一和产品销量的第一;

递进的 CTE

不同的CTE之间还可以引用,下面我们计算销售额最高的区域来演示一下:

with region_amount as (select region, orders.quantity * products.price as amount from orders, products where orders.product_id = products.product_id ),top_region as (select region, sum(amount) total_amount from region_amount group by region)select * from top_region order by total_amount desc;
  • 第一个CTE查询得到按区域分类的各产品销售额,但是区域有重复行;第二个CTE进一步进行计算,按区域计算得到销售总额;
  • 主查询得到全部区域的一个销售总额排序;

递归查询


有些时候,我们需要递归处理一些查询,此时就要用到WITH RECURSIVE这个关键字,它的SQL语句形式如下:

WITH RECURSIVE cte_name (column1, column2, ...)AS(-- 非递归项SELECT select_list FROM table1 WHERE conditionUNION [ALL]-- 递归语句SELECT select_list FROM cte_name WHERE recursive_condition) SELECT * FROM cte_name;

递归的with语句形式与上面的非递归类型,只是递归的CTE语句中包含两部分:

  • 非递归项,这是第一次递归的结果,也就是起始值;
  • 递归项,开始n次递归调用,它调用CTE自身;直到查询返回值为空时,结束递归;并将n次查询结果通过union或union all合并形成最终结果集;
  • 前两者通过 union 或 union all进行合并,其中union的结果集会去掉重复的行;

递归的过程中,会产生临时表,存放本次递归的结果,下次递归时就从这个表进行查询,当表为空时就停止递归。

简单递归查询

下面我们来看一个简单的例子;

计算1到100的和,这是经常循环改递归的一个算法,这里用CTE查询来实现如下:

WITH RECURSIVE t(n) AS (VALUES (1)UNION ALLSELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;

当然它最常用的是进行图的遍历递归或者树的遍历递归查询。

总结


本文主要分享了CTE语句的基础用法,包括单个CTE,多个CTE,CTE之间引用,以及CTE中的递归用法,熟悉CTE的用法之后,对于之前复杂的SQL就可以改造成多个CTE的形式,更加易于理解和维护,大家可以尝试一下。

结尾

非常感谢大家的支持,在浏览的同时别忘了留下您宝贵的评论,如果觉得值得鼓励,请点赞,收藏,我会更加努力!

作者邮箱:study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出,互相学习。

注:未经同意,不得转载!