Alibabacloud Odps Sql Generation

MaxCompute SQL Engine Syntax Skill

Provides MaxCompute SQL engine syntax guidance for text2sql scenarios. MaxCompute is based on Hive SQL extensions and has significant differences from ANSI standard SQL.

Usage

Load corresponding reference files based on question type (can be combined). The load column indicates relative loading weight (heavy ≈ large, medium ≈ moderate), for budget estimation, not exact token counts. Each file's opening paragraph describes its own scope.

Relationship: text2sql_principles.md provides engine-independent NL→SELECT generation principles; maxcompute_select_guide.md is the single authoritative source for MaxCompute DQL dialect rules (unsupported syntax/functions/partitioning/types/SET parameters). sql_query_patterns.md provides query template snippets only, without duplicating rules.

Out of Scope

The following scenarios exceed this skill's scope:

• MaxCompute non-SQL interfaces: Tunnel / MapReduce / Graph / PyODPS DataFrame API, SDK invocation methods
• Console and permission management: Quota requests, IAM / RAM roles, project owner operations — use Aliyun console or support tickets
• Execution plan-level deep tuning: Only lists common SET parameters and hints; does not analyze specific plan nodes / Fuxi DAG / data skew formation paths
• Cluster-side / platform-side failures: Worker crashes, resource scheduling failures, MetaStore transaction conflicts, storage layer read/write errors — these are support ticket issues, not SQL issues

MaxCompute SELECT 方言规则

生成 MaxCompute SELECT 查询前读取此文件。覆盖 MaxCompute 与标准 SQL 的差异：不工作的写法、函数名映射、分区规则、类型陷阱、扩展语法、SET 参数。每条规则附触发的实际报错信息或 MaxCompute 的设计原因，遇到边界情况时按"为什么"判断而非死搬。

组合方式：自然语言转 SELECT 时，用 text2sql_principles.md 做逻辑规划（意图、结果粒度、表列映射、输出契约），最终 SQL 一次性写成 MaxCompute 可执行语法，不先生成 ANSI SQL 中间稿。

示例说明：本文档为了突出方言语法，部分示例使用 SELECT *。text2sql 最终输出仍应遵守 text2sql_principles.md：除非用户明确要求透传所有列，或模板语义必须保留全列，否则列出明确字段。

---

一、不工作的写法（会直接报错）

以下写法在 MaxCompute 上会报错。每条都附错误码和原因。

1. ORDER BY 默认要求带 LIMIT

odps.sql.validate.orderby.limit=true 是默认值；当 SQL 包含 ORDER BY 时必须配合 LIMIT，否则提交校验会报错。不要在没有 ORDER BY / 没有 Top-N / 没有"最大/最小/排名"语义的查询上无故添加 LIMIT — 多余的 LIMIT 会改变结果集大小，破坏题意。可通过下述方式关闭：

• 项目级：SETPROJECT odps.sql.validate.orderby.limit=false;
• 会话级：SET odps.sql.validate.orderby.limit=false;

注：ORDER BY 不能与 DISTRIBUTE BY / SORT BY 同时出现。

-- DON'T（默认模式下报错）
SELECT * FROM orders WHERE dt = '2024-01-15' ORDER BY amount DESC;

-- DO
SELECT * FROM orders WHERE dt = '2024-01-15' ORDER BY amount DESC LIMIT 100;

2. 类型转换只能用 CAST，无 ::type 简写

-- DON'T
SELECT col::BIGINT FROM t;

-- DO
SELECT CAST(col AS BIGINT) FROM t;

3. 取前 N 行用 LIMIT，无 SELECT TOP N

-- DON'T
SELECT TOP 10 * FROM t;

-- DO
SELECT * FROM t LIMIT 10;

4. 大小写不敏感匹配用 LOWER + LIKE，无 ILIKE

-- DON'T
SELECT * FROM t WHERE name ILIKE '%张%';

-- DO
SELECT * FROM t WHERE LOWER(name) LIKE '%张%';

5. 字符串拼接用 CONCAT 或 ||

+ 是数值运算符，不能用于字符串拼接。

SELECT CONCAT('a', 'b');   -- 返回 'ab'
SELECT 'a' || 'b';         -- 返回 'ab'

6. NULL 判断只用 IS NULL

-- DON'T
SELECT * FROM t WHERE col = NULL;

-- DO
SELECT * FROM t WHERE col IS NULL;

7. WHERE 不能引用 SELECT 别名

-- DON'T
SELECT amount * 0.1 AS tax FROM orders WHERE tax > 10;

-- DO
SELECT amount * 0.1 AS tax FROM orders WHERE amount * 0.1 > 10;
-- 或用子查询
SELECT * FROM (SELECT amount * 0.1 AS tax FROM orders) tmp WHERE tax > 10;

8. SUM(布尔表达式) 不工作

MaxCompute 没有隐式 bool→int 转换，SUM(condition) 报 function sum cannot match any overloaded functions with (BOOLEAN)。改用 CASE WHEN 或 COUNT_IF。

-- DON'T: SUM(bool) 不工作
SELECT SUM(status = 'A') FROM orders;
SELECT SUM(gender_id = 1) FROM superhero;

-- DO: 用 CASE WHEN 或 COUNT_IF
SELECT SUM(CASE WHEN status = 'A' THEN 1 ELSE 0 END) FROM orders;
SELECT SUM(CASE WHEN gender_id = 1 THEN 1 ELSE 0 END) FROM superhero;

---

二、函数名称规则

以下列出MaxCompute函数与其他数据库的差异。标注"均可"表示多种写法都支持。

日期时间

当需要当前时间时，写 GETDATE()、NOW() 或 CURRENT_TIMESTAMP() 均可：

SELECT GETDATE();            -- 返回 DATETIME
SELECT NOW();                -- 返回 DATETIME
SELECT CURRENT_TIMESTAMP();  -- 返回 TIMESTAMP（需加括号）

当需要日期格式化时，推荐 `TO_CHAR(d, fmt)`（跨类型默认可用）。DATE_FORMAT 存在但需要 SET 前置条件：

-- 推荐（默认可用）
SELECT TO_CHAR(create_time, 'yyyy-mm-dd');

-- DATE_FORMAT 需先 SET
SET odps.sql.hive.compatible=true;
SELECT DATE_FORMAT(create_time, 'yyyy-MM-dd');

注意格式串风格不同：

• TO_CHAR 用 Oracle 风格小写 yyyy-mm-dd hh:mi:ss（mi=分）
• DATE_FORMAT 用 Java SimpleDateFormat 风格 yyyy-MM-dd HH:mm:ss（HH=24h，mm=分；非 Hive 模式 mm=月）

当需要字符串转日期时，写 TO_DATE(s, fmt)：

-- DON'T
SELECT STR_TO_DATE('2024-01-15', '%Y-%m-%d');

-- DO
SELECT TO_DATE('2024-01-15', 'yyyy-mm-dd');

日期加减用 DATEADD(date, delta, unit)；MaxCompute 没有 DATE_ADD ... INTERVAL：

