开篇词 避免踩坑 搭建数据分析师的核心竞争力

你好，我叫“取经儿”，是北京回龙观的一名数据“民工”，你可以直接叫我“经哥”。

我毕业后先在电信工作了 3 年，2015 年开始转行成为一名数据分析师。之后近 6 年的时间里，我就职过多家互联网公司，服务过很多产品线，从 To B 到 To C ，从用户侧到商业广告侧，从日活百万的 App 到日活过亿的服务，从每天收入十几万的产品到日入几千万的产品等，都给了我很多宝贵的经验积累。

数据分析工作中的趣味与挑战

于我而言，从事数据分析工作是一件有趣又具有挑战的事儿。

趣味性来自哪里呢？比如，我们可以零距离接触产业的核心业务数据。数据分析师是站在数据宝藏上来俯视企业运转的，如果你有心，会看到很多其他同学看不到的有趣的数据场景，同时天然有机会成为最了解公司业务的人。

同样，在数据分析工作中，也存在着不少“挑战”。例如，如何从底层日志采集到前端 BI 展示？如何通过科学定义数据指标体系来更准确地刻画业务？等等。这些不是简单地写写 SQL 统计下就可以的，你需要解析层日志以及准确理解数据的业务含义，另外随着业务发展数据字段的增加和变化，也需要一起梳理清楚，很多时候会涉及多个团队甚至多个部门的协作才可以完成。

更大的挑战，还来自公司具备了完善的数据基础设施后，如何通过数据挖掘来驱动业务增长？如何从落地解决问题角度出发，制定有效的数据策略？这些数据策略不仅仅应当作为日常业务的支持，还应当科学合理、切实解决数据中暴露出的问题，引导未来公司的策略发展。而且，企业对数据策略的要求越来越高，依赖性越来越强。

好消息是，诸多挑战反而为数据分析师的发展提供了机遇。不少大厂对数据分析师的需求陡增，尤其那些正在跑马圈地的互联网企业，甚至会雇用几百名数据分析师协同工作。他们奋斗在公司各条产品线上，服务于各个细分模块，这在 5 年前完全是不可想象的。而且，数据分析行业还存在着巨大的潜力等待更多的人来发掘。

数据分析师的困惑

企业需求背后，大量人才也开始涌入数据分析行业。但入行数据分析师，究竟需要具备哪些能力？数据分析师的日常工作都有哪些？日常工作日益繁杂，如何体现数据分析师的价值，如何长远发展？都是经哥经常听到的一些话题，很多数据分析师在不同从业阶段总会面临不少类似的问题和困惑。举几个例子，你可以切实感受下。

• 困惑一：数据分析工具有很多，单可视化的就有很多种，都需要学吗？

很多招聘信息里，要求你熟悉各种五花八门的数据分析工具。比如 Excel、Hive、Shell、Python，比如仅可视化工具就有 Tableau、PowerBI、百度 Echarts、开源的 Superset 等。它们不仅能够展示普通的柱状图、折线图、散点图、箱图等，甚至还能展示大数据迁移图、用户路径分析图、社交关系图等。

我们的同学往往在这里就迷惑了。我们真的需要那么多工具吗？每种工具的特色功能我们都需要细致学习吗？其实，完全没有必要，这些工具最核心的逻辑都是相同的。你只需要熟悉其中一种，其他工具能够很快复用。经哥会在专栏的第一部分帮助你做好工具学习上的取舍。

• 困惑二：数据摆在面前，但业务提出的问题还是不知道如何着手分析？

你是不是经常被老板问的一脸茫然？比如“上周环比周活明显下降，收入为什么还上升了？”“为什么用户人均使用时长变长，但互动率却下降了？”“要提升新客 ARPU 值，如何优化现有新客定价策略？”

面对这些问题，很多同学开始慌了，一头雾水地找起了答案。如果是这种情况，说明你还是缺少分析问题的框架和能力，工作中可能被动接需求多于主动思考，希望经哥的课程能帮助你建立业务思维基本能力和良好的工作习惯。

• 困惑三：花费很大精力产出的数据结论，成为产品运营工作汇报的半页 PPT，数据的价值如何体现？

上周产品同学让帮忙查询的收入异常波动，产品在周会上已经跟老板解释清楚；之前运营和数据同学一起碰撞的运营方案，也已经落地，效果很好，运营同学得到老板的极力赞赏。

很多做数据分析的同学认为，自己辛苦的成果好像是白白让给产品、运营同学给老板汇报，数据分析师的价值没有得到体现。为了解答这个困惑， 带你重新定义数据价值，经哥会为你分享数据是如何在引导产品和运营工作发挥重要作用的。

• 困惑四：辛辛苦苦做出的分析结论，业务方没有任何反馈，数据还怎么驱动业务增长？

专题报告辛苦做了 1 个月，洋洋洒洒 100 页，自信满满交给了业务方，业务方却没有任何反馈？

这种情况，极可能是你的分析报告偏离实际业务，或者无法落地。比如，都知道新用户留存和续费很重要，并且当前新用户留存情况业务方自己也十分清楚，这时候业务方需要的是通过数据应该优化哪些地方来提升留存，而不需要你再把已知的东西再分析一遍。对此，我会在课程中专门分享数据策略是如何驱动业务增长的，包括如何提出问题、分析数据、落地策略这样一个闭环。

• 困惑五：找工作简历洋洋洒洒，写了精通 SQL、Python，为什么投递后石沉大海杳无音信？

太多太多的同学投递简历，明明看着岗位要求跟自己极度匹配，技能也会，工作年薪，学历甚至年龄，每个条件都很吻合，可就是投递简历后一点回音都没有，这是为什么呢？

这里面其实涉及人才供需问题。相同的人，不同的简历表达风格，不同的投递策略，得到面试概率截然不同，而如何写简历、如何进行投递，这才是你需要掌握的问题关键。

