DuckDB：创建宏复用业务代码

DuckDB：创建宏复用业务代码

引用

CSDN

1.

https://m.blog.csdn.net/neweastsun/article/details/145162128

DuckDB是一种高性能的嵌入式数据库管理系统，其宏（Macros）功能允许用户定义可重用的代码片段，类似于函数，但在编译时进行文本替换。本文将详细介绍DuckDB宏的定义、分类、重载等特性，并通过多个完整示例展示如何在SQL查询中复用业务代码。

DuckDB Macro

在 DuckDB 中，宏（Macros）是一种可以让用户定义可重用代码片段的机制。它类似于函数，但在编译时会进行文本替换，从而可以在 SQL 查询中方便地复用一些复杂的表达式或查询逻辑。

CREATE MACRO语句可以在数据库中创建标量或表宏（函数）。宏可能只有一条SELECT语句（类似于VIEW），但它有接受参数的好处。

• 定义语法：
  CREATE MACRO macro_name (parameters) AS macro_body;

例如，定义一个简单的宏来计算两个数的和：

CREATE MACRO add_numbers(a INT, b INT) AS a + b;

这里定义了一个名为add_numbers的宏，它接受两个整数参数a和b，宏的主体是a + b，这个表达式会在宏被调用时进行计算。

调用方式与函数类似。使用上面定义的宏，可以在 SQL 查询中这样调用：

SELECT add_numbers(3, 5);

宏的优势

• 代码复用：可以避免在多个查询中重复编写相同的复杂逻辑。例如，如果有一个复杂的条件过滤逻辑，如根据多个列的组合条件来筛选数据，将这个逻辑封装在宏中，就可以在不同的查询中重复使用，减少代码量并提高可维护性。

• 提高可读性：对于复杂的表达式或逻辑，使用宏可以让查询更易读。比如，有一个涉及到多个数学运算和函数调用的复杂计算，将其封装为宏并赋予一个有意义的名字，当在查询中看到这个宏调用时，能更容易理解其目的。

Scalar Macros

对于标量宏，CREATE macro后跟宏的名称，以及一组括号内的可选参数。接下来是关键字AS，后面跟着宏的文本。按照设计，标量宏只能返回单个值。

下面是ifelse宏示例：

CREATE MACRO ifelse(a, b, c) AS CASE WHEN a THEN b ELSE c END;

创建具有公共表表达式的宏。注意，参数名优先于列名。要解决这个问题，可以使用表名消除歧义。

CREATE MACRO plus_one(a) AS (WITH cte AS (SELECT 1 AS a) SELECT cte.a + a FROM cte);

参数带缺省值示例：

CREATE MACRO add_default(a, b := 5) AS a + b;
select add_default(10); # 返回15

创建宏arr_append（功能等同于array_append）：

CREATE MACRO arr_append(l, e) AS list_concat(l, list_value(e));

Table Macros

对于表宏，语法类似于标量宏，只是AS被替换为AS table。表宏可以返回任意大小和形状的表。

没有参数表宏示例：

CREATE MACRO static_table() AS TABLE
    SELECT 'Hello' AS column1, 'World' AS column2;

带参数表宏示例：

CREATE MACRO dynamic_table(col1_value, col2_value) AS TABLE
    SELECT col1_value AS column1, col2_value AS column2;

创建临时表宏，返回多行数据。如果它已经存在，它将被替换（当连接结束时将自动删除）：

CREATE OR REPLACE TEMP MACRO dynamic_table(col1_value, col2_value) AS TABLE
    SELECT col1_value AS column1, col2_value AS column2
    UNION ALL
    SELECT 'Hello' AS col1_value, 456 AS col2_value;

带列表参数表宏：

CREATE MACRO get_users(i) AS TABLE
    SELECT * FROM users WHERE uid IN (SELECT unnest(i));

要在任意表上定义宏，请使用query_table函数。例如，下面的宏计算表上的逐列校验和：

CREATE MACRO checksum(table_name) AS TABLE
    SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR))
    FROM query_table(table_name);
CREATE TABLE tbl AS SELECT unnest([42, 43]) AS x, 100 AS y;
SELECT * FROM checksum('tbl');

宏重载

可以根据参数的数量重载宏，这对标量宏和表宏都适用。通过提供重载，我们可以让add_x（a, b）和add_x（a, b, c）具有不同的函数体。

CREATE MACRO add_x
    (a, b) AS a + b,
    (a, b, c) AS a + b + c;

调用：

SELECT
    add_x(21, 42) AS two_args,
    add_x(21, 42, 21) AS three_args;

宏函数的重载必须在创建时设置，不可能用相同的名称定义两次宏，除非我们先删除首次定义的宏。

宏完整示例

DuckDB有强大的宏机制，允许为常见任务创建简化方式。例如，我们可以定义格式化宏，将非负整数漂亮地打印成一个包含几十亿、百万和千位（不舍入）的短字符串，如下所示：

CREATE MACRO pretty_print_integer(n) AS
    CASE
        WHEN n >= 1_000_000_000 THEN printf('%dB', n // 1_000_000_000)
        WHEN n >= 1_000_000     THEN printf('%dM', n // 1_000_000)
        WHEN n >= 1_000         THEN printf('%dk', n // 1_000)
        ELSE printf('%d', n)
    END;
SELECT pretty_print_integer(25_500_000) AS x;
┌─────────┐
│    x    │
│ varchar │
├─────────┤
│ 25M     │
└─────────┘