前段时间,有朋友在群里问,46万行数据,分客户及订单先后求其累计数——即输入46万条,按客户算出每一条的累计数后,仍然输出46万条……


优化 | 46万行数据按客户分订单求累计数,用SUMIFS卡出翔,用PQ只要1分钟!_数据处理

       显然,如果数据量较小,这个问题值机在Excel里用函数SUMIFS进行多条件求和,很简单,但是,当数据量到了几十万条时,用SUMIFS会直接卡死,有兴趣的朋友也可以到后面下载文件试试。


优化 | 46万行数据按客户分订单求累计数,用SUMIFS卡出翔,用PQ只要1分钟!_数据_02


优化 | 46万行数据按客户分订单求累计数,用SUMIFS卡出翔,用PQ只要1分钟!_数据_03

       实际上,在绝大多数的情况下,数据处理很少是这种输入明细还要以完全明细的方式去得到结果的,如大神“焦棚子”所言,这样的结果是没有人会看的。


优化 | 46万行数据按客户分订单求累计数,用SUMIFS卡出翔,用PQ只要1分钟!_数据_04


       但是,回到这个问题,用Power Query来解,怎样呢?是否有较好的办法在可接受的时间内解决?

       答案当然是肯定的!

       我们先来看一下数据和需求——主要就是针对客户id(c_id)按billid的先后,对jdmoney汇总到当前行:


优化 | 46万行数据按客户分订单求累计数,用SUMIFS卡出翔,用PQ只要1分钟!_数据_05

        首先,碰到这种问题,我们首先就是要考虑分组的方法,因为如果直接使用筛选计算(Table.SelectRows),效率通常都很低,对于这一点,我在文章《​​PQ算法调优 | 充分利用分组功能,提升数据处理效率 - 1:从分类加索引问题谈起​​》等文章里提到过。

        具体操作如下:


优化 | 46万行数据按客户分订单求累计数,用SUMIFS卡出翔,用PQ只要1分钟!_数据_06

      于是,我们得到按客户分组的结果——每个客户的数据都归到一个单独的表上:


优化 | 46万行数据按客户分订单求累计数,用SUMIFS卡出翔,用PQ只要1分钟!_数据处理_07

      这样,后续的累计每次只要针对当前客户的数据进行判断和计算即可。但是,这个计算量仍然很庞大,因为有1W+多个客户,每个客户的订单数量从1到1w+不等。


优化 | 46万行数据按客户分订单求累计数,用SUMIFS卡出翔,用PQ只要1分钟!_数据_08

       这时,如果我们仍然采用传统的筛选判断,仍然会很慢,因为你要操作1W多个表里不同数量数据的判断(示例文件中用Table.MinN函数的方法可以试一下)。相反,如果能避免判断再计算,那将能在很大程度上提升计算的效率——而这往往需要根据具体问题具体分析。


优化 | 46万行数据按客户分订单求累计数,用SUMIFS卡出翔,用PQ只要1分钟!_数据_09

       回到这个问题,数据有一个特点,即客户的订单ID是从小到大排序的(如果本身数据没有排序好,那可以加个排序的步骤),也就是说,我们并不需要判断,而直接取到当前行个数的数据直接求和即可。因此,修改分组的公式如下:


优化 | 46万行数据按客户分订单求累计数,用SUMIFS卡出翔,用PQ只要1分钟!_数据_10

= Table.Group(更改的类型, {"c_id"}, 
{{"计数", (t)=>Table.AddColumn(
Table.AddIndexColumn(t,"序号",1,1),
"LJ",each List.Sum(
Table.FirstN(t,[序号])[jdmoney]
)
),
type table}})


       通过以上分组并修改公式得到每个客户各订单的当前累计数后,展开即可得到完整结果,经我电脑测试,运行时间大约70秒(刚开始时比较慢一些),电脑配置:


优化 | 46万行数据按客户分订单求累计数,用SUMIFS卡出翔,用PQ只要1分钟!_数据_11


       是否还有更优的方法?期待您的建议。示例文件复制以下链接下载:

https://share.weiyun.com/5YKFr4Z