我曾在全球独角兽公司进行亿级用户数据分析，也曾在垂类行业处理百万日活数据，还有过短期内频繁更换工作的“踩坑”期，以及扎根小米的实战经验，以上这些困惑和问题，可以说我大多真实经历过。

回头看这坑坑洼洼的数据分析道路，我希望借机梳理多年来的认知和经验，给你总结归纳出一部“防坑记”来。希望它能够帮助后来的同学减少迷茫和困惑，巧妙地避开你大概率会遇到的这些“坑”，帮助你打造数据分析师的核心竞争力。

课程设计

市面上关于数据分析的优秀课程其实挺多，大概可以分为三类，一是工具或编程类（比如SQL、Hadoop、Python、Excel等），二是算法类（类似十大数据挖掘算法等），三是分析思路和案例类（比如异常分析、A/B 实验、增长黑客等），可以从不同层面帮助你提升数据分析能力。

而经哥这个课程，是希望聚焦数据分析工作中最主要的数据处理技能和数据分析思维，帮助你思考如何用数据解决实际问题。具体来讲，课程共为 5 部分，25 课时。

第一，数据处理技能。无论你是初入数据分析行业的新兵，还是有经验的高级数据分析师，这部分内容都能极大提升你在处理数据时的效率，而且每课时的核心代码都是可以实际上手操作的，希望你能将其融入日常工作中，缩短数据处理占用的时间。

第二，业务分析思维。很多同学，说自己没有项目经验，希望了解数据分析师日常是怎么分析和解决问题的，这部分内容正是你需要的。经哥将在这部分还原数据分析师工作场景，分享数据分析师如何与产品、运营一起完成业务提升，在工作中如何平衡临时项目支持与专题分析，相信可以帮助你提升业务分析思维，提高与业务方的沟通效率。

第三，数据策略落地。数据策略近两年热度大增，核心还是基于数据分析来挖掘价值。如何挖掘数据价值，提高业务能力？你需要的是数据策略。经哥将在这部分讲解数据策略，希望可以让你认识策略是如何制定并在科学验证后驱动业务增长的。

第四，商业广告体系。全世界的互联网公司，90% 收入来自广告业务，商业分析师绝对是互联网大厂青睐的对象。最近一年，我都在从事信息流广告数据分析。我发现它和用户侧核心指标差异很大，是个非常值得注意的点。这里，我将带你认识广告指标体系以及流量经济。

第五，工作求职经验。我把大家最关心的有关找工作的两个重要环节——写简历 + 投简历，专门梳理放到这部分。相信很多时候并不是你不够优秀，只是不善于表达自己，经哥在最后告诉你如何提高面试概率。

讲师寄语

数据分析行业在蓬勃发展，数据分析师将在数据价值挖掘中扮演越来越重要的角色。数据分析师也是一个极具挑战的职业，未来越来越多的公司决策需要依赖数据分析完成，数据分析日渐成为真正掌握公司核心业务的岗位。

如果你已经选定数据分析行业，希望你能够在前期熟悉基础数据处理技能后，将重心更多放到解决业务问题和落地数据策略上，这样数据的价值和个人价值才能充分体现。也希望经哥这门课程，能够对你提供些许帮助，解答从业困惑，建立学习自信，找到努力的方向。

课程就要开始了，你准备好了吗？关于数据分析的各种困惑与成长，记得来留言区找经哥。

---

01 入行必备：数据处理的十大技巧

从今天开始我们一起系统地学习数据分析。

作为一名数据分析师，相信你经常会被问到这样一个问题：“你们数据分析师平时工作都用哪些工具啊？”一般的回答通常是：“数据分析的工具挺多的，但最常用的还是 Excel 和 SQL。”这样的对话虽然对数据分析岗位理解得不算非常到位，但至少对数据分析师必须掌握的基础数据处理技能理解到位了，基础数据处理技能是成为数据分析师的必要条件。

数据分析师每天都在和数据打交道，必须精通数据处理技能，才能够提高数据分析的效率，进而扩大数据挖掘和价值探索的边界。

但分析师在面对不同格式的数据时，往往不知道选用哪个工具进行处理最高效。因为数据处理的工具有很多，不同工具都有各自的特点和应用场景。针对这种情况，本课时我先从重要性、频率、效率三方面入手，分享 10 个平时工作中的小技巧，来帮助你提升基础数据处理能力。

Excel 篇

Excel 作为最常用的 Office 办公软件之一，拥有强大的数据处理能力，各行业不同岗位的同学都离不开 Excel。对于数据分析师而言，Excel 更是每天必用的工具。这里我分享两个 Excel 独特且重要的技巧：vlookup 函数及透视表功能。这两个技巧对数据分析师来说非常实用，而且是 Excel 典型代表，其他数据工具很少有像 vlookup 这样互动性强又便捷的函数。而透视表被很多 BI 工具模仿，因为它靠简单拖曳就可完成数据统计，如果使用得当，可以帮助我们节约不少工作时间，提高工作效率。

巧用 vlookup 函数

vlookup 函数功能，即通过表格首列查找该函数指定需要查询的值，并返回当前表格中指定列的数值。函数表达式如下所示：

vlookup(查找值，数据表，列序数，匹配条件)

下面通过一个案例帮你来理解这个函数。现有一列记录了用户注册省份名称的数据，希望你可以补充省份对应的“省份简称”和“省会城市”数据。这种情况下，如果你没有掌握vlookup 函数，就需要一行行地手动添加，需要处理的数据量少还好说，但如果需要补充的数据源有几十万行，那该怎么办？手动处理会变成一场噩梦。这时，通过 vlookup 函数，就可以瞬间解决这个问题。

第一步，我们需要制作一个省份、简称、省会的映射表，如图所示。

第二步，添加 vlookup 函数，按照前面我们讲的格式填写 4 个参数，如图所示。

• 第1个参数 M8 指的是要查找单元格的值。

• 第2个参数 F:H 指的是要查找的位置。

• 第3个参数数字 2，表示要返回的值的区域中的列号。

