【产品调研】关于“CTE” 的用法调研，参与即送50积分

Billmay表妹 · 2021 年5 月 14 日 11:39

CTE 即将完成开发，

为了更好地优化 CTE 相关功能，

我们希望 TiDB 社区的伙伴们，可以助工程师一臂之力，

完成本项调研，一起建设 CTE ，

请在以下评论区完整地列出您关于 CTE 的用法，

以便于我们后续更好地做相关的功能优化。

请在帖子评论告知以下情况：

（1）使用 With 子句的目的。（常见的目的有树形查询、化简 SQL、提高性能等）
（2）使用的类型。（非递归的、递归的）
（3）With 中是否包含聚合函数，窗口函数等复杂查询。（最好能简单说明下）
（3）数据规模。（可以说明下数量级，例如是百万行、千万行）
（4）期望的执行时间。（多少毫秒，多少秒等）
（5）期望的资源占用。（内存，磁盘等有什么预期）
（6）是否会考虑和 TiFlash 一起使用？

完成答复6个问题的伙伴，

即可获得50积分，

如果您不想公开答复的内容，

您也可以私信给我，同样也能获得积分哟~

hooopo · 2021 年5 月 14 日 17:09

简化查询，增加SQL可读性和可维护性。我的理解CTE的作用相当于编程语言里的临时变量。起到复用和增加可读性的作用。在SQL里，如果没有CTE，很多复杂查询就只能用子查询来完成，嵌套层次太多不容易阅读和维护。有了CTE的支持，可以把一些嵌套层次很深的查询展开。例如，discourse里的一个报表查询：

      WITH mods AS (
      SELECT
      id AS user_id,
      username_lower AS username,
      uploaded_avatar_id
      FROM users u
      WHERE u.moderator = 'true'
      AND u.id > 0
      ),
      time_read AS (
      SELECT SUM(uv.time_read) AS time_read,
      uv.user_id
      FROM mods m
      JOIN user_visits uv
      ON m.user_id = uv.user_id
      WHERE uv.visited_at >= '#{report.start_date}'
      AND uv.visited_at <= '#{report.end_date}'
      GROUP BY uv.user_id
      ),
      flag_count AS (
          WITH period_actions AS (
          SELECT agreed_by_id,
          disagreed_by_id
          FROM post_actions
          WHERE post_action_type_id IN (#{PostActionType.flag_types_without_custom.values.join(',')})
          AND created_at >= '#{report.start_date}'
          AND created_at <= '#{report.end_date}'
          ),
          agreed_flags AS (
          SELECT pa.agreed_by_id AS user_id,
          COUNT(*) AS flag_count
          FROM mods m
          JOIN period_actions pa
          ON pa.agreed_by_id = m.user_id
          GROUP BY agreed_by_id
          ),
          disagreed_flags AS (
          SELECT pa.disagreed_by_id AS user_id,
          COUNT(*) AS flag_count
          FROM mods m
          JOIN period_actions pa
          ON pa.disagreed_by_id = m.user_id
          GROUP BY disagreed_by_id
          )
      SELECT
      COALESCE(af.user_id, df.user_id) AS user_id,
      COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count
      FROM agreed_flags af
      FULL OUTER JOIN disagreed_flags df
      ON df.user_id = af.user_id
      ),
      revision_count AS (
      SELECT pr.user_id,
      COUNT(*) AS revision_count
      FROM mods m
      JOIN post_revisions pr
      ON pr.user_id = m.user_id
      JOIN posts p
      ON p.id = pr.post_id
      WHERE pr.created_at >= '#{report.start_date}'
      AND pr.created_at <= '#{report.end_date}'
      AND p.user_id <> pr.user_id
      GROUP BY pr.user_id
      ),
      topic_count AS (
      SELECT t.user_id,
      COUNT(*) AS topic_count
      FROM mods m
      JOIN topics t
      ON t.user_id = m.user_id
      WHERE t.archetype = 'regular'
      AND t.created_at >= '#{report.start_date}'
      AND t.created_at <= '#{report.end_date}'
      GROUP BY t.user_id
      ),
      post_count AS (
      SELECT p.user_id,
      COUNT(*) AS post_count
      FROM mods m
      JOIN posts p
      ON p.user_id = m.user_id
      JOIN topics t
      ON t.id = p.topic_id
      WHERE t.archetype = 'regular'
      AND p.created_at >= '#{report.start_date}'
      AND p.created_at <= '#{report.end_date}'
      GROUP BY p.user_id
      ),
      pm_count AS (
      SELECT p.user_id,
      COUNT(*) AS pm_count
      FROM mods m
      JOIN posts p
      ON p.user_id = m.user_id
      JOIN topics t
      ON t.id = p.topic_id
      WHERE t.archetype = 'private_message'
      AND p.created_at >= '#{report.start_date}'
      AND p.created_at <= '#{report.end_date}'
      GROUP BY p.user_id
      )
      SELECT
      m.user_id,
      m.username,
      m.uploaded_avatar_id,
      tr.time_read,
      fc.flag_count,
      rc.revision_count,
      tc.topic_count,
      pc.post_count,
      pmc.pm_count
      FROM mods m
      LEFT JOIN time_read tr ON tr.user_id = m.user_id
      LEFT JOIN flag_count fc ON fc.user_id = m.user_id
      LEFT JOIN revision_count rc ON rc.user_id = m.user_id
      LEFT JOIN topic_count tc ON tc.user_id = m.user_id
      LEFT JOIN post_count pc ON pc.user_id = m.user_id
      LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id
      ORDER BY m.username

ref: https://github.com/discourse/discourse/blob/dc6b547ed89f652b5406489d76140b76cf8e0d1d/app/models/concerns/reports/moderators_activity.rb

期待的CTE是和子查询有同样能力的一个功能，所以聚合和窗口函数这些一定是需要的。CTE使用场景也是在分析类查询中，会考虑和TiFlash一起用。顺便说一下之前在PG上使用CTE的一些坑，PG12之前的CTE默认行为是物化结果，导致执行计划和普通子查询会产生差异，比如不能push down：

# 由于PG12之前，CTE默认物化结果，这个执行计划会是一个全表扫
WITH cte AS (
  SELECT * FROM foo
)
SELECT * FROM cte WHERE id = 500000;

默认物化这个行为会导致PG12之前的CTE在一些场景相比子查询损失性能，不得不牺牲可读性继续用子查询。

PG12之后增加了一个NOT MATERIALIZED标记，解决了这个问题：

# PG12之后，外层条件可以下推了
WITH cte AS NOT MATERIALIZED (
  SELECT * FROM foo
)
SELECT * FROM cte WHERE id = 500000;

Oracle的默认行为和PG是相反的，默认不物化CTE，但可以加hint来强制物化：

# Oracle加hint之后强制物化，条件不下推
 WITH cte AS (
    SELECT /*+ MATERIALIZE */ * FROM foo
)
SELECT * FROM cte WHERE id = 500000;

Kongdom · 2021 年5 月 15 日 02:32

（1）使用 With 子句的目的：树形查询、化简 SQL、提高性能
（2）使用的类型：递归的
（3）With 中是否包含聚合函数，窗口函数等复杂查询：包含
（3）数据规模：千万级
（4）期望的执行时间：2s以内
（5）期望的资源占用：暂无，越低越好
（6）是否会考虑和 TiFlash 一起使用：肯定会

xfworld · 2021 年5 月 16 日 01:37

（1）使用 With 子句的目的。树形查询或者递归查询
（2）使用的类型。递归
（3）With 中是否包含聚合函数，窗口函数等复杂查询。不包含
（3）数据规模。千万行，按照索引关联的表也是千万行
（4）期望的执行时间。 2S 以内
（5）期望的资源占用。少占用内存，可多占用磁盘，但是需要有效的释放机制
（6）是否会考虑和 TiFlash 一起使用？否

GangShen · 2021 年5 月 17 日 05:52

xiaocaiyun8 · 2021 年5 月 27 日 03:39

这个可以参考下hive(或者星环)里面的实现,应该是创建了一个临时表,用完就删掉的.

如果是采用创建临时表的方案,对于with 子句应该是可以不限制语法(比如聚合,窗口数据规模等都不应该限制).

当然,实际使用中是否需要必须创建这个临时表,这个需要根据后续对这个子查询的调用次数,如果只有一次,就不是必须的.

xyxiaoyou · 2021 年5 月 28 日 06:35

（1）解决模型的迁移问题，从Presto迁移到TiDB，否则每个With需要考虑拆分定义成View
（2）数据规模，目前三十多台16C32G机器跑的Presto集群，负载80%+
（3）快于视图
（4）越低越好

Billmay表妹 · 2021 年7 月 7 日 06:26

感谢参与产品调研，已帮你完成加分：
+50经验值
+50积分

Billmay表妹 · 2021 年7 月 7 日 06:27

感谢参与产品调研，已帮你完成加分：
+50经验值
+50积分

Billmay表妹 · 2021 年7 月 7 日 06:27

感谢参与产品调研，已帮你完成加分：
+50经验值
+50积分

Billmay表妹 · 2021 年7 月 7 日 06:27

感谢参与产品调研，已帮你完成加分：
+50经验值
+50积分

Billmay表妹 · 2022 年10 月 31 日 19:12

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。