↓跳到主要内容
  1. Skills/
  2. thinking in SQL | 废话SQL，思维与实践/

什么是表函数

··字数 2672·6 分钟
sql 概念
目录

表函数，顾名思义，以表（tabled-value，多行数据集）为返回值的函数。与单行函数、聚合函数相比，它比较小众，以至于有很多多年从事数据开发的人员都闻所未闻。

虽然小众，但并不高深，只是应用场景差异罢了。

表函数是以结果集为单位来返回查询的结果。🤔 已经有子查询、视图、以及构建复杂SQL的 common-table-expression，为什么还会有表函数的存在？

下文借助 sakila 示例数据库 中的 rewards_report 过程来解析，国内无法访问 github的， 戳这里 。

先看示例 sakila 中的过程 rewards_report #

本程序是 mysql 的存储过程，支持在过程中无头 select * from ... 语句查询结果输出打印到终端。oracle、postgres 没有这种用法。

代码位置: routines-mysql.sql#rewards_report ，国内访问此 链接 

CREATE PROCEDURE rewards_report (
      IN min_monthly_purchases TINYINT UNSIGNED
    , IN min_dollar_amount_purchased DECIMAL(10,2)
    , OUT count_rewardees INT
)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT 'Provides a customizable report on best customers'
proc: BEGIN

    DECLARE last_month_start DATE;
    DECLARE last_month_end DATE;

    /* Some sanity checks... */
    IF min_monthly_purchases = 0 THEN
        SELECT 'Minimum monthly purchases parameter must be > 0';
        LEAVE proc;
    END IF;
    IF min_dollar_amount_purchased = 0.00 THEN
        SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
        LEAVE proc;
    END IF;

    /* Determine start and end time periods */
    SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
    SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
    SET last_month_end = LAST_DAY(last_month_start);

    /*
        Create a temporary storage area for
        Customer IDs.
    */
    CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);

    /*
        Find all customers meeting the
        monthly purchase requirements
    */
    INSERT INTO tmpCustomer (customer_id)
    SELECT p.customer_id
    FROM payment AS p
    WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
    GROUP BY customer_id
    HAVING SUM(p.amount) > min_dollar_amount_purchased
    AND COUNT(customer_id) > min_monthly_purchases;

    /* Populate OUT parameter with count of found customers */
    SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;

    /*
        Output ALL customer information of matching rewardees.
        Customize output as needed.
    */
    SELECT c.*
    FROM tmpCustomer AS t
    INNER JOIN customer AS c ON t.customer_id = c.customer_id;

    /* Clean up */
    DROP TABLE tmpCustomer;
END

该过程提供两个输入参数，min_monthly_purchases 月最小支付次数、min_dollar_amount_purchased 月最小支付金额，并返回同时符合这两个条件的客户。

第 1 步，先检查参数。

    /* Some sanity checks... */
    IF min_monthly_purchases = 0 THEN
        SELECT 'Minimum monthly purchases parameter must be > 0';
        LEAVE proc;
    END IF;
    IF min_dollar_amount_purchased = 0.00 THEN
        SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
        LEAVE proc;
    END IF;

第 2 步，转换月初、月末参数：

    /* Determine start and end time periods */
    SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
    SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d');
    SET last_month_end = LAST_DAY(last_month_start);

第 3 步，找到符合条件的客户号：

     /*
        Create a temporary storage area for
        Customer IDs.
    */
    CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);

    /*
        Find all customers meeting the
        monthly purchase requirements
    */
    INSERT INTO tmpCustomer (customer_id)
    SELECT p.customer_id
    FROM payment AS p
    WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
    GROUP BY customer_id
    HAVING SUM(p.amount) > min_dollar_amount_purchased
    AND COUNT(customer_id) > min_monthly_purchases;

最后，输出符合条件的客户信息：

    /*
        Output ALL customer information of matching rewardees.
        Customize output as needed.
    */
    SELECT c.*
    FROM tmpCustomer AS t
    INNER JOIN customer AS c ON t.customer_id = c.customer_id;