• 第4个参数设置为 FALSE 是表示严格匹配才返回，如果设置为 TRUE 或 1 则表示模糊匹配。

接下来只需要复制函数到 N 列的每个对应的单元格，瞬间省份的简称就填写到对应位置上了，见下图。


可以看到，通过上面的操作，我们非常便捷地完成了通过表格首列（即 F 列）查找该函数指定需要查询的值（如"山东省"这个值），并返回当前表格中指定列（即 G 列）的数值（即"鲁"）。这就是 vlookup 函数最常用的一个应用场景。即给出查询内容，在指定区域查询对应内容，然后返回。

下面我们看 vlookup的另外一种应用场景，连续数值划分区间。现假设有一列记录了 10 万条 App 用户注册年龄的数据，我们希望能够按年龄段进行划分（比如：0~7 岁、8~18 岁、19~24 岁、25~35 岁、36~50 岁、51 岁以上等），以便统计各年龄段用户的分布情况。面对这样的需求，vlookup 仍然能够轻松实现，下面我们一步步完成数据的统计。

第一步，先按需求准备划分的年龄段，输入两列数据。第一列是划分段最小值，第二列填写分段名称，这里要注意上个分段和下个分段的数据连续性，即下个分段最小值是上个分段最大值加 1。以“0~7 岁”为例，“0”为该分组下限，“7”为下个分组上线减 1。

第二步，输入 vlookup 函数， 填写 4 个参数，然后按回车键就会输出正确的结果，如图所示。


上面介绍了两个 vlookup 常用案例，也是数据分析师日常处理数据经常遇到的场景，一个快速匹配，一个是连续数值分段。工作中，使用 vlookup 函数的场景我们经常遇到，希望你能够活学活用，利用它实实在在地提高我们的工作效率。

巧用透视表

接下来我们学习第二个技巧。同 vlookup 函数一样，透视表在我们的工作中也非常的重要。你可以把透视表理解为数据处理的"最后一公里"，它可以很方便通过鼠标“点点点”完成数据的分组统计、排序、求平均、行列计算占比等常用数据分析功能，并且如果原始数据更新，透视表的数据也会同步更新。这对我们进行数据分析整理无异于提供了一个便捷的工作方式和手段，下面我们通过一个例子来介绍 Excel 透视表的常用功能。

数据源包括 4 列数据，分别是用户注册 ID、性别、省份、App 使用时长。我们希望了解该 App 对不同性别用户的吸引力，以及女性用户在不同地区的覆盖情况。我们看透视表如何完成分析目标。

针对这个目标，我们可以统计下面两个数据：

1. 不同性别用户数量差异、人均 App 使用时长、总使用时长占比；

2. 女性用户在不同省份的数量、女性用户人均 App 使用时长，以及女性用户总使用时长占比。

第一步，生成透视表。 使用Ctrl+A 快捷键，将数据源全部选中，然后点击菜单栏“插入”->“数据透视表”。

你可以看到透视表主要包括字段列表、透视表区域，以及筛选器等，三者有着不同的作用。

第二步，将“性别”字段拖入“行”区域。将“App 使用时长”字段拖入两次到“值”区域， 第一次，将值字段设置为求平均值；第二次，将值字段设置为求和。

接下来，单击其中一个弹框左侧“值字段设置”对话框，将值字段设置为想要的汇总字段计算类型，这里一个设置求和，一个设置求平均值。

第三步，统计女性用户所处不同省份的 App 使用时长分布。这是个筛选操作，我们把性别拖入筛选器。然后，再将省份拖入列区域，将 App 使用时长拖入值区域，并设置相应统计方式，我们便可得到想要的结果。

由于透视表出色的数据处理能力及其灵活性、普适性。很多编程语言如 Python 中的 pivot_table 函数，以及 SQL 中的 pivot 函数，都是模仿的透视表功能。

上面举出了 Excel 经常用到的两个典型技巧，非常有用。但对于数据分析来说，也不过是冰山一角。 Excel 作为数据分析师必备技能，学习的正确方式：动手实践->遇到问题->网上搜索+官网 Help->再实践->记录核心技巧案例，如果能够按照这样的路径相信你很快就能熟练掌握。

Hive SQL 篇

目前所有大厂数据分析师都在使用 Hive SQL 进行数据分析。所以之后会有一个课时来专门讲解 Hive。在这里，先给你介绍下 Hive SQL 中两个重要技巧：行列互转和 row_number 函数。这两个技巧在面试中经常被问及，非常的实用。

行列互转

行列互转，在数据处理中比较常见，简单来说，它指的就是把一行拆成多行，或者把多行按照某种条件聚合为一行。这里，我会分别举例说明你在什么工作场景中会用到行列转换，以及如何操作。

（1）行转列用法。

假设一个字段是由一个数组构成，实际统计时可能需要把这个数组展开后再进行统计。比如，为解决各类业务问题，算法会针对不同用户进行多种 A/B 实验策略，最后看不同策略中哪一个对解决业务问题有效。

这样，一个用户可能会被很多数据策略命中（如：新客策略、地域策略、女性策略等），这时如果需要查看全体用户被女性策略命中的数据情况，就需要先行转列，之后在筛选出女性策略，再进行统计。

下面看下行转列使用的函数：

lateral view explode(split表达式) tableName as columnName

tableName 表示虚拟表的名称。columnName 表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可；如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开。

该函数在 SQL 语言中 from 之后， where 之前使用， 下面通过上面举例的 A/B 测试场景的统计需求，来了解这个函数用法：

-- 用户测试数据表如下：
select uid, exp_id_list,pv from test_table2;
-- 输出两列如下：
1000001	exp_2001,exp_2002,exp_2003	3
1000002	exp_2011,exp_2012,exp_2013,exp_2015	5
1000003	exp_2001,exp_2002	2

在用户测试数据下加入行转列代码。

