SQL 参考

R2 SQL 是 Cloudflare 的无服务器、分布式分析查询引擎，用于查询存储在 R2 Data Catalog 中的 Apache Iceberg ↗ 表。本页记录了支持的 SQL 语法。

查询语法

SELECT [DISTINCT] column_list | expression | aggregate_function | window_function
FROM namespace_name.table_name
[JOIN namespace_name.table_name ON condition]
[WHERE conditions]
[GROUP BY column_list]
[HAVING conditions]
[QUALIFY window_condition]
[ORDER BY expression [ASC | DESC]]
[LIMIT number]

可以使用集合操作（UNION、UNION ALL、INTERSECT、EXCEPT）组合两个或多个查询。

模式发现命令 (Schema discovery commands)

SHOW DATABASES

列出所有可用的命名空间。

SHOW DATABASES;

SHOW NAMESPACES

SHOW DATABASES 的别名。列出所有可用的命名空间。

SHOW NAMESPACES;

SHOW TABLES

列出特定命名空间内的所有表。

SHOW TABLES IN namespace_name;

DESCRIBE

描述表的结构，显示列名和数据类型。

DESCRIBE namespace_name.table_name;

SELECT 子句

语法

SELECT [DISTINCT] column_specification [, column_specification, ...]

列规范

列名：column_name
所有列：*
限定通配符：table_name.*
列别名：column_name AS alias
表达式：算术、函数调用、CASE 表达式和类型转换

示例

SELECT * FROM my_namespace.sales_data LIMIT 10
SELECT customer_id, region, total_amount FROM my_namespace.sales_data LIMIT 10
SELECT region, total_amount * 1.1 AS total_with_tax FROM my_namespace.sales_data LIMIT 10

DISTINCT

SELECT DISTINCT 返回唯一的行。DISTINCT ON (...) 根据 ORDER BY 子句确定保留哪一行，返回列出的表达式的每个组合的第一行。

-- 唯一的组合
SELECT DISTINCT region, department FROM my_namespace.sales_data

-- 按金额保留每个区域的第一行
SELECT DISTINCT ON (region) region, customer_id, total_amount
FROM my_namespace.sales_data
ORDER BY region, total_amount DESC

对于大型数据集上唯一值的计数，approx_distinct() 是更快的选择。

公用表表达式 (CTEs)

CTE 允许您使用 WITH 定义可在主查询中引用的命名临时结果集。CTE 可以引用不同的表，并且可以包含 JOIN。CTE 也可以在主查询中与其他 CTE 或常规表连接。

语法

WITH cte_name AS (
    SELECT ...
    FROM namespace_name.table_name
    [WHERE ...]
)
SELECT ... FROM cte_name

链式 CTEs

CTE 可以引用先前定义的 CTE。

WITH filtered AS (
    SELECT customer_id, department, total_amount
    FROM my_namespace.sales_data
    WHERE total_amount > 0
),
summary AS (
    SELECT department,
           COUNT(*) AS order_count,
           round(AVG(total_amount), 2) AS avg_amount
    FROM filtered
    GROUP BY department
)
SELECT *
FROM summary
WHERE order_count > 100
ORDER BY avg_amount DESC

与另一个表连接的 CTE

WITH enterprise_zones AS (
    SELECT zone_id, domain, plan
    FROM my_namespace.zones
    WHERE plan = 'enterprise'
)
SELECT ez.domain, f.action, COUNT(*) AS cnt
FROM enterprise_zones ez
INNER JOIN my_namespace.firewall_events f ON ez.zone_id = f.zone_id
GROUP BY ez.domain, f.action
ORDER BY cnt DESC
LIMIT 20

两个 CTE 相互连接

WITH top_zones AS (
    SELECT zone_id, COUNT(*) AS req_count
    FROM my_namespace.http_requests
    GROUP BY zone_id
    ORDER BY req_count DESC
    LIMIT 50
),
zone_threats AS (
    SELECT zone_id, COUNT(*) AS threat_count
    FROM my_namespace.firewall_events
    WHERE risk_score > 0.5
    GROUP BY zone_id
)
SELECT tz.zone_id, tz.req_count, COALESCE(zt.threat_count, 0) AS threat_count
FROM top_zones tz
LEFT JOIN zone_threats zt ON tz.zone_id = zt.zone_id
ORDER BY tz.req_count DESC
LIMIT 20

FROM 子句

语法

SELECT * FROM namespace_name.table_name

