矩阵分析学习指导_一个打工人的学习日记

前言

双十一刚刚过去不久，光荣的打工人已经变成了丁工人，钱花完了，也舒服了，今天就静下心来整理下最近的一些学习心得（我就姑且大言不惭的称之为心得吧 ）。

两个月前，出于补充自己知识体系漏洞的原因，我报名了拉勾的线上课-数据分析实战训练营，看好这个课程，主要是因为课程体系和授课方式很适合我，

首先说说拉勾数据分析训练营的学习框架

1. 数据分析思维与业务流程；
2. MySQL数据分析实战；
3. BI可视化工具；
4. 大数据查询利器-HIVE；
5. 数据分析必备理论基础-统计学；
6. 数据分析必备编程语言-Python；
7. 数据分析项目与实战；
8. 数据挖掘算法与实战；
9. SPSS数据建模方法与业务实现；
10. 就业指导。

作为一个在数据分析行业摸了几年鱼的选手，这个课程体系给我的感觉是很科学，而且对于初学者来说，单单是学完前三个阶段便具备了一定的数据分析能力，大多数的数据分析岗位对这三个方面的需求也是最多的，后边几个模块，则是对一个数据分析师的工作能力做了一个更强的补充，尤其是挖掘算法和Python对进阶中级数据分析师是很重要的。

而训练营的学习模式是录播加直播的线上授课方式

好处是非常适合我这种没有太多连续闲散时间的打工人，并且因为课程相对于线下授课更加灵活，可以把这些容易浪费的碎片时间利用起来，而周六日则可以对本周的学习做一些阶段性的总结甚至有精力的话还可以做一些拓展（996和007的加班狗们表示对此不甚赞同 ）。当然有好处肯定也有一些劣势，相对于线下课程，答疑解惑可能没有那么直接和迅速，但是针对这一点，训练营也有固定的导师团队随时在线上答疑解惑，所以这些劣势也不是很大，相对于优势来说，简直可以当没有了。

除了课程体系还有授课方式外，训练营另外一个比较吸引我的地方就是所有模块

几乎都是结合实际案例来讲解，

这样可以防止干巴巴的知识点过于枯燥乏味，并且在学习的同时自然而然的就了解到了这些知识点要怎么使用才能更好地服务于工作，这里要点个大大的 。

课程情况大概就是这样，目前为止，我们已经学习了三个阶段的知识，第四阶段正在进行中，这里我先对前三个阶段的知识点做一下梳理总结

那么首先，先来说一下第一阶段：

一、数据分析思维与业务流程

1.工作流程梳理

条条大路通北京，但不是每一条路都跑的是复兴号。

以前我做分析，有一个问题就是没有体系，简单来说就是没有一套流程来科学管理自己的工作，导致的问题就是虽然单拎出来每个环节都可以做出来，但是用来捋顺思路的时间占了很大一部分，所以工作效率不是很高，一个良好的工作流程管理，能让工作效率显著提高，这是毋庸置疑的。在训练营我首先学到的就是作为一个数据分析师，怎样去开战分析工作，才能让工作效率更高，这就不得不提到幽默的三丰老师，这一阶段他给我们首先讲解的是一个完整的分析流程是怎样展开的：

1. 首先就是要了解事件背景，这个事件发生在什么时候？我们做了什么活动？活动的目的是拉新还是促活还是提升转化？了解了具体的事件背景，我们才能更正确的做出分析判断。
2. 接下来很重要的一点就是明确问题，即主要问题或者我们的目标是什么？销售额降低了还是渠道的支出升高但是没有什么产出增长？我们的目的是减少投入还是优化问题环节？
3. 在明确了主要目标之后，就需要对问题进行细分拆解，看看为了达成这个主要目标，需要从哪几方面入手。
4. 细分目标做完了，接下来就是达到这些目标的具体要求。也就是量化目标，给出一个标准。
5. 其实上边这些步骤一般在工作中都是决策管理层负责做，我们的主要工作任务是从第四步开始：制定方案，针对相应目标，给出具体的问题解决方案。
6. 评估方案，评估一下方案的可行性，是否能达到目标，能达到目标的多少？需要多长时间？投入多少成本才可以达到目标？如果方案评估可行，那么就继续进行下一阶段，进行抽样实验，如果方案不可行，重新整理制定。
7. 实验，也就是具体的方案实施环节了。
8. 实验评估，这个环节也是数据分析师的主要工作环节之一，实验或者活动结束之后，我们要对得到的数据进行整理归纳，分析出实验的效果怎么样，有没有达到预期，如果没有达到预期问题出在哪里，我们该对这些出问题的地方做怎样的优化调整。
9. 结案，输出分析报告。

