三元运算符生成器 UDF

UDFs User Defined Functions | ClickHouse Docs https://clickhouse.com/docs/en/sql-reference/functions/udf

def gen_sql_zone_if(range_list, field='cnt', group_val_flag='g'):
    """
    [0,2)[2,3)[3,7)
    与 if 函数相同。
    CK-sql语法: cond ? then : else
    e.g
    [[1, 3], [3, 6], [7, 9]]
    (cnt>=1 and cnt<3) ? g0 : (cnt>=3 and cnt<6) ? g1 : (cnt>=7 and cnt<9) ? g2 : g3
    [[3, 3], [3, 6], [7, 7]]
    cnt<3 ? g0 : (cnt>=3 and cnt<6) ? g1 : cnt>=7 ? g2 : g3
    """
    n = len(range_list)
    n1 = n - 1
    pre = ''
    ret = ''
    else_flag = 'else'
    for i in range(n):
        l, r = range_list[i]
        cond = f'''({field}>={l} and {field}<{r})'''
        if l == r:
            if i == 0:
                # 负无穷
                cond = f'''{field}<{r}'''
            elif i == n1:
                # 正无穷
                cond = f'''{field}>={l}'''
            else:
                assert False, "区间端点值配置错误"
        ret = f'''{cond} ? {group_val_flag}{i} : {else_flag}'''
        if pre:
            ret = pre.replace(else_flag, ret)
        pre = ret
        ret = ret.replace(else_flag, f'''{group_val_flag}{n}''')
    return ret

翻译

搜索

复制