PG_CTE

发表于 本文字数： 1.4k 阅读时长 ≈ 1 分钟

[TOC]

CTE（Common Table Expressions）

简单讲，CTE就是日常SQL中出现的with语句，其原理就是通过提前将数据查询出来后作为临时结果集使用，可以与SELECT \ INSERT \ UPDATE \ DELETE的SQL连用。

优点

  1. 可读性强
    • CTE 允许你将复杂的查询拆分成易于理解和管理的块。这使得查询更易于阅读、理解和维护。
  2. 重用性
    • CTE 可以在一个查询中多次引用，这使得可以将复杂的逻辑组件分解成可重复使用的部分。
  3. 递归查询
    • CTE 允许你执行递归查询，这是一种对于层次化数据结构（如组织结构或树形结构）非常有用的功能。
  4. 优化器支持
    • PostgreSQL 的查询优化器可以对 CTE 进行优化，以确保最佳执行计划。

缺点

  1. 性能开销
    • 在某些情况下，使用 CTE 可能会导致性能开销。在处理大量数据时，可能会出现性能下降。
  2. 可读性降低
    • 尽管 CTE 可以提高可读性，但如果不正确使用，可能会导致查询变得更难理解。特别是在多个 CTE 之间建立复杂的关系时。
  3. 内存消耗
    • CTE 通常需要在内存中存储临时结果集，因此对于大型数据集可能会导致内存消耗较高。
  4. 不能在索引中使用
    • 不能在 CTE 中创建索引，这可能会导致在某些情况下查询性能下降。
  5. 递归查询潜在的性能问题
    • 对于大型或者深度很深的递归查询，可能会导致性能问题。

示例

  1. INSERT - 插入

    1
    2
    3
    4
    5
    6
    7
    WITH r AS （
      SELECT code, name
      FROM t1
    )
    INSERT INTO t2(code,name)
    SELECT code,name
    FROM r;

  2. UPDATE - 更新

    1
    2
    3
    4
    5
    6
    7
    8
    WITH r AS （
      SELECT code, name
      FROM t1
    )
    UPDATE t2 
    SET t2.name = t1.name
    FROM t1
    WHERE t1.code = t2.code;

  3. SELECT - 查询

    1
    2
    3
    4
    5
    6
    7
    WITH r AS （
      SELECT code, name
      FROM t1
    )
    SELECT t2.*
    FROM t2
    WHERE EXISTS (SELECT 1 FROM t1 where t1.code = t2.code);

  4. DELETE - 删除

    1
    2
    3
    4
    5
    6
    WITH r AS （
      SELECT code, name
      FROM t1
    )
    DELETE FROM t2
    WHERE code IN (SELECT code FROM t1);

  5. RECURSIVE - 递归查询

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    WITH RECURSIVE r AS (
        SELECT id, name, parent_id, 1 as level
        FROM organization
        WHERE parent_id IS NULL

        UNION ALL

        SELECT o.id, o.name, o.parent_id, oh.level + 1
        FROM organization o
        JOIN r oh ON o.parent_id = oh.id
    )
    SELECT id, name, level
    FROM r;

WITH在一定程度能 解决数据库查询上的一些问题，但并不是每次适合，需要对照上述的优缺点，自行判断是否需要使用。