套装组合问题

题目:

现有一张订单明细表,包含下单时间、订单编号以及商品SKU码,一笔订单可能会购买多件商品,所以一个订单编号对应一个或多个SKU码。
以及一张套装组合表,共有5种套装,每个套装包含若干个商品SKU。

若某笔订单下的商品能够构成套装,则认为该订单是卖出套装而产生。
要求每种套装卖出多少套,并列出购买套装的订单编号。

解法:

let
    订单编号 = Excel.CurrentWorkbook(){[Name="订单编号"]}[Content],
    订单分组 = Table.Group(订单编号, {"所在订单编号"}, {"SKU列表", each [SKU码]}),
    套装组合 = Excel.CurrentWorkbook(){[Name="套装组合"]}[Content],
    套装分组 = Table.Group(套装组合, {"套装ID"}, {"SKU列表", each [SKU码]}),
    套装订单编号 = Table.AddColumn(套装分组, "符合套装订单编号", each Text.Combine(List.Transform(Table.SelectRows(订单分组,(x)=>List.ContainsAll(x[SKU列表],[SKU列表]))[所在订单编号],Text.From),"/")),
    删除多余列 = Table.RemoveColumns(套装订单编号,{"SKU列表"})
in
    删除多余列

前面4行分别将两张表导入查询编辑器,并使用分组功能按照订单号和套装ID将SKU码聚合到一个list中。

接下来对分组后的套装表添加一列, 对分组后的订单表进行筛选。
如果订单表中的list包含套装表中list的所有SKU码,则是套装订单。
筛选完成后,得到所有符合条件的订单号list,再用Text.Combine将list合并为一个文本。
又因为默认的订单编号的数据类型为number,所以合并之前还要用List.Transform+Text.From转换为text。
最后一步将多余的列删除即可。

附件

20 Replies to “套装组合问题”

  1. Text.Combine(List.Transform(Table.SelectRows(订单分组,(x)=>List.ContainsAll(x[SKU列表],[SKU列表]))[所在订单编号],Text.From),"/")

    要是有F9 功能
    (x)=>List.ContainsAll(x[SKU列表],[SKU列表])
    就能方便理解了

    1. 这个套路用过很多次啦~x[SKU列表]表示订单表里的SKU列,[SKU列表]表示套装表里的SKU列。
      类似DAX里的filter(订单表,List.ContainsAll([SKU列表],earlier([SKU列表])),当然DAX里没有包含的函数。
      把这个看懂就懂了http://pqfans.com/1726.html

    1. 注意第一个[SKU列表]前面有个x,在第五行的公式中,有两个表函数:
      先看Table.AddColumn,对象是套装分组表,所以_表示套装分组表本身,_[SKU列表]表示套装分组表中的[SKU列表],其中_可省略简写为[SKU列表]。
      再看Table.SelectRows,对象为订单分组表,本身后面应该也是each _,但正如你所说,如果都用_就无法把两个表区分开来,所以用了(x)=>x的形式,x表示订单分组表本身,x[SKU列表]表示订单分组表中的[SKU列表]。

      1. 豁然开朗啊,刚才详细看了《深入理解函数》,里面的情形与此互为参照了,我觉得你可以写本书了啊~~

      2. 个人觉得写成两个 [KSU列表] 可以写成不一样的,也好区分,比如:skulist 和 skult。
        老师别见笑

      1. 老师我的意思是,这个问题描述里,不是说,“要求每种套装卖出多少套,并列出购买套装的订单编号“ 么,现在只是求出了哪一种套装是否被买过,但是应该怎么求出套装卖出的数量呢?

        1. 这个就比较复杂了,有点类似凑数,并且当卖出的商品可以凑成多种套装组合时,还涉及到优先级的问题,需要更多的约束条件

          1. 迭代展开,一直到展到不包含任一组合。前面的组合就是可能的多套装组合。不过这个案例数据确实都只满足一个套装。

  2. 其中 List.ContainsAll引用的 x[SKU列表]和[SKU列表]是基于当前表格各自的行上下文吧,而不是一整个列对应的大List组,一开始愣是没反应过来

  3. 如果列出购买套装的订单编号,同时列出符合订单编号的数量,需要多加一列。多加个函数也能实现
    请教下是否有办法一次增加2列,直接列出 订单编号 和 符合订单编号 的数量

    1. = Table.SplitColumn(套装分组, "SKU列表", each {Text.Combine(List.Transform(Table.SelectRows(订单分组,(x)=>List.ContainsAll(x[SKU列表],_))[所在订单编号],Text.From),"/"),Table.RowCount(Table.SelectRows(订单分组,(x)=>List.ContainsAll(x[SKU列表],_)))},{"符合套装订单编号","符合套装数量"})

      再次拜读阳神大作,找到了可以用Table.SplitColumn()函数一次添加多列的办法,而且不用删除多余的列

      Table.SplitColumn资料:
      https://pqfans.com/1418.html

      注意,刚才我说的是生成list,这个list可以是根据第二参数所指定的列拆出来的,也可以和原来的列没有半毛钱关系。
      基于这个思路,我们可以玩出一些不按常理出牌的套路:
      = Table.SplitColumn(源, "成绩",each {_&"大爱中华"})

      甚至可以用来一次添加多列:
      = Table.SplitColumn(源, "成绩",each List.Repeat({_},5))

    1. List.Transform({1233,1444,1555}, Text.From) = List.Transform({1233,1444,1555}, each Text.From(_))
      输出结果:{"1233","1444","1555"} 就是把列表里的数值型转成字符型

  4. A00001和A00004”符合套装订单编号“有重复,重复部分应归在A00001.
    A00002和A00005”符合套装订单编号“有重复,重复部分应归在A00002.

发表回复

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