-- DON'T
SELECT DATE_ADD(create_time, INTERVAL 7 DAY);

-- DO
SELECT DATEADD(create_time, 7, 'dd');

当需要日期差时，写 DATEDIFF(d1, d2, unit)，第三个参数可选（默认天）：

SELECT DATEDIFF(end_date, start_date, 'dd');   -- 显式指定天
SELECT DATEDIFF(end_date, start_date);         -- 默认也是天

当需要提取年月日时时，写 YEAR(d) / MONTH(d) / DAY(d) / HOUR(d) 或 DATEPART(d, unit)：

SELECT YEAR(create_time), MONTH(create_time), DAY(create_time);
-- 或
SELECT DATEPART(create_time, 'yyyy'), DATEPART(create_time, 'mm');

当需要日期截断时，写 DATETRUNC(d, unit) 或 DATE(d) / TO_DATE(d)：

SELECT DATETRUNC(create_time, 'mm');  -- 截断到月（返回月初）
SELECT DATETRUNC(create_time, 'dd');  -- 截断到天
SELECT DATE(create_time);             -- 截断到天（简写）
SELECT TO_DATE(create_time);          -- 截断到天（简写）

注意：默认模式下 TRUNC 是数值截断函数（如 TRUNC(125.815, 0) → 125.0），不能用于日期。Hive兼容模式下 TRUNC(d, unit) 可用于日期。

易混淆函数：

- `DATETRUNC(d, unit)` — DQL 用的日期截断函数（本节）

- `TRUNC_TIME(d, unit)` — DDL AUTO PARTITIONED BY 专用，仅在建表语句里用

>

两个函数名差一个下划线，单位也不同（DATETRUNC 用短名 'dd'，TRUNC_TIME 用长名 'day'）。

其他日期函数：

• UNIX_TIMESTAMP(d) — 转Unix时间戳
• FROM_UNIXTIME(ts) — Unix时间戳转日期（仅1个参数，格式化需配合TO_CHAR）
• LAST_DAY(d) — 月末日期（返回STRING）
• LASTDAY(d) — 月末日期（返回DATETIME，经典函数）
• WEEKDAY(d) — 星期几（0=周一, 6=周日）
• WEEKOFYEAR(d) — ISO周数

字符串

当需要分组拼接时，写 WM_CONCAT(sep, col)（分隔符在前）：

SELECT WM_CONCAT(',', name) FROM t GROUP BY dept;
SELECT WM_CONCAT(',', name) WITHIN GROUP (ORDER BY name) FROM t GROUP BY dept;  -- 排序拼接
-- GROUP_CONCAT 不可用
-- STRING_AGG 在引擎中可执行（PostgreSQL 风格 STRING_AGG(col, sep)），但官方文档未列出，推荐用 WM_CONCAT

当需要查找子串位置时，INSTR(str, sub) 和 LOCATE(sub, str) 均可，注意参数顺序相反：

SELECT INSTR(name, 'abc');    -- 大串在前，子串在后
SELECT LOCATE('abc', name);   -- 子串在前，大串在后

当需要正则提取时，REGEXP_EXTRACT 和 REGEXP_SUBSTR 均可：

SELECT REGEXP_EXTRACT(text, '(\\d+)', 1);              -- 提取捕获组
SELECT REGEXP_SUBSTR(text, '\\d+');                     -- 返回匹配子串
SELECT REGEXP_SUBSTR(text, '\\d+', 1, 2);              -- 第2次匹配，从位置1开始

当需要字符串分割时，写 SPLIT(str, sep) 返回ARRAY，取指定段用 SPLIT_PART(str, sep, index)：

SELECT SPLIT('a,b,c', ',');            -- 返回 ARRAY ['a','b','c']
SELECT SPLIT_PART('a,b,c', ',', 2);   -- 返回 'b'

聚合与条件

NULL 替换用 NVL(expr, default) 或 COALESCE；MaxCompute 没注册 IFNULL：

-- DON'T
SELECT IFNULL(amount, 0) FROM orders;

-- DO
SELECT NVL(amount, 0) FROM orders;

当需要条件计数时，除了 SUM(CASE WHEN) 外，也可用 COUNT_IF(condition)：

SELECT COUNT_IF(status = 'active') FROM users;  -- 等价于 SUM(CASE WHEN ... THEN 1 ELSE 0 END)

当需要按条件取极值对应的值时，用 MAX_BY / MIN_BY：

-- 找出每个部门薪资最高的员工名
SELECT dept, MAX_BY(name, salary) AS top_earner FROM emp GROUP BY dept;
-- 找出每个用户最近一笔订单金额
SELECT user_id, MAX_BY(amount, create_time) AS latest_amount FROM orders GROUP BY user_id;

当需要收集为数组时，写 COLLECT_LIST 或 COLLECT_SET（官方文档化）：

SELECT COLLECT_LIST(product_id) FROM orders GROUP BY user_id;   -- 含重复
SELECT COLLECT_SET(product_id) FROM orders GROUP BY user_id;    -- 去重
-- ARRAY_AGG 在引擎中可执行，但官方文档未列出，推荐用 COLLECT_LIST

JSON

默认推荐：`GET_JSON_OBJECT(json_str, path)` —— 直接对 STRING 求值，不需要任何 SET，是 text2sql 最稳的默认方案。

-- DO
SELECT GET_JSON_OBJECT(log, '$.user_id');
SELECT GET_JSON_OBJECT(log, '$.user.name');                -- 嵌套
SELECT GET_JSON_OBJECT(log, '$.items[0].id');              -- 数组元素

-- DON'T
SELECT log->>'user_id';                    -- PostgreSQL 风格不支持
SELECT JSON_EXTRACT(log_str, '$.k');       -- 第一参数必须是 JSON 类型而非 STRING（报 0130121）

JSON 类型路径（`JSON_EXTRACT` / JSON 字面量 / `CAST(... AS JSON)`）——需要先显式启用 JSON 类型系统：

SET odps.sql.type.json.enable=true;
SELECT JSON_EXTRACT(CAST(log_str AS JSON), '$.k') FROM t;  -- 先 CAST 成 JSON 再 _EXTRACT
SELECT JSON '{"a": 1}' AS j;                                -- JSON 字面量

不开启时直接 JSON_EXTRACT(STRING, ...) 报 ODPS-0130121: invalid type STRING of argument 1 for function json_extract, expect JSON。

当需要批量提取JSON多个字段时，用 JSON_TUPLE 配合 LATERAL VIEW 更高效：

SELECT t.id, j.user_id, j.action
FROM logs t
LATERAL VIEW JSON_TUPLE(t.log, 'user_id', 'action') j AS user_id, action;

类型转换

类型转换只用 CAST(expr AS type)，目标类型用 MaxCompute 类型名（不是 MySQL 风格的 CHAR/SIGNED 等）：

CAST(col AS STRING)         -- 转字符串（不是 CAST AS CHAR / CAST AS TEXT）
CAST(col AS BIGINT)         -- 转整数（不是 CAST AS SIGNED / CAST AS INTEGER）
CAST(col AS DOUBLE)         -- 转浮点
CAST(col AS DECIMAL(10,2))  -- 转精确数值