-- 行转列代码
select uid
, exp_id 
, pv
from test_table2 
lateral view explode(split(exp_id_list,',')) tb as exp_id

经过行转列操作之后可以看出，每个 uid 对应的实验 id 都变成了一行：

-- 行转列输出如下：
-- 可以看出，每个uid对应的每个实验id都变成一行
1000001	exp_2001	3
1000001	exp_2002	3
1000001	exp_2003	3
1000002	exp_2011	5
1000002	exp_2012	5
1000002	exp_2013	5
1000002	exp_2015	5
1000003	exp_2001	2
1000003	exp_2002	2

这样即可找出女性策略的用法。以上为“行转列”功能及使用方法。

（2）列转行用法

相比行转列的逆向行为就是列转行。相对来说我工作中遇到行转列的情况更多，但的确也会遇到必须用到列转行的场景，下面我们就通过一个案例具体看下。

假设有一张表，记录的是用户注册 id、用户历史使用的手机型号。我们希望了解用户累计使用过的不同手机型号的用户数分布。

这时，就可以通过列转行后再进行 group by 汇聚统计来处理。测试原始数据如代码所示：

-- 测试数据表如下：
select * from test_table1;
-- 输出如下:
1000001	mi_6
1000001	mi_9
1000002	mi_10
1000003	mi_9
1000003	mix_3
1000005	mi_9
1000005	mix_3
1000008	mi_10
1000009	mi_9
1000009	mix_3
1000010	mi_9
1000010	mix_3
1000021	mi_8
1000021	mi_9
1000023	mi_8
1000023	mi_9

在原始测试数据后加入列转行代码。

-- 列转行代码
-- collect_list
select uid
, sort_array(collect_set(phone)) as phone_list
from test_table1
group by uid

执行后输出如下所示：

-- 列转行输出如下: 
1000001	["mi_6","mi_9"]
1000002	["mi_10"]
1000003	["mi_9","mix_3"]
1000005	["mi_9","mix_3"]
1000008	["mi_10"]
1000009	["mi_9","mix_3"]
1000010	["mi_9","mix_3"]
1000021	["mi_8","mi_9"]
1000023	["mi_8","mi_9"]

经过列转行操作之后，用户累计使用过的手机型号就一目了然了。

row_number 函数

row_number 函数的功能是为了将分组统计后的每组数据取 TopN 输出。这个功能对我们来说非常实用。

比如，我们分组汇总各行业大客户的充值金额并排序后，很自然希望看到每组数量 Top 客户的情况，目的是及时掌控头部客户的信息，以服务好大客户，使其能够长久为公司贡献现金流。不少公司面试会专门出类似面试题目，目的就是考察 row_number 函数用法，也可见其重要性。

下面，我们来介绍 row_number 函数各参数含义，以及用法。

row_number() over(partition by column1 order by colomn2 desc/asc)

row_number 函数先按照 column1 分组， 然后按 column2 降序排列。

select * 
, row_number() over(partition by customer_id order by price) as id 
from test_table3

接下来针对 test_table3 按照 id 调用 row_number 函数。

-- 输出数据如下:
-- 第一列客户id， 第二列客户充值金额， 第三列为客户充值金额大小倒序排列的序号
10001	100   	1
10001	300 	2
10001	500	3
10001	600  	4
10003	10000	1
10003	2000	2
10003	500	3
10004	1500	1
10004	3000	2
10004	3500	3
10004	5000	4
10004	8000	5

你可以看到输出结果中每个用户 id 为一个分组，每组按客户充值金额倒序排列，并生成第三个字段，即按每组充值金额倒序排列的序号。

学习完 row_number 函数典型应用场景，能够快速帮助我们组内排序。工作中体现快速高效的实用技巧还有很多，下面再分享些在写 SQL 的过程中会让你眼前一亮的两个小技巧。

巧用 Sublime 写 SQL

Sublime 是一款功能强大的高级文本编辑器，可以安装很多插件满足比如前端开发、Python 后端开发等不同的开发需要。

数据分析师日常也需要使用文本编辑器来编写 SQL 脚本，或者查看数据文件。这里注意，数据分析师用 Sublime 写 SQL 脚本时，经常使用的两个技巧：查找替换和列选。

• 查找替换

这个功能任何一个编辑器都有，但是 Sublime 对查找支持的更全。比如：正则匹配。

下面举个例子，当要查询某一批用户 uid 的数据时，这时需要将这批用户 uid 用引号和英文逗号分开。

菜单：Find (查找)-> Replace (替换)，然后输入要替换的换行符以及要替换成的'，'。

替换结果如下：


这个结果一般用于 SQL 脚本 in 操作，筛选一批 uid 对应信息。比如：

select * 
from test_table3 
where customer_id in ('1000001','1000002','1000003','1000005','1000008','1000009','1000010','1000021','1000023')

• 列选

除此之外，列选也会经常用到，列选后同时操作多行来完成脚本编写。比如：SQL 中 case when 如果要匹配的代码行数很多，但样式类似时，就可以使用列选功能同时编写多行，提升效率。

如下所示， 要针对 customer_uid 分别给予不同名称， 由于 SQL 结构一样，就可以通过列选后，同时编辑多行：


以上两个属于在使用 Sublime 编写 SQL 时经常使用的技巧，当然 Sublime 的强大功能远不止此，它还能安装很多第三方插件完成功能扩展，如果你感兴趣可以到官网深入了解。

Shell 篇

Shell 脚本因其功能强大，也是数据分析师日常必须掌握的工具，这里我简单介绍一些对数据分析师来说很常用的功能。

批量生成 SQL

身为数据分析师，我们经常会遇到这样的数据分析需求：业务方希望看最近 1 个月内用户某指标数据，并且这个指标是个性化的，导致现有报表无法支撑数据。需要我们手动去跑。

这就存在一个问题：我们计算所需要的表，数据量很大，例如一天日志数据量可能是千亿级别的。这种情况下，我们靠着有限的计算资源，每次统计一天数据，就需要提交 30 个脚本了。

