Parent Child Hierarchies (I)

本文将延续施阳老师的《BOM树形结构分解》,细化路径图的构造以及展开。由于篇幅的缘故,所有的内容将分为多个章节进行讲解。第一章节的主题为通过M语言构造用于分析的数据。

Group1Group2
Lv1A000001A000002
Lv2B000001B000002
Lv3C000001C000002
Lv4D000001D000002

在以上表格的每一组中,Lv4的成员隶属于Lv3的成员,Lv3的成员隶属于Lv2的成员,Lv2的成员隶属于Lv1的成员。在现实生活中这种隶属关系经常用以下的方式进行表达:

ParentChild
A000001A000001
A000001B000001
B000001C000001
C000001D000001
A000002A000002
A000002B000002
B000002C000002
C000002D000002

在第二个表格中,处于字段Child的成员隶属于处于同一行中字段Parent对应的成员。不难发现,"D"开头的成员隶属于"C"开头的成员,"C"开头的成员隶属于"B"开头的成员,"B"开头的成员隶属于"A"开头的成员,"A"开头的成员隶属于自身,这说明了第一个表格与第二个表格所表达的含义是相同的。接下来,将通过M语言仿制以上表格,为了方便解释代码,最大层数MaxLv被固定为4,目标表格的行数Row也被固定为4,这意味着目标表格只含有1组。首先, 需要构造以下ChildList串列:

以下为生成这种串列所需的代码:

let
    ChildList=
        Text.ToList(
            Text.FromBinary(
                #binary(
                    List.Numbers(
                        65,
                        MaxLv 
                    )
                )
            )
        )
in
    List.Buffer( ChildList )

在以上代码中,#binary()接受的参数是65为首,步长为1,最后一个元素为65+MaxLv-1=68的串列{65..68}。然后,Text.FromBinary()代入#binary()的结果后生成连续的英文字母文本"ABCD",最后该文本被Text.ToList()分解为目标串列{"A","B","C","D"}。

为构造出目标表格,还需要以下ParentList串列:

以下为得到ParentList所需要的代码:

let
    ParentList=
        List.Combine(
            {
                {"A"},
                List.RemoveLastN(
                    ChildList,
                    1
                )
            }
        )
in
    List.Buffer( ParentList )

在以上代码中,ParentList是基于ChildList构造的,因为ParentList不需要ChildList的最后一个元素,所以使用List.RemoveLastN()移除了该元素。接着利用List.Combine()把"A"置于移除最后一个元素的ChildList的开头就完成ParentList的构造。

在构造好ParentList和ChildList之后就可以通过以下代码生成目标表格。

let
    PCTable=
        Table.FromRows(
            List.TransformMany(
                List.Numbers(
                    1,
                    Row / MaxLv
                ),
                each
                    List.Numbers(
                        0,
                        MaxLv
                    ),
                (x, y) ⇒
                    let
                        NumInText = Number.ToText( x, "000000" )
                    in
                        {
                            Text.Combine(
                                {
                                    ParentList{y},
                                    NumInText
                                }
                            ),
                            Text.Combine(
                                {
                                    ChildList{y},
                                    NumInText
                                }
                            )
                        }
            ),
            type table [ Parent = text, Child = text ]
        )
in
    PCTable

由于Row和MaxLv被假定为4,所以Row/MaxLv为1,这意味着List.TransformMany()的第一个参数为{1},第二个参数为{0,1,2,3}。一般而言,List.TransformMany()会把第一个参数中的每一个元素作为变量x依次传入第三个参数中,在固定x的值后把第二个参数中的每一个元素作为变量y依次传入第三个参数中。在此例中,第一个参数的唯一元素作为变量x传入第三个参数中后会通过Number.ToText()转化为"000001", 然后依次把第二个参数中的每一个元素作为变量y传入到第三个参数中提取出ParentList与ChildList的第y+1个的元素,最后把提取出的两个字母分别与"000001"结合,历遍第二个参数后得到复合串列:{{"A000001", "A000001"},{"A000001", "B000001"},{"B000001", "C000001"},{"C000001", "D000001"}}。这个串列通过Table.FromRows()的转化得到下图所示表格:

如果需要产生行数固定为Row且每组最大层数不是固定但最多为26的表格,需要使用以下代码:

let
    Source =
        List.Generate(
            () ⇒
                [
                    FieldA=
                        List.Numbers(
                            0,
                            Number.RandomBetween( 0, MaxLv )
                        ),
                    FieldB = 1,
                    FieldC = List.Count( FieldA )
                ],
                each  [FieldC] ﹤= ( Row + MaxLv - 1 ),
                each
                    [
                        FieldA=
                            List.Numbers(
                                0,
                                Number.RandomBetween( 0, MaxLv )
                            ),
                        FieldB = [FieldB] + 1,
                        FieldC = [FieldC] + List.Count( FieldA )
                    ],
                each
                    Table.FromRows(
                        List.Transform(
                            [FieldA],
                            (x) ⇒
                                let
                                    NumInText = Number.ToText( [FieldB], "000000" )
                                in
                                    {
                                        Text.Combine(
                                            {
                                                ParentList{x},
                                                NumInText
                                            }
                                        ),
                                        Text.Combine(
                                            {
                                                ChildList{x},
                                                NumInText
                                            }
                                        )
                                    }
                        ),
                        type table [Parent=text, Child=text]
                    )
        ),

    mTable = Table.Combine( Source ),

    RemoveRows = Table.FirstN( mTable, Row )
in
    RemoveRows

为方便解释代码,目标表格的行数Row被固定为10,最大层数MaxLv被设为4,并且以上图为例讲解循环的过程。List.Generate()的第一个参数为一记录,该记录有三个字段,字段FieldA的值为{0,1,2},字段FieldB的值为1,字段FieldC的值为{0,1,2}的串列长度。因为字段FieldC的值等于3,且该值小于等于(Row+MaxLv-1)=13,第一个参数会作为自变量传入第四个参数。在第四个参数中,List.Transform()会历遍字段FieldA的值的每一个元素使之提取出ParentList和ChildList的第x+1个元素,然后取出的两个字母会分别与由字段FieldB的值转化而来的"000001"结合起来,完成历遍后得到复合串列:{{"A000001", "A000001"},{"A000001", "B000001"},{"B000001", "C000001"}}。这个串列代入Table.FromRows()后得到#table(type table [Parent=text, Child=text], {{"A000001", "A000001"},{"A000001", "B000001"},{"B000001", "C000001"}})。之后进入下一轮循环,字段FieldA的值被更新为{0,1,2,3}, 字段FieldB的值被更新为累计的循环次数(2),字段FieldC的值被更新为累计的表格行数(7)。因为此时字段FieldC的值为7,且该值小于等于13,更新后的记录会作为自变量传入第四个参数。在第四个参数中,List.Transform()会历遍字段FieldA的值的每一个元素使之提取出ParentList与ChildList的第x+1个的元素,然后取出的两个字母会分别与由字段FieldB的值转化而来的"000002"结合起来,完成循环后得到复合串列:{{"A000002", "A000002"},{"A000002", "B000002"},{"B000002", "C000002"}, {"C000002", "D000002"}}。这个串列代入Table.FromRows()后得到#table(type table [Parent=text, Child=text], {{"A000002", "A000002"},{"A000002", "B000002"},{"B000002", "C000002"}, {"C000002", "D000002"}})。之后进入下一轮循环,假设字段FieldA的值被更新为{0,1,2,3}, 字段FieldB的值被更新为累计的循环次数(3),字段FieldC的值被更新为累计的表格行数(11)。因为此时FieldC的值为11,且该值小于等于13,更新后的记录会作为自变量传入第四个参数。在第四个参数中,List.Transform()会历遍字段FieldA中的每一个元素使之提取出ParentList与ChildList的第x+1个的元素,然后取出的两个字母会分别与由FieldB的值转化而来的"000003"结合起来,完成历遍后得到复合串列:{{"A000003", "A000003"},{"A000003", "B000003"},{"B000003", "C000003"}, {"C000003", "D000003"}}。这个串列代入Table.FromRows()后得到#table(type table [Parent=text, Child=text], {{"A000003", "A000003"},{"A000003", "B000003"},{"B000003", "C000003"}, {"C000003", "D000003"}})。之后进入下一轮循环,假设字段FieldA的值被更新为{0,1,2,3}, 字段FieldB的值被更新为累计的循环次数(4),字段FieldC的值被更新为累计的表格行数(15)。因为此时字段FieldC的值为15大于Row+MaxLv-1=13,所以循环至此结束,更新后的记录不会被传入第四个参数。List.Generate()的结果为含有三个子表格的串列,需要使用Table.Combine()上下合并为一个表格,但是由于上下合并后的表格有11行,而目标表格只需要10行,所以需要使用Table.FirstN()提取前10行。

附件

发表评论

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