如何在ClickHouse自定义函数中设置参数默认值？Oracle函数迁移需求

功能需求 #

需要实现与Oracle函数PercentTEST等价的ClickHouse自定义函数，满足：

• 传入精度参数时，结果按指定位数取整；未传入时保留原始计算值
• 分母为NULL时返回NULL
• 分母为0时返回指定的zeroreject参数值（默认NULL）

Oracle原函数代码 #

CREATE OR REPLACE FUNCTION PercentTEST
  (p_divisor IN number, p_denom IN number, p_round IN number default -1, p_zeroreject IN number default null)
  return number
is
 vReject NUMBER;
begin
  if p_denom is NULL then
    return null;
  elsif p_denom = 0 then
    return p_zeroreject;
  end if;
  vReject:=100*p_divisor/p_denom;
  IF p_round <> -1 THEN
    vReject:=round(vReject,p_round);
  END IF;
  return vReject;
end;

问题背景 #

ClickHouse不支持在函数定义中使用DEFAULT关键字设置默认参数，用户尝试的写法触发语法错误：

CREATE OR REPLACE FUNCTION PercentTEST on cluster 'mycluster' AS (num, denom, precision DEFAULT -1) -> if(isFinite(num / denom), round((100 * num) / denom, precision), NULL)

错误信息：

Code: 62. DB::Exception: Syntax error: failed at position 86 ('DEFAULT'): DEFAULT -1) -> if(isFinite(num / denom), round((100 * num) / denom, precision), NULL). Expected one of: token, Dot, Comma, ClosingRoundBracket, OR, AND, BETWEEN, NOT BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, REGEXP, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, MOD, DIV, IS NULL, IS NOT NULL, alias, AS. (SYNTAX_ERROR) (version 23.3.8.21)

可行解决方案 #

方案1：函数重载（推荐） #

利用ClickHouse的函数重载特性，创建多个同名函数分别对应不同参数个数的调用场景，完全匹配Oracle的调用方式：

1. 2参数版本（不传精度和zeroreject）

CREATE OR REPLACE FUNCTION PercentTEST on cluster 'mycluster' AS (num, denom) -> 
    if(denom IS NULL, NULL,
       if(denom = 0, NULL,
          100 * num / denom))

2. 3参数版本（传入精度参数）

CREATE OR REPLACE FUNCTION PercentTEST on cluster 'mycluster' AS (num, denom, precision) -> 
    if(denom IS NULL, NULL,
       if(denom = 0, NULL,
          if(precision != -1, round(100 * num / denom, precision), 100 * num / denom)))

3. 4参数版本（传入精度和zeroreject参数）

CREATE OR REPLACE FUNCTION PercentTEST on cluster 'mycluster' AS (num, denom, precision, zeroreject) -> 
    if(denom IS NULL, NULL,
       if(denom = 0, zeroreject,
          if(precision != -1, round(100 * num / denom, precision), 100 * num / denom)))

调用示例：

• SELECT PercentTEST(3,8) → 返回37.5（无取整）
• SELECT PercentTEST(3,8,1) → 返回37.5（按1位取整，37.5保留1位仍为37.5）
• SELECT PercentTEST(3,0,-1,0) → 返回0（分母为0时返回指定的zeroreject值）

方案2：可变参数函数 #

通过可变参数接收所有输入，再通过条件判断处理参数默认值，只需创建一个函数：

CREATE OR REPLACE FUNCTION PercentTEST on cluster 'mycluster' AS (args...) -> 
    let num = args[1],
        denom = args[2],
        precision = if(length(args) >= 3, args[3], -1),
        zeroreject = if(length(args) >= 4, args[4], NULL)
    in if(denom IS NULL, NULL,
          if(denom = 0, zeroreject,
             if(precision != -1, round(100 * num / denom, precision), 100 * num / denom)))

调用示例与Oracle完全一致，无需区分参数个数。

两种方案对比：

• 重载方案逻辑清晰，调用方式直观，符合Oracle使用习惯
• 可变参数方案只需维护一个函数，参数扩展更灵活

内容的提问来源于stack exchange，提问作者sunnetcikesenmanyakpipi