数据分析问答总结

一、SQL窗口函数

1.是什么

OLAP（Online Anallytical Processing联机分析处理），对数据库数据进行实时分析处理。

2.基本语法：

• <窗口函数>OVER （PARTITION BY <用于分组的列名>

                   ORDER BY <用于排序的列名>）

注：

<窗口函数>都有哪些：

1.专用窗口函数：rank（相等的值排名相同，计数）、dense_rank（相等的值排名相同，不计数）、row_number（对相等的值不进行区分）、first_value（用于获取在分组内的第一个值）、last_value、lead（用于在查询结果集中访问当前行之后的行的数据）、lag（在查询结果集中访问当前行之前的行的数据）等

SELECT 
    order_id,
    customer_name,
    order_amount,
    order_amount - LAG(order_amount) OVER (ORDER BY order_id) AS previous_order_difference
FROM orders;

 2.聚合窗口函数：count, sum, avg, max, min等，除count，其他聚合函数忽略NULL。

• PARTITION BY：类似于聚合函数中的GROUP BY子句，但是在窗口函数中，要写成PARTITION BY
• ORDER BY：和普通查询语句中的ORDER BY没什么不同

3. 窗口函数和聚合函数的区别

1.用OVER关键字区分窗口函数和聚合函数。

2.聚合函数每组只返回一个值，窗口函数每组可返回多个值。

4.注意事项

1.原则上只能写在SELECT子句中，因为窗口函数是对WHERE或者GROUP BY子句处理后的结果进行操作。over（）里的分组以及排序的执行，晚于where、group by、order by的执行。

2.常见主流数据库目前都支持窗口函数。

3.partition子句原则上可省略，但这就失去了窗口函数的意义。

5.为什么要用

group by分组汇总后改变了表的行数，一行只有一个类别。而partition by和rank函数不会减少原表中的行数。

窗口函数表示“范围”的意思，partition by分组后的结果。

二、A/B test

1. 原理

核心：假设检验。检验实验组和对照组的指标是否有显著性差异。

先做出假设，然后获取数据，最后根据数据来进行检验。

假设：

零假设：实验组 & 对照组：指标相同，无显著差异

备择假设：实验组 & 对照组： 指标不同，有显著差异

检验：根据指标的属性以及样本量的大小选择合适的检验方法。常用的检验方法有Z检验、t检验、卡方检验和F检验。

• t检验：总体正态分布、总体方差未知或独立小样本平均数的显著性检验、平均数差异显著性检验。
• Z检验：总体正态分布、总体方差已知或独立大样本平均数的显著性检验、平均数差异显著性检验。
• 卡方检验：检验实验组是否服从理论分布（将对照组看成理论分布）。

2. 流程

1.确定实验目标

评价指标和护栏指标

评价指标是驱动公司实现核心价值的指标，要具有可归因性、可测量性、敏感性和稳定性；

护栏指标也就是辅助指标。

评价指标重点关注一个目标，护栏指标可以选择多个作为辅助，避免达成一个目标造成别的利益的损失。

比如：要提升广告收入，我们在页面部分多插入一条或多条广告，虽然短期内是提高了收入，但是长期以来用户体验就会变差，造成用户反感继而流失。

2.设计实验：

• 建立假设：建立零假设和备选假设，零假设一般是没有效果，备择假设是有效果。
• 选取实验单位：有以下三种，常用用户粒度。

用户粒度：以一个用户的唯一标识来作为实验样本。好处是符合A/B实验的分桶单位唯一性，不会造成一个实验单位处于两个分桶，造成的数据不置信。

设备粒度：以一个设备标识为实验单位。相比用户粒度，如果一个用户有两个手机，那么也可能出现一个用户在两个分桶中的情况，所以会造成数据不置信。

行为粒度：以一次行为为实验单位，也就是用户某一次使用该功能，实验桶，下一次使用可能就被切换为基线桶，会造成大量的用户处于不同的分桶。不推荐。

• 计算样本量：很重要

太小：实验结果不会可信

太大：影响面越大。负面影响，流量和资源的浪费。

• 流量分配：分流（指直接将整体用户切割为几块，用户只能在一个实验中，不会相互影响。实验之间是互斥的）和分层（指将同一批用户，不停地随机后，处于不同的桶种。同一用户属于多个不同的实验，且相互之间不影响，企业中常用）
• 计算试验周

三、统计学专有名词

1.比率专有名词

