介绍PostgreSQL CTE(common table expressions)

介绍PostgreSQL CTE(common table expressions)

本文我们学习如何使用PostgreSQL CTE(common table expressions)简化复杂查询。

介绍 common table expressions

PostgreSQL CTE(common table expressions) 是临时结果，可以在其他SQL中引用，如SELECT, INSERT, UPDATE 和 DELETE，其仅存在于查询执行期间。下面显示创建CTE的语法：

WITH cte_name (column_list) AS (
    CTE_query_definition 
)
statement;

• 首先，指定CTE的名称，接着是可选的列字段列表
• 其次，在with子句体内，指定查询作为返回结果集，如果没有显示指定列字段列表，则CTE_query_definition 的select 字段列表将作为CTE的字段列表。
• 第三，在其他SQL语句中可以向使用表或视图一样使用CTE，语句包括SELECT, INSERT, UPDATE 和 DELETE。

CTE一般用于简化复杂join和子查询。

示例

下面通过示例更好说明CTE用法。我们使用sample 示例数据库 中的 film 和 rental 表。

请看示例：

WITH cte_film AS (
    SELECT film_id, title,
            (CASE 
                WHEN length < 30 THEN 'Short'
                WHEN length >= 30 AND length < 90 THEN 'Medium'
                WHEN length > 90 THEN 'Long'
            END) length    
    FROM film
)

SELECT film_id, title,length
FROM cte_film
WHERE length = 'Long'
ORDER BY  title; 

我们首先使用with子句定义CTE，命名为cte_film.

WITH cte_film AS (
    SELECT film_id, title,
            (CASE 
                WHEN length < 30 THEN 'Short'
                WHEN length >= 30 AND length < 90 THEN 'Medium'
                WHEN length > 90 THEN 'Long'
            END) length    
    FROM film
)

CTE包括两个部分：

• 第一部分定义CTE的名称，这里是cte_film.
• 第二部分定义一个select 语句用于填充表达式.

接着我们在select中使用cte_film，用于返回length 值为 ‘Long’的记录.

join子句中使用CTE

在下面示例中，我们使用rental和staff表，下面语句说明如何join CTE和表：

WITH cte_rental AS (
    SELECT staff_id, COUNT(rental_id) rental_count
    FROM   rental
    GROUP  BY staff_id
)
SELECT s.staff_id,
    first_name,
    last_name,
    rental_count
FROM staff s
    INNER JOIN cte_rental USING (staff_id); 

• 首先，我们定义CTE,其staff id 和 租借数量
• 然后，使用 staff_id join staff表 和 CTE

和窗口函数一起使用CTE

下面语句描述如何和rank()窗口函数一起使用CTE：

WITH cte_film AS  (
    SELECT film_id, title, rating, length,
        RANK() OVER ( PARTITION BY rating ORDER BY length DESC) length_rank
    FROM  film
)
SELECT *
FROM cte_film
WHERE length_rank = 1;

• 首先，我们定义CTE，返回film信息及每个等级根据长度排名信息，别名为length_rank
• 其次，选择length_rank为1的film

总结

使用CTE有以下优点：

• 提升复杂查询的可读性。我们可以使用CTE以更加可读的方式组织复杂查询
• 能够创建递归查询。递归查询是引用自身，当需要查询层次数据如组织或物料清单信息时，使用递归查询很方便。
• 和窗口函数一起使用。通过和窗口函数一起创建初始结果集，然后使用select对结果进一步进行处理。