从数据到信息
从信息到洞察

Excel 一亿行大数据分析实践(总结篇)

背景

本文是我在电子表格大会分享内容的文字总结,如果你是第一次阅读,可以通过下文了解一下背景知识。
Excel 你能分析这么多数据吗?(活动预告)

测试目的本次测试目的并非与其他数据分析方法对比优劣、而是尝试介绍一种完全基于 EXCEL 的本地化大数据集处理方式。

 

分析师处理大数据集时常用方式
Excel 一亿行数据分析实践(总结篇)

 

本次演示的方式
Excel 一亿行数据分析实践(总结篇)

 

这种方式的优点

  • 低成本。减少工具间的切换成本,直接使用 Excel 作为存储和分析工具。
  • 展现灵活。展现端继续使用 Excel,发挥它灵活、自定义程度高的优势。
  • 便于交付。其他方式得到的结果为了便于交付,还要导出为 Excel,而现在整个分析流都在 Excel 内部完成。
  • 结果可交互。PowerPivot 相当于一个存储了源数据的 OLAP 引擎,通过控制切片器等外部筛选条件,可以迅速、动态的查看结果,使用其他方法,可能需要返回分析端改变计算条件重新导出。

测试项目一:数据导入和耗时

向 Excel 导入大数据,有两种方式:

  1. PowerPivot 导入,直接导入,不支持数据转换和清洗操作。
  2. PowerQuery 导入,在导入前可以对数据做预处理。

本次使用的测试数据集共有 19 列,有多列需要进行格式转换和日期提取操作,使用第一种方式,需要导入后在 PowerPivot 内部进行,使用方式二可以在载入前完成,很明显的是,对于方式二,预处理步骤越多,加载时间会越长。下图展示了不同量级不同导入方式的耗时情况(单位:秒)
Excel 一亿行数据分析实践(总结篇)

 

为了直接对比 PowerQuery 和 PowerPivot 的加载效率,增加了一个*号方式,这种方式不对数据做任何清洗转换,直接加载到模型,与 PowerPivot 步骤相同。

 

通过导入过程观察到的现象

对比前两行结果,PowerQuery 的数据导入效率与 PowerPivot 不分伯仲。

PowerQuery 没有数据量的限制,而 PowerPivot 不到导入超过 2G 的文件。

清洗步骤和数据量的增多,都会显著增加 PowerQuery 的导入时间,比如一亿行数据,即使三个简单的清洗步骤,用时已经超过了 30 分钟

 

结论

PowerPivot 导入方式使用的是 Access 连接器,受限于 Access 文件本身的限制,不能导入超过 2G 的数据,这也说明,PowerPivot 数据存储能力超过了 Access。

PowerQuery 是轻型 ETL 工具,处理大数据集性能不强。

如果尝试使用 Buffer 函数缓存数据,会发现这个缓存过程非常漫长,实际上,Buffer 函数并不适合缓存大数据集,因为无法压缩数据,内存可能会很快爆掉。

 

测试项目二:文件压缩比率

 

Excel 一亿行数据分析实践(总结篇)

影响文件压缩比率的因素,主要是数据集本身的特征和 PowerPivot 引擎的性能

结论

数量级越大,压缩比率越高。

同一数据量级,清洗步骤越多,最终文件会越大,并且随着数据量的增加,这种现象会越明显。

 

测试项目三:数据分析的效率 – 简单分析

我们真正关心的内容是,Excel 能否快速、高效的对大数据集开展分析。

简单分析定义的场景:逐月统计有多少位顾客发生了购买。做法是把年和月拖入透视表行字段,将 CustomerKey 拖入值区域,修改值汇总方式为统计不重复值。

测试发现,即便使用一亿行数据,这个计算过程的用时也很短,小于 1s。于是我增加了一点难度,加入两个切片器对结果做交叉筛选,计算用时仍然小于 1s,看来 PowerPivot 处理这类分析比较轻松,最终此项测试没有计时。
Excel 一亿行数据分析实践(总结篇)

测试项目四:复杂分析效率 – 新客户统计

Excel 一亿行数据分析实践(总结篇)统计新客户数量,逻辑是:逐月计算当月产生购买的顾客中,有多少是新客户(第一笔购买发生在当月)。为了获取 PowerPivot 引擎的计算用时,测试在 DAX Studio 内完成,同时为了模拟透视表的计算结果,需要对原度量值的写法做一点改动。

EVALUATE
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( '1 亿'[Order`Date (年)] ),
        VALUES ( '1 亿'[Order`Date (月索引)] )
    ),
    "newcustomers",
    VAR currentcustomer =
        CALCULATETABLE ( VALUES ( '1 亿'[CustomerKey] ) )
    VAR oldcustomer =
        FILTER (
            currentcustomer,
            CALCULATE (
                MIN ( '1 亿'[Order`Date] ),
                ALLEXCEPT ( '1 亿', '1 亿'[CustomerKey] )
            )
                < CALCULATE ( MIN ( '1 亿'[Order`Date] ) )
        )
    RETURN
        COUNTROWS ( EXCEPT ( currentcustomer, oldcustomer ) )
)
ORDER BY
    '1 亿'[Order`Date (年)],
    '1 亿'[Order`Date (月索引)]