---

三、日期格式字符串

MaxCompute日期格式中，`mm`是月份，`mi`是分钟。这是最容易出错的地方。

-- 正确的格式字符串
TO_CHAR(d, 'yyyy-mm-dd')           -- 2024-01-15（mm=月）
TO_CHAR(d, 'yyyy-mm-dd hh:mi:ss')  -- 2024-01-15 14:30:00（mi=分）
TO_CHAR(d, 'yyyymmdd')             -- 20240115

-- DON'T: 把分钟写成mm
TO_CHAR(d, 'yyyy-mm-dd hh:mm:ss')  -- 错误！mm是月份，会输出 2024-01-15 14:01:00

格式字符对照: yyyy=年, mm=月, dd=日, hh=时, mi=分, ss=秒

三套时间单位（重要：函数间不通用）

MaxCompute 同时存在三套时间单位字符串，必须按函数选用，混用会报错：

-- DON'T: TRUNC_TIME 不接受短名
TRUNC_TIME(event_time, 'dd')   -- 报错：invalid datePart

-- DO
TRUNC_TIME(event_time, 'day')  -- 正确
DATETRUNC(event_time, 'dd')    -- 正确（DATETRUNC 用短名）

---

四、分区表规则

查询分区表时，WHERE中必须包含分区列条件

-- DON'T: 全表扫描，会被拒绝执行
SELECT * FROM orders;

-- DO
SELECT * FROM orders WHERE dt = '2024-01-15';
SELECT * FROM orders WHERE dt >= '2024-01-01' AND dt <= '2024-01-31';

分区过滤位置取决于 JOIN 类型

放 WHERE 还是 ON 不影响分区剪裁（CBO 会下推），但影响 JOIN 语义：

• INNER JOIN：放 WHERE 或 ON 等价，推荐 WHERE，更直观。
• LEFT/RIGHT OUTER JOIN：保留侧过滤放 WHERE；非保留侧（NULL 补齐侧）过滤必须放 ON——放 WHERE 会过滤掉 JOIN 不上时补齐的 NULL 行，使 OUTER 退化成 INNER。
• LEFT SEMI / LEFT ANTI JOIN：右表（被检查存在性的表）过滤必须放 ON——SEMI/ANTI 不输出右表列，WHERE 引用右表列会报错。

-- INNER JOIN: 推荐 WHERE
SELECT a.order_id, b.name
FROM orders a JOIN users b ON a.user_id = b.user_id
WHERE a.dt = '2024-01-15' AND b.dt = '2024-01-15';

-- LEFT JOIN: 右表分区过滤必须放 ON
SELECT a.order_id, b.name
FROM orders a LEFT JOIN users b
  ON a.user_id = b.user_id AND b.dt = '2024-01-15'
WHERE a.dt = '2024-01-15';

-- LEFT ANTI JOIN: 右表分区过滤必须放 ON
SELECT u.user_id
FROM users u
LEFT ANTI JOIN orders o
  ON u.user_id = o.user_id AND o.ds = '${bizdate}'
WHERE u.ds = '${bizdate}';

全表扫描需显式设置

分区表没有分区过滤时，MaxCompute默认拒绝执行。如确需全表扫描：

SET odps.sql.allow.fullscan=true;
SELECT * FROM partitioned_table;

分区列通常是STRING类型，即使表示日期

分区值的具体格式由建表时定义，format 不是 MaxCompute 强制的，必须看实际表 schema/sample。两种常见格式都合法：

-- 紧凑格式（MaxCompute 调度系统 ${bizdate} 默认产出，最常见）
WHERE dt = '20240115'
WHERE dt >= '20240101' AND dt <= '20240131'

-- ISO 格式（部分项目约定）
WHERE dt = '2024-01-15'

字符串字典序比较对两种格式都成立。生成 SQL 前如不确定，先用 SHOW PARTITIONS table_name 或读 sample 数据确认。

本文档其他章节示例为简洁可读，多用 '2024-01-15' 写法；text2sql 实际生成时以目标表的真实分区格式为准。

获取最新分区数据时，用 MAX_PT

SELECT * FROM dim_users WHERE dt = MAX_PT('project_name.dim_users');

---

五、类型陷阱

STRING 与数值比较显式 CAST

-- DON'T: 隐式转换可能精度丢失
SELECT * FROM t WHERE string_col > 2;

-- DO
SELECT * FROM t WHERE CAST(string_col AS BIGINT) > 2;

DECIMAL(precision, scale) 需要 odps2 模式

DECIMAL(p,s) 带精度的 DECIMAL 类型默认 project 模式下报错（"precision and scale is not currently supported"），需要 SET odps.sql.decimal.odps2=true; 启用。

SET odps.sql.decimal.odps2=true;
SELECT CAST(1.1 AS DECIMAL(10,2));   -- 启用后可用

DECIMAL 与 DOUBLE 不要混合运算

-- DON'T: 2.2是DOUBLE字面量，会导致整个表达式降级为DOUBLE
SELECT CAST(1.1 AS DECIMAL(10,2)) + 2.2;

-- DO: 所有操作数统一为DECIMAL
SELECT CAST(1.1 AS DECIMAL(10,2)) + CAST(2.2 AS DECIMAL(10,2));

整数字面量默认是 INT 类型（溢出 INT 值域才升 BIGINT），参与 DECIMAL 运算需 CAST

来源：[MaxCompute 2.0 数据类型版本] 文档原文："整型常量的语义会默认为 INT 类型……如果常量过长，超过了 INT 的值域而又没有超过 BIGINT 的值域，则会作为 BIGINT 类型处理"。

-- DON'T: 100 是 INT，与 DECIMAL 列相除的精度行为不可靠
SELECT amount / 100 FROM orders;

-- DO: 显式 CAST 为 DECIMAL，避免精度截断
SELECT amount / CAST(100 AS DECIMAL(10,2)) FROM orders;

金额推荐 DECIMAL(18,2)，单价推荐 DECIMAL(18,4)。

---

六、MaxCompute特有函数

本章列出第二章函数映射之外的MaxCompute特有函数。

数组操作

ARRAY_CONTAINS(tags, 'vip')           -- 判断包含
SIZE(arr_col)                          -- 数组长度
SORT_ARRAY(arr_col)                    -- 排序
ARRAY_DISTINCT(arr_col)                -- 去重
ARRAY_JOIN(arr_col, ',')               -- 拼接为字符串

MAP操作

properties['city']                     -- 下标取值
MAP_KEYS(map_col)                      -- 提取所有键
MAP_VALUES(map_col)                    -- 提取所有值
STR_TO_MAP('k1=v1&k2=v2', '&', '=')  -- 字符串转MAP

其他

