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检验。
在样本数量比较大情况下,采用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.
指标拆解
按照链路拆解 每一个步骤的转化率