sql，python

学习记录

EXPLAIN解析

id

我的理解是SQL执行的顺序的标识,SQL从大到小的执行

1. id相同时，执行顺序由上至下
2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

---

select_type

示查询中每个select子句的类型

1. SIMPLE(简单SELECT,不使用UNION或子查询等)
2. PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
3. UNION(UNION中的第二个或后面的SELECT语句)
4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
5. UNION RESULT(UNION的结果)
6. SUBQUERY(子查询中的第一个SELECT)
7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
8. DERIVED(派生表的SELECT, FROM子句的子查询)
9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

partions

type

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

1. ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

2. index: Full Index Scan，index与ALL区别为index类型只遍历索引树

3. range:只检索给定范围的行，使用一个索引来选择行

4. ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

5. eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

6. const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

7. NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

key

key列显示MySQL实际决定使用的键（索引）

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

*表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）*

不损失精确性的情况下，长度越短越好

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

filtered

Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

1. Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
2. Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
3. Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
4. Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
5. Impossible where：这个值强调了where语句会导致没有符合条件的行。
6. Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

开窗函数【OVER(PARTITION BY)函数介绍】

第一大类：聚合开窗函数====》聚合函数(列) OVER (选项)，这里的选项可以是PARTITION BY子句，但不可是ORDER BY子句

第二大类：排序开窗函数====》排序函数(列) OVER(选项)，这里的选项可以是ORDER BY子句，也可以是　OVER（PARTITION BY子句　ORDER BY子句），但不可以是PARTITION BY子句

a. 排序情况

1. rank() 学生成绩排名（1,2,2,4,4,6）
2. dense_rank() (1,2,2,3,4,4,5)
3. row_rank() （1,2,3,4,5,……）排序

获取日期的函数

1. year()年份,month()月

计算日期的函数

SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

优化插入记录速度

禁用索引

用户画像

大数定律与中心极限定理

大数定律

$$\frac{1}{n}\sum _{i=0}^{\infty }{x}_i=u$$

中心极限定理

## 匹配样本

A/B测试

首先，在试验过程中存在2个假设，这两个假设的关系我们需要先搞清楚。

原假设：我们希望通过试验结果推翻的假设

备择假设：我们希望通过试验结果验证的假设

在A/B测试过程中，因为我们试验的目的是通过反证法证明测试版本和对照版本有明显的不同（提升），所以在这个场景中，原假设就是原始版本和试验版本无差异，而备择假设就是这两个版本存在差异；这也很好理解，因为我们肯定不希望做了半天测试，得到的结果是两个版本一点差异都没有吧？

显著性水平p < a 拒绝原假设 ，

双比例检验

计算检验以比较两个总体的成功比例（p1 和 p2）。它将每个样本中的成功计数（x1 和 x2）和每个样本中的观察计数（n1 和 n2）作为输入。

双样本 T 检验

Z 检验和 T 检验的区别

• Z 检验用于大样本(n > 30)，或总体方差已知；
• T 检验用于小样本(n < 30)，且总体方差未知时，适用性优于 Z 检验，而在大样本时，Z 检验和 T 检验的结论趋同。
• 注：Z 分布与 T 分布，是两个分布，概率函数公式的存在差异，T 分布较 Z 分布多了一个自由度的变量，惩罚小样本，增加其拒绝 H0 的难度，因而小样本采用 T 检验，优于 Z 检验。
  

广告中的 CPI、CPA、CPM、CPC 盈利模式各自的特点是

1. CPL(Cost Per Leads)：以搜集潜在客户名单多少来收费
   即每次通过特定链接，注册成功后付费的一个常见广告模式。这是我们通常称谓的引导注册，比如很久之前很火爆的“亚洲交友联盟”。

2. CPC(Cost Per Click；Cost Per Thousand Click-Through) ：每次点击的费用
   以每点击一次计费。这样的方法加上点击率限制可以加强作弊的难度，而且是宣传网站站点的最优方式。目前Google Adsense就采用此方式。