2.分析方法与适用环境

大概的工作流程我们已经知道了，那么到了具体的工作中，我们一般都是用什么方法，这些分析方法又适用于那些场景呢？

2.1.分组分析与对比分析

其实这是两种分析方法，之所以放在一起说，是因为这两种分析方法经常一起使用

分组分析法是根据对象的特征进行聚合归类，按照一定的维度把数据聚合到一起，把总体数据中具有不同性质的数据区分开，把具有相同性质的数据合并到一起，比如我们经常使用的指标DAU（日活），MAU（月活）、日均销量等等都属于分组分析应用场景，而我们把这些指标展现出来之后，我们只是看到一个总体的情况，此时如果我们加入对比分析，就可以看到每个维度所聚合的数据差异，这就用到了对比分析法。

对比分析法通常用于简单的优质品类查找，我们可以按照对比对象把之前经过分组的不同品类的商品通过柱形图的方式并行排列，来查看在一定的时间段内，各类商品的销售情况，并进行排序，从而一目了然的找到销量最好的商品和销量最差的商品；当然我们也可以通过饼图的方式来查看每类商品所占总销售额的比例，还可以对本月和上月的总销售额进行对比，查看销量的环比。但是对比分析仍然存在一定的缺点，就是对比分析往往只能以单一指标进行对比，当我们需要多维度分析的时候，对比分析就显得有些力不从心了，这就要说到下边两种能在更多维度进行对比分析的分析方法：矩阵关联分析与综合分析法。

2.2.矩阵关联分析

矩阵关联分析，又称为象限分析，通常用于多维度寻找优质资源或优质产品，

矩阵关联分析是指将事物的两个重要指标作为分析的依据，进行分类关联分析，找出解决问题的一种分析方法，只要两个指标之间线性无关且放在一起有意义都可以用象限分析 。比如我们可以通过把ROI和次日留存进行矩阵关联分析，来查找优质渠道，并对“偏科“的渠道进行针对性优化，对劣质渠道深挖，看看是否还有保留的必要。

矩阵关联分析虽然相比于对比分析多了一个指标维度，但是当我们有更多方面需要考虑的时候，矩阵关联分析也有一些捉襟见肘了，那么这个时候，有没有对更多指标进行综合对比的分析方法呢？当然有，这就是综合分析法

2.3.综合分析法

综合分析法通常情况下是在我们需要对三个及以上的指标进行综合对比的时候用到，综合分析法有目标优化矩阵表和主成分分析两种主要分析方法，这里我们主要说一下目标优化矩阵表的分析方法。

目标优化矩阵表的原理是把人脑的模糊思维简化为计算机，得出量化结果，特点是简单、快捷，步骤主要分为三步：

这里我们以渠道指标体系为例进行讲解：

上图为我们计算出各个渠道四项指标的明细数据，接下来我们对他进行矩阵优化

1）首先将纵轴上的指标依次与横轴上的指标对比，由专家进行投票决定，如果纵轴上的指标比横轴上的指标重要，那么在两个指标交叉的单元格中填"1"，否则填"0"，然后将每行结果相加为指标重要性合计得分，并将该项分数与所有指标的总分相除，得出各项指标权重（如某指标重要性合计得分为0，则可以把每个指标的重要性合计得分加“1”，再计算指标权重）。

通过原始表我们可以看到，各项指标的量纲不同，这样的话我们进行矩阵优化会受到影响，那么我们接下来进行第二步，

2）数据标准化，去除量纲影响。

作用:同一指标体系内，统一指标数量级，防止出现数量级不同影响决策的情况。

公式：(x-min)/(max-min)，

