DuckDB 宏的两种用法

参考文档
1.把值当做参数，用变量传入宏
下面的宏第一个参数是字符串，第二个参数是字符串列表

set variable va='ggrubuwrwuwbwwbwuuubuggggwwgurbwwubrbbgrurgbbwu';

set variable vb=['grbuu','bbg','rwrwrru','bw','bubrwg','rubrbg','ggubr','uw'];

CREATE or replace MACRO decom2(col1_value, col2_value) AS TABLE
with recursive test as (select col1_value a, col2_value b) --(select 'bike' a, ['bi', 'l','ike']b)-- 
, n as(select length(a) n from test)
, maxlen as(select array_aggr([len(word) for word in b], 'max') maxlen from test)
, test2 as(select col1_value a, list(b order by lenb) b from (select length(b)lenb,list(b) b from unnest(col2_value)b(b) group by length(b)))
, dp as(select repeat([0::bigint], n)dp from n)
, t using key(a)as(
    select True l,a,a a1,b, 1 i, dp from dp,test2
    union
    (with recursive d using key(i) as( 
      select l lo,a,a1,b,i, 1 le, dp d from t
      union
      select a[i-le+1:i] in b[le] and i-(le) =0,a,a[i-le+1:i],b,i, le+1, d[1:i-1]||
                 [case when a[i-le+1:i] in b[le] then d[i]+case when i-(le) >0 then d[i-(le)] else 1 end else d[i] end]
               ||d[i+1:] from  n, maxlen,d where le<=least(i, maxlen)
      )
    select lo,a,a1,b,d.i+1 lv, d from d  where d.i<=length(a) --and le=d.i+1
    )
)
select a,dp[length(a)]from t where i=length(a)+1;
--select i,a,b,a1,dp from t;

select * from decom2(getvariable('va') , getvariable('vb'));

┌─────────────────────────────────────────────────┬───────────────┐
│                        a                        │ dp[length(a)] │
│                     varchar                     │     int64     │
├─────────────────────────────────────────────────┼───────────────┤
│ ggrubuwrwuwbwwbwuuubuggggwwgurbwwubrbbgrurgbbwu │    1801368315 │
└─────────────────────────────────────────────────┴───────────────┘
Run Time (s): real 0.389 user 0.864000 sys 0.548000

2.把表名当做参数传入宏
这个表名既可以是物理表，也可以是CTE中的虚拟表，用query_table函数转换，下面宏的两个参数都是表名

CREATE or replace MACRO decom4(col1_value, col2_value) AS TABLE
with recursive  
test2 as(select a,b from query_table(col1_value),
            (select list(b order by lenb) b from
                (select length(b)lenb,list(b) b from query_table(col2_value) group by length(b))
          ))
, n as(select length(a) n from test2)
, maxlen as(select array_aggr([len(word) for word in b], 'max') maxlen from test2)

, dp as(select repeat([0::bigint], n)dp from n)
, t using key(a)as(
    select True l,a,a a1,b, 1 i, dp from dp,test2
    union
    (with recursive d using key(i) as( 
      select l lo,a,a1,b,i, 1 le, dp d from t
      union
      select a[i-le+1:i] in b[le] and i-(le) =0,a,a[i-le+1:i],b,i, le+1, d[1:i-1]||
                 [case when a[i-le+1:i] in b[le] then d[i]+case when i-(le) >0 then d[i-(le)] else 1 end else d[i] end]
               ||d[i+1:] from  n, maxlen,d where le<=least(i, maxlen)
      )
    select lo,a,a1,b,d.i+1 lv, d from d  where d.i<=length(a) --and le=d.i+1
    )
)
select a,dp[length(a)]from t where i=length(a)+1;


with recursive co as(
select trim(unnest(string_split(c, ',')))b from (from read_csv('2419-input01.txt', header=0, delim='-')t(c) limit 1)), 
w as(select a from read_csv('2419-input01.txt', header=0, delim='-', skip=2) t(a))
from decom4('w','co');

┌─────────┬───────────────┐
│    a    │ dp[length(a)] │
│ varchar │     int64     │
├─────────┼───────────────┤
│ bbrgwb  │             0 │
└─────────┴───────────────┘

上面查询中读取的数据文件2419-input01.txt的内容

r, wr, b, g, bwu, rb, gb, br

bbrgwb