DECODE(status, 1, 'A', 2, 'B', 'unknown')  -- 类似CASE WHEN简写
APPROX_DISTINCT(user_id)              -- 近似去重（大数据场景）
CONCAT_WS(',', a, b, c)               -- 分隔符拼接（**任一参数 NULL 整体返回 NULL**，与 MySQL/Hive 不同）
REGEXP_COUNT(s, '\\d+')               -- 正则匹配计数
GREATEST(a, b, c) / LEAST(a, b, c)    -- 最大/最小值
MEDIAN(col)                           -- 中位数（任意数值列）
PERCENTILE(int_col, 0.5)              -- 百分位精确算法（**官方文档限 BIGINT；DOUBLE 输入会静默截断为整数，DECIMAL 报错**）
PERCENTILE_APPROX(numeric_col, 0.5)   -- 百分位近似算法（DOUBLE 等数值列均可，**保留小数**，DOUBLE 场景推荐用此）

---

七、DQL扩展语法

QUALIFY — 窗口函数结果过滤，避免嵌套子查询

-- DON'T: 用子查询包装
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM emp
) tmp WHERE rn = 1;

-- DO: 用QUALIFY更简洁
SELECT * FROM emp
QUALIFY ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) = 1;

LEFT SEMI JOIN / LEFT ANTI JOIN — 代替 EXISTS / NOT EXISTS

-- 当需要 EXISTS 语义时，写 LEFT SEMI JOIN
SELECT a.* FROM users a LEFT SEMI JOIN orders b ON a.id = b.user_id;

-- 当需要 NOT EXISTS 语义时，写 LEFT ANTI JOIN
SELECT a.* FROM users a LEFT ANTI JOIN orders b ON a.id = b.user_id;

PIVOT / UNPIVOT

-- 行转列
SELECT * FROM t
PIVOT (SUM(amount) FOR category IN ('food' AS food, 'drink' AS drink)) AS pvt;

-- 列转行
SELECT * FROM t
UNPIVOT INCLUDE NULLS (value FOR metric IN (revenue, cost, profit)) AS unpvt;

Hint（小表广播 / MAPJOIN）

SELECT /*+ MAPJOIN(small) */ * FROM big JOIN small ON big.id = small.id;

何时加：右表（被广播表）小到能放进单 worker 内存（默认阈值 128MB，由 odps.optimizer.auto.mapjoin.threshold 控制），且 CBO 没自动选 MAPJOIN 时。CBO 默认会自动判断小表广播，多数情况下不需要显式 Hint。显式 Hint 在小表超阈值时反而触发 ODPS-0123065: Join exception ... small table exceeds，此时应去掉 Hint 或调大 odps.sql.mapjoin.memory.max。非等值 JOIN / 笛卡尔积场景必须加 Hint（CBO 不会自动加）。

其他Hint: SKEWJOIN(倾斜)、RANGEJOIN(范围)、DYNAMICFILTER(动态过滤)、DISTMAPJOIN(分布式MapJoin)、CONDITIONALJOIN(条件JOIN)、SELECTIVITY(选择率)、MATERIALIZE(物化子查询)

LIKE ANY / LIKE ALL — 多模式匹配

SELECT * FROM t WHERE name LIKE ANY ('%张%', '%李%', '%王%');
SELECT * FROM t WHERE tags LIKE ALL ('%vip%', '%active%');

WINDOW 命名窗口

SELECT user_id, SUM(amount) OVER w AS total, ROW_NUMBER() OVER w AS rn
FROM orders WHERE dt = '2024-01-15'
WINDOW w AS (PARTITION BY user_id ORDER BY create_time);

集合操作

SELECT * FROM t1 UNION ALL SELECT * FROM t2;    -- 合并(含重复，推荐)
SELECT * FROM t1 UNION SELECT * FROM t2;         -- 合并(去重，代价高)
SELECT * FROM t1 INTERSECT SELECT * FROM t2;     -- 交集
SELECT * FROM t1 MINUS SELECT * FROM t2;         -- 差集 (MINUS = EXCEPT)

LIMIT

SELECT * FROM t LIMIT 10;
SELECT * FROM t LIMIT 10 OFFSET 20;

其他扩展语法速查

\* 时间旅行（TIMESTAMP/VERSION AS OF）仅支持 Transactional Table 2.0 / Iceberg / Delta 等具备版本化能力的表；普通 ODPS 表上使用会报错。

\\ WITH RECURSIVE：offline 模式默认可用，MCQA / MCQA2 模式拒绝（编译期报 rcte.session.mode）。需要在 MCQA 下用，先 SET odps.mcqa.disable=true;。迭代次数上限由 odps.sql.rcte.max.iterate.num 控制（默认 10，硬上限 100）。

---

八、正则表达式

正则规范因模式而异：

• 默认（legacy）模式：MaxCompute 自定义正则规范（Java 兼容子集）
• Hive 兼容模式（SET odps.sql.hive.compatible=true;）：完整 Java 正则规范

SQL 字符串中反斜杠必须双重转义（客户端提交时统一规则）。

-- \d 在SQL中写成 \\d
SELECT * FROM t WHERE col RLIKE '\\d{4}-\\d{2}-\\d{2}';
SELECT REGEXP_EXTRACT(text, '(1[3-9]\\d{9})', 1) AS phone FROM t;
SELECT REGEXP_REPLACE(str, '[^0-9]', '') AS digits_only FROM t;

\d \w \s 可用，*? +? 非贪婪可用，(?=...) 前向断言可用。

---

九、窗口函数Frame

当使用累计求和、移动平均时，必须显式指定 ROWS frame

默认 frame 行为依赖模式，做累计聚合 / 移动平均时必须显式指定 ROWS（否则 Hive vs 非 Hive 模式下结果不同）：

• Hive 兼容模式（SET odps.sql.hive.compatible=true）：默认 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• 非 Hive 模式 + ORDER BY + 聚合函数（AVG/COUNT/MAX/MIN/STDDEV/SUM）：默认 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

注：ROW_NUMBER / RANK / DENSE_RANK 等位置类窗口函数不需要显式 ROWS 子句。仅累计/移动聚合需要。

-- DON'T: 依赖默认RANGE frame
SELECT SUM(amount) OVER (ORDER BY dt) FROM t;

-- DO: 显式指定ROWS
SELECT SUM(amount) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t;

-- 移动平均: 显式指定窗口大小
SELECT AVG(amount) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM t;

ROWS=物理行计数，RANGE=逻辑值范围（重复值同组），GROUPS=按组（SQL:2011标准）。

支持EXCLUDE: EXCLUDE CURRENT ROW / EXCLUDE GROUP / EXCLUDE TIES / EXCLUDE NO OTHERS

---

十、SQL 数量限制

以下限制经常触发报错，生成SQL时需注意不要超出。

SQL 文本与执行计划规模：

集合与 JOIN：

子查询返回行数：

对象与分区规模：

结果与数据：

---

十一、运算符差异

百分比/比率计算: 整数除法会截断为0，必须 CAST(... AS DOUBLE) 或 CAST(... AS DECIMAL)。详见第五章类型陷阱。

---

十二、数据类型

特有类型:

• DATETIME — 不带时区的日期时间，字面量: DATETIME '2024-01-01 00:00:00'
• TIMESTAMP_NTZ — 无时区的Timestamp（区别于DATETIME和TIMESTAMP）
• STRING — 不限长字符串，大多数场景使用STRING而非VARCHAR
• JSON — JSON类型，支持可选schema: JSON<name:STRING, age:INT>
• VECTOR(type, dim) — AI向量类型
• BLOB — 二进制大对象
• GEOGRAPHY — 地理空间类型

复杂类型:

• ARRAY<STRING> — 数组
• MAP<STRING, INT> — 键值对
• STRUCT<name:STRING, age:INT> — 结构体

字面量:

DATETIME '2024-01-01 00:00:00'
JSON '{"key": "value"}'           -- 需先 `SET odps.sql.type.json.enable=true;` 启用 JSON 类型
INTERVAL '1' DAY                  -- 需先 `SET odps.sql.type.system.odps2=true;` 启用 INTERVAL_DAY_TIME 类型
INTERVAL '1-2' YEAR TO MONTH      -- 同上，需 odps2 启用 INTERVAL_YEAR_MONTH 类型
CURRENT_DATE() / CURRENT_TIMESTAMP()
-- 注：CURRENT_DATE 无括号形式需先 `SET odps.sql.hive.compatible=true;`；
--     LOCALTIMESTAMP 在 MaxCompute 不可用，请使用 CURRENT_TIMESTAMP() 代替；
--     `CURRENT_TIMESTAMP()` 默认返回 `TIMESTAMP`；若 `SET odps.sql.timestamp.function.ntz=true`，则返回 `TIMESTAMP_NTZ`

---

十三、常用 SET 参数速查表

会话级参数，写在 SQL 前用 SET key=value;。仅列文本生成会用到的；运行期资源调优类（mapper/reducer memory 等）见 sql_common_errors.md。

兼容性 / 方言开关

限制开关

优化器 Hint（Hint 形式优先于 SET）

odps2 与 Hive 兼容模式不互斥：odps2 控制类型系统，Hive 兼容控制 SQL 语法。两者都可同时开启。

MaxCompute SQL 错误恢复手册

SQL 执行失败后的决策参考。错误消息已自解释、用户无需任何动作的错误此处不展开。

自动重试策略

DML 写入幂等性提醒：自动重试默认仅安全用于 SELECT、编译期错误、以及 INSERT OVERWRITE 这类幂等写入。INSERT INTO / MERGE / UPDATE / DELETE 不能盲目重试——同一作业可能已部分提交，重试会重复写入或与上次结果冲突。重试这类语句前必须先确认 instance 状态（成功/失败/未提交）。

如何匹配条目

错误码前缀定位大类：0130xxx 编译 / 0123xxx 运行 / 0140xxx Sandbox / 1850xxx MCQA。

ODPS-0130071 和 ODPS-0010000 是 wrapper（同码含多种子场景），必须用消息关键词分流，见下文。

专用语义错误码

ODPS-0130071 是通用语义异常 wrapper（覆盖大量子场景）

不要试图穷举 0130071 的子场景——它是 MaxCompute 兜底语义错误码，所有没有专用错误码的语义异常都用它。

正确流程： 1. 先比对上面"专用语义错误码"表 → 命中则按那条修复 2. 错误码确为 0130071 → 按消息关键词查下文"高频子场景"

---

编译期错误

SQL 规模超限（三个同根错误码）

触发阶段不同，修复方向一致：

修复（只能降 SQL 复杂度，无 SET 开关）：

• 拆 CTE 或多条 INSERT OVERWRITE 串联，用中间表
• 减 JOIN 层数；利用分区裁剪缩小叶子扫描规模
• 单 SELECT 建议 ≤ 5 个窗口函数（软上限）；UNION ALL ≤ 256 张

ODPS-0010000 wrapper：Plan 过大 vs worker OOM（消息分流）

错误码 0010000 复用于两种性质相反的场景，必须按消息文本判断：

修复顺序建议（worker OOM）： 1. 看 logview / instance summary，识别失败的 task 类型与具体算子（HashJoin / SortMerge / WindowAgg / UDF） 2. 查倾斜：DistinctValueCounts、JOIN key 分布、单 task input bytes 是否远高于平均 3. 倾斜先按倾斜处理（SKEWJOIN hint / 加随机后缀 / 拆热点 key），再考虑加内存 4. 上述都不奏效，且数据量本身就大 → 调对应阶段内存或拆 SQL 5. 易误判：看到 Size of Plan 字样不要调 stage 内存（根因是编译期计划大小）；HashJoin/窗口算子膨胀有时光加内存解决不了，得先治倾斜

ODPS-0130071 高频子场景

ODPS-0130252 笛卡尔积不允许

几个约定俗成的 rewrite pattern：

Dummy key 全组合是 N×M 笛卡尔积，结果集会爆炸式增长。只在以下条件全满足时使用：(1) 业务明确需要全组合；(2) 两侧基数都很小且可控；(3) 用户接受存储/计算成本；(4) 配合 /*+ MAPJOIN(small) */ 把小表广播。否则用窗口函数 / 分组聚合 / 半笛卡尔（带筛选条件的 JOIN）替代。

兜底（慎用）：SET odps.sql.allow.cartesian=true;

ODPS-0123091 脏值 CAST 失败

加 CAST() 无效——CAST 本身就是报错处。数据里有脏值。

修复（按优先级）： 1. 预过滤：WHERE col RLIKE '^-?[0-9]+$' 后再 CAST —— 显式控制脏值如何处理（剔除/标记/单独表） 2. 上游审计：把脏值写入审计表 / 加日志，不要静默吞噬 3. 兜底：SET odps.function.strictmode=false; 或 SET odps.sql.udf.strict.mode=false;（公有云常见前者，不同环境参数名可能不同）。这个开关不是"忽略整行"——它让无效 CAST 转成 NULL 通过，整行其他列照常输出。所以下游必须能区分"业务 NULL"和"CAST 失败 NULL"，否则会造成数据质量问题

---

运行时错误

ODPS-0123065 Join exception

两种触发路径，错误文本不直接区分：

调大 MAPJOIN 内存：SET odps.sql.mapjoin.memory.max=<N>;（单位 MB，常见 1024-4096）。同时检查 JOIN key 是否严重倾斜。

ODPS-0123131 User defined function exception

UDF 抛异常 / 输入数据使 UDF 出错。

修复：

• 对照 UDF 签名检查输入列类型
• UDF 内部加 try/catch 兜底，避免脏值导致整个作业失败
• 上游加 WHERE 预过滤脏值
• 优化 UDF 代码（可能要 UDF 作者修）

ODPS-0123144 UDF 超时

错误文本含 kInstanceMonitorTimeout + usually caused by bad udf performance。根因是 UDF 操作太慢（死循环/复杂算法/外部调用）。

修复（按推荐顺序）： 1. 先定位慢点：看 logview UDF profiling，识别是死循环 / 复杂算法 / 外部调用 / 还是个别坏数据卡住 2. 上游加 WHERE 预过滤异常输入（坏数据是常见根因，比 timeout 调大见效快） 3. SET odps.sql.executionengine.batch.rowcount=32;（减小单批处理量，缓解 batch 内坏数据连锁） 4. 优化 UDF 代码（根治：去除外部慢调用、加缓存、改算法） 5. 临时放宽 timeout：SET odps.sql.udf.timeout=3600;（范围 0-3600s，默认 600；odps.function.timeout 是旧 alias）—— 仅在前面措施都不奏效且需赶时间产出时使用