公式解释：(被标准化值-此指标内最小值)/(此指标内最大值-此指标内最小值)

前两步完成了，接下来进行最后一步，

3）利用指标权重和标准化后数据计算综合得分，然后进行排名。

这里我们如果是用Excel进行计算的话，需要用到一个数组函数MMULT，关于MMULT，这里可以说一下：

1.函数功能:返回两数组的矩阵乘积之和。结果矩阵的行数与array1的行数相同，矩阵的列数array2的列数相同。

2.语法

MMULT(array1,array2)

Array1, array2是要进行矩阵乘法运算的两个数组。

3.参数说明:

使用MMULT函数，需要认识下面三点:

①先判断两个矩阵能不能做乘法，判断方法:数组1的列数必须与数组2的行数相等。

②知道矩阵是如何相乘的。计算结果的行数与Array1的行数相同或者计算结果的列数与Array2的列数相同。

③MMULT矩阵中只能包含数值，任意单元格为空或包含文字将返回错误值#VALUE!。

4.MMULT不用三键结束(数组结束方式)的话就是第1行第1列的数，用三键结束则是一个数组;MMULT作为其它函数参数时则是一个数组参加运算，不需用三键结束。

5.MMULT两矩阵相乘的过程是:用第一参数的元素分别乘以第二参数对应的元素，然后将结果相加得到最终结果。

2.4.趋势分析法

趋势分析法常用于纵向分析，查看一段时间内数据的走势是平稳，上升或者是下降，作用是掌握大的方向，常用图形为折线图+趋势线的方式。

2.5.漏斗分析

漏斗分析是一套流程式的数据分析，他能够科学反映出用户行为状态以及从起点到终点各阶段用户转化率的情况，适用于业务流程相对规范，周期较长，环节较多的流程分析，能够直观的发现和说明问题所在。

做漏斗分析通常有以下几步：

1）做出漏斗分析图；

2）找出问题环节；

3）分析问题环节产生问题的原因；

4）设计实验改善问题环节；

5）对实验结果进行分析解析。

2.6.RFM分析模型

通过一个客户的①近期购买行为R、②购买的总体频率F、③以及花了多少钱M这3项指标来描述该客户的价值状况。

R是指用户的最近一次消费时间，用最通俗的话说就是，用户最后一次下单时间距今天有多长时间了，这个指标与用户流失和复购直接相关反映了用户的粘性。

F是指用户下单频率，通俗一点儿就是，用户在固定的时间段内消费了几次。这个指标反映了用户的消费活跃度。

M是指用户消费金额，其实就是用户在固定的周期内在平台上花了多少钱，直接反映了用户的贡献值忠诚度。

现实一般情况下，F和M都不限定时间，直接按照用户计算此用户总购买次数和总消费金额。

三个参数中，最重要的是M（总消费金额）。

3.结案报告的编写

分析方法主要设计到具体的数据分析，那么结案报告就主要使我们对实验或者说是活动的评估了。

3.1.首先说下结案报告的主流编写方式大致是这样的：

总体的来说，是总→分→总的一个流程，

1. 结论先行，我们需要对我们的分析做一个总体的结论，即我们通过分析发现了什么问题，做了怎样的优化，得到了什么样的结果。
2. 接下来对分析所用到的指标和数据做一个简要的说明，防止数据或者指标过于晦涩难懂。
3. 过程数据做支撑，对我们具体所做的分析做一个具体的讲解，这是对结论的一个扩充和详细说明。
4. 根据分析给出总结建议，再提下结论，并说下之后的策略。

3.2.接下来说下我们对一个实验或者是一个活动的具体评估流程：

结案报告最忌讳亮出了一堆数据但是没说这些数据反应了什么问题，所以评估一个活动的正确方式是：

1. 对活动效果进行判断：好或者是不好。
2. 如果活动效果好，那么还能不能继续做，接下来要怎么做；如果是不好，那么是效果非常差，没法用，还是存在可以改进的地方。
3. 效果好，接下来的活动策略该在这个基础上如何处理，是维持现有策略还是加大力度；如果活动效果差，但是可以改进，我们要优化哪些环节，该如果优化这些环节。

明确了评估的正确方式，我们就可以开始评估了：

