公共表表达式（CTE）

CTE 与 视图 类似，它们帮助您简化查询的复杂性，将长而复杂的 SQL 语句分解，并提高可读性和可重用性。

您已经在 快速开始 文档中阅读了一个 CTE 的例子。

什么是公共表表达式 （CTE）？

公共表表达式 (CTE) 是可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中引用的临时结果集。CTE 有助于将复杂的查询分解成更可读的部分，并且可以在同一个查询中多次引用。

CTE 的基本语法

CTE 通常使用 WITH 关键字定义。基本语法如下：

  1. WITH cte_name [(column1, column2, ...)] AS (
  2.     QUERY
  3. )
  4. SELECT ...
  5. FROM cte_name;

示例解释

接下来，我们将通过一个完整的示例来演示如何使用 CTE，包括数据准备、CTE 创建和使用。

步骤 1：创建示例数据

假设我们有以下两个表：

  • grpc_latencies：包含 gRPC 请求延迟数据。
  • app_logs：包含应用程序日志信息。
  1. CREATE TABLE grpc_latencies (
  2.     ts TIMESTAMP TIME INDEX,
  3.     host VARCHAR(255),
  4.     latency FLOAT,
  5.     PRIMARY KEY(host),
  6. );
  8. INSERT INTO grpc_latencies VALUES 
  9. ('2023-10-01 10:00:00', 'host1', 120),
  10. ('2023-10-01 10:00:00', 'host2', 150),
  11. ('2023-10-01 10:00:05', 'host1', 130);
  13. CREATE TABLE app_logs (
  14.     ts TIMESTAMP TIME INDEX,
  15.     host VARCHAR(255),
  16.     log TEXT,
  17.     log_level VARCHAR(50),
  18.     PRIMARY KEY(host, log_level),
  19. );
  21. INSERT INTO app_logs VALUES 
  22. ('2023-10-01 10:00:00', 'host1', 'Error on service', 'ERROR'),
  23. ('2023-10-01 10:00:00', 'host2', 'All services OK', 'INFO'),
  24. ('2023-10-01 10:00:05', 'host1', 'Error connecting to DB', 'ERROR');

步骤 2：定义和使用 CTE

我们将创建两个 CTE 来分别计算第 95 百分位延迟和错误日志的数量。

  1. WITH 
  2. metrics AS (
  3.     SELECT 
  4.         ts, 
  5.         host, 
  6.         approx_percentile_cont(latency, 0.95) RANGE '5s' AS p95_latency
  7.     FROM 
  8.         grpc_latencies
  9.     ALIGN '5s' FILL PREV
  10. ),
  11. logs AS (
  12.     SELECT 
  13.         ts, 
  14.         host,
  15.         COUNT(log) RANGE '5s' AS num_errors
  16.     FROM
  17.         app_logs
  18.     WHERE 
  19.         log_level = 'ERROR'
  20.     ALIGN '5s' BY (HOST)
  21. )
  22. SELECT 
  23.     metrics.ts,
  24.     metrics.host,
  25.     metrics.p95_latency,
  26.     COALESCE(logs.num_errors, 0) AS num_errors
  27. FROM 
  28.     metrics 
  29. LEFT JOIN logs ON metrics.host = logs.host AND metrics.ts = logs.ts
  30. ORDER BY 
  31.     metrics.ts;

输出：

  1. +---------------------+-------+-------------+------------+
  2. | ts                  | host  | p95_latency | num_errors |
  3. +---------------------+-------+-------------+------------+
  4. | 2023-10-01 10:00:00 | host2 |         150 |          0 |
  5. | 2023-10-01 10:00:00 | host1 |         120 |          1 |
  6. | 2023-10-01 10:00:05 | host1 |         130 |          1 |
  7. +---------------------+-------+-------------+------------+

详细说明

  1. 定义 CTEs：

  • metrics:

    1. WITH 
    2. metrics AS (
    3.     SELECT 
    4.       ts, 
    5.       host, 
    6.       approx_percentile_cont(latency, 0.95) RANGE '5s' AS p95_latency
    7.     FROM 
    8.       grpc_latencies
    9.     ALIGN '5s' FILL PREV
    10. ),

    这里我们使用范围查询计算每个 host 在每个 5 秒时间窗口内的第 95 百分位延迟。

  • logs:

    1. logs AS (
    2.   SELECT 
    3.     ts, 
    4.     host,
    5.     COUNT(log) RANGE '5s' AS num_errors
    6.   FROM
    7.     app_logs
    8.   WHERE 
    9.     log_level = 'ERROR'
    10.   ALIGN '5s' BY (HOST)
    11. )

    同样地，我们计算每个 host 在每个 5 秒时间窗口内的错误日志数量。

  1. 使用 CTEs： 在最终的查询部分：

    1. SELECT
    2.     metrics.ts,
    3.     metrics.host,
    4.     metrics.p95_latency,
    5.     COALESCE(logs.num_errors, 0) AS num_errors
    6. FROM
    7.     metrics
    8. LEFT JOIN logs ON metrics.host = logs.host AND metrics.ts = logs.ts
    9. ORDER BY
    10.     metrics.ts;

    我们对两个 CTE 结果集进行左连接，以获得最终的综合分析结果。

总结

通过 CTE，您可以将复杂的 SQL 查询分解为更易于管理和理解的部分。在本示例中，我们分别创建了两个 CTE 来计算第 95 百分位延迟和错误日志的数量，然后将它们合并到最终查询中进行分析。 阅读更多 WITH。