ODPS-1850001 MCQA 查询加速模式限制

ODPS-0140171 Sandbox violation / archive 加载失败

三种不同性质错误共用这个错误码，不能一刀切：

重要：odps.ext.hive.lazy.simple.serde.native=true 不是 ACL 绕过 flag，只是切换读取代码路径。Sandbox 保护的是 Java 代码加载安全，对"数据层面权限拒绝"无效。

UDF 注册 / 调用问题

按错误根因分两类：

(A) 注册侧问题（jar / 注解 / 类签名 / 环境）—— 修 UDF 本身，不动调用 SQL

(B) 调用侧问题（参数类型不匹配）—— 改 SQL 的输入类型

MaxCompute SQL 常用模式模板

面向 text2sql 场景，提供 MaxCompute 常用 DQL（SELECT 查询） 模板。生成 SQL 时优先匹配这些模式，而非从零构造。

模板使用约定：

• ${bizdate} 是 MaxCompute 调度系统的日期变量（当前业务日期）。生成 SQL 时按用户意图替换为具体日期字符串（如 '2024-01-15'）或 TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyy-mm-dd')（昨天）。
• 模板中的列名（order_id、user_id 等）为占位符，请替换为目标表的真实列；尽量列出明确列名而非 SELECT *。当外层模板必须保留源表所有列（如 PIVOT 输出、纯翻页透传）时可保留 *。

---

1. 每组取Top N（每组前N）

自然语言示例："每个部门薪资最高的3个人"

SELECT employee_id, name, department, salary
FROM (
    SELECT employee_id, name, department, salary,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
    WHERE ds = '${bizdate}'
) tmp
WHERE rn <= 3;

注意: 不要用 GROUP BY + LIMIT，MaxCompute的LIMIT是全局的。必须用窗口函数。子查询里只 SELECT 后续会用到的业务列 + rn，外层不要返回 rn 给最终结果。

---

2. 去重保留最新一条

自然语言示例："每个用户保留最近一条订单"

SELECT order_id, user_id, amount, create_time
FROM (
    SELECT order_id, user_id, amount, create_time,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn
    FROM orders
    WHERE ds = '${bizdate}'
) tmp
WHERE rn = 1;

变体 — 按多列去重:

SELECT user_id, product_id, qty, update_time
FROM (
    SELECT user_id, product_id, qty, update_time,
           ROW_NUMBER() OVER (PARTITION BY user_id, product_id ORDER BY update_time DESC) AS rn
    FROM user_products
    WHERE ds = '${bizdate}'
) tmp
WHERE rn = 1;

---

3. 累计求和 / 运行总计

自然语言示例："按日期累计销售额"

SELECT
    ds,
    daily_amount,
    SUM(daily_amount) OVER (ORDER BY ds ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM daily_sales
WHERE ds >= '2024-01-01' AND ds <= '2024-01-31';

注意: 必须显式指定 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，不要依赖默认frame。

---

4. 同比 / 环比

自然语言示例："每月销售额及环比增长率"

SELECT
    month_id,
    amount,
    LAG(amount, 1) OVER (ORDER BY month_id) AS prev_month_amount,
    ROUND((amount - LAG(amount, 1) OVER (ORDER BY month_id))
          / LAG(amount, 1) OVER (ORDER BY month_id) * 100, 2) AS mom_growth_pct
FROM monthly_sales
ORDER BY month_id
LIMIT 10000;

同比（去年同期）:

SELECT
    month_id,
    amount,
    LAG(amount, 12) OVER (ORDER BY month_id) AS same_month_last_year,
    ROUND((amount - LAG(amount, 12) OVER (ORDER BY month_id))
          / LAG(amount, 12) OVER (ORDER BY month_id) * 100, 2) AS yoy_growth_pct
FROM monthly_sales
ORDER BY month_id
LIMIT 10000;

---

5. 连续N天活跃

自然语言示例："连续登录3天以上的用户"

SELECT user_id, MIN(ds) AS start_date, MAX(ds) AS end_date, COUNT(*) AS consecutive_days
FROM (
    SELECT user_id, ds,
           DATEADD(TO_DATE(ds, 'yyyy-mm-dd'),
                   -ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ds), 'dd') AS grp
    FROM (
        SELECT DISTINCT user_id, ds
        FROM user_login
        WHERE ds >= '2024-01-01' AND ds <= '2024-01-31'
    ) t1
) t2
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

核心思路: 日期减去行号，连续日期会得到相同的grp值。

⚠️ 前提：模板假设 ds 是 ISO 格式（'2024-01-15'）。若实际表 ds 是紧凑格式（'20240115'），把 TO_DATE(ds, 'yyyy-mm-dd') 改为 TO_DATE(ds, 'yyyymmdd')，并相应调整 WHERE 范围字面量为 '20240101'/'20240131'。

---

6. 行转列 (PIVOT)

自然语言示例："每个用户各类型订单的总金额"

⚠️ 部分 project 不支持 PIVOT —— 如执行报 "unsupported syntax" 错误，回退到方式二 CASE WHEN。UNPIVOT 已正式可用。

方式一: PIVOT

SELECT *
FROM (
    SELECT user_id, order_type, amount
    FROM orders
    WHERE ds = '${bizdate}'
) src
PIVOT (
    SUM(amount)
    FOR order_type IN ('food' AS food, 'drink' AS drink, 'other' AS other_type)
) pvt;
-- PIVOT 限制：
--   * PIVOT (...) 顶层必须是聚合函数，不能再嵌一层普通函数（写 ROUND(SUM(amount),2) 报错）
--   * 聚合函数内部允许标量表达式（如 SUM(amount * rate)）
--   * 不能混入窗口函数 / 表函数；IN 列表的值必须是常量字面量

方式二: CASE WHEN (推荐，类型值不确定或 PIVOT 不可用时)

SELECT
    user_id,
    SUM(CASE WHEN order_type = 'food' THEN amount ELSE 0 END) AS food_amount,
    SUM(CASE WHEN order_type = 'drink' THEN amount ELSE 0 END) AS drink_amount
FROM orders
WHERE ds = '${bizdate}'
GROUP BY user_id;

---

7. 列转行 (UNPIVOT)

自然语言示例："将月度指标列转为行"

方式一: UNPIVOT (推荐)

SELECT user_id, metric_name, metric_value
FROM monthly_metrics
UNPIVOT (
    metric_value FOR metric_name IN (revenue, cost, profit)
) unpvt
WHERE ds = '${bizdate}';

方式二: UNION ALL (备选)

SELECT user_id, 'revenue' AS metric_name, revenue AS metric_value FROM monthly_metrics WHERE ds = '${bizdate}'
UNION ALL
SELECT user_id, 'cost', cost FROM monthly_metrics WHERE ds = '${bizdate}'
UNION ALL
SELECT user_id, 'profit', profit FROM monthly_metrics WHERE ds = '${bizdate}';