3. CPM(Cost Per Mille，或者Cost Per Thousand；Cost Per Impressions) ：每千次展示费用。广告条每显示1000次展示的费用
   网上广告收费最科学的办法是按照有多少人看到你的广告来收费。按访问人次收费已经成为网络广告的惯例。很类似电视广告，根据收视率，以及广告看的时长或者是广告自己的时间长度来给广告定价，但比电视广告要可控的多。每CPM的收费究竟是多少，要根据以主页的热门程度(即浏览人数)划分价格等级，采取固定费率。国际惯例是每CPM收费从5美元至200美元不等。不过国内这类广告并不多见，因为各种作弊手段的层出不穷，往往展示次数巨大，但访问者实际并没有看到或者看到很少，令广告主白白支出广告费。

4. CPS(Cost Per Sales)：以实际销售产品数量来换算广告刊登金额
   即根据每个订单/每次交易来收费的方式。用户每成功达成一笔交易，网站主可获得佣金。比如现在比较火爆的淘宝客就是这种方式，应该说对于国内来说，这也是最靠谱的网络营销模式了。

5. CPA(Cost Per Action) ：每行动成本
   CPA计价方式是指按广告投放实际效果，即按回应的有效问卷或定单来计费，而不限广告投放量。CPA的计价方式对于网站而言有一定的风险，但若广告投放成功，其收益也比CPM的计价方式要大得多。广告主为规避广告费用风险，只有当网络用户点击旗帜广告，链接广告主网页后，才按点击次数付给广告站点费用。

6. CPR(Cost Per Response)： 每回应成本
   以浏览者的每一个回应计费。这种广告计费充分体现了网络广告“及时反应、直接互动、准确记录”的特点，但是，这个显然是属于辅助销售的广告模式，对于那些实际只要亮出名字就已经有一半满足的品牌广告要求，大概所有的网站都会给予拒绝，因为得到广告费的机会比CPC还要渺茫。

7. CPP(Cost Per Purchase) ：购买成本
   无论是CPA还是CPP，广告主都要求发生目标消费者的“点击”，甚至进一步形成购买，才予付费：CPM则只要求发生“目击”(或称“展示”、“印象”)，就产生广告付费。无论是CPA还是CPP，广告主都要求发生目标消费者的“点击”，甚至进一步形成购买，才予付费：CPM则只要求发生“目击”(或称“展示”、“印象”)，就产生广告付费。这些网络广告营销形式很多，但并不是所有的形式都合适，根据网站的流量，内容定位来选择一个最合适的方式才能让网络营销利益最大化，目前国内赚的很辛苦但也赚的很可观的是CPS类的淘宝客，另外为了保证广告主的利益，对于流量大的网站、论坛等CPC也是不错的选择。

8. CPI(Cost Per Install) : 按安装付费
   比如以手机app为例，每一次安装，广告商就要付钱，而且只要这app一直装着，广告商也只付这一次钱，并且只管你装不装不管你看不看。CPI算是一种比较有效率的收费方式。

9. PPC广告
   根据点击广告或者电子邮件信息的用户数量来付费的一种网络广告定价模式。

10. PPL广告
    根据每次通过网络广告产生的引导付费的定价模式。

11. PPS广告
    根据网络广告所产生的直接销售数量而付费的一种定价模式。

AARRR

.png)]

流量分析

背景

• 渠道分析

• 渠道分析——从哪来；

• 转化分析——经过什么过程；

• 价值分析——产生什么价值；

• 波动分析——日常的监控分析。

渠道的关键指标及分析方法

• 第三是渠道的关键指标以及分析方法。
• 关键指标指前期看有效用户数和次留，中期看次日、7 日、30 日留存，后期看 ROI（ROI=［（收入－成本）／投入］＊100%）。
• 其中要注意，由于渠道都是收费的，所以有效用户数会有刷量的嫌疑，所以除了看直接量级，还要看有主动行为的用户数，比如上节课里面的停留大于 3 秒的用户数。所有渠道最终的目的还是商业变现，所以一定要计算每个渠道的 ROI，及时把 ROI 小于 1 的渠道砍掉。
• 分析方法包括结构分析、趋势分析、对比分析、作弊分析。
• 结构分析：先按照一级渠道来拆解，再按照二级渠道来拆解。
• 趋势分析：需看每个渠道的变化趋势，包括量级和留存。
• 对比分析：不同渠道间的趋势对比。
• 作弊分析：包含用户行为分析和机器学习，这块可以用 Python 来完成。

问题诊断：定位异常值的简便方法

定位异常值的原因