R2 SQL 查询可以引用一个或多个表。表被指定为 namespace_name.table_name。可以使用 JOIN 或逗号分隔语法组合多个表。有关详细信息，请参阅 JOIN 子句部分。

JOIN 子句

R2 SQL 支持在单个查询中连接多个 Iceberg 表。所有连接类型均使用标准 SQL 语法。

支持的连接类型

连接类型	语法	描述
Inner join	`INNER JOIN ... ON`	返回在两个表中都匹配的行
Left outer join	`LEFT JOIN ... ON`	返回左表的所有行，不匹配的右表行返回 NULL
Right outer join	`RIGHT JOIN ... ON`	返回右表的所有行，不匹配的左表行返回 NULL
Full outer join	`FULL OUTER JOIN ... ON`	返回两个表的所有行，没有匹配项的地方返回 NULL
Cross join	`CROSS JOIN`	两个表的笛卡尔积
Implicit join	`FROM t1, t2 WHERE t1.id = t2.id`	在 `WHERE` 中具有连接条件的逗号分隔表

语法

-- 显式 JOIN
SELECT columns
FROM namespace.table1 alias1
[INNER | LEFT | RIGHT | FULL OUTER | CROSS] JOIN namespace.table2 alias2
  ON alias1.column = alias2.column
[WHERE conditions]

-- 隐式 join
SELECT columns
FROM namespace.table1 alias1, namespace.table2 alias2
WHERE alias1.column = alias2.column

多向连接

您可以在单个查询中连接三个或更多表：

SELECT z.domain, h.method, f.action, COUNT(*) AS cnt
FROM my_namespace.zones z
INNER JOIN my_namespace.http_requests h ON z.zone_id = h.zone_id
INNER JOIN my_namespace.firewall_events f ON z.zone_id = f.zone_id
WHERE h.status_code >= 400
GROUP BY z.domain, h.method, f.action
ORDER BY cnt DESC
LIMIT 20

自连接

一个表可以使用不同的别名与自身连接：

SELECT f1.source_ip, f1.zone_id AS zone1, f2.zone_id AS zone2
FROM my_namespace.firewall_events f1
INNER JOIN my_namespace.firewall_events f2
  ON f1.source_ip = f2.source_ip
  AND f1.zone_id < f2.zone_id
WHERE f1.action = 'block'
LIMIT 20

连接条件

连接条件使用带有等号 (=) 或基于表达式的谓词的 ON 子句。
连接谓词中支持函数（例如，ON LOWER(a.col) = LOWER(b.col)）。
可以使用 AND 组合多个条件。

说明

不支持嵌套（括号内的）连接。请将多向连接编写为 JOIN 子句的平坦序列，而不是用括号将它们分组。

-- 不支持
SELECT * FROM (t1 JOIN t2 ON t1.id = t2.id) JOIN t3 ON t2.id = t3.id

-- 支持
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id JOIN t3 ON t2.id = t3.id

连接的最佳实践

包含 WHERE 过滤器以减少中间结果的大小，特别是对于多向连接。
通过共享维度表连接大型事实表，而不是直接交叉连接两个大型表。
使用 LIMIT 来限制结果大小。

子查询 (Subqueries)

R2 SQL 支持在查询的多个位置使用子查询。

FROM 中的子查询（派生表）

FROM 子句中的子查询会创建一个派生表，可以在外部查询中引用它：

SELECT sub.domain, sub.total_requests
FROM (
    SELECT z.domain, COUNT(*) AS total_requests
    FROM my_namespace.zones z
    INNER JOIN my_namespace.http_requests h ON z.zone_id = h.zone_id
    GROUP BY z.domain
) sub
WHERE sub.total_requests > 1000
ORDER BY sub.total_requests DESC
LIMIT 20

派生表可以与其他派生表或常规表连接：

SELECT req.domain, req.total_reqs, fw.total_events
FROM (
    SELECT zone_id, domain, COUNT(*) AS total_reqs
    FROM my_namespace.zones z
    INNER JOIN my_namespace.http_requests h ON z.zone_id = h.zone_id
    GROUP BY zone_id, domain
) req
INNER JOIN (
    SELECT zone_id, COUNT(*) AS total_events
    FROM my_namespace.firewall_events
    GROUP BY zone_id
) fw ON req.zone_id = fw.zone_id
ORDER BY fw.total_events DESC
LIMIT 20

`IN` / `NOT IN` 子查询