1. 首先我们要知道我们的活动是一个什么类型的活动，或者问题是一个什么类型的问题；
2. 接下来根据问题的类型筛选出主要的评估指标；
3. 对这些评估指标设定一个判定的标准，比如是总体想要达到什么水平，或者是和活动前后对比，和上一次活动对比等等；
4. 标准设定好之后就看相关指标是否达到了设定的标准；
5. 没有达到标准的环节是在哪些环节；
6. 对没有达到标准的环节给出一些可行性建议。

以上，是结案报告的编写要点。

二、MySQL（以下SQL代码运行环境皆为MySQL8.0）

数据分析师工作中基础的基础是要有数据，但是像Excel这种数据文件，存储的数据量非常有限，而且当计算较多且数据量较大的时候，Excel的性能问题便摆在了我们的面前，这个时候我们就需要用到一个性能更强大的数据载体--数据库，这里我所学习的数据库便是MySQL数据库，关于对MySQL数据库的基础知识就不再正文中展示了，大家可以点进这个链接看一下：

有道云笔记​note.youdao.com

一开始也说了，训练营的课程一般都是结合案例来讲，那么下面说一下在MySQL这个模块学到的两个重要业务知识：

留存率与连续打卡类问题的解决思路。

1.留存率的计算。

计算留存率在数据分析师的工作中是个高频工作，新上线产品需要计算，活动前后需要计算，留存率能给我们的决策提供很重要的指标支持，那么留存率具体需要怎么计算呢？

首先，先看一下留存的定义

1.1.留存的含义：

留存：指基准日访问的用户在之后的n日当天再次访问的人数；

留存率 = 基准日之后的n天当日返回的用户数 / 基准日的用户数 * 100%

或者指基准日产生某个行为的用户在之后的第一天，第二天，第三天……第n天的当天再次产生该行为的人数。

1.2.留存的意义：

留存代表一个用户愿意再次使用你的产品；而一个产品能够被用户再次使用，意味着这个产品是能够满足用户长期需求的，能够让用户产生一定粘性的产品。

如今互联网产品大多为免费产品，依靠持续的广告转化、用户持续的购买转化、用户持续的会员付费来维持收益。

那么，怎样才能达到持续的广告转化？持续的购买转化？持续的会员付费？

广告转化、购买、会员付费，我们都需要用户来完成。那么持续的广告转化、购买、会员付费，我们就需要每天都有一定量的用户来完成以上过程，也就是说我们需要我们的产品每天都是有活跃用户的。

那么从活跃用户的构成，我们就知道怎么维持我们的活跃用户规模：一方面持续有新用户流入，另一方面持续有留存用户留存；

那么为什么留存用户重要？部分有过从业经验的同学应该知道，产品的自然新增用户是比较少的，如果我们仅靠产品自然用户增长，那么必然我们的活跃用户量级会非常小，虽然能够获得收益，但是这部分收益很可能养不起我们的公司；但是如果我们想要用购买新用户的方式获得持续的较大规模的用户池，又会产生比较大的成本，这样做容易亏本。所以我们需要新增留存两手抓；想方设法留住我们新增的用户，让其长期地在我们的产品内产生价值，这样做，比起单做新增，在成本和效果上都是要好的。

1.3.留存的实际应用场景

留存的主要应用场景有两个：

1）产品整体视角的持续留存情况

也就是我们需要定期分析用户在产品的留存是否维持在一个正常的范围，有没有突然地涨跌情况，如果有的话问题出在哪里，为什么会出现这样的问题，如何解决留存异常的问题。遇到这类问题首先需要确定的是整体用户的留存异常还是个别群体用户的留存异常，如果是整体用户的留存异常，我们需要分析我们的产品是否发生异常，可以通过用户的行为路径确定异常产品位置；如果是个别用户的留存异常，需要通过用户拆解的方法来定位有异常的用户群体，然后通过这类用户的特征来分析问题原因。

2）新增用户视角的每批新增用户留存情况。