例中使用了临时表，并利用 mysql 存储过程可以直接向外输入查询结果的特性，来完成复杂的计算并返回结果。

这在应用开发时非常常见，先输入某个参数来确定某个实体标识，根据返回的结果再获取该实体相关的其它信息。则需要多步才能完成的SQL。

当然，本例仅是例子，正好用来讲解什么是表函数，它可能不是最优的。若懂得写复杂程序更高的 SQL，那么单个完整的 SQL 也可以得到同样的结果。

而在 oracle、postgres 中，存储过程并没有 mysql 的这种特性，取而代之的是 table function ，则表函数。

存储过程与函数的区别 #

幸亏，具有存储过程、自定义函数功能的数据库产品，对这两者的定义是一致的，调用方法也类似。

存储过程（stored procedure）、自定义函数（user-defined function）都是过程（routines），均是用户可以定义固定例程中动作。从字面上理解，存储过程具有一套完整逻辑，而函数则是完成对输入参数的转换或计算，它也可以是一个过程，由此使得初学者非常容易搞混淆。 再从代码内容上看，有时也非常难以区分。

如果从概念上不好理解，那么可以从特征、用途、上下文这些方面的差异来看。

比较 函数 存储过程
参数类型 有输入、输出参数 有输入、输出参数
输出 返回值 输出参数（mysql 比较特殊，可以在存储过程中输出查询结果到终端）
调用方式 SQL 中 函数名(参数) 命令行或客户端 call 过程名(参数)

由于调用方式会限定使用场景，因为从这个角度就比较好理解。

非要用一句话说出它们的区别 ⚠️ 函数是数学上的概念，它是计算器、转换器、过滤器，过程是由多个步骤的操作所组成的程序。它们的定义并不是从相互区别的角度来定义的。

postgres 中的表函数 #

本例是 sakila 示例库的 postgres 版本，代码位于 routines-pg.sql#rewards_report ，国内无法访问 github 的， 请戳👉 

create or replace function rewards_report (
      min_monthly_purchases int
    , min_dollar_amount_purchased DECIMAL(10,2)
    , last_month date default CURRENT_DATE
)
returns setof customer
language plpgsql
as $$
declare
    v_last_month date;
    v_last_month_start DATE;
    v_last_month_end DATE;
BEGIN

    /* Some sanity checks... */
    assert min_monthly_purchases > 0, 'Minimum monthly purchases parameter must be > 0';

    assert min_dollar_amount_purchased > 0.00, 'Minimum monthly dollar amount purchased parameter must be > $0.00';

    /* Determine start and end time periods */
    v_last_month := date_trunc('month', last_month);
    v_last_month_start := date_trunc('month', v_last_month - interval '1 month');
    v_last_month_end := v_last_month_start + '1 month'::interval - '1 day'::interval;

    /*
        Output ALL customer information of matching rewardees.
        Customize output as needed.
    */
    return query
    with pay as
    (
      select customer_id
  --         , payment_date
      from   payment
      where  payment_date BETWEEN v_last_month_start AND v_last_month_end
      group  by customer_id
      having SUM(amount) > min_dollar_amount_purchased
      and    COUNT(1) > min_monthly_purchases
    )
    select customer.*
    from   pay
    inner  join customer
    using  (customer_id);
END $$
;

第 1 步，声明函数返回值为返回多行结果

create or replace function rewards_report (
      min_monthly_purchases int
    , min_dollar_amount_purchased DECIMAL(10,2)
    , last_month date default CURRENT_DATE
)
returns setof customer
language plpgsql

returns setof customer 表示返回值的结构与 customer 表相同，plpgsql 是指让解析器使用 plpgsql 语言来解析代码。

第 2 步，参数检查

    /* Some sanity checks... */
    assert min_monthly_purchases > 0, 'Minimum monthly purchases parameter must be > 0';

    assert min_dollar_amount_purchased > 0.00, 'Minimum monthly dollar amount purchased parameter must be > $0.00';

