sql学习笔记

SQL

1.rank（） ，dense_rank(), row_number()

MySQL中rank()、row_number()、dense_rank()排序_Simon的博客-CSDN博客

Hive和SQL的窗口函数_huangyinzhao的博客-CSDN博客_hive sql 窗口函数

ROW_NUMBER() –从1开始，按照顺序，生成分组内记录的序列 1 2 3 4 5 

RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位 1 2 3 3 5 

DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位，名次之间没有间隔。 1 2 3 3 4 

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;

Sql 中内连接、外连接、全连接、交叉连接的区别_Stay Hungry-CSDN博客_内连接和外连接的区别

窗口函数sum

【鲸析】534. 游戏玩法分析 III（考点：窗口累加 vs. 自连接） - 游戏玩法分析 III - 力扣（LeetCode）

sum() over(partition by xx order by xx asc)

2.左连接、右连接、内连接、全连接

外连接（out join）

外连接分为外左连接（left outer join/left join ）和外右连接（right outer join/right join ）。

左连接，去左边的表的全部，右边的表按条件，符合的显示，不符合的为null。

内连接（inner join/join）

也称为等值连接，返回交集。

SQL INNER JOIN 关键字

交叉连接（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.时间加减写法

hivesql中datediff，date_add和date_sub的用法_hello_fancy的博客-CSDN博客

• 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

14.取中位数

1、从小到大排序，计算n

2、找排序和n的关系

        1）正反序排序。rn1>=cnt/2 and rn2>=cnt/2

        2)rnk in (cnt/2, cnt/2+1, (cnt+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），代表我们希望通过试验结果验证的假设。

为什么要做AB test

数据分析|如何做一个ABtest测验 - 知乎

数据分析｜ABtest全流程实验都在这里了！_流量

AB test详解_LitraLIN的博客-CSDN博客_abtest

在A/B实验中，主要是对样本均值进行检验，所以用t检验和Z检验。

在样本数量比较大情况下，采用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.决策树和随机森林的优缺点

随机森林用于重要特征变量的筛选

利用随机森林对特征重要性进行评估_xiezhen_zheng的博客-CSDN博客_随机森林特征重要性

以随机森林为例解释特征重要性 - 知乎

袋外数据误差：

随机森林进行特征重要性度量的详细说明 - 简书

8.kmeans聚类算法的优缺点，如何确定kmeans聚类的类别数

9.rfm模型

衡量客户价值和客户创利能力。

最近一次消费 recent

消费频率 frequency

消费金额 money

10.aarrr模型

对应用户生命周期的五个环节。

外文名

AARRR

Acquisition用户获取

Retention用户留存

Activation用户激活

Revenue获得收益

Referral推荐传播

11.星球模型和雪花模型

12.建模中遇到数据缺失怎么办？

删除

插补填充

当做属性值

13.数据倾斜是什么，怎么处理

14.NMF聚类方法

非负矩阵分解(NMF)简介 - 知乎

15.离散系数

变异系数

16.做预测

二分类

对于维度为m+1特征为x样本的二分类问题，有负类记为0，正类记为1。

找到一个h（x），使得

0≤h（x）≤1

分类准则如下：

决策树

决策树基本上就是把我们以前的经验总结出来。会经历两个阶段：构造和剪枝。

构造就是生成一棵完整的决策树。简单来说，构造的过程就是选择什么属性作为节点的过程，

剪枝就是给决策树瘦身，这一步想实现的目标就是，不需要太多的判断，同样可以得到不错的结果。之所以这么做，是为了防止“过拟合”（Overfitting）现象的发生。

逻辑回归

logistic回归又称logistic回归分析，是一种广义的线性回归分析模型，常用于数据挖掘，疾病自动诊断，经济预测等领域。例如，探讨引发疾病的危险因素，并根据危险因素预测疾病发生的概率等。以胃癌病情分析为例，选择两组人群，一组是胃癌组，一组是非胃癌组，两组人群必定具有不同的体征与生活方式等。因此因变量就为是否胃癌，值为“是”或“否”，自变量就可以包括很多了，如年龄、性别、饮食习惯、幽门螺杆菌感染等。自变量既可以是连续的，也可以是分类的。然后通过logistic回归分析，可以得到自变量的权重，从而可以大致了解到底哪些因素是胃癌的危险因素。同时根据该权值可以根据危险因素预测一个人患癌症的可能性。

logit、probit模型

17、统计功效

【A/B测试算法大揭秘】第五篇：少了它，版本决策将毫无意义 | 人人都是产品经理http://www.woshipm.com/pmd/394039.htmlAB测试里的统计功效（Power）是个啥？ - 知乎我们做AB实验的时候，经常能看到这样一个指标：统计功效（power），或者是它的变体（1-β）。 并且我们通常还会发现，这个指标会影响我们实验的可信度，甚至还会影响我们所需要的实验样本量。一些严谨的科研项目，…https://zhuanlan.zhihu.com/p/149941019

18、置信区间

如何通俗地解释「置信区间」和「置信水平」？ - 知乎想做个过度自信的问卷，涉及量置信区间，可是本人真心不懂啊，有大神帮帮忙吧。https://www.zhihu.com/question/24801731

业务

1.费米问题

经典面试题“费米问题”如何回答？有哪些比较好的案例？ - 知乎在科学中，尤其是在物理和工程教育中，费米问题或费米估算是一个用来做量纲分析，估算和清晰地验证一个假…https://www.zhihu.com/question/21997407

2.

指标拆解

按照链路拆解 每一个步骤的转化率