这时，如果相同的 SQL 写 30 遍就太低效了。我们可以通过 Shell 脚本批量生成 30 个 SQL 脚本，让每个脚本计算一天的数据。

举个例子：下面是按天统计的脚本， 我们目的是同时并发运行 30 天数据。

# 如下是待生成的SQL脚本模板run_template.sh:
# 下面是按天统计脚本，$1参数是Shell脚本第1个参数
dates=$1
hive -e"select count(1) 
from test_table 
where dt=${dates}">out_${dates}

下面是同时运行 30 天数据的写法，每 1 行统计 1 天数据，只需要修改日期即可：

# 每1行统计1天数据， 只需要修改日期即可
nohup sh run_template.sh 20200715 &
nohup sh run_template.sh 20200716 &
nohup sh run_template.sh 20200717 &
nohup sh run_template.sh 20200718 &
nohup sh run_template.sh 20200719 &
... ...
nohup sh run_template.sh 20200801 &
nohup sh run_template.sh 20200802 &
nohup sh run_template.sh 20200803 &
nohup sh run_template.sh 20200804 &
... ...
nohup sh run_template.sh 20200813 &
nohup sh run_template.sh 20200814 &
nohup sh run_template.sh 20200815 &

巧用 Shell 可以起到事半功倍的效果，数据分析师经常要和 Shell 命令打交道，下面我再分享几个常用的 Shell 命令。

定时任务执行

定时任务，不仅服务器开发同学经常用到。数据分析师也经常使用，比如：我们经常遇到业务方每天要 App 前一天的核心数据。其实，这种情况通过 Shell 设置定时任务就可以轻松解决。这样，我们只需要每天执行统计任务，再定时将数据结果邮件发送给业务方即可。

Linux 定时任务使用 crontab 命令来完成。如下是系统对于 crontab 命令用法的解释。5 个星号表示设定周期执行的颗粒度，分别对应：分钟、小时、天、月、周。

cat /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
# For details see man 4 crontabs
# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed

下面利用 crontab 编写几个定时执行的实例，你一眼就能看明白 5 个"*"各种的功能定义：

# 每小时的第30分钟， 执行脚本
30 * * * * sh ~/data/your_run.sh 
# 每天的凌晨3点30分， 执行脚本
30 3 * * * sh ~/data/your_run.sh 
# 每个月第1天凌晨3点30分钟， 执行脚本
30 3 1 * * sh ~/data/your_run.sh 
# 每周六凌晨3点第30分钟， 执行脚本
30 3 * * 5 sh ~/data/your_run.sh

定时任务简单、易用，是用机器解放人力重复劳动的典型应用。尽量将重复劳动的部分抽象出来，在工作中，一定要有这个思维。

grep 命令统计

Linux 系统中 grep 命令是用来查找文本中符合条件的字符串。grep 参数有 20 多种，可以满足各类查找的长尾功能需求。我们这里主要介绍几种常见的用法，如果你有时间并且感兴趣可以通过 man grep 命令查看 grep 命令的所有长尾用法。

• 查找文件中包含某字符串的行。

# 查找包含英文字符串: "zhang"的行
grep "zhang" yourFileName
# 查找包含中文字符串: 包括"北京"的行
grep "北京" yourFileName
# 查找包含中文字符串: 包括"北"或者"京"的行
grep "[北|京]" yourFileName

• 反向查找：查找不含某字符串的行。

grep -v yourFileName

• 正则查找：根据正则匹配条件查找。

grep -e "正则表达式" yourFileName

• 查找字符不区分大小写：

grep –i "被查找的字符串" yourFileName

grep 是用来完成字符串查找的 Linux 命令，下面来讲如何对满足条件的文本进行编辑的 Linux 命令。

sed 命令统计

sed 是一种编辑功能的命令，它可以对满足某种条件的行进行删除，也可以对匹配到的字符进行替换。

比如：文本很大，有成千上万行，但是要删掉第一行标题，这时我们不能选择打开一个这么庞大的文件，然后手动删除，我们只能使用 sed 命令进行删除。而替换的场景也是类似的，而且，sed 能做到批量快速替换。

下面，我们看下 sed 常用命令：

查找并打印：

# 查找包含"9"的行，并打印
sed -n '/9/p' yourFileName

查找并替换：

# 查找包含'mi'的行， 并替换为iphone
sed 's/mi/iphone/g' yourFileName

查找并删除：

# 查找包含’9‘的行， 删除改行后的结果输出， 原文件不变
sed '/9/d' yourFileName
# 查找包含’9‘的行， 在原文件基础上删除改行， 不打印输出
sed -i '/9/d' yourFileName

sed 命令功能强大，我们只需要了解其常用的功能，对于长尾的功能和参数， 当用到时再查看帮助文档即可，完全不需要记。

awk 命令统计

相比 grep、sed 命令，awk 更像一种高级编程语言，因为它拥有很多内置函数，是 Linux 系统中强大的文本处理工具。

数据分析师必须熟练使用 awk 来处理数据。下面我来举几个我日常使用 awk 完成初步数据统计分析的案例。

• 查询某几列并打印：

# SQL 写法：
select column_1, column_3 from test_table
# awk 写法：
awk -F'\t' '{print $1,$3}' yourFileName

• 查询某列值大于 100 的行并打印：

# SQL 写法：
select column_1, column_3 from test_table where column_1>=100

# awk 写法：
awk -F‘\t’ ‘{if($1>=100) print $1,$3}’ yourFileName

• 统计按某列 group by 分组汇总各组行数并打印：

# SQL 写法：
select column_1, count(1) as cnt from test_table group by column_1
# awk 写法：
awk -F'\t' '{a[$1]+=1}END{for(i in a) print i, a[i]}' yourFileName

awk 同样还有很多高级用法，其中上面提到 awk 其实就是一门高级语言，配合 Shell 管道命令可以和其他 Shell 命令无缝衔接，可以说威力无穷。