一般出现在计算投放ROI的场景，也就是我们投放一批用户，能够给我们带来多少收益。我们会用这些用户的“整个生命周期能够带来的收入”（LTV）去除以“投放时的支出”来计算我们的ROI。“整个生命周期能够带来的收入”计算的是引入一批新用户，当用户完全流失时，这部分用户所带来的所有收入。但是有时我们需要提前预估新用户在整个生命周期能够带来的收入，我们就需要首先用留存来估计用户的生命周期，然后用估计出的生命周期再去计算用户的整个生命周期能够带来的收入。

1.4.案例实操：

计算某APP的每日留存数据。

明确计算留存率需要用到哪些数据：

字段	说明
user_id	用户ID，作为留存率的分析主体
dates	用户访问时间(在计算不同的留存的时候，这个时间也可以取不同的维度)

根据所需要用到的数据，创建用户活跃信息表并导入数据：

create table temp_user_act
(user_id int,
dates date);

（接下来的每一步计算以视图方式计算）

接下来我们思考下，计算留存率实际上是计算什么东西？

首先，留存率这个指标的载体是人，或者说是设备，以某一天为基准日，把这一天访问的用户作为样本，对比之后几天这批用户还有多少用户在继续访问，而具体到人，就是这个人在之后哪一天又回来访问过了，知道了这一点，实现起来就简单了很多：

SQL实现：

1）以user_id为连接条件，把这个表进行自连接，并将右侧小于左侧的日期排除掉,这一步得出的是每个用户在基准日登录和他之后访问时间的组合。

create view table_join as 
select 
	a.user_id as user_id_a,
	a.dates as dates_a,
	b.user_id as user_id_b,
	b.dates as dates_b
from temp_user_act a 
left join temp_user_act b
on a.user_id=b.user_id
where b.dates>=a.dates
order by user_id_a,dates_a;

执行结果：

自连接之后的结果

其实从这里我们就可以初步看出每个用户在每个基准日之后登录的时间了，接下来

2）以左侧日期为首日，分别计算出第2天人数，第3天人数、第4天人书以及第8天人数。

create view table_lcrs as
select 
	dates_a,
	count(distinct if(datediff(dates_b,dates_a)=0,user_id_a,null)) as users_day1,
	count(distinct if(datediff(dates_b,dates_a)=1,user_id_a,null)) as users_day2,
	count(distinct if(datediff(dates_b,dates_a)=2,user_id_a,null)) as users_day3,
	count(distinct if(datediff(dates_b,dates_a)=3,user_id_a,null)) as users_day4,
	count(distinct if(datediff(dates_b,dates_a)=7,user_id_a,null)) as users_day8
from table_join
group by dates_a
order by dates_a;

• datediff(A,B)=1 表示日期A为日期B之后的一天。
• count(distinct case when datediff(A,B)=1 then uid else null end) 或者 count(distinct if(datediff(A,B)=1,uid,null)) 就是求出“日期B为基准日A之后的第一天”的用户数计数

执行结果：

首日人数2、3、4、8日留存人数

到了这一步，留存人数已经求出来了，接下来就是求留存率：

3）利用上述数据计算出每日的用户数以及次日留存率，二日留存，三日留存率，7日留存率；

create view table_lcl as
select
	dates_a ,
	users_day1,
	concat(round(users_day2/users_day1*100,2),'%') as lcl_day2,
	concat(round(users_day3/users_day1*100,2),'%') as lcl_day3,
	concat(round(users_day4/users_day1*100,2),'%') as lcl_day4,
	concat(round(users_day8/users_day1*100,2),'%') as lcl_day8
from table_lcrs 
order by dates_a;

执行结果：

次日留存率以及二三七日留存率

到这里位置，我们的留存率计算就结束了，接下来，我们可以根据留存率做趋势分析，查看异常点，也可以深入挖掘一些其他的指标，留存率在活动运营上具有很重要的意义，理解并熟悉了留存率的适用情况和计算方式对我们的业务分析会有很大的帮助，当然我们还可以求出留存率的环同比，或者不限于七日留存，拓展到15日留存甚至30日留存，还可以按周计算周留存（一般为8周留存），这个要看我们的具体业务场景，不同的场景使用适当的时间粒度和统计周期。

