通用/公用表表达式CTE

最新推荐文章于 2025-03-27 10:32:00 发布

谢轲

最新推荐文章于 2025-03-27 10:32:00 发布

阅读量768

点赞数 8

文章标签： sql

本文链接：https://blog.youkuaiyun.com/qq_39504802/article/details/144027972

版权

前端时段在一些遇到的SQL问题记录-优快云博客中写过CTE，还写错了写成了CET，前两天要用，发现不太对劲（具体怎么不对帖子最后会写），于是今天研究了下。为了内容完整，我将内容一并放在这个帖子中。

SQL 中的 CTE（Common Table Expression，公用表表达式）是一种临时的结果集，它在执行 SQL 查询时可以像表一样被引用。CTE 的作用主要是提高查询的可读性和可维护性，特别是在处理复杂查询时。CTE 是通过 WITH 关键字来定义的。

CTE 的基本结构

CTE 由 WITH 关键字开始，后面跟着定义的 CTE 名称和查询。CTE 可以被视为一个临时的视图，只在当前查询的范围内有效。

基本语法：

WITH cte_name AS (
    -- CTE 的查询逻辑
    SELECT ...
)
SELECT ...
FROM cte_name;

CTE 的作用

提高可读性： CTE 使复杂的 SQL 查询更加模块化。通过将复杂的子查询提取到 CTE 中，主查询变得更加简洁易读。
复用查询结果：如果在一个查询中多次使用相同的子查询逻辑，可以将其提取到 CTE 中，从而避免重复书写相同的查询代码。
递归查询： CTE 还支持递归查询（使用 WITH RECURSIVE）。递归查询特别适用于树形数据（如组织结构、目录结构等）和需要生成序列的数据（如时间序列）。

CTE 的限制

作用范围有限：CTE 仅在定义它的查询语句中有效，不能跨查询使用。
性能影响：在某些情况下，使用 CTE 可能会导致性能问题，特别是在递归查询中，因为每次递归都需要重新执行 CTE。如果查询非常复杂或递归深度较大，可能会导致性能下降。

CTE 与子查询的对比

CTE 和子查询在某些方面是相似的，但它们之间有一些区别：

子查询：通常嵌套在主查询的 SELECT、FROM 或 WHERE 子句中，适用于一次性、简单的查询。
CTE：是将查询逻辑分离出来，写在查询顶部，可以多次引用，尤其适用于复杂查询或递归查询。CTE 的可读性和可维护性比嵌套子查询要好。

循环CTE

with RECURSIVE time_series as (
	select timestamp '2024-10-01 00:00:00' as date_time  --初始条件
	union all
	select date_time + interval '15' minute  --循环条件
	from time_series
	where date_time + interval '15' minute <= timestamp '2024-10-31 23:59:00'  --终止条件
)
select date_time;

--也可以将这个结果集写道一张临时表中，将上述select date_time替换为下面内容
select date_time 
into TEMPORARY table temp_time_series
from time_series;

CTE其实和子查询有些类似，但更加灵活。

循环CTE在原本的定义上增加了RECURSIVE关键字，表示循环CTE。

多说一些吧，

timestamp是SQL 数据类型之一，表示时间戳，即日期和时间的组合。它通常用于存储精确到秒的日期和时间数据。在这段SQL中，timestamp指明后面的常量值是一个时间戳类型的常量。

interval 是一种用于表示时间间隔的 SQL 类型。它允许你在日期和时间上执行加法和减法操作，例如增加天数、小时、分钟等。

into TEMPORARY table temp_time_series的作用是将查询结果存储到一个临时表中，该表只在当前会话（连接）期间存在，关闭数据库连接后会自动销毁。

定义多个CTE

WITH cte_test1 AS (
    -- CTE 的查询逻辑
    SELECT ...
),cte_test2 as (
    -- CTE 的查询逻辑
    SELECT * from cte_test...
)
SELECT ...
FROM cte_test2;

可同时定义多个CTE，中间用逗号隔开，可在之后的CTE中引用之前定义的CTE。

CTE实际案例

我需要说明的是，在下面的例子中虽然可以使用CTE来实现问题的解决方案，但还需要其他知识才能顺利通过，其实遇到的任何困难一般不会顺利解决，生活就是这样啊，艰难苦涩又漫长。

假设有一张表a，表示安保人员巡查楼的时间，每15分钟巡查，且每次巡查费用为5元，每次巡查会在表中记录一条数据。现在我们想查看某个安保人员一天的巡查记录，看他是否有漏掉没有巡检的时date_time

name	date_time	value
apf	2024-06-06 12:15:00	5
xk	2024-06-06 12:30:00	5
apf	2024-06-06 12:45:00	5
apf	2024-06-06 13:15:00	5

如果直接查询，查到的只是某个人员当天的巡检记录，还需要自己补全没有巡检的时间节点。

用循环CTE做一张时间戳步进表，并将结果存入一张临时表中。

--请注意，以下代码为金仓数据库sql，mysql和这个sql语法大差不差，去掉15的冒号即可。
with RECURSIVE time_series as (
	select timestamp '2022-11-23 00:00:00' as date_time
	union all
	select date_time + interval '15' minute 
	from time_series
	where date_time + interval '15' minute <= timestamp '2022-11-23 23:59:00'
)
select date_time 
into TEMPORARY table temp_time_series
from time_series;

使用普通CTE，将表a的name字段与时间戳表求笛卡尔积，得到每一位保安的全部时间戳。之后定义第二个CTE表达式，将原表的value字段通过左连接恢复到笛卡尔积表中。

--使用原表关联，补全所有name的15分钟时间戳，name空缺的value使用0补全
with timeSeries as (
select distinct a.name,a.date_time  
--数据表
from a
cross join temp_time_series b 
--查询条件，也可不写，对全表所有name求笛卡尔积
where a.name in(……)

),filledData as (
select ts.name,ts.date_time,coalesce(a.value,0) 
from timeSeries ts left join a 
on ts.dev_id = a.dev_id and ts.date_time = a.data_time
)
select * from filledData 
order by name,date_time;

笛卡尔积：字段a与字段b的所有可能的有序对。也就是字段a和所有的字段b连接，依次类推。实现所有保安名称与时间戳的配对。

coalesce函数：该函书可包含多个参数，他将从前到后依次检查每个参数，并返回第一个不为null的值。这个函数放在这里其实有点脱裤子放屁，也可以直接写 0.0 as value。该函数对空字符串等一些特殊null的态度未知，需要你自己探索。