sql学习笔记

sql 专栏收录该内容
13 篇文章 2 订阅

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博客_内连接和外连接的区别

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

统计

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检验。

preview在样本数量比较大情况下,采用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测试算法大揭秘】第五篇:少了它,版本决策将毫无意义 | 人人都是产品经理icon-default.png?t=L892http://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.

指标拆解

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

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

七天笔记本

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值