2023-07
27

PQ为什么不改进

By xrspook @ 8:26:19 归类于: 烂日记

上周开始我就在用Power Query跟Power Pivot做跨表的数据合并。与其说是数据合并,不如说是数据查询。一开始我用的是PQ,因为从感觉上来说好像 PQ做这个就够了,但当我把东西都做出来了以后发现PQ很多规则都非常奇怪。让我觉得要试一下PP到底怎么样。根本原因是明明数据量很少,但是PQ的运行效率却很低,而且运行效果很不稳定。从0-1生成PQ的过程比较挣扎,虽然整体的思路我都有,我知道我要有什么后效果。但是该如何实现还是花费了我不少时间,比如查询参数应该用什么格式的表格表达出来。一开始我把4个日期和3个文本以左右的方式表达。的确这样的取数没有什么问题。虽然实际上PQ是用列去进行各种魔法运算的,但要精确定位到某个单元格也就是某条记录一点问题都没有。后来当我要用PP,那个东西至今我不知道如何在某列混杂着各种内容的单元格里获取我需要的数据。要顺畅用起PP,我得把日期参数跟文本参数拆分为两个表格。文本参数我不是直接给PP用,而先给PQ,所以横的竖的都无所谓。日期参数是直接在PP里做限定,所以必须以PP的规格去设定表格的形式。这仅仅是参数的表达,是最简单的东西。如果以普通人的视角考虑,某一列数据日期和文本混搭一点问题都没有,但是从机器的角度考虑,从我使用的那两个软件的规范考虑,显然这样是不行的,又或者说不是不行,是你为什么非得以一种如此随意的方式去做这么简单的设定呢?混搭的方式,肯定也会得到你想要的结果,但是对软件新手来说,绕那么一大圈显然就比较费劲了。

用PQ和PP的方式做出来的两个查询都能实现我的目标。数据都是没有问题的,但是一个文件体积很大,一个查询时间很长,且查询效率忽高忽低不稳定。这两个都不是我想要的。我不过是想做一个查询而已,很简单的东西,实际上我就只需要一个结果。那个结果以我想要的方式输出,后续的格式化纯粹是让我自己觉得比较顺眼好看而已。但是这两个Microsoft 365内置的Power都不能达到我的预期目标。

在挣扎之前,我觉得应该用PQ实现目标,但实际上出来的效果跟我想象的相差挺远,最根本的原因是我实在不太理解PQ的数据处理。PQ是用来做数据清洗的,所以从某个大表里获取数据,然后进行各种筛选,接着以各种目标形式输出表格,理论上这是很简单的事情。这大表的查询几乎可以这么说,一定是引用外表,因为源数据已经很大,你不可能在上面直接运行,虽然其实一直以来我都是这么干的,但是那个时候我并没有进行跨表操作。从现在的运行效果看来,即便是同一个代表同一个源数据,最终需要以几种方式输出分组筛选后的结果,最终要生成多少个查询效果,我就得把那个源数据查询多少次。理论上怎么会干这么傻的事情呢?直接把大的源数据查询一次缓存起来,往后就不需要调用了。但问题是从我现在的观察看来。最终我要多少个查询结果,他们就同时开始查询多少遍,于是有些时候就会导致有些查询结果失败,你得刷新再来。原因是这个查询正在使用那个源数据,那个查询也在用那个源数据,为了抢那个源数据打架了,抢不赢那个就刷新失败。都是查询一个源数据,我考虑过既然无法避免它们一次又一次查询,那么我就把那几个查询按顺序来,完了一个再到下一个,但实际上这个也是无法控制的。都说VBA是单线程的,但是PQ是多线程的,单线程虽然慢,但是多线程这样打架,最终反而得不到我想要的效果。从理论上说,我把那个大表一开始就缓存起来,后面的都用内存缓存,这很正常啊。我设置查询的优先等级,先刷新一些,然后再刷新另外一些,这也很正常啊,为什么却没有一个很直接的实现方式呢?有些人想到要用VBA去控制PQ的刷新顺序,但是VBA却很难判定某个刷新是不是完毕了,VBA也很难做到这个刷新完毕了再开始下一个。

接下来我要试一下python方案,我的目标是查询时间小于10秒,生成的文件小于100K。

4 条评论

  1. 我经常使用python处理数据,如果你用python的话,我推荐你安装一个anaconda,用里面的Jupyter Notebook做代码编辑器。
    如果用python处理,可以使用pandas。源数据可以是excel文件,导入到dataframe里面处理,如果你会sql的话,可以使用pandasql来处理表之间的关联,不会的话就用dataframe里面自带筛选、连接功能。
    如果你需要结果也需要excel格式来展示,你可以使用openpyxl来处理,我一般是先用excel做一个带格式的模板,然后用openpyxl把目标数据写到模板里面去。
    如果本机内存够大的话,在几十万条记录里面筛选都快得很。

    • 我用的是VSCode,因为是C语言过来的人,经历过赤手空拳记事本写代码的时代。说起python,现在的教程默认anaconda,但我还是喜欢用通用的编辑器。

      • 恩恩,握爪,我算是工具爱好者,过去市面上大部分流行的计算机语言我都会一点,当然都不深入。用vi字符编辑器编写C语言代码曾经是我的工作。
        我现在是一个非计算机从业人员,工作需要的计算基本上都用python处理。python的特点就是简单、丰富的库,可以让你把精力集中到问题的解决上,非计算机教育背景的人也可以用得很好。
        我强烈建议你在做数据分析的时候放弃使用通用编辑器或者其它IDE写python代码。你用过jupyter notebook就知道,或者你在使用前上网查下jupyter notebook,它有个“cell”(代码块)这样一个概念,每个cell都可以被单独编写、调试,这样编写代码解决问题就变得跟人大脑思维过程一致,变成一种探索。

有话要说

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

© 2004 - 2024 我的天 | Theme by xrspook | Power by WordPress