Skip to main content
  1. 经验技巧分享/
  2. thinking in SQL | 废话SQL,思维与实践/

什么是表函数

··985 字
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 函数