根据值是否存在于子查询的结果中来过滤行：

-- 查找来自企业区域的请求
SELECT method, status_code, COUNT(*) AS cnt
FROM my_namespace.http_requests
WHERE zone_id IN (
    SELECT zone_id FROM my_namespace.zones WHERE plan = 'enterprise'
)
GROUP BY method, status_code
ORDER BY cnt DESC
LIMIT 20

-- NOT IN 示例
SELECT zone_id, COUNT(*) AS cnt
FROM my_namespace.http_requests
WHERE zone_id NOT IN (
    SELECT zone_id FROM my_namespace.firewall_events WHERE action = 'block'
)
GROUP BY zone_id
LIMIT 10

注意

可为空的列上不支持 NOT IN 子查询。如果子查询列可以包含 NULL 值，请改用 NOT EXISTS。

-- 代替在可为空的列上使用 NOT IN：
SELECT z.domain
FROM my_namespace.zones z
WHERE NOT EXISTS (
    SELECT 1 FROM my_namespace.firewall_events f
    WHERE f.zone_id = z.zone_id
)
LIMIT 20

`EXISTS` / `NOT EXISTS` 子查询

测试匹配相关条件的行是否存在：

-- 查找具有被阻止防火墙事件的区域 (半连接)
SELECT z.domain, z.plan
FROM my_namespace.zones z
WHERE EXISTS (
    SELECT 1 FROM my_namespace.firewall_events f
    WHERE f.zone_id = z.zone_id AND f.action = 'block'
)
ORDER BY z.domain
LIMIT 20

-- 查找没有防火墙事件的区域 (反连接)
SELECT z.domain, z.plan
FROM my_namespace.zones z
WHERE NOT EXISTS (
    SELECT 1 FROM my_namespace.firewall_events f
    WHERE f.zone_id = z.zone_id
)
ORDER BY z.domain
LIMIT 20

标量子查询

返回单个值的子查询可以用于 SELECT、WHERE 或 HAVING：

-- 在 SELECT 中（每行的常量值）
SELECT z.domain, z.plan,
       (SELECT COUNT(*) FROM my_namespace.zones) AS total_zones
FROM my_namespace.zones z
WHERE z.plan = 'enterprise'
LIMIT 10

-- 在 WHERE 中（比较）
SELECT z.domain, z.plan, z.requests_30d
FROM my_namespace.zones z
WHERE z.requests_30d > (
    SELECT AVG(requests_30d) FROM my_namespace.zones
)
ORDER BY z.requests_30d DESC
LIMIT 20

WHERE 子句

语法

SELECT * FROM namespace_name.table_name WHERE condition [AND | OR condition ...]

条件

比较运算符

=, !=, <>, <, >, <=, >=

空值检查

column_name IS NULL
column_name IS NOT NULL

布尔检查

IS TRUE, IS FALSE, IS NOT TRUE, IS NOT FALSE
IS UNKNOWN, IS NOT UNKNOWN

范围

column_name BETWEEN value1 AND value2
column_name NOT BETWEEN value1 AND value2

列表成员关系

column_name IN ('value1', 'value2')
column_name NOT IN ('value1', 'value2')

模式匹配

column_name LIKE 'pattern'
column_name NOT LIKE 'pattern'
column_name ILIKE 'pattern' (不区分大小写)
column_name NOT ILIKE 'pattern'
column_name SIMILAR TO 'regex_pattern'

逻辑运算符

AND
OR
NOT

示例

SELECT * FROM my_namespace.sales_data
WHERE timestamp BETWEEN '2025-09-24T01:00:00Z' AND '2025-09-25T01:00:00Z'

SELECT * FROM my_namespace.sales_data
WHERE status = 200 AND response_time > 1000

SELECT * FROM my_namespace.sales_data
WHERE (region = 'North' OR region = 'South')
  AND total_amount IS NOT NULL

SELECT * FROM my_namespace.sales_data
WHERE department ILIKE '%eng%'

GROUP BY 子句

语法

SELECT column_list, aggregation_function(column)
FROM namespace_name.table_name
[WHERE conditions]
GROUP BY column_list

示例

SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
GROUP BY department

SELECT department, category, SUM(total_amount) AS total
FROM my_namespace.sales_data
GROUP BY department, category

GROUPING SETS, ROLLUP, 和 CUBE

这些扩展在单个查询中计算多个分组，包括小计和总计。