Excel 一亿行数据分析实践(总结篇)
计算用时(毫秒)
Excel 一亿行数据分析实践(总结篇)

二次运算的用时指的是首次运算结束后,不清空缓存再次执行重复计算所花费的时间。相比第一次运算,节约时间在 30%左右。原因是 DAX 的两个引擎中,有一个可以缓存计算结果,被缓存的内容可以在之后被公式内部调用,也可以跨公式调用。

结合这个知识,对 DAX 的表达式进行优化,可以获得更好的性能表现,下面是新客统计优化之后的写法,我们来对比计值时间的变化。

EVALUATE
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( '1 亿'[Order`Date (年)] ),
        VALUES ( '1 亿'[Order`Date (月索引)] )
    ),
    "newcustomers",
    VAR currentcustomer =
        CALCULATETABLE ( VALUES ( '1 亿'[CustomerKey] ) )
    VAR oldcustomer =
        FILTER (
            currentcustomer,
            CALCULATE (
                MIN ( '1 亿'[Order`Date] ),
                ALLEXCEPT ( '1 亿', '1 亿'[CustomerKey] )
            )
                < CALCULATE ( MIN ( '1 亿'[Order`Date] ) )
        )
    RETURN
        COUNTROWS ( EXCEPT ( currentcustomer, oldcustomer ) )
)
ORDER BY
    '1 亿'[Order`Date (年)],
    '1 亿'[Order`Date (月索引)]

Excel 一亿行数据分析实践(总结篇)
优化后计算用时(毫秒)

Excel 一亿行数据分析实践(总结篇)
可以看出引擎的缓存起到了显著效果,二次计算直接调用首次运算的结果,计算时间不随数据量的增加而增加。

以一亿行数据集的结果为例,对比算法优化前后的用时:
Excel 一亿行数据分析实践(总结篇)

 

测试项目四:复杂分析效率 – 流失客户统计

与新客的呈现方式相同,依然是逐月计算当月的流失客户,不同的是流失客户的定义更为复杂。流失天数允许用户自定义,被判定流失的客户需同时满足以下两个条件:

  1. 所有在当月之前最后一次购买的日期+自定义流失天数,落在当前时间区间内。
  2. 当月如果发生购买,第一次购买日期不能早于判定流失的日期。

流失客户公式和计算结果
Excel 一亿行数据分析实践(总结篇)计值流如此复杂的一个公式,PowerPivot 会耗时多久呢,我只用了一亿行数据的文件做测试,

结果是首次计算 4093ms,二次计算 1720ms。

 

说明

以上测试模拟了透视表的呈现布局,而且你可以加入切片器改变公式的上下文条件,迅速得出特定产品、特定商户和特定促销活动的新客户以及流失客户,非常方便。时间统计基于少量的测试结果,存在一定偶然性,仅供参考。

测试环境电脑配置也是影响计算性能的重要因素,以上进行的所有测试都基于台式机,在做现场分享的时候,我在笔记本电脑上重新运行了一遍流失客户公式,两个环境的用时如下:
Excel 一亿行数据分析实践(总结篇)结合平时其他测试,我的笔记本执行同样的计算,用时平均在台式机的两倍左右。两台电脑的配置如下
Excel 一亿行数据分析实践(总结篇)

提升 CPU 主频、核心数、1、2、3 级缓存;内存的大小和频率都会提升引擎的性能表现。

 

总结

对于本地化大数据集的分析,本文提供了一种新的可能,严格来讲,2010 年的时候你已经可以使用,只不过彼时它羽翼未丰,计算性能和稳定性难堪大任。

而现在,你已经见识到了这套工具(PowerPivot+PowerQuery)的能力,无论大数据还是复杂运算,Excel 公式和 VBA 已经无法望其项背。

一般说来,积累通常是好事,财富可以通过积累不断增加、写作能力可以通过积累不断增强,但在某些知识领域,迭代是如此的快速和彻底,以至于底层的方法论都将被淘汰掉,过去的知识成为此刻的负担,你最好尽快丢掉过去,拥抱未来。

3
说点什么

avatar
1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
1 评论数
2 被回复的评论
2 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
大M
游客
大M

最后一句话画龙点睛了……庆幸当年没学太深的VBA 😉

如果只是吃内存,还好办,内存的拓展性还是比较强的。

如果是吃CPU单核性能,就没那么大的拓展性了……理论上来说,要等上一步计算结果才能算下一步的,都吃CPU单核性能。

不过随着产业的进步,这个能力还会进一步拓展,单PC能处理的数据量已经完全满足100人左右的企业了,而这些企业所占的数量和影响就业的人数是最多的。

以前这些企业是买不起昂贵的数据服务的(包过软硬件以及人才),现在有如此廉价且好用的方案,值得向他们传教,要不,咱Power BI极客也改成Power BI传教?开个玩笑,哈哈。