1. 同比（Year-over-Year，YoY）：

   • 同比是指与同期相比较，通常是与去年同一时间相比。例如，如果你想了解今年1月的销售额同比增长，你会将其与去年1月的销售额进行比较。
   • 计算公式：同比增长率=(当前期数据−去年同期数据）/去年同期数据×100%

2. 环比（Month-over-Month，MoM）：

   • 环比是指与上一期相比较，通常是与前一个月相比。例如，如果你想了解今年2月的销售额环比增长，你会将其与去年1月的销售额进行比较。
   • 计算公式：环比增长率=(当前期数据−上一期数据）/上一期数据×100%

3. 定基比（Index，或称为基期比）：

   • 定基比是指将数据与一个固定的基准期进行比较。这个基准期的指数被设定为100，其他时期的指数相对于基准期进行比较，以反映变化趋势。
   • 计算公式：定基比=(当前期数据 / 基准期数据)×100%

四、异动分析

1.定义

核心指标超出一定的阈值。由结果（异常）找可能的原因，属于归纳法，最后验证可能的原因是由原因推结果，属于演绎法，异常分析是归纳法与演绎法的综合应用。

2.排除常见因素

利用MECE（Mutually Exclusive Collectively Exhaustive）模型对数据异动进行分类，尽可能地列举数据异动的所有类别，并且使得各个类别之间彼此独立。最终，将数据异动的原因分为五个类别，分别是数据的周期性波动、业务内部因素影响、外部因素影响、数据传输问题、其他意外因素。

首先要排除前四种因素：

• 数据的周期性波动——跟行业特点相关。（预测分析：关键影响因素的相关性（回归、时间序列分析））
• 业务内部因素影响——运营、产品迭代、广告效应等
• 外部因素影响——天气、政策、竞品等
• 数据传输问题：跟产品和研发核实数据准确性，取数逻辑是否有误——统计口径是否变更——报表是否成功执行——中间表是否出问题——数据解析是否有问题——客户端服务端是否有问题。

3.针对意外因素引起的数据波动怎么分析？

总结起来就是：描述统计；拆解溯源；

1、描述性统计，确定问题的轻重缓急

• 从时间线上异常多久了，突然性异常（如日活，本周等）、持续性异常（连续下降/连续上升）还是累积性异常（如月度下降）？
• 具体波动幅度是多大？环比同比以及是否有过类似情况，有可以做对比？
• 该指标相关的指标是否异常？

一般来说，持续性下跌≥一次性下跌≥周期性下跌。
如果是一次性变化，一般都是短期/突发事件；
如果是周期性变化，一般跟行业特点有关，如果本周期的数值明显比上周期跌得更厉害，就得注意，可能在周期性变化背后隐藏了其他问题；
如果是持续性变化，往往有深层次的原因，比如用户需求转移，行业繁荣/枯萎，渠道形态变迁等。

重+急：反馈；重+缓：深入分析； 轻+急：去平均化，定位问题； 轻+缓：找不到可以再观测观测。

2、根据指标去细分&交叉拆解

1)拆解维度，确定驱动指标

• 拆解维度分三类
  • 一般可以从业务指标背后的公式以及相关变量以及背后的用户路径等不同的维度拆解问题
  • 常见的基础拆解维度：地区、登录渠道、入口、新老用户、平台、版本号等
  • 根据业务经验，一些可能有影响的其他维度
• 辅助指标，影响系数
  • 拆解后将波动数据及相关指标和历史数据做对比，计算影响系数。
  • 影响系数越大，说明此处为主要原因所在（只是一种说法，其实是看细分后数据的变化幅度，主要是为了去平均化，找到引起波动的源头数据）。

2）定量和定性驱动指标的常用方法

• 怎么确定大概率是哪些指标波动/因此波动？

• 怎么确定相关变量/维度的相关性大小，方法一般是（线性相关分析）：
  • 变量/维度层面：计算相关系数，或者绘制相关矩阵，
  • 属性层面：进行交叉分析，绘制交叉分析表格或者交叉表相关分析。
• 非线性相关分析：
  • 定性分析：散点图，肉眼观察变量间的变化是否有一定的规律可寻；
  • 定量分析：可参考：Distance correlation（距离相关系数）,当距离相关系数为0或者很接近0时，可以认为是没有非线性关系的。

3）间接影响（有隐藏变量） 分布不均匀：直接对比绝对量，就相当于默认整体中分布是均匀的，但是往往都不是，解决方法是引入整体分布作对比，例如计算TGI指标。

4、异动分析例子：

异动归因的一些理解思路 - 知乎

5、两种异动归因的方法，分别是维度下钻、驱动指标。

维度下钻

常见的归因问题均可认为是指标 Y 发生某种“变化”或偏离某种正常状态时，我们希望理解其(主要)原因。

当E(Y(X)) = E(Y|set X=x) 与x有关（即不等于某个常数）时，我们认为X对Y有因果关系（即 X → Y）。Yi的改变称为属性改变影响面，X的改变称为结构改变影响面。

驱动指标

通常情况下，X与Y存在相关性，甚至存在因果关系。 例如标签页vv = 视频详情页PV *标签点击率 *标签页 CTR，则其异动可能是前述的几个指标的异动造成的，前述指标可能是驱动指标。上述这类指标拆解通常有明确的产品路径（Customer Journey），带来因果关系。但也存在并非显性路径，而有某种隐形业务逻辑的，我们可以将目标指标的异动归因到其驱动指标，在对指标的变化进一步归因。

驱动指标从指标的类型上来分，可以分为类别型驱动指标和连续性驱动指标，我们把用户留存（Y）当作我们的关键指标，解释两类驱动型指标的分析思路。

类别型驱动指标

类别型驱动指标关心的是子群用户与大盘用户（或者其他子群用户）在各种特征（即指标）上的差异。留存分析建议使用的类别型变量包括：用户客观指标和平台行为指标。以用户客观指标为例，可以分为年龄段，性别，省份等等。 这里我们用TGI （Target Group Index）来表示目标子群在整体表现的强烈的程度。

百度百科：

TGI指数=[目标群体中具有某一特征的群体所占比例/总体中具有相同特征的群体所占比例]*100

连续型驱动指标

连续型驱动指标关系X与Y的变化趋势，即相关性，以及增速拐点

 最后会形成一个类似于Sigmoid函数的 “S型曲线”。拐点处为斜率最大值。业务同学可据此设计抓手，把拐点之前的用户，作为提留存的目标用户。