---

02 如虎添翼：开源 OLAP 工具如何助力数据分析

今天我们开始学习使用开源工具 OLAP 助力数据分析。

在数据经济时代，数据价值挖掘是企业发展的新动力。比如，电商平台为了获取更多的订单可能比你自己更了解你的兴趣偏好。短视频平台为了更多地抢占用户使用时长使用智能推荐让你“刷”不停，等等。

互联网大厂通过强大的数据基础建设，让数据分析挖掘的成本变得更低，使公司各个业务线都可以轻松获取数据，让数据赋能业务发展。而基础数据建设从底层到顶层依次包括了数据收集、数据仓库建设和 BI/OLAP。

本课时，我们就来分享下处于数据基建顶层的，也就是最接近业务侧数据分析的 OLAP 工具。

简单了解 OLAP

OLAP 的定义

想要搞清楚什么是 OLAP，我们先要理解“信息数据”这个概念。信息数据是从原始数据中转化出来的，能够真正为用户所理解，并真实反映企业多维度特性的数据。而 OLAP 的官方定义是使分析人员、管理人员或执行人员能够从多种角度对信息数据进行快速、一致、交互存取，从而获得对数据更深入了解的一类软件技术。

我们也可以将其简单理解为是一款帮助分析师从多个角度分析和挖掘现有数据的工具。它的目标是满足多维度数据查询和报表需求，核心点是 “多维度”。如下图:

OLAP 的优点

上节课我们也提到了大厂和小厂的区别，本质上是数据基础建设的区别。其中 OLAP 技术的应用，便是产生区别的原因之一。OLAP 技术在大厂应用比较普及，很多小厂则没有这个能力来开发和维护 OLAP。

OLAP 在很大程度上，能够充分降低数据分析的使用门槛，大大降低数据挖掘的成本。即方便数据分析师挖掘数据，又能让不熟悉 SQL 语言的产品、运营等业务同学通过简单的鼠标拖曳完成数据分析。

接下来我们通过对比传统手写脚本处理数据需求和使用 OLAP 处理数据需求的区别，来感受 OLAP 为数据分析师带来的便捷。首先，我们看下传统手写脚本的基本步骤：

1. 明确数据需求和统计口径；

2. 锁定统计所需要的数据表和字段；

3. 编写脚本，并测试语法正确无误；

4. 运行脚本，等待数据结果；

5. 任务执行结束，导出数据结果；

6. 将结果汇总整理后发送需求方。

步骤虽然看起来并不复杂，但在具体操作时即使是对数据处理特别熟悉的同学也需要花费精力按部就班地一步步操作，一步步检查，耗时又费力。

比如，第 2 个步骤中日常统计的数据表可能多达几十张，这时就需要把统计所需的表和字段都找出来，相当费时；第 3 个步骤中如果涉及多个表 join 和限制条件，哪怕是对 SQL 身经百战的我，实际上一次写完运行没有任何问题的情况也不多，等等。在每个步骤中都有可能遇到棘手的问题，特别是对数据表不太熟悉的同学，或是新来的同学，那么他花费的时间可能会更多。

但使用 OLAP 就可以帮助我们规避掉这些问题，OLAP 处理数据需求的大致步骤如下所示：

1. 确定数据需求和统计维度；

2. 确认 OLAP 系统统计维度完整；

3. 鼠标拖曳维度，像 Excel 透视表一样，瞬间得到计算结果；

4. 下载数据结果，直接发送给业务方。

你可以发现，使用 OLAP 过程中，不涉及编写脚本。因此也不需要一遍遍测试 SQL 脚本语法是否正确，自然也不涉及表和字段的确认。最重要的是，OLAP 计算神速，很快就可以轻松获取统计结果。

OLAP 之所以能够快速处理数据，是因为在搭建 OLAP 时会提前定义好常用的分析维度，预计算各维度的统计值，使得数据分析师可以在进行“上卷下钻”时能够将数据加载到内存中快速汇聚统计。

我们举个简单的例子，一张拥有上亿用户的画像表，包括用户 ID、年龄、性别、省份四个字段，其中用户 ID 是不重复的唯一值。这张表作为 OLAP 工具计算的数据源，想要实时计算不同维度的用户数量，就需要每天定时预处理各个维度的用户量。

你可以将预处理简单理解为将该表做类似下面的处理。

select age, gender, province, count(1) as cnt
from user_profile
group by age, gender, province

上面经过处理后的结果，将即使上亿行的表，也可以缩小为万级别，即: 年龄数 * 性别数 * 地域数= 100 * 2 * 31 = 6200，即预处理后的数据量，可以大大缩短计算时间。
相信通过上面的例子，你就可以明白 OLAP 为什么查询那么快了。

OLAP 主流技术

介绍完什么是 OLAP，以及 OLAP 对于数据分析方面的优势，下面我们来看下各厂都在使用什么样的 OLAP 工具。

这里我重点介绍 3 个 OLAP 工具，它们是目前互联网大厂应用比较广的，也是我自己经常搭建使用的。

• Druid：由广告公司 MetaMarkets 开源的实时大数据分析引擎，2011 年创建，并于 2012 年开源。主要用于大规模事件流数据（Event Stream Data）的存储和分析。Druid 被阿里、小米、网易、优酷、微博等公司广泛应用。

• Presto：Facebook 2013 年开源的 OLAP 工具。Airbnb 和 Dropbox、京东、有赞、微博等公司使用该工具。

• Clickhouse：第一大搜索引擎 Yandex 开发的列式储存数据库。 ClickHouse 比 Vertica 约快5倍，比 Hive 快 279 倍。比 My SQL 快 801 倍。字节跳动、阿里、微博......几乎所有主流互联网公司，都会使用到 ClickHouse。

我们大概了解了目前较为主流的 OLAP 技术，现在让我们再分别看看这些技术的情况。

Druid