1. 宏观上的问题：大环境的政策问题、其它平台的数据是否异常
2. 某个特殊时间节点重大节日的异常
3. 版本发布导致数据异常
4. 运营活动突发事件
5. 当以上问题与细分用户群体与其它常见维度时，根据业务属性拆分或者漏斗模型的转化拆分对比
6. 根据用户行为归因的数据异常，当其它基本维度的数据的变化趋势都相近时，
   1. 从非基本维度分析如：用户时怎么打开app成为活跃用户的，打开的渠道，哪个渠道发生了异常
   2. 用户使用的需求，产品的需求是否出现了不满足用户的情况
7. 作弊问题出现异常数据

综合评估法(条件熵)

数据预处理

box-cox转化

from scipy import stats

# 接上一个例子，假设biz_df包含Yelp商家点评数据。

# Box-Cox变换假定输入数据都是正的。

# 检查数据的最小值以确定满足假定。

 biz_df['review_count'].min()

# 设置输入参数*λ*为0，使用对数变换（没有固定长度的位移）。

 rc_log = stats.boxcox(biz_df['review_count'], lmbda=0)

# 默认情况下，SciPy在实现Box-Cox转换时，会找出使得输出最接近于正态分布的*λ*参数。

rc_bc, bc_params = stats.boxcox(biz_df['review_count'])

bc_params

-0.4106510862321085

1. 是一种统计将非正态因变量转为正态的技术

样本不平衡

1. 增加少类样本的数据与其它类型的样本数量到达1:1的比例
2. 随机在多类样本的数据中做随机抽样也达到1：1的比例
3. 进行算法的合成生成新的样本，常见的算法k-mean,smote

模型评估

分类评估

• 二分类

  • 正确率

  • $$正确率=\frac{正确数量}{总数量}$$

  • 样本不平衡的评估

    • ​ 混淆矩阵

    • ​ 正确预测的正例=TP

    • ​ 错误预测的反例=FN

    • ​ 错误预测的正例=FP

    • ​ 正确预测的反例 =TN

      实际\预测	+	-
      +	TP	FN
      -	FP	TN

$$\begin{gathered} 真正例:TPR=\frac{TP}{(TP+FN)} \\ 假正例:TPR=\frac{FP}{(FP+TN)} \end{gathered}$$
​
​ - 对应不平衡的数据追求的是TP,TN为1

• roc曲线，曲线不同阀值下的点所构成的曲线

  • 评估模型优劣

    面积为 1 时模型最优，y=x为p=0.5的预测函数线段

非分类评估

关联分析

the 10 top 10 algorithms in data mining

10.cart

7.naive bayes

7.knn

7.adaboost

6.pagerank

5.em

4.apriori

3.svm

2.k-means

1.c4.5

xgb

#%% split training set to validation set 

data_tr  = xgb.DMatrix(Xtrain, label=Ztrain)

data_cv  = xgb.DMatrix(Xval   , label=Zval)

evallist = [(data_tr, 'train'), (data_cv, 'valid')]

parms = {'max_depth':8, #maximum depth of a tree
         'objective':'reg:squarederror',
         'eta'      :0.3,
         'subsample':0.8,#SGD will use this percentage of data
         'lambda '  :4, #L2 regularization term,>1 more conservative 
         'colsample_bytree ':0.9,
         'colsample_bylevel':1,
         'min_child_weight': 10}

modelx = xgb.train(parms, data_tr, num_boost_round=200, evals = evallist,
                  early_stopping_rounds=30, maximize=False, 
                  verbose_eval=10)

print('score = %1.5f, n_boost_round =%d.'%(modelx.best_score,modelx.best_iteration))

pred_xgb = modelx.predict(xgb.DMatrix(test))

fig =  plt.figure(figsize = (15,15))

axes = fig.add_subplot(111)

xgb.plot_importance(modelx,ax = axes,height = 0.5)

plt.show();plt.close()
weight': 10}

modelx = xgb.train(parms, data_tr, num_boost_round=200, evals = evallist,
                  early_stopping_rounds=30, maximize=False, 
                  verbose_eval=10)

print('score = %1.5f, n_boost_round =%d.'%(modelx.best_score,modelx.best_iteration))

pred_xgb = modelx.predict(xgb.DMatrix(test))

fig =  plt.figure(figsize = (15,15))

axes = fig.add_subplot(111)

xgb.plot_importance(modelx,ax = axes,height = 0.5)

plt.show();plt.close()