sql学习笔记
SQL
1.rank（） ，dense_rank(), row_number()
https://blog.csdn.net/huangyinzhao/article/details/80507967
ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列
RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位，名次之间没有间隔。
select cookieid,createtime,pv,
rank() over(partition by cookied order by pv desc) as rn1,
dense_rank() over(partition by cookied order by pv desc) as rn2,
row_number() over(partition by cookied order by pv desc) as rn3
from didi每个组的top n
select a.*
from
(select cookieid,createtime,pv,
row_number() over(partition by cookied order by pv desc) as rn3
from didi) a
where rn3<=10;
https://blog.csdn.net/wcc27857285/article/details/86439313
2.左连接、右连接、内连接、全连接
外连接（out join）
外连接分为外左连接（left outer join/left join ）和外右连接（right outer join/right join ）。
左连接，去左边的表的全部，右边的表按条件，符合的显示，不符合的为null。
内连接（inner join/join）
也称为等值连接，返回交集。
https://www.w3school.com.cn/sql/sql_join_inner.asp
交叉连接（cross join）
交叉连接，返回左表中的所有行，左表中的每一行和由表中的所有行组合。交叉连接也叫做笛卡尔积。
笛卡尔积：在数学中，两个集合X和Y的笛卡尔积，又称为直积，表示为XxY。
举例：
现在，我们有两个集合A和B。
A = {0,1} B = {2,3,4}
集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：
A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；
B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；
以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。
从以上的数据分析我们可以得出以下两点结论：
1，两个集合相乘，不满***换率，既 A×B ≠ B×A;
2，A集合和B集合相乘，包含了集合A中元素和集合B中元素相结合的所有的可能性。既两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数;
交叉连接有两种，隐式和显式。
隐式：
select o.id,o.order,c.id,c.name
from order o,customer c
where o.id=1;
显式：
select o.id,o.order,c.id,c.name
from order o cross join customer c
where o.id=1
全连接（full join）
全连接是在结果中除了显示满足连接的条件的行外，还显示了join两侧表中所有满足检索条件的行
3.union all
追加查询
4.sql的优化方法
- 避免全表扫描，考虑在 where 和order by
- 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
- 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
- in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
5.时间加减写法
https://blog.csdn.net/qq_35958094/article/details/80460644
- 1.日期比较函数: datediff语法:
datediff(string enddate,string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
举例：
hive> select datediff('2016-12-30','2016-12-29');
- 2.日期增加函数: date_add语法:
date_add(string startdate, intdays)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
举例：
hive>select date_add('2016-12-29',10);
2017-01-08
- 3.日期减少函数: date_sub语法:
date_sub (string startdate,int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
举例：
hive>select date_sub('2016-12-29',10);
2016-12-19
- 4.查询近30天的数据
select * from table where datediff(current_timestamp,create_time)<=30；
create_time 为table里的字段，current_timestamp 返回当前时间 2018-06-01 11:00:00
- 5.date_format(date_add(current_date, -1), 'yyyyMMdd') --把2021-01-01变成格式20210101
- 6.unix_timestamp() 转换为时间戳
select unix_timestamp('2011-12-07 13:01:03') as a
-
MySQL 中函数 timestampdiff()
TIMESTAMPDIFF(
DAY,
tablea.date,
tableb.`pay_time`
) <= 30
6.ifnull()
ifnull(a,b) >> a
ifnull(null,a) >> a
nvl()
7.COALESCE()函数
定义：返回列表中第一个非null表达式的值。如果所有表达式求值为null，则返回null
COALESCE()函数有两种用法：
1.COALESCE ( expression1, expression2 );
2.COALESCE ( expression1, expression2, ... expression-n );
8.lag()
9.lead()
10.id连续，考虑窗口函数，作差相等。
表：Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加
编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
返回按 visit_date 升序排列的结果表。
查询结果格式如下所示。
Stadium table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
Result table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。
答：在大数据方向下，遇到这种球连续的问题第一时间就要想到开窗球差值。
所以
首先过滤出people>100的字段
开窗，用id减去rank排名，并根据id进行排序。 若是连续的那么，差值一定是相同的
where过滤出条数>=3的完成解题
with t1 as (
select
id,
visit_date,
people,
#求出差值，因为id一定不会相同，所以使用最熟悉的rank就好
id-rank() over(order by id) rk
from stadium
where people >= 100
)
select
id,
visit_date,
people
from t1
#where条件过滤出条数大于3的
where rk in (
select rk from t1 group by rk having count(1) >= 3);
11.update
给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。
注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。
例如：
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
运行你所编写的更新语句之后，将会得到以下表:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
update salary
set sex=IF(sex='f','m','f')12.交换数据
作者：houzidata
链接：https://leetcode-cn.com/problems/exchange-seats/solution/tu-jie-mian-shi-ti-ru-he-jiao-huan-shu-ju-by-houzi/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表，则输出结果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。
SELECT
(CASE
WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
# id为奇数而且总数不是奇数，那么这个人的id+1
WHEN MOD(id, 2) != 0 AND counts = id THEN id
# id为奇数而且总数是奇数，那么id不变
ELSE id - 1
#id为偶数，那么id-1
END) AS id,
student
FROM
seat,
(SELECT
COUNT(*) AS counts #总共有多少人
FROM
seat) AS seat_counts
ORDER BY id ASC;
13. where 1=2
新建空白数据表，要求表结构、字段数据类型和table2完全一致，
select * into table1
from table2
where 1=2
统计
1.
当人们尝试探究两种变量（比如新生录取率与性别）是否具有相关性的时候，会分别对之进行分组研究。然而，在分组比较中都占优势的一方，在总评中有时反而是失势的一方。该现象于20世纪初就有人讨论，但一直到1951年，E.H.辛普森在他发表的论文中阐述此一现象后，该现象才算正式被描述解释。后来就以他的名字命名此悖论，即辛普森悖论。
个别录取率男>女，总录取率男<女。
2.
异常点检验的方法？
- 对变量进行排序，对照最大值和最小值、全距等统计量看出数据的离群情况。
- 描述性统计。
- 散点图。看出离群值。
- 箱体图。展示四分位数。把上下触须作为数据分布的边界，任何高于上触须和低于下触须的值视为异常值。
- zscore。假定服从高斯分布。归一化。
- 均方差。如果数据近似正态分布，99.7%的数据位于三个标准差范围内。在三个标准差范围之外的数据，视为异常值。
3.
你建了一个多元回归模型，发现r2并不高，为了改进r2，你去掉截距项，r2从0.3变成0.8，是否可能，怎样才能达到这个效果？
模型设定错误，应该不含截距项。
4.
给一个数据集，你已经建立好了分类模型，取得了99%的有效性，用这些指标衡量模型是否够好足够吗？如果不够，还需要看哪些指标呢？
5.abtest 流程和步骤
A/B-test是为同一个目标制定两个方案，在同一时间维度，分别让组成成分相同（相似）的用户群组随机的使用一个方案，收集各群组的用户体验数据和业务数据，最后根据显著性检验分析评估出最好版本正式采用。
- 1.abtest中可以样本进行二一对照的试验吗？
(可以，前提做一个方差齐性检验)
- 2.abtest中中实验组和对照组的均值显著性检验用什么方式检验？这两个组的数据呈现什么样的分布
Z检验，正态分布
- 3.给我讲讲abtest的流程和步骤
典型A/B实验的步骤包含确认实验目标、设计A/B实验方案、上线实验与过程监控、结果复盘。
1-确定实验目标。沉默用户找回，验证不同的召回发券策略的效率。找到效率最高的策略。
2-设计实验。明确目标用户、试验周期、最小样本量、用户分组、分流比例、分组策略。
目标用户：过去30-180天未下单的老客户。
实验周期：测试一周。
最小样本量：输入原始的召回率、策略优化后的召回率、显著性水平，可以得出样本量。
用户分组及策略：
实验组1 30% 发放满20-5的优惠券，并通过精准营销短信触达
实验组2 30% 发放满30-6的优惠券，并通过精准营销短信触达
实验组3 30% 发放满40-10的优惠券，并通过精准营销短信触达
对照组 10% 不进行任何策略
3-上线实验与过程监控
检查的问题：
空白组是否真的空白，有无空白组用户领导优惠券，如果有排查分流系统问题
1个用户是否只属于1个组，有无存在多个组的情况
分流是否和预定的分流比例一致，有误差要寻找原因
实验样本是否是预先设定的目标实验样本，判断试验是否进行了用户筛选过滤，比如是否存在近30天内有交易的活跃用户领导优惠券。
4-结果复盘之roi评估
通过实验组1、2、3分别和对照组最招，得出3组策略的效率。roi的分子是投入的总资源成本，产出是用户的原价交易额、单量、利润等。此处用原价交易额作为产出。
考虑门槛和面额。
无法衡量就无法优化…… abtest 系统是进行变量控制和优化方向选取的工具，循环：衡量-发现-迭代-验证。 线上分流实验是进行推荐算法优化的必由之路
原假设，又叫零假设、无假设（Null Hypothesis），代表我们希望通过试验结果推翻的假设。
备择假设（Alternative Hypothesis），代表我们希望通过试验结果验证的假设。
https://www.douban.com/note/785734844/
https://zhuanlan.zhihu.com/p/75762862
https://www.sohu.com/a/437693769_165070
https://blog.csdn.net/qq_48314528/article/details/110633167
在A/B实验中，主要是对样本均值进行检验，所以用t检验和Z检验。
t检验：t检验常用于总体正态分布、总体方差未知或独立小样本平均数的显著性检验、平均数差异显著性检验。
Z检验：Z检验常用于总体正态分布、方差已知或独立大样本的平均数的显著性和差异的显著性检验。
- T 检验，亦称 student t 检验 ( Student’s t test ) ，主要用于样本含量较小 ( 例如 n<30 ) ，总体标准差 σ 未知的正态分布数据。T 检验是用 t 分布理论来推论差异发生的概率，从而比较两个平均数的差异是否显著。
适用条件： 已知一个总体均数；可得到一个样本均数及该样本标准误； 样本来自正态或近似正态总体。
- Z 检验是一般用于大样本 ( 即样本容量大于 30 ) 平均值差异性检验的方法。它是用标准正态分布的理论来推断差异发生的概率，从而比较两个平均数的差异是否显著。 当已知标准差时，验证一组数的均值是否与某一期望值相等时，用 Z 检验。
Z 检验的步骤 适用条件：已知一个总体均数；可得到一个样本均数及该样本标准误； 样本来自正态或近似正态总体。
- p-value ，就是当原假设为真时，所得到的样本观察结果或更极端结果出现的概率。如果 p-value 很小，说明原假设情况的发生的概率很小，而如果出现了，根据小概率原理，我们就有理由拒绝原假设，p-value 越小，我们拒绝原假设的理由越充分。p-value 代表的是不接受原假设的最小的显著性水平，可以与选定的显著性水平直接比较。例如取 5% 的显著性水平，如果 p-value 大于 5% ，就接受原假设，否则不接受原假设。这样不用计算 t 值，不用查表。p-value 能直接跟显著性水平比较；而 t 值想要跟显著性水平比较，就得换算成 p-value ，或者将显著性水平换算成 t 值。在相同自由度下，查 t 表所得 t 统计量值越大，其尾端概率 p 越小，两者是此消彼长的关系，但不是直线型负相关。
6.贝叶斯公式
7.决策树和随机森林的优缺点
随机森林用于重要特征变量的筛选
https://blog.csdn.net/xiezhen_zheng/article/details/82011908
https://zhuanlan.zhihu.com/p/257139517
袋外数据误差：
https://www.jianshu.com/p/8985bc8e4a12
8.kmeans聚类算法的优缺点，如何确定kmeans聚类的类别数
9.rfm模型
衡量客户价值和客户创利能力。
最近一次消费 recent
消费频率 frequency
消费金额 money
10.aarrr模型
对应用户生命周期的五个环节。
外文名
AARRR
Acquisition用户获取
Retention用户留存
Activation用户激活
Revenue获得收益
Referral推荐传播
11.星球模型和雪花模型
12.建模中遇到数据缺失怎么办？
删除
插补填充
当做属性值
13.数据倾斜是什么，怎么处理
14.NMF聚类方法
https://zhuanlan.zhihu.com/p/22043930
15.离散系数
变异系数
16.做预测
二分类
对于维度为m+1特征为x样本的二分类问题，有负类记为0，正类记为1。
找到一个h（x），使得
0≤h（x）≤1
分类准则如下：
决策树
决策树基本上就是把我们以前的经验总结出来。会经历两个阶段：构造和剪枝。
构造就是生成一棵完整的决策树。简单来说，构造的过程就是选择什么属性作为节点的过程，
剪枝就是给决策树瘦身，这一步想实现的目标就是，不需要太多的判断，同样可以得到不错的结果。之所以这么做，是为了防止“过拟合”（Overfitting）现象的发生。
逻辑回归
logistic回归又称logistic回归分析，是一种广义的线性回归分析模型，常用于数据挖掘，疾病自动诊断，经济预测等领域。例如，探讨引发疾病的危险因素，并根据危险因素预测疾病发生的概率等。以胃癌病情分析为例，选择两组人群，一组是胃癌组，一组是非胃癌组，两组人群必定具有不同的体征与生活方式等。因此因变量就为是否胃癌，值为“是”或“否”，自变量就可以包括很多了，如年龄、性别、饮食习惯、幽门螺杆菌感染等。自变量既可以是连续的，也可以是分类的。然后通过logistic回归分析，可以得到自变量的权重，从而可以大致了解到底哪些因素是胃癌的危险因素。同时根据该权值可以根据危险因素预测一个人患癌症的可能性。
logit、probit模型
业务
1.费米问题
2.
指标拆解
按照链路拆解 每一个步骤的转化率
博世公司福利 94人发布