GROUPING SETS：精确计算您列出的分组。() 生成总计。
ROLLUP：从左到右计算层次化的小计。ROLLUP(a, b) 按 (a, b)、(a) 和 () 进行分组。
CUBE：计算列出列的每种组合。CUBE(a, b) 按 (a, b)、(a)、(b) 和 () 进行分组。

-- 每个部门的小计加上一个总计
SELECT department, SUM(total_amount) AS total
FROM my_namespace.sales_data
GROUP BY ROLLUP(department)

-- 部门和类别的每种组合
SELECT department, category, SUM(total_amount) AS total
FROM my_namespace.sales_data
GROUP BY CUBE(department, category)

-- 显式分组
SELECT department, category, SUM(total_amount) AS total
FROM my_namespace.sales_data
GROUP BY GROUPING SETS ((department, category), (department), ())

HAVING 子句

语法

SELECT column_list, aggregation_function(column) AS alias
FROM namespace_name.table_name
GROUP BY column_list
HAVING aggregation_function(column) comparison_operator value

示例

SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
GROUP BY department
HAVING COUNT(*) > 1000

SELECT region, SUM(total_amount) AS total
FROM my_namespace.sales_data
GROUP BY region
HAVING SUM(total_amount) > 1000000

ORDER BY 子句

语法

ORDER BY expression [ASC | DESC] [, expression [ASC | DESC], ...]

ASC：升序（默认）
DESC：降序
支持多列排序

示例

SELECT customer_id, total_amount
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
ORDER BY total_amount DESC
LIMIT 50

SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
GROUP BY department
ORDER BY dept_count DESC, department ASC

LIMIT 子句

语法

LIMIT number

类型：仅限整数
默认值：500

示例

SELECT * FROM my_namespace.sales_data LIMIT 100

窗口函数 (Window functions)

窗口函数计算与当前行相关的一组行的值，而不会将它们折叠为单个输出行。窗口使用包含可选的 PARTITION BY、ORDER BY 和帧规范的 OVER (...) 子句内联定义。

语法

function(args) OVER (
    [PARTITION BY expression [, ...]]
    [ORDER BY expression [ASC | DESC] [, ...]]
    [frame_specification]
)

支持的函数

类别	函数
排名	`ROW_NUMBER`, `RANK`, `DENSE_RANK`, `PERCENT_RANK`, `CUME_DIST`, `NTILE`
偏移	`LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`, `NTH_VALUE`
聚合	`SUM`, `AVG`, `COUNT`, `MIN`, `MAX`, 以及其他与 `OVER` 一起使用的聚合函数

示例

-- 在每个分区内对行进行排名
SELECT customer_id, region,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_amount DESC) AS rank_in_region,
       LAG(total_amount) OVER (PARTITION BY region ORDER BY total_amount DESC) AS prev_amount
FROM my_namespace.sales_data

-- 带有显式帧的运行总计
SELECT customer_id, total_amount,
       SUM(total_amount) OVER (ORDER BY total_amount ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_total
FROM my_namespace.sales_data

QUALIFY

QUALIFY 根据窗口函数的结果过滤行，类似于 HAVING 如何过滤分组行。

-- 仅保留每个区域中金额排名前 3 的客户
SELECT customer_id, region, total_amount
FROM my_namespace.sales_data
QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_amount DESC) <= 3

集合操作 (Set operations)

集合操作组合两个或多个 SELECT 语句的结果。

语法

SELECT ... FROM table1
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT ... FROM table2

支持的操作

操作	描述
`UNION`	返回两个查询的所有行，并删除重复项
`UNION ALL`	返回两个查询的所有行，包括重复项
`INTERSECT`	仅返回同时出现在两个查询结果中的行
`EXCEPT`	返回出现在第一个查询中但未出现在第二个查询中的行

示例

Union

-- 查找具有防火墙阻止或高风险请求的区域
SELECT zone_id FROM my_namespace.firewall_events WHERE action = 'block'
UNION
SELECT zone_id FROM my_namespace.http_requests WHERE risk_score > 0.8

Intersect

-- 查找既有防火墙阻止又有区域表条目的区域
SELECT zone_id FROM my_namespace.firewall_events WHERE action = 'block'
INTERSECT
SELECT zone_id FROM my_namespace.zones WHERE plan = 'enterprise'

Except

-- 查找没有防火墙事件的企业区域
SELECT zone_id FROM my_namespace.zones WHERE plan = 'enterprise'
EXCEPT
SELECT zone_id FROM my_namespace.firewall_events

