SQL - Hive UDTF and ANSI SQL TVF

在 Hive SQL 里，UDTF 是 User-Defined Table-Generating Function 的缩写，也叫 用户自定义表生成函数。

• 作用：
  普通的 UDF（User-Defined Function）是一行进一行出的（1→1），UDAF（User-Defined Aggregate Function）是多行进一行出（N→1），而 UDTF 是一行进多行出（1→N）。
  也就是说，输入一条记录，它可以“拆解”或“展开”为多条记录。

• 常见例子：
  Hive 自带的 explode() 就是一个 UDTF，它可以把数组或 map 展开成多行：

   

  -- explode 把数组拆成多行
  SELECT explode(array('a', 'b', 'c')) AS col;
  -- 输出：
  -- a
  -- b
  -- c
  
  -- 如果要和其他列一起用，要配合 LATERAL VIEW
  SELECT id, word
  FROM books
  LATERAL VIEW explode(split(content, ' ')) t AS word;

   

• 特点：

  1. 只能用在 SELECT 子句或 LATERAL VIEW 中。

  2. 每次返回的是一张虚拟表（可能包含多列多行）。

  3. 在 Hive 中实现 UDTF 需要继承 org.apache.hadoop.hive.ql.udf.generic.GenericUDTF。

 

类型	输入 → 输出	调用方式	典型例子
UDF	1 → 1	SELECT my_func(col)	upper()
UDAF	N → 1	SELECT group_func(col) GROUP BY ...	sum()
UDTF	1 → N	SELECT ... FROM ... LATERAL VIEW my_func(col)	explode()

 

 

ANSI SQL 标准里并没有专门的“UDTF”这个术语或函数。
不过在 ANSI SQL 以及其他数据库实现中，有类似的功能，比如 table-valued function（表值函数，TVF）：

• PostgreSQL / SQL Server：
  支持直接定义返回表的函数（返回多行多列），可以在 FROM 子句中调用。

• Oracle：
  可以通过 pipelined table function 来实现类似功能。

• ANSI SQL 标准：
  允许在 FROM 子句里调用能返回一组行的函数，但标准里叫 table function，不是 UDTF。

所以：

• UDTF 这个名字是 Hive 特有的命名，主要出现在 Hadoop 生态。

• ANSI SQL 里对应的概念是 table-valued function，但名称不同，接口风格也可能不同。

 

什么是 table-valued function（TVF）

• 定义：返回“一张表（多行多列）”的函数。与普通标量函数（返回单个值）或聚合函数（多行→一行）不同，TVF 的返回值可以直接放进 FROM 子句当成表来用。

• 调用位置：FROM / JOIN 子句中，像表或子查询一样参与过滤、联接、聚合。

• 相关特性（标准 SQL）：

  • 可以给函数结果取别名，并显式列出列名。

  • 需要相关参数（引用左侧表的列）时，用 LATERAL（标准自 SQL:2008 起引入）来“相关调用”。

  • 可以被嵌套在其它查询中、再做 WHERE / GROUP BY / ORDER BY 等操作。

名字上各家不同：Hive 叫 UDTF；标准里叫 table function；SQL Server 叫 TVF；Oracle 有 pipelined table function；本质一致：函数返回表。

---

标准式语法要点（概念模型）

注意：各数据库 DDL/语法细节不完全一致。下面先给“标准风格”的直觉，再给可运行的方言示例。

1. 定义一个返回表的函数（概念）

-- 伪代码：标准风格（各家语法略有差别）
CREATE FUNCTION top_n_orders(p_n INTEGER)
RETURNS TABLE (order_id INTEGER, customer_id INTEGER, amount DECIMAL(12,2))
RETURN
  SELECT order_id, customer_id, amount
  FROM orders
  ORDER BY amount DESC
  FETCH FIRST p_n ROWS ONLY;  -- 标准的行限制写法

 

2. 在 FROM 中调用

SELECT *
FROM top_n_orders(5) AS t(order_id, customer_id, amount);

 

3. 与基表联接

SELECT c.customer_name, t.order_id, t.amount
FROM customers AS c
JOIN top_n_orders(5) AS t ON t.customer_id = c.customer_id;

 

4. 相关（依赖左表列）调用：LATERAL

-- 对每个客户取该客户自己金额最高的 N 笔订单
SELECT c.customer_id, c.customer_name, o.order_id, o.amount
FROM customers AS c
CROSS JOIN LATERAL (
  SELECT order_id, amount
  FROM orders
  WHERE customer_id = c.customer_id      -- 这里引用了左表 c 的列
  ORDER BY amount DESC
  FETCH FIRST 3 ROWS ONLY
) AS o;

 

5. TVF 内部也能是递归查询（标准的 WITH RECURSIVE），做通用“序列生成器”：

-- 伪代码：返回 [start, stop] 的整数序列
CREATE FUNCTION seq(p_start INTEGER, p_stop INTEGER)
RETURNS TABLE (n INTEGER)
RETURN
  WITH RECURSIVE s(n) AS (
    SELECT p_start
    UNION ALL
    SELECT n + 1 FROM s WHERE n < p_stop
  )
  SELECT n FROM s;

 

调用：

SELECT n FROM seq(1, 5);   -- 1,2,3,4,5

 

配合 LATERAL（相关子表）：

-- 对每位客户，生成 1..rank 的数字，用来展开多行
SELECT c.customer_id, s.n
FROM customers AS c
CROSS JOIN LATERAL seq(1, c.rank) AS s(n);

 

