模糊匹配(优化篇)

不难发现,《匹配范围》中提到的第三种解题思路也适用于模糊匹配。接下来,先交代一下背景:假设有一表格(表格Data)只含有地址,现需要根据另一含有城市的表格(表格LookupTB)匹配出运费。

表格Data
表格LookupTB

这个问题最直接的解决方法是为表格Data添加一计算列对表格LookupTB进行筛选,只保留城市为地址一部分的行,以下为该思路对应的代码:

let
    AddCol =
        Table.AddColumn(
            Data,
            "nTable",
            (x)=˃
                Table.SelectRows(
                    LookupTB,
                    (y)=˃
                        Text.StartsWith(
                            x[地址],
                            y[城市]
                        )
                ),
                type text
        ),
    ExpandCol =
        Table.ExpandTableColumn(
            AddCol,
            "nTable",
            {"运费"}
        ),
    DataType =
        Table.TransformColumnTypes(
            ExpandCol,
            {
                "运费",
                type number
            }
        )
in
    DataType

笔者测试了使用不同行数的表格Data运行以上代码所需要的运行时间,其结果如下图所示:

第二种方法从表格LookupTB出发,通过一系列转换使表格LookupTB能够和表格Data进行内连接,以下为该思路对应的代码(结果为表格ParaTB):

let
    Distinction = 
        List.Buffer(
            List.Distinct(Data[地址])
        ),
    ToRows = Table.ToRows(LookupTB),
    CartesianProduct =
        List.TransformMany(
            ToRows,
            (x)=˃
                List.Split(
                    List.Select(
                        Distinction,
                        (y)=˃
                            Text.Contains(
                                y, 
                                List.First(x)
                            )
                    ),
                    1
                ),
            (w,j)=˃
                List.Combine(
                    {
                        w,
                        j
                    }
                )
        ),
    ToTable =
        Table.FromRows(
            CartesianProduct,
            type table
                [
                    城市 = text,
                    运费 = number,
                    地址 = text
                ]
        )
in
    ToTable

虽然以上代码的思路与《匹配范围》的第三种解题思路如出一辙,但是实现的过程牵涉到比较抽象的List.Transformnay(),所以仍需要详细地解释一番。为了完成需要的转换,首先需要把表格Data的地址列提取出来使之转换为串列,并使用List.Distinct()移除串列中重复的元素。然后,需要以行为单位把表格LookupTB转换为复合串列并传递至List.Transformany()作为该函数的第一个参数。接着,List.Transformany()会依次传递第一个参数中的每一个元素到第二个参数和第三个参数。第二个参数是对移除重复后的地址串列进行筛选而来的,移除重复后的每一个地址会与当前的城市进行比较,如果地址中包含该城市,这个地址就会被保留,否则就会被移除。请注意每一个被保留的地址会依次传递至第三个参数,被传递至第三个参数的当前地址会成为由第一个参数传递至第三个参数的当前元素的最后一个元素。最后,使用Table.FromRows()把复合串列还原为表格就完成所有的转换,下图为转换后的结果:

表格ParaTB

完成转换后,就可以对表格Data(左表)与表格ParaTB(右表)进行内连接,然后展开运费就完成解题。以下为这些步骤对应的代码:

let
    InnerJoin =
        Table.NestedJoin(
            Data,
            "地址",
            ParaTB,
            "地址",
            "nTable",
            JoinKind.Inner
        ),
    Expansion =
        Table.ExpandTableColumn(
            InnerJoin,
            "nTable",
            {"运费"}
        ),
    DataType =
        Table.TransformColumnTypes(
            Expansion,
            {
                "运费",
                type number
            }
        )
in
    DataType

笔者测试了使用不同行数的表格Data运行以上代码所需要的运行时间,其结果如下图所示:

附件

6 Replies to “模糊匹配(优化篇)”

  1. 虽然说免费的东西 楼主没有义务让所有人都懂,但既然公开写东西就应该尽量让更多的人看懂,逻辑尽量清晰点,否则白写,也做无用功。
    很多东西还是没交代清楚,我想半天才看懂的。
    完全可以更简洁一点的
    简单说就是
    1.将 地址表,价格表 导入同一个 let in(在同一个查询 定义两个变量,一个变量代表一张表,比如源1,源2)
    2.在 源1表,也就是地址表 添加列,输入each Table.SelectRows(价格表,(x)=>Text.Contains(_[地址],x[城市])))
    核心点无非就是 如果 地址文本 包含 城市文本 则把这行筛选出来。
    最后再展开价格

    1. 本文涉及的卡迪尔内积不好解释,还请见谅。之所以需要使用比较复杂的解法是希望数据比较多时也能在比较少的运行时间内得到结果。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注