---

8. 数组展开 (LATERAL VIEW)

自然语言示例："展开用户标签数组"

SELECT t.user_id, tag.tag_value
FROM user_tags t
LATERAL VIEW EXPLODE(t.tags) tag AS tag_value
WHERE t.ds = '${bizdate}';

带索引的展开:

SELECT t.user_id, tag.pos AS tag_index, tag.val AS tag_value
FROM user_tags t
LATERAL VIEW POSEXPLODE(t.tags) tag AS pos, val
WHERE t.ds = '${bizdate}';

OUTER (保留无标签用户):

SELECT t.user_id, tag.tag_value
FROM user_tags t
LATERAL VIEW OUTER EXPLODE(t.tags) tag AS tag_value
WHERE t.ds = '${bizdate}';

---

9. JSON字段提取（JSON 提取）

自然语言示例："从JSON日志中提取用户行为"

-- 提取单层字段
SELECT
    GET_JSON_OBJECT(log_content, '$.user_id') AS user_id,
    GET_JSON_OBJECT(log_content, '$.action') AS action,
    GET_JSON_OBJECT(log_content, '$.timestamp') AS event_time
FROM raw_logs
WHERE ds = '${bizdate}';

-- 提取嵌套字段
SELECT
    GET_JSON_OBJECT(log_content, '$.user.name') AS user_name,
    GET_JSON_OBJECT(log_content, '$.user.age') AS user_age
FROM raw_logs
WHERE ds = '${bizdate}';

-- 提取JSON数组元素
SELECT
    GET_JSON_OBJECT(log_content, '$.items[0].name') AS first_item_name
FROM raw_logs
WHERE ds = '${bizdate}';

---

10. MAP操作

自然语言示例："从属性MAP中提取指定键"

-- 提取MAP值
SELECT user_id, properties['city'] AS city
FROM user_profiles
WHERE ds = '${bizdate}';

-- MAP展开为行
SELECT t.user_id, kv.key AS prop_key, kv.value AS prop_value
FROM user_profiles t
LATERAL VIEW EXPLODE(t.properties) kv AS key, value
WHERE t.ds = '${bizdate}';

-- 字符串解析为MAP再提取
SELECT
    STR_TO_MAP(params, '&', '=')['source'] AS traffic_source
FROM page_views
WHERE ds = '${bizdate}';

---

11. 分区动态获取（最新分区）

自然语言示例："查询最新分区的数据"

-- 方式一: MAX_PT (推荐)
SELECT user_id, name, status
FROM dim_users
WHERE ds = MAX_PT('project_name.dim_users');

-- 方式二: 子查询
SELECT user_id, name, status
FROM dim_users
WHERE ds = (SELECT MAX(ds) FROM dim_users WHERE ds <= '${bizdate}');

---

12. EXISTS / NOT EXISTS 改写

自然语言示例："查找没有下过单的用户"

方式一: LEFT ANTI JOIN (推荐)

SELECT u.*
FROM users u
LEFT ANTI JOIN orders o ON u.user_id = o.user_id AND o.ds = '${bizdate}'
WHERE u.ds = '${bizdate}';

方式二: NOT EXISTS

SELECT u.*
FROM users u
WHERE u.ds = '${bizdate}'
  AND NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.user_id AND o.ds = '${bizdate}'
  );

"有下单记录的用户" — LEFT SEMI JOIN

SELECT u.*
FROM users u
LEFT SEMI JOIN orders o ON u.user_id = o.user_id AND o.ds = '${bizdate}'
WHERE u.ds = '${bizdate}';

---

13. N日留存 / Cohort Retention

自然语言示例："各日新用户的次日 / 7日 / 30日留存"。

-- 关键：cohort_ds 必须用"全量历史 MIN(ds)"求出，否则把老用户首次活跃日截断到分析窗口起点，
-- 会把老用户误算成"新用户"。先求 cohort，再过滤 cohort_ds 落入分析窗口。
WITH cohort_full AS (
    -- 全量历史下每个用户的首次活跃日（不限分析窗口）
    SELECT user_id, MIN(ds) AS cohort_ds
    FROM events
    GROUP BY user_id
),
cohort AS (
    -- 仅保留首次活跃日落在分析窗口内的（即真正的"新用户"）
    SELECT user_id, cohort_ds
    FROM cohort_full
    WHERE cohort_ds >= '20240101' AND cohort_ds <= '20240131'
),
active AS (
    -- 分析窗口 + 30 日观察期内的活跃记录（去重到天粒度）
    SELECT DISTINCT user_id, ds
    FROM events
    WHERE ds >= '20240101' AND ds <= '20240301'
)
SELECT
    c.cohort_ds,
    COUNT(DISTINCT c.user_id) AS cohort_size,
    COUNT(DISTINCT CASE WHEN DATEDIFF(TO_DATE(a.ds, 'yyyymmdd'), TO_DATE(c.cohort_ds, 'yyyymmdd'), 'dd') = 1
                        THEN a.user_id END) AS d1_retained,
    COUNT(DISTINCT CASE WHEN DATEDIFF(TO_DATE(a.ds, 'yyyymmdd'), TO_DATE(c.cohort_ds, 'yyyymmdd'), 'dd') = 7
                        THEN a.user_id END) AS d7_retained,
    COUNT(DISTINCT CASE WHEN DATEDIFF(TO_DATE(a.ds, 'yyyymmdd'), TO_DATE(c.cohort_ds, 'yyyymmdd'), 'dd') = 30
                        THEN a.user_id END) AS d30_retained
FROM cohort c
LEFT JOIN active a ON c.user_id = a.user_id
GROUP BY c.cohort_ds
ORDER BY c.cohort_ds;

关键点：

• cohort_ds 求全量历史最小值，再过滤窗口落点 —— 否则老用户首次活跃日被截到窗口起点，会被误算成"新用户"
• DATEDIFF(active_day, cohort_day, 'dd') 算 N 日偏移，按 N 分桶 COUNT(DISTINCT)
• ds 是 'yyyymmdd' 格式则用 TO_DATE(..., 'yyyymmdd')；ISO 'YYYY-MM-DD' 用 'yyyy-mm-dd'

---

14. 区间查找 / Range Join

自然语言示例："查找每个事件所属的时间段"

-- Hint 里的表名必须用实际别名 p（被广播侧），不是原表名 time_periods
SELECT /*+ RANGEJOIN(p, 86400) */
    e.event_id, e.event_time, p.period_name
FROM events e
JOIN time_periods p
    ON e.event_time >= p.start_time AND e.event_time < p.end_time
WHERE e.ds = '${bizdate}';

注意: Range Join 需要加 Hint 优化性能。RANGEJOIN(table, N) 中的 N 是预估匹配范围，单位与连接列同单位——若列是 BIGINT 秒时间戳则 N 单位为秒（86400=1天），若列是毫秒时间戳则 N 也是毫秒。

---

15. 多维聚合 (GROUPING SETS / CUBE / ROLLUP)