---

典型使用场景

• 封装复杂子查询：把复杂业务逻辑包装成 TVF，FROM tvf(args) 处一行搞定，便于复用与测试。

• 对每行“展开”多行（1→N）：例如“按阈值拆分”、“生成时间刻度/序列”、“多值参数展开”等。

• 依赖左表列的计算：用 LATERAL 做“行级子查询”，比相关子查询更直观。

• 组合式数据管道：TVF 的输出再喂给下一个 TVF，形成可重用的查询管道。

---

各主流数据库的等价写法

下面每段都能直接运行（根据你所用数据库选择）。语义上都体现“函数返回表”。

PostgreSQL（最贴近标准、语法优雅）

定义 TVF（SQL 语言）：

CREATE OR REPLACE FUNCTION top_n_orders(p_n integer)
RETURNS TABLE (order_id int, customer_id int, amount numeric)
LANGUAGE sql
AS $$
  SELECT order_id, customer_id, amount
  FROM orders
  ORDER BY amount DESC
  FETCH FIRST p_n ROWS ONLY
$$;

 

调用与联接：

SELECT * FROM top_n_orders(5) AS t(order_id, customer_id, amount);

SELECT c.customer_name, t.order_id, t.amount
FROM customers c
JOIN top_n_orders(5) t ON t.customer_id = c.customer_id;

 

相关调用（LATERAL）：

SELECT c.customer_id, o.order_id, o.amount
FROM customers c
CROSS JOIN LATERAL (
  SELECT order_id, amount
  FROM orders
  WHERE customer_id = c.customer_id
  ORDER BY amount DESC
  FETCH FIRST 3 ROWS ONLY
) o;

 

SQL Server（T-SQL）

Inline TVF（推荐，性能好）：

CREATE OR ALTER FUNCTION dbo.top_n_orders (@n int)
RETURNS TABLE
AS
RETURN
  SELECT TOP (@n) order_id, customer_id, amount
  FROM dbo.orders
  ORDER BY amount DESC;

 

调用与联接：

SELECT * FROM dbo.top_n_orders(5);

SELECT c.customer_name, t.order_id, t.amount
FROM dbo.customers AS c
JOIN dbo.top_n_orders(5) AS t
  ON t.customer_id = c.customer_id;

 

相关调用（T-SQL 用 APPLY，等价于标准的 LATERAL）：

SELECT c.customer_id, o.order_id, o.amount
FROM dbo.customers AS c
CROSS APPLY (
  SELECT TOP (3) order_id, amount
  FROM dbo.orders
  WHERE customer_id = c.customer_id
  ORDER BY amount DESC
) AS o;
-- 若要保留左侧行即使右侧为空：OUTER APPLY ≈ LEFT JOIN LATERAL

 

Oracle

Pipelined Table Function（PL/SQL）：可逐行“流式”产出，适合大表。

-- 定义记录与表类型
CREATE OR REPLACE TYPE order_row AS OBJECT (
  order_id     NUMBER,
  customer_id  NUMBER,
  amount       NUMBER
);
/
CREATE OR REPLACE TYPE order_tab AS TABLE OF order_row;
/

-- 定义管道函数
CREATE OR REPLACE FUNCTION top_n_orders(p_n IN NUMBER)
  RETURN order_tab PIPELINED
AS
BEGIN
  FOR r IN (
    SELECT order_id, customer_id, amount
    FROM orders
    ORDER BY amount DESC
    FETCH FIRST p_n ROWS ONLY
  ) LOOP
    PIPE ROW(order_row(r.order_id, r.customer_id, r.amount));
  END LOOP;
  RETURN;
END;
/

 

调用（注意 Oracle 的 TABLE()）：

SELECT * FROM TABLE(top_n_orders(5)) t;

 

相关调用（Oracle 也支持 CROSS/OUTER APPLY，相当于 LATERAL）：

SELECT c.customer_id, o.order_id, o.amount
FROM customers c
CROSS APPLY (
  SELECT order_id, amount
  FROM orders
  WHERE customer_id = c.customer_id
  ORDER BY amount DESC
  FETCH FIRST 3 ROWS ONLY
) o;

 

---

实战范例小抄（可改造为你自己的库）

1. 把“常用 TOP N 查询”封装为 TVF

• 好处：集中复用、改一个地方全系统受益、易加权限控制。

2. 序列/日期刻度生成 TVF + LATERAL

• 生成每个用户注册日起的 7 天窗口：

  • 用 seq(start, start+6) 产出 7 行，再 DATEADD/+ interval 得到日期。

  • 外层聚合即可统计“入职后一周的活跃”。

3. 参数化维表展开

• 传入“阈值/标签/枚举”，TVF 返回命中的维度行；在事实表上按需 JOIN。

---

设计与性能建议

• 优先“内联/纯 SQL” TVF（比如 SQL Server 的 inline TVF、Postgres SQL 语言函数）：优化器最容易下推谓词、做连接重排，通常比多语种（PL/pgSQL、T-SQL 多语句）更快。

• 尽量无副作用、不可变（deterministic）：方便缓存与并行。

• 明确列名与类型：给 TVF 的结果显式列定义，调用时也给别名 + 列名列表，避免“列名漂移”。

• 与 LATERAL/APPLY 配合：做“行相关”的 N 条明细展开时，语义直观、性能通常优于相关子查询。

• 权限与可观测性：TVF 是很好的逻辑边界，可以在函数层做授权、记录审计。