首先来看看 Druid。Druid 设计之初，是为了满足PB 级别大数据量的实时数据分析，设计上借鉴了 Google 数据分析工具 PowerDril，满足快速查询、水平扩展、实时分析三个目标。

Druid 之所以能够做到实时数据分析，主要有以下原因：

1. Druid 支持实时数据摄入，且可以立即查询；

2. 类似其他 OLAP 工具，摄入数据时先预计算，以节省数据存储量级；

3. 列式存储。区别传统行式存储，每次查询要加载整个表，列式存储只需加载指定列数据，大大提升性能。由于列式存储这个优势，目前主流 OLAP 都采用列式存储；

4. 水平扩展。可部署到几十甚至几百台集群，支持万亿条记录查询。

以上是支持 Druid 做到实时数据分析的主要原因，其他方面，对索引、容错的支持也加强了 Druid 实时查询性能。

我们简单了解了 Druid 为什么在如此大数据量的情况下还能够做到实时查询，对于 Druid 的数据摄入、数据存储、索引建立等偏工程细节的问题， 这里不做过多介绍，如果你感兴趣可以到 Druid 官网进行学习。

下面，我再介绍下 Druid 的应用场景。Druid 适合导入数据量大，更新频次少，且要求实时查询的场景。比如，海量用户行为分析、用户画像分析等。Druid 官网列举的应用场景如图所示。

在实际使用中 Druid 可用于用户互动行为的实时数据查询和监控。比如：衡量运营活动的用户参与度，新旧功能的 A/B Test 数据对比，以及不同渠道用户行为数据等。Druid 的搜索和过滤功能使用户能够快速，轻松地按任意一组属性向下钻取。并根据年龄、性别、地理位置等获取想要统计维度的用户数据。

除了以上通过互动用户界面完成数据统计和展示外，Druid 还支持标准 SQL 查询。当然，Druid 也不是万能的，它并不能完全代替 Hive 查询。举两个例子。

1. 查询中涉及多个大表之间 join，即：Druid对表关联操作支持很有限。

2. 数据查询对延时要求不高，但对用户某具体行为颗粒度的场景分析。因为预计算会损失用户行为的个性化信息，所以这种情况是不容许进行预计算操作的。

在合适的场景中，Druid 会加速数据价值的运转效率。这里你可以思考下，想一想自己公司的什么业务可以使用 Durid？通过使用 Druid 卓越的实时数据分析能力，我们可以为公司创造什么样的数据价值？

Presto

接下来，我们再来了解一下 Presto。Presto 是 Facebook 于 2013 年开源的高性能分布式 SQL 查询引擎，为解决海量日志数据的分析而被开发。

在 Presto 诞生以前，Facebook 数据分析使用的工具是大家耳熟能详的 Hive。但 Facebook 的数据科学家渐渐发现，以 MapReduce 为底层计算框架的 Hive，计算性能和耗时越来越不能满足快速发展的业务数据分析需求。这是因为 Hive 速度太慢了，一个简单的数据查询也需要花费几分钟或者长达几个小时。

基于数据科学家的快速查询数据的强烈需求，Facebook 经过长期的调研和试用，却没有找到合适自身业务分析需求的 OLAP 工具。于是在 2012 年，Facebook 决定自己开发，并在公司内部推广使用，由此 Presto 也就诞生了，Preso 的性能要好于 Hive 10 倍。

我们先来简单了解下 Presto 设计架构，这将有助于你理解其优势、劣势和应用场景。Presto 使用的是和其他大数据处理引擎相同的架构：Master-Slave 架构，即主从架构。架构上分为大概四部分，我们先了解下这四部分的主要内容。

• Coordinator：即 Presto Master。我们可以将其简单理解为“包工头”。外面揽活，搞到"计算"任务后，将任务拆分给工人。

• Worker：即 Presto Slave 。简单理解为干活儿的工人，从包工头那里分配到"计算"任务，负责把活儿干好后（计算后的结果数据），返回给包工头，即 Master。

• Discovery Service：保存 Worker 结点信息。

• Connector：连接器。包括 Hadoop 或 MySQL 等组件的连接器，负责实际执行查询任务。


我们再来了解一下 Preso SQL 的运行过程，主要分为四步：

1. Coordinator 接到 SQL 后，通过 SQL 脚本会被解析成语法树，这个过程同时会 check SQL 语法，如果有错误会在此暴露，并结束查询任务；

2. SQL 语法没问题，会通过 Connector 查询 metadata（元数据），即 SQL 里查询的哪张表的哪些字段，以及字段类型，如果发现 SQL 字段类型错误同样会返回停止查询任务；

3. 经过上面两个步骤后，SQL 脚本最终被映射了一个查询计划，并被分发到逻辑计划查询器中， 从而转化成多个 task 任务；

4. task 将数据信息解析后，返回给查询计划，最后 task 会被分到各个分布式的机器（Worker）实际执行。

讲完 Presto 架构设计和运行步骤，我们了解下 Presto 特点和优势。

1. 快！Presto 最大的特点是快，它的设计初衷是解决快速查询大数据问题，期望查询时间是在几秒或者几分钟，因此速度是 Hive 的 10 倍以上；

2. Presto 可以查询完全基于内存计算的分布式 SQL 查询引擎。所有查询、计算都可以在内存中进行；

3. Presto 可以接入数据源，包括 Hive、Kafaka、MySQL、Redis 等；

4. Presto 为标准 SQL，支持复杂 SQL 查询。

看完上面的 Presto 的优势，你可能会产生一个疑问， Presto 大数据处理性能这么优秀，为什么没有完全替代 Hive？为什么目前 Hive 依旧是应用最广的大数据离线处理工具？

回答这个问题，就要了解一下 Presto 的缺点和适应场景了。下面，我说下 Presto 一些缺点，上面的疑问就会得到解答。