除了上边所用到的创建视图进行多步计算，还可以使用with子句，with子句可以看做是一个临时视图（不是视图，只是看做），用完就丢，不会在数据库内创建对象，常用业务我们可以用视图实现，不常用业务，我们可以用with子句计算，以上边的问题为例，可以这样写：

with T1 as 
(select 
	a.user_id as user_id_a,
	a.dates as dates_a,
	b.user_id as user_id_b,
	b.dates as dates_b
from temp_user_act a 
left join temp_user_act b
on a.user_id=b.user_id
where b.dates>=a.dates
order by user_id_a,dates_a),

T2 as
(select 
	dates_a,
	count(distinct if(datediff(dates_b,dates_a)=0,user_id_a,null)) as users_day1,
	count(distinct if(datediff(dates_b,dates_a)=1,user_id_a,null)) as users_day2,
	count(distinct if(datediff(dates_b,dates_a)=2,user_id_a,null)) as users_day3,
	count(distinct if(datediff(dates_b,dates_a)=3,user_id_a,null)) as users_day4,
	count(distinct if(datediff(dates_b,dates_a)=7,user_id_a,null)) as users_day8
from T1
group by dates_a
order by dates_a)
	
select
	dates_a ,
	users_day1,
	concat(round(users_day2/users_day1*100,2),'%') as lcl_day2,
	concat(round(users_day3/users_day1*100,2),'%') as lcl_day3,
	concat(round(users_day4/users_day1*100,2),'%') as lcl_day4,
	concat(round(users_day8/users_day1*100,2),'%') as lcl_day8
from T2
order by dates_a;

执行结果如下：

以上即为留存率的计算。

2.累计打卡类问题

这个问题我在做的时候查了很多资料，发现这是一个很普遍的问题，但是网上的解决方法要不然就是又臭又长，要不然就是逻辑错误。在这个问题上，训练营的老师和同学分别给出了两种解决方式，前者是使用变量进行计算，后者是利用偏移函数多次偏移求得，但是两者都用到了开窗函数，开窗函数是MySQL8.0加入的新的函数类型，具体的解释请看我上边的笔记链接。

2.1.首先是了解案例背景：

某短视频公司有作者发布视频统计表如下：

表名：temp_author_act

字段：

字段名	字段类型	字段说明
datas	date	发布日期
author_id	varchar(5)	作者id

用SQL出作者的最近三个月内的最大断更天数、平均断更天数和最大持续更新天数；

名词解释：

持续更新天数：如果一个作者在某几天中每一天都有更新，那么这段时间的天数称为这个作者的持续更新天数。

断更天数：如果一个作者两次更新中间隔了几天没有更新，那么这几天的天数称为这个作者的断更天数。

对于最大断更天数，平均断更天数都很好计算，利用开窗函数的特性，我们可以很方便的求出，而对于持续更新天数，就需要理解一个模型：把更新的天视为1，断更的天视为0，那么就需要对1进行累加计算，并且在遇到0的时候停止累计，并在下一个1重新开始累加。

明白了这个问题，那么下面开始解决问题：

2.2.解决步骤

1）首先第一部仍然是建立表格并导入数据：

create table temp_author_act
(
dates date,
author_id varchar(5));

2）求出上次更新时间和断更时长（当前日期-上次更新时间）：

CREATE VIEW table_dgsc AS
SELECT
	dates,
	author_id,
	date_lag1, #上次更新时间点
	DATEDIFF( dates, date_lag1 ) - 1 AS dgsc #用本次更新时间与上次更新时间相减，求出断更时长
FROM
	(SELECT 
		dates, 
		author_id, 
		LAG ( dates,1) OVER ( PARTITION BY author_id ORDER BY dates ) as date_lag1 #lag求上次更新时间
	FROM temp_author_act) a;

执行结果：

3）求出最长断更时间，平均断更时间和最长连续更新时间：

CREATE VIEW table_mal AS 
SELECT
 a.*,
 b.maxlx 
