三元运算符生成器 UDF
UDFs User Defined Functions | ClickHouse Docs https://clickhouse.com/docs/en/sql-reference/functions/udf
def gen_if_sql(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, "区间端点值配置错误"
_else_flag = f"""{'' if i == n1 else '('}{else_flag}{'' if i == n1 else ')'}"""
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