1. 我们知道 Presto 运算时是将查询任务拆分到多个 Worker 机器上去分别进行内存运算。其中哪怕一个 Worker 由于各式各样的原因挂掉（比如内存溢出等），整个 Presto 查询任务就会失败。相比较而言，Hive 的容错性能就要好很多。一台机器挂掉或者被其他计算任务抢占，计算也并不会因此失败。它会重新向 Master 申请资源，继续计算。

2. Presto 属于纯内存计算，不适合大表之间的多表 join 操作。否则容易引起内存溢出 OOM，造成查询任务失败。

3. Presto 采用 MPP（Massively Parallel Processing：大规模并行处理）架构，本身 MPP 架构使用场景就是秒级、毫秒级的查询场景，速度很快。但 MPP 有个明显缺点，即短板效应。如果一个 Worker 节点计算慢于其他节点，那整个计算任务都会受限于该节点。在实际工作中，Presto 接入的很可能就是 HDFS 数据源，不同节点的数据不一定分布均匀，这使得不同 Worker 干活效率不一样。而 Hive、Spark 等采用的批处理系统则会避免这一点。

由此可见，Presto 既有优点又有缺点。那么什么样的应用场景适合 Presto 呢 ？经哥在工作中发现，下面两个场景对 Presto 来讲最为合适。

1. 满足数据分析师临时查询需求，希望查询结果在几秒到几分钟内快速返回，这样的场景是适合 Presto 的。如果查询超过半小时，那说明该查询不适合 Presto 计算场景。

2. 支持 PB 级别查询需求，但不适合大表之间 join 操作的场景。用几个百亿级别大表进行 join 的复杂操作，可能使得 Presto 几十分钟没有结果，甚至挂掉。这样的计算任务可以交给 Hive 或者 Spark 等批处理分布式计算引擎完成。

Presto 相比上面介绍的 Druid，主要是解决 SQL 查询引擎的问题，将 SQL 查询转换成分布式任务，快速到数据存储区获得必要的数据，并且返回结果。Presto 直接访问 HDFS 或者其他数据存储层，并没有像 Druid 那样优化存储结构。它无法将原始数据进行预计算后建立索引并存储。

但好处是保留数据全部的数据信息，在计算性能满足的条件下，可以支持所有数据的行粒度信息查询。而 Druid 是在一定程度上提前设计分析常用的维度，压缩数据并存储，这个必然牺牲一些数据的个性化信息，目的是满足核心指标的快速上卷下钻，但对一些个性化 case 数据无法兼顾。

ClickHouse

我们再来了解一下 ClickHouse。ClickHouse 是俄罗斯 Yandex（号称俄罗斯 Google）在 2016 年开源的⾼性能分析型 SQL 数据库，主要面向 OLAP 场景。ClickHouse 凭借优异的查询性能，在互联网各大厂广泛应用，包括阿里、腾讯、字节、快手、携程、贝壳等。

ClickHouse 作为一颗新星，将大数据处理效率发挥到了极致。在数据存储和计算方面，它没有使用 Hadoop 生态，而是采用 Local Attached Storage 作为存储（即本地附加存储），这样使得整个数据 I/O（数据的输入和输出）从根本上被消除了。而基于 Hadoop 生态的大数据处理，从而引起的磁盘 I/O 占据整个数据处理的时间和资源。

ClickHouse 拥有以下三点优势。

1. 提供极致的查询性能。比传统数据处理引擎快 100~1000 倍，数据吞吐能力高达50MB~200MB/s。使用体验非常好。

2. 大数据的极低存储成本。ClickHouse 针对 OLAP 场景，开发高效列式存储、数据压缩算法，可以将原数据压缩 10 倍，极大提高单机数据存储和计算能力。可以简单理解为，原来一台机器存储 1TB 原始日志，而采用 ClickHouse 可以存储 10TB 原始日志。

3. 支持 SQL 查询，并同时支持 join 等复杂计算逻辑。ClickHouse 之所以能拥有极致的计算性能，即使简单的查询，ClickHouse 也会使用服务器一半的 CPU 去执行，所以其充分利用了机器的计算资源，并实现单机多核并行计算、集群分布式计算、列存储且列计算等。

同样，ClickHouse 也存在着自己的劣势。ClickHouse 的劣势主要体现在两点上。

1. 不支持事务操作，即数据的删除、更新。

2. 不支持高并发，建议 QPS 为 100。即每秒查询操作不要超过 100 个。

但对于 OLAP 应用场景， 其劣势正好显得不那么重要，我们使用时更注重其在大数据处理上的优势。因为 OLAP 查询一般为历史数据，很少变更，另外作为公司内部数据分析和产品人员使用，人数不会太多，同时每秒 100 个查询足够满足要求。

ClickHouse 也有一些合适的应用场景，在实时计算时，数据流通过 Kafka 或者 Flink 实时处理之后，通过 JDBC 方式批量导入 ClickHouse 中。在离线计算时，数据落地 HDFS ODS 层，离线通过 Spark 或 MR 的 batch 形式批量导入 ClickHouse 中。

ClickHouse 起步晚，但是作为大数据领域后浪发展迅猛。在公司中，ClickHouse 能为业务方，包括数据分析师、产品、运营、运维等提供高效数据 OLAP 查询，让业务方更快更高效的获得想要的数据，极大提升了数据价值挖掘。

总结

本课时主要介绍了 Druid、Presto、ClickHouse 这三个 OLAP 技术。可以看出，相比 Hive、Spark 等大数据计算引擎，它们的共同特征就是让数据查询变得更简单、更快速！秒级甚至毫秒级！但为了达到快速查询的目的， 它们也在其他方面做出牺牲。这并没有影响它们被广泛应用。作为数据分析师，在数据分析和处理过程中，都希望能够压缩数据处理的时间，如果有更快速且满足业务分析需求的工具和技术，必然是首选。

OLAP 就是这样的一种技术，满足数据分析师快速查询需求。希望通过本课时的学习，让你在简单了解各大厂应用的主流的 OLAP 技术的同时，能够深刻认识到 OLAP 在数据分析过程中的发挥的重要作用。

---