FROM
	(SELECT
		author_id,
		MAX( dgsc ) AS maxdg,
		AVG( IF ( dgsc < 0, 0, dgsc ) ) AS avgdg 
	FROM table_dgsc 
	GROUP BY author_id) a
	LEFT JOIN 
	(SELECT
		author_id,
		MAX( DATEDIFF( date_lag1, date_lag2) + 1 ) as maxlx
	FROM
		(SELECT
			author_id,
			dates,
			date_lag1,
			dgsc,
			LAG ( dates, 1) OVER ( PARTITION BY author_id ORDER BY dates ) date_lag2
		FROM table_dgsc 
		WHERE dgsc != 0 or dgsc is null) a
		GROUP BY author_id) b 
ON a.author_id = b.author_id;

执行结果：

三大指标

这个SQL稍微有点长，不太好理解，那么下面把这个SQL拆开来看:

①第一步：首先用where做筛选，筛选出所有更新截止的时间点，并利用偏移函数lag求出上次更新截止的时间点：

SELECT
	author_id,
	dates,
	date_lag1,
	dgsc,
	LAG ( dates, 1) OVER ( PARTITION BY author_id ORDER BY dates ) date_lag2
FROM table_dgsc 
WHERE dgsc != 0 or dgsc is null) a
GROUP BY author_id

②第二步：用上次更新时间-上次更新截止时间点，那么着中间的时间，就是连续更新的时间（结果要+1，因为这个时间点是更新截止的时间点，要把他算进来。），再用max聚合求出最大的一个值，也就是最长连续更新时间：

SELECT
	author_id,
	MAX( DATEDIFF( date_lag1, date_lag2) + 1 ) as maxlx
FROM
	(SELECT
		author_id,
		dates,
		date_lag1,
		dgsc,
		LAG ( dates, 1) OVER ( PARTITION BY author_id ORDER BY dates ) date_lag2
	FROM table_dgsc 
	WHERE dgsc != 0 or dgsc is null) a
GROUP BY author_id;

执行结果：

③求出另外两个指标，其他两个指标其实很好算，直接带出来就行了

SELECT
	author_id,
	MAX( dgsc ) AS maxdg,
	AVG( IF ( dgsc < 0, 0, dgsc ) ) AS avgdg 
FROM table_dgsc 
GROUP BY author_id;

执行结果：

④第四步，也就是上边整条大SQL求出来的结果，把三个指标根据作者ID连接到一起，得出最后结果。

这里我们可以看到，多次使用偏移函数求出连续更新时长虽然SQL比较简单，但是理解起来过于困难，那么有更简单一些的方法吗？

有，这就是用变量法求连续更新类问题：

with p as
(select 
*,
lag(dates,1) over (partition by author_id order by dates) as dates1,-- 求出上次更新时间
@row:=0 -- 声明变量
from temp_author_act)

select 
*,
if(datediff(dates,dates1)=1,@row:=@row+1,@row:=0) as lxhx
from p

①首先第一步求出上次更新时间并同时声明变量，留作下一步使用；

②第二步，用IF做判断，变量可以跨行累计，根据上一步求得结果，用本次更新时间-上次更新时间=1就是没断更，>1就是有断更，连续更新一次就把赋值+1，累加求和，当断更的时候再赋值变量为0，重新开始计算，这样，就很好理解了，和我们一开始说的思路是一样的。

可以发现，变量法在计算连续更新的问题上思路更清晰，并且SQL也没有变的更复杂，所以解决此类问题，建议使用变量法。

那么我们计算连续更新问题有什么意义呢？

2.3.此类分析的意义

如今的大多数内容APP都是以平台模式运营的。

什么叫做平台模式？

比如我们现在能接触到的大多数内容产品“抖音”、“Bilibili”、“微博”、“今日头条”等等。我们使用到的这个APP，是由APP的制作公司提供的，也就是这个APP会有几个主要的页面，每个页面怎么引导用户进入内容页面，播放内容的方式有哪些、内容间切换会用什么方式等等，都是我们的APP制作公司决定的；而我们在这个APP里看到的大多数内容，都是由内容作者提供的。

为了能够让我们的平台能够有持续的内容产出，我们就需要分析作者的活跃行为。主要为我们的作者运营提供有力帮助。

一方面让我们找出活跃作者，增加政策扶持；

一方面让我们找到快要流失的作者，及时召回这部分作者、挽回损失。

这就是分析此类问题的意义所在。