Kusto表中拆分动态字符串并求和第4个值的实现方法

现有如下Kusto表：

datatable(str:string) [
    "a,b,2,10,d,e;a,b,c,14,d,e;a,b,c,10,d,e",
    "a,b,c,11,d,e;a,b,c,12,d,e;a,b,c,13,d,e;a,b,c,10,d,e",
    "a,b,c,20,d,e;a,b,c,25,d,e",
]

需求：将每行中按分号分隔的每个子串里的第4个数值相加，示例结果如下：

10+14+10=34
11+12+13+10=46
20+25=45

我编写的单行列函数可实现单个字符串的计算：

let calculateCostForARow = (str:string) {
    print row = split(str,";")
    | mv-expand row
    | parse row with * "," * "," * "," cost:long "," * 
    | summarize sum(cost)
};
calculateCostForARow("a,b,c,11,d,e;a,b,c,12,d,e;a,b,c,13,d,e;a,b,c,10,d,e")

但将其应用到整张表时，使用toscalar会出现问题：

let calculateCostForARow = (str:string) {
    toscalar(print row = split(str,";")
    | mv-expand row
    | parse row with * "," * "," * "," cost:long "," * 
    | summarize sum(cost))
};
datatable(str:string) [
    "a,b,c,10,d,e;a,b,c,10,d,e;a,b,c,10,d,e",
    "a,b,c,10,d,e;a,b,c,10,d,e;a,b,c,10,d,e;a,b,c,10,d,e",
    "a,b,c,10,d,e;a,b,c,10,d,e",
]
| project calculateCostForARow(str)

请问还有其他可行的实现方法吗？

方法1：直接表级操作（无需自定义函数） #

不需要单独编写函数，直接通过mv-expand拆分每行的子串，提取数值后按原始行分组求和：

datatable(str:string) [
    "a,b,2,10,d,e;a,b,c,14,d,e;a,b,c,10,d,e",
    "a,b,c,11,d,e;a,b,c,12,d,e;a,b,c,13,d,e;a,b,c,10,d,e",
    "a,b,c,20,d,e;a,b,c,25,d,e",
]
| mv-expand split(str, ";") as sub_str
| parse sub_str with * "," * "," * "," cost:long "," *
| summarize total_cost = sum(cost) by str

方法2：修正自定义标量函数写法 #

如果坚持使用自定义函数，可先将中间结果存入临时变量，再用toscalar提取求和值：

let calculateCostForARow = (str:string) {
    let temp_result = print row = split(str, ";")
    | mv-expand row
    | parse row with * "," * "," * "," cost:long "," * 
    | summarize sum(cost);
    toscalar(temp_result)
};
datatable(str:string) [
    "a,b,2,10,d,e;a,b,c,14,d,e;a,b,c,10,d,e",
    "a,b,c,11,d,e;a,b,c,12,d,e;a,b,c,13,d,e;a,b,c,10,d,e",
    "a,b,c,20,d,e;a,b,c,25,d,e",
]
| project total_cost = calculateCostForARow(str)

方法3：用正则提取简化逻辑 #

利用extract_all正则表达式一次性提取所有子串中的第4个数值，再求和：

datatable(str:string) [
    "a,b,2,10,d,e;a,b,c,14,d,e;a,b,c,10,d,e",
    "a,b,c,11,d,e;a,b,c,12,d,e;a,b,c,13,d,e;a,b,c,10,d,e",
    "a,b,c,20,d,e;a,b,c,25,d,e",
]
| extend costs = extract_all(@"[^,]+,[^,]+,[^,]+,(\d+)", str)
| mv-expand costs to typeof(long)
| summarize total_cost = sum(costs) by str

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