自然语言示例："按区域、产品分别统计，同时要总计"

SELECT
    region,
    product,
    SUM(amount) AS total,
    GROUPING(region) AS g_region,
    GROUPING(product) AS g_product
FROM sales
WHERE ds = '${bizdate}'
GROUP BY GROUPING SETS ((region, product), (region), ())
ORDER BY region, product
LIMIT 10000;

• GROUPING(col) = 0 表示该列在当前分组中（参与分组）；= 1 表示该列被聚合（NULL 占位）
• GROUPING_ID(a, b, ...) 把多个 GROUPING() 合并为一个 bitmap 整数
• CUBE(a, b) = 所有组合 (a,b), (a), (b), ()
• ROLLUP(a, b) = 层次组合 (a,b), (a), ()

---

16. 翻页查询

自然语言示例："查第3页，每页10条"

MaxCompute支持 LIMIT ... OFFSET ...，也可用 ROW_NUMBER 实现更灵活的分页。

WITH ranked AS (
    SELECT order_id, user_id, amount, create_time,
           ROW_NUMBER() OVER (ORDER BY order_id) AS rn
    FROM orders
    WHERE ds = '${bizdate}'
)
SELECT order_id, user_id, amount, create_time
FROM ranked
WHERE rn BETWEEN 21 AND 30
ORDER BY rn
LIMIT 10;

核心思路: ROW_NUMBER 编号后用 BETWEEN 取指定范围。页码 P、每页 N 条时：WHERE rn BETWEEN (P-1)*N+1 AND P*N。

---

17. CTE 多层复杂查询

自然语言示例："高价值用户的订单统计"

当查询包含多层嵌套、同一逻辑被引用多次时，优先用 WITH...AS 提高可读性。

WITH high_value_users AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    WHERE ds >= '2024-01-01' AND ds <= '2024-01-31'
    GROUP BY user_id
    HAVING SUM(amount) > 10000
),
user_orders AS (
    SELECT o.user_id, o.order_id, o.amount, o.ds
    FROM orders o
    JOIN high_value_users h ON o.user_id = h.user_id
    WHERE o.ds >= '2024-01-01' AND o.ds <= '2024-01-31'
)
SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM user_orders
GROUP BY user_id
ORDER BY total_amount DESC
LIMIT 100;

递归CTE: WITH RECURSIVE cte AS (基础查询 UNION ALL 递归查询) SELECT * FROM cte;

递归 CTE 运行约束（不写终止条件直接生成不可运行 SQL）：

• 仅 offline 模式默认可用；MCQA / MCQA2 拒绝（编译期报 rcte.session.mode）。在 MCQA 下用前先 SET odps.mcqa.disable=true;
• 默认最大迭代 10，硬上限 100，更大值被截断；调大用 SET odps.sql.rcte.max.iterate.num=100;
• 递归分支必须有收敛终止条件（如 WHERE level < 10），否则即使迭代上限也会撞上限直接报错

Text2SQL 通用生成原则

自然语言问题 + 表结构生成 SELECT 查询时读取此文件。本文只负责意图解析、schema 映射、结果粒度、JOIN、聚合、过滤和输出契约；MaxCompute 语法、函数、分区格式和 SET 参数由 maxcompute_select_guide.md 覆盖。

---

1. 生成顺序

1. 确认可用表和字段：只使用上下文提供的 schema，不臆造表名、列名、枚举值、日期上下文或 join key。 2. 确定结果粒度：一行代表实体明细、时间粒度、分组结果，还是排名结果。 3. 选择最小查询：只 SELECT 必要列，只 JOIN 必要表，不默认 DISTINCT，不加无关业务过滤。 4. 一次性写出 MaxCompute 可执行 SQL：本文只做逻辑规划；最终语法以 maxcompute_select_guide.md 为准，不产出 ANSI SQL 中间稿。

如果没有可用表，或 schema 无法回答问题，返回空 SQL 并说明缺失信息。

---

2. Schema 映射

• 优先使用表描述、字段注释和值域提示；注释比字段名更贴近用户意图时，按注释选列并写入 assumptions。
• 用户提到实体属性（名称、状态、年龄、类目等）时，选择承载该属性的实体表，即使用户没有直接说表名。
• 没有现成指标时才派生计算，例如收入可由 price * quantity 得到；派生逻辑写入 explanation 或 assumptions。
• 两表无可靠 join path 时不要硬连；可查桥表/关系表，仍不确定则返回空 SQL 或写明假设。

---

3. 聚合与过滤

• 问记录/订单/事件数量通常用 COUNT(*)；问用户/客户/商品数量通常用 COUNT(DISTINCT entity_id)，除非 schema 已保证一行一个实体。
• 聚合查询中，SELECT 的非聚合列必须进入 GROUP BY；聚合结果过滤用 HAVING。
• 比率、分类、条件计数用 CASE WHEN 或方言层提供的等价函数，并明确分子分母。
• 有值域提示时优先按值域解析过滤值；不要编造枚举。
• 相对时间必须依赖提供的日期上下文；没有日期上下文时使用占位符并写入 assumptions。
• 表结构标记分区列时，时间范围优先落到分区列；如果方言要求分区过滤但用户未给时间范围，按方言层选择最新分区、占位符或全表扫描开关，并写入 assumptions。

---

4. JOIN 与排名

• 先确定主表：通常是问题主体实体或事实表。
• 需要保留主表所有行时使用 LEFT JOIN，例如"所有用户及其订单数"。
• 只需要匹配行时使用 INNER JOIN，例如"下过单的用户"。
• 每个 JOIN 必须有显式 ON 条件；禁止逗号隐式 JOIN。
• 警惕一对多 JOIN 放大指标；聚合前先把多端表压到目标粒度。
• 全局 Top/Bottom 用 ORDER BY + LIMIT；用户未给 N 时按上下文决定是否加 LIMIT，并写入 assumptions。
• "每组 Top N"、"每个实体最新一条" 使用 ROW_NUMBER() / RANK() 窗口函数，不要用全局 LIMIT 代替。

---

5. 输出契约

如果用户或调用方指定了输出格式，优先遵守该格式。否则，自然语言生成 SELECT 时返回纯 JSON，不要包 markdown code fence：

{
  "sql": "<generated SELECT query>",
  "explanation": "<brief explanation>",
  "tables": ["table1"],
  "assumptions": ["assumption if any"]
}

无法回答时：

{
  "sql": "",
  "explanation": "Cannot generate SQL: <reason>",
  "tables": [],
  "assumptions": []
}

SQL 排版要求：主要子句分行，JOIN 的 ON 条件缩进，WHERE 条件逐行列出，SQL 关键字大写，字符串字面量用单引号。

---

6. 反模式

• SELECT *，除非模板或用户明确要求透传所有列。
• 编造表、字段、枚举值、日期上下文或 join key。
• 没有 ON 条件的 JOIN；用全局 LIMIT 代替每组 Top N。
• 聚合查询遗漏 GROUP BY；用 WHERE 过滤聚合结果。
• 不必要的 DISTINCT。
• 在问题未要求时加额外业务过滤。