要求

集合操作中的所有查询必须返回相同数量的列。
相应的列必须具有兼容的数据类型。
结果中的列名取自第一个查询。

EXPLAIN

返回查询的执行计划而不运行它。

EXPLAIN SELECT department, COUNT(*) AS dept_count
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
GROUP BY department;

EXPLAIN FORMAT JSON

以结构化 JSON 格式返回执行计划以进行编程式分析。

EXPLAIN FORMAT JSON SELECT * FROM my_namespace.sales_data LIMIT 10;

表达式 (Expressions)

表达式可用于 SELECT、WHERE、GROUP BY、HAVING 和 ORDER BY 子句。

字面量

SELECT 42 AS int_val, 3.14 AS float_val, 'hello' AS str_val, TRUE AS bool_val, NULL AS null_val
FROM my_namespace.sales_data LIMIT 1

算术运算符

+, -, *, /, %

SELECT customer_id, total_amount * 1.1 AS total_with_tax, total_amount % 10 AS remainder
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

字符串连接

SELECT customer_id || ' - ' || region AS label
FROM my_namespace.sales_data
LIMIT 5

CASE 表达式

搜索形式：

SELECT customer_id,
    CASE
        WHEN total_amount > 1000 THEN 'high'
        WHEN total_amount > 100 THEN 'medium'
        ELSE 'low'
    END AS tier
FROM my_namespace.sales_data
LIMIT 10

简单形式：

SELECT customer_id,
    CASE region
        WHEN 'North' THEN 'N'
        WHEN 'South' THEN 'S'
        ELSE 'Other'
    END AS region_code
FROM my_namespace.sales_data
LIMIT 10

类型转换 (Type casting)

-- CAST
SELECT CAST(total_amount AS INT) AS amount_int FROM my_namespace.sales_data LIMIT 5

-- TRY_CAST (在失败时返回 NULL 而不是错误)
SELECT TRY_CAST(customer_id AS INT) AS id_int FROM my_namespace.sales_data LIMIT 5

-- 简写 (::)
SELECT total_amount::INT AS amount_int FROM my_namespace.sales_data LIMIT 5

EXTRACT

SELECT EXTRACT(YEAR FROM timestamp) AS yr,
       EXTRACT(MONTH FROM timestamp) AS mo,
       EXTRACT(DAY FROM timestamp) AS dy
FROM my_namespace.sales_data
LIMIT 1

数据类型参考

类型	描述	示例值
`integer`	整数	`1`, `42`, `-10`, `0`
`float`	小数	`1.5`, `3.14`, `-2.7`, `0.0`
`string`	文本值	`'hello'`, `'GET'`, `'2024-01-01'`
`boolean`	布尔值	`true`, `false`
`timestamp`	RFC3339	`'2025-09-24T01:00:00Z'`
`date`	日期值	`'2025-09-24'`
`struct`	命名字段	`struct_col['field_name']`
`array`	有序列表	`array_col[1]` (从 1 开始)
`map`	键值对	`map_keys(map_col)`

运算符优先级

比较运算符：=, !=, <, <=, >, >=, LIKE, BETWEEN, IS NULL, IS NOT NULL
AND (较高优先级)
OR (较低优先级)

使用括号来覆盖默认优先级：

SELECT * FROM my_namespace.sales_data WHERE (status = 404 OR status = 500) AND region = 'North'

完整查询示例

基本查询

SELECT *
FROM my_namespace.sales_data
WHERE timestamp BETWEEN '2025-09-24T01:00:00Z' AND '2025-09-25T01:00:00Z'
LIMIT 100

带有排序的过滤查询

SELECT customer_id, timestamp, status, total_amount
FROM my_namespace.sales_data
WHERE status >= 400 AND total_amount > 5000
ORDER BY total_amount DESC
LIMIT 50

带有 HAVING 的聚合

SELECT region, COUNT(*) AS region_count, AVG(total_amount) AS avg_amount
FROM my_namespace.sales_data
WHERE status = 'completed'
GROUP BY region
HAVING COUNT(*) > 1000
ORDER BY avg_amount DESC
LIMIT 20

条件分类

SELECT customer_id,
    CASE
        WHEN total_amount >= 1000 THEN 'Premium'
        WHEN total_amount >= 100 THEN 'Standard'
        ELSE 'Basic'
    END AS tier,
    total_amount
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
ORDER BY total_amount DESC
LIMIT 20