筛选表的几种方法效率比较

Power Query 阿森纳里 ⋅ 于 2020-01-01 16:36:41 ⋅ 最后回复由 libo5563 2020-01-27 20:56:32 ⋅ 1152 阅读

因为要根据某种规则筛选表,我找到几种方法,因为数据源有几百万行,所以需要比较一下几种方法的效率,找到用时最短的方法。

例如有这样一张销售表,现在需要筛选出京津冀的订单。
销售表
最基本的方法就是直接筛选:

方法1:直接筛选

筛选的行 = Table.SelectRows(源, each 
    ([省份] = "北京" or 
     [省份] = "天津" or 
     [省份] = "河北"))

但是省份的分类是可变的,根据需要随时会有新的分类方法,记录在一个表中:
分类表
这样方法1就需要经常修改语句,很麻烦,于是我尝试其他几种方法:

方法2:List.Contains

筛选的行 = Table.SelectRows(源, each 
    List.Contains(
        {"北京","天津","河北"}, 
        [省份]))

最容易的是可以想到List.Contains,这样{"北京","天津","河北"}就可以用变量来替换了。

然后我又想到了List.MatchesAny:

方法3:List.MatchesAny

筛选的行 = Table.SelectRows(源, each 
    List.MatchesAny(
        {"北京","天津","河北"}, 
        (current) => [省份]=current))

不知道作用原理是不是匹配到一个值就不再进行其他元素的匹配,如果是这样的话效率应该比List.Contains要高一些。

然后又想到了List.Accumulate:

方法4:List.Accumulate

筛选的行 = Table.SelectRows(源, each 
    List.Accumulate(
        {"北京","天津","河北"}, 
        false, 
        (state, current) => 
            if [省份]=current 
            then true 
            else state))

这种方法也是判断3遍,但是如果匹配上了的话就返回true,可能有点类似于递归,但是效率应该不如递归更高,但是应该会比List.Contains高一些。

还有递归法:

方法5:递归

筛选的行 = Table.SelectRows(源, each 
    let list={"北京","天津","河北"},
    fx=(n)=>
        if [省份]=list{n} 
        then true 
        else if n=List.Count(list)-1 
            then false 
            else @fx(n+1)
    in fx(0))

这种方法的原理是把销售表每一行的省份值在list中进行匹配,从第1个元素进行匹配,如果="北京"就停止匹配并返回true,如果≠"北京"的话再看是不是="天津",如果="天津"就停止匹配并返回true,如果≠"天津"再进行下一个匹配……直到最后一个元素"河北",如果没有匹配上的话就返回false,其实意思就是判断销售表中每一行的省份是不是属于“京津冀”,是的话就筛出来,不是就筛掉,和方法1直接筛选的道理是一样的,只是用递归可以把“京津冀”设为一个变量,方便动态变化。

递归法写法复杂一些,但是它有设有中断规则,对于list中的元素匹配上一个就停止,不再进行后面没必要的匹配运算,我感觉效率会更高一些,这个例子很简单看不出来,但是如果销售表有上亿行,而list中的城市也有几百个,比如要计算双十一当天1亿笔淘宝订单中发往三线城市(估计500+吧)的订单销售额合计,这时候我估计递归法计算会快一些。

总结

方法1最简单,但是我感觉也是要穷举list中的每一个元素,和List.Contains、List.Accumulate等方法计算量一样,但是语句简单些,可能会计算快一些。
方法2和3都要穷举list中的所有元素,计算量会比较大。

5种方法的运算速度我估计是方法5>方法4>方法1>方法3>方法2,如果有合适的例子可以测试一下。

施阳的文章《模拟绝对引用累计计数》中提到可以测试不同方法的刷新时间,但是我不知道怎么测试的啊,我现在只能是大概判断,不知道是不是PQ也可以用DAX Studio或者类似工具测试刷新时间,知道的朋友可以留言告诉我一下啊,多谢啦。


实例下载:
链接:https://pan.baidu.com/s/1CAwQWObOK3e7uTW9jSlwLw
提取码:rc6i

本帖已被设为精华帖!
本帖由 Alex 于 10个月前 加精
点赞
成为第一个点赞的人吧 :bowtie:
最佳答案
  • 飞天篮球 Talk is cheap, show me your code.
    2020-01-01 20:51:27

    @阿森纳里

    其实用这一步,Table.Combine(List.Repeat({Table.Buffer(源)},100000))扩充数据量不好 。Table.Combine是很耗资源的。我觉得比较妥当的还是数据源有100万行,测试会准确一些,不要在步骤里去构建。Table.SelectRows+List.Contains不慢的,150万行还是能胜任的。常量枚举,在筛选条件少的时候枚举一下也行。fyi!

回复数量: 5
  • 阿森纳里 数据从业者,PBI爱好者
    2020-01-01 18:11:14

    经群友提醒,我用Table.Combine(List.Repeat({Table.Buffer(源)},100000))扩充数据量,测试了一下,结果从快到慢是方法2>方法1>方法4>方法3>方法5,这个结果和我设想的不一样啊,看来用递归来设置中断规则的方法并没有像想象中的快,据群友所说可能是因为随着数据量的增大到了一定时候就会堆栈溢出了,只好是不懂学习中……

  • 飞天篮球 Talk is cheap, show me your code.
    2020-01-01 20:51:27

    @阿森纳里

    其实用这一步,Table.Combine(List.Repeat({Table.Buffer(源)},100000))扩充数据量不好 。Table.Combine是很耗资源的。我觉得比较妥当的还是数据源有100万行,测试会准确一些,不要在步骤里去构建。Table.SelectRows+List.Contains不慢的,150万行还是能胜任的。常量枚举,在筛选条件少的时候枚举一下也行。fyi!

  • 阿森纳里 数据从业者,PBI爱好者
    2020-01-02 11:28:47

    @飞天篮球 根据大神指点我又试了试,把数据源扩充到10万行,方法1直接写入常量筛选,方法2345的List引用变量,测试结果是速度由快到慢:1>2≈3≈4>5,看起来直接写入常量是最快的,引用变量的话3个List函数速度都差不多,List.Contains略快于其他两个,而递归是最慢的,这和我预想的非常不一样啊 ;-D

  • wdx223
    2020-01-02 17:48:08

    示例数据,用Table.Group,加载到excel表,大概2秒左右

  • libo5563
    2020-01-27 20:56:32

    file 把区域做个参数表,先筛一下,再和数据表合并查询,再筛数据 好像速度还行。

暂无评论~~
  • 请务必阅读并严格遵守《社区管理规范与使用说明》
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,使用方法请见 发送表情,可用的 Emoji 见 :metal: :point_right: Emoji 列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板粘贴上传, 格式限制 - jpg, png, gif
  • 不支持上传附件,请尽可能用文字和图片将问题描述清楚,如实在需要上传附件,可上传到 共享网盘 后分享链接
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!
Ctrl+Enter