第 3 步，计算月初、月末

    /* Determine start and end time periods */
    v_last_month := date_trunc('month', last_month);
    v_last_month_start := date_trunc('month', v_last_month - interval '1 month');
    v_last_month_end := v_last_month_start + '1 month'::interval - '1 day'::interval;

第 4 步，返回查询结果

    /*
        Output ALL customer information of matching rewardees.
        Customize output as needed.
    */
    return query
    with pay as
    (
      select customer_id
  --         , payment_date
      from   payment
      where  payment_date BETWEEN v_last_month_start AND v_last_month_end
      group  by customer_id
      having SUM(amount) > min_dollar_amount_purchased
      and    COUNT(1) > min_monthly_purchases
    )
    select customer.*
    from   pay
    inner  join customer
    using  (customer_id);

postgres 版本的 rewards_report 没有使用临时表，而是直接返回符合条件的客户信息。而 mysql 版本是存储过程，除返回客户信息外，还计算了符合条件的客户数并通过 out 参数返回。注意两者间的差别。 关于 postgres 表函数说明，请看官方文档。

表函数的使用场景 #

应用端需要多次访问数据库、分多个步骤才能获得结果的逻辑，可以使用表函数来封装这个过程，以减少与数据库之间的交互次数。

当视图无法满足查询需要，需要通过参数来快速减少目标数据行从而提高返回速度的优化，也可以使用表函数来优化视图查询。

当 SQL 逻辑较为复杂（如出现需要根据不同的标识，通过循环递归找出下属关联数据，典型的有机构树、多级部门等）时，可使用表函数来封装，为应用或工具提供参数查询支持。如 BI 工具展示报表或者仪表盘时，通常需要对当前用户进行数据权限控制，根据当前用户所在机构来确定其可见数据范围。若组织机构关系多级、重叠或边缘情况较多时，此时很能通过一个简单的视图或者SQL可以得到可见机构树、可见数据范围，通过表函数可以简化这个实现。

在数据开发场景，当一个逻辑过于复杂，但团队内又有复用需求时，可以使用表函数来简化其他人的工作。

表函数的具体用法与例子 #

表函数可以当做 表引用 (table reference) 来使用，即表函数可以出现在 SQL 查询中可以使用表、视图的语法位置。

如 postgres 的 generate_series 、sqlite3 的 generate_series 中用来生成序列号的函数。

postgres generate_series #

-- postgres
select * from  generate_series(5,10);
-- 或
select generate_series(5,10);

结果为：

 generate_series
-----------------
               5
               6
               7
               8
               9
              10

若利用序列，来生成一个日期表、多个随机数：

select seq
     , current_date + seq   days
     , random() 
from   generate_series(1,10) as g(seq);

结果为:

 seq |    days    |       random
-----+------------+---------------------
   1 | 2022-06-07 |  0.5527516758024689
   2 | 2022-06-08 |  0.3199863529096909
   3 | 2022-06-09 |  0.3691096135249481
   4 | 2022-06-10 |  0.8725697231037977
   5 | 2022-06-11 |  0.9455529251811186
   6 | 2022-06-12 |  0.8801941403919891
   7 | 2022-06-13 | 0.29041301758386595
   8 | 2022-06-14 |  0.6232114466479182
   9 | 2022-06-15 | 0.20182827894610966
  10 | 2022-06-16 |  0.3596291655157948

sqlite3 generate_series #

select value from generate_series(5,10);

结果为:

| value |
|-------|
| 5     |
| 6     |
| 7     |
| 8     |
| 9     |
| 10    |

各数据库对表函数的文档 #

数据库 文档链接
oracle 14.49 PIPE ROW Statement 、 14.50 PIPELINED Clause 、
postgres F.40. 内置表函数 、 9.25. 返回集的函数 、 38.5.8. SQL Functions as Table Sources 、 38.5.9. SQL Functions Returning Sets 、 38.5.10. SQL Functions Returning TABLE
sqlite3 2.1.2. Table-valued functions 、 series 函数
↑