本文转载自公众号：小素数，作者：mashu。
对于数据分析师而言，把数取对是一项最基本的能力，因为所有的数据加工、分析工作都依赖于这一步。在面试中，接受面试官对SQL的考察也是不可少的一道坎。
对于产品经理和运营而言，具备基本的取数能力则能够节省很多繁琐的沟通，同时对解读数据看板、合理设计项目的KPI也大有裨益。工作中偶尔会有运营同学问有没有SQL的学习链接，其实SQL学起来并不难，这个系列将用3天时间让你上手SQL实操。
备注：不同的数据库环境，函数及其使用方法不太一致，不过基本也大同小异。
在企业的数据仓库中，存在着许多从生产表生成的抽表，一般按照天为分区每日刷新数据，分析师在抽表的基础上根据运营需求进行加工提炼出基于某个主题的加工表用于分析。数仓的抽表可分为两种类型：全量表和增量表，全量表是某个日期的业务状态切片，增量表记录某个日期新增加的业务记录。举例而言，用户表是全量表，订单表是增量表，如果以天为分区，用户表中，最新的分区记录着到目前为止全部的用户，而订单表最新的分区记录仅那一日的订单。理解这一点对后面的取数工作非常重要。
网络上各种连接方法都能搜索到，但我个人觉得少即是多，熟练运用以下三种就足够了：
左连接：select * from A left join B on A.uid=B.uid
表A中所有记录行都在结果中，而表B只有uid跟A表匹配的记录行才出现，不能与A.uid匹配的记录行为空。
内连接：select * from A join B on A.uid=B.uid
只有两个表的uid相匹配的记录行才出现在结果中。
全连接：select * from A outer join B on A.uid=B.uid
表A和表B在uid匹配的记录行合并数据，不能匹配记录行左右各自保留数据，另一方的记录为空，所以结果里面包含了两个表所有的数据。如果加一条where A.uid is null or B.uid is null，那么就把俩个表uid匹配的行删掉，剩下的是仅其中一方有的。
SQL提供了很多方便易用的内置函数，来处理数据格式和进行数据加工计算，我们可以根据数据案例进行讲解。
假设有原始数据一个订单增量表ordertb，字段：
dt,user_id,order_id,seller_id,cat_id,payment
接下来我们将根据这个表结合使用场景来研究SQL的基本和进阶用法。五角星⭐️的个数代表难度级别。
聚合函数比较简单，因此不用详细介绍。
聚合函数常与case...when...then...else...end在一起使用，能够对限定条件的记录进行计数求和。
avg ⭐️
max ⭐️
median ⭐️
percentile ⭐️⭐️
min ⭐️
count ⭐️
sum ⭐️
stddev ⭐️
stddev_samp ⭐️
场景：'2019-04-10'日订单的均实付客单价、最大/最小实付客单价、实付客单价中位数、实付客单价80%分位数、总下单人数、总实收入、实付客单价总体标准差、实付客单价样本标准差
select
avg(payment),
max(payment),
min(payment),
median(payment),
percentile(payment,0.8),
count(distinct user_id),
sum(payment),
stddev(payment),
stddev_samp(payment)
from ordertb
where dt='2019-04-10'
窗口函数属于比较高阶的用法，简单来讲，窗口函数就是对某个字段进行分组后在组内进行聚合，它出现在select后面from前面，Group则是对全局数据进行分组聚合，出现在where后面。
avg ⭐️⭐️
count ⭐️⭐️
max ⭐️⭐️
median ⭐️⭐️
min ⭐️⭐️
sum ⭐️⭐️
stddev ⭐️⭐️
场景：'2019-04-10'日每个订单的实付客单，以及对应的卖家的均实付客单价，最大/最小实付客单价、实付客单价中位数、总下单人数、总实收入、实付客单标准差（比较一下这种写法跟用group by哪里不同？）
select
order_id,
payment,
seller_id,
avg(payment) over(partition by seller_id),
max(payment) over(partition by seller_id),
min(payment) over(partition by seller_id),
median(payment) over(partition by seller_id),
count(distinct user_id) over(partition by seller_id),
sum(payment) over(partition by seller_id),
stddev(payment) over(partition by seller_id)
from ordertb
where dt='2019-04-10'
row_number ⭐️⭐️⭐️
用途：分组在组内对记录进行排序，用row_number的话，如果第10个跟第11个相等的gmv，那么第11个则不会出现于结果中
备注：窗口函数不能出现在同一个Select查询的where子句下，这时候使用qualify条件语句筛选，而不是having。某些数据库不支持qualify用法，则需要增加一层嵌套进行筛选。
场景1：'2019-04-01'至昨日'2019-04-10'每个品类下卖家的总实收入排名前10
select
cat_id,
seller_id,
row_number() over(partition by cat_id order by gmv desc) as rk
from
(select
cat_id,
seller_id,
sum(payment) as gmv
from ordertb t1
where dt between '2019-04-01' and '2019-04-10'
group by cat_id,
seller_id) t
qualify rk<=10
场景2：2019年3月连续5天下单的人数
备注：关键在于怎么识别连续，只要把订单日减按用户ID分组的订单排序，如果一个用户连续下单的话，相减得到的日期相等
with basedata as
(select
distinct dt,user_id
from ordertb
where dt between '20190401' and '20190410'
),
orderdt_minus_rownum as
(
select
user_id,
dt,
row_number() over(partition by user_id order by order_dt asc) as rk,
dateadd(to_date(dt,'yyyy-mm-dd'),-(row_number() over(partition by user_id order by order_dt asc)),'dd') as diff
from basedata
),
conti_days as
(
select
user_id,
count(1) as d
from orderdt_minus_rownum
group by user_id,
diff
)
select * from conti_days
where d>=5
dense_rank ⭐️⭐️⭐️
用途：分组在组内对记录进行排序，用dense_rank的话，如果第10个跟第11个相等的gmv，那么第10、11个都是同样的排名，都出现于结果中rank ⭐️⭐️⭐️
用途：分组在组内对记录进行排序，用rank的话，如果第10个跟第11个相等的gmv，那么第10、11个都是同样的排名，都出现于结果中，并且第11名置空，后续记录从第12名开始
row_number、dense_rank、rank这三个窗口函数在使用时要结合业务使用场景决定需要用那个，避免搞混淆。
lag & lead ⭐️⭐️⭐️⭐️
用途：分组在组内找往过去/未来数第N个数据
场景：每个卖家的订单数量周同比
select
dt,
seller_id,
ordernum,
lag(ordernum,7) over(partition by seller_id order by dt) as ordernum_t7,
lag(ordernum,7) over(partition by seller_id order by dt)/ordernum as rate_t7
(select
dt,
seller_id,
count(order_id) as ordernum
from ordertb
where dt between '2019-04-01' and '2019-04-10'
group by
dt,
seller_id)
ntile ⭐️⭐️⭐️⭐️
用途：分组在组内对某列进行分桶
percent_rank ⭐️⭐️⭐️⭐️
用途：分组在组内对某列找某条记录所处的相对排名
cume_dist ⭐️⭐️⭐️⭐️
用途：分组在组内对每列找某条记录所处的累计百分比
场景：对每个品类下的卖家以GMV均分成4桶，并找到每个卖家组内排名，和GMV累计百分比
select
cat_id,
seller_id,
gmv,
ntile(4) over(partition by cat_id order by gmv desc) as buck,
percent_rank() over(partition by cat_id order by gmv desc) as prank,
cume_dist() over(partition by cat_id order by gmv desc) as cdist
from
(select
cat_id,
seller_id,
sum(payment) as gmv
from ordertb
where dt between '2019-04-01' and '2019-04-10'
group by cat_id,
seller_id
dateadd ⭐️⭐️
用途：日期 时间间隔
dateadd(date, 45,'dd') # date往后推45天
dateadd(date, -45,'dd') # date往前推45天
datediff⭐️⭐️
用途：第一个日期减去第二个日期得到的天数，可正可负
datediff('2008-11-30','2008-11-29','dd')
datepart⭐️⭐️
用途：日期 时间间隔
datepart('2018-06-08 01:10:00', 'yyyy')
输出：2018-06-08
datetrunc ⭐️⭐️
用途：返回日期被截取指定时间单位后的日期值，常用于按时间单位分组
datetrunc('2018-06-08 01:10:00', 'month')
输出：2018-06-01 00:00:00
to_date⭐️⭐️
用途：将不规则的日期字符串转化日期类型
备注：这个函数对日期格式要求比较严，输入的日期其月份和日期必须两位数字
to_date('2018年09月02日','yyyy年mm月dd日')
输出：2018-09-02 00:00:00
to_char⭐️⭐️
用途：将日期类型date按照format指定的格式转成字符串
to_char('2018-07-18 00:00:00', 'yyyymmdd')
输出：20080718
weekday ⭐️⭐️
用途：返回日期所在当前周的第几天。周一作为一周的第一天，返回值为0,周日返回6。输入必须是日期类型。
select weekday(to_date('20190421','yyyymmdd'))
输出：6
weekofyear ⭐️⭐️
用途：返回日期所在周为当年的第几周
weekofyear(to_date('20190421','yyyymmdd'))
输出：16
round ⭐️
用途：四舍五入
floor ⭐️
用途：向下取整
trunc ⭐️
用途：截取到指定的小数位数
round(118.815,2),
floor(118.815),
trunc(118.815, 1),
trunc(118.815, 2),
trunc(118.815, -2)
输出：
118.82
118
118.80000000000001
118.81
100.0
concat & concat_ws⭐️⭐️
用途：合并字符串，前者不接受空值，后者接受
select CONCAT('11','22','33')
>112233
select CONCAT('11','22',NULL)
>NULL
select CONCAT_WS(',','11','22','33')
>11,22,33
select CONCAT_WS(',','11','22',NULL)
>11,22
get_json_object⭐️⭐️
用途：从json格式中摘取变量值
场景：从{'orderId':'4','PayInfo':'[{\'pay_id\':1,\'money\':1650}]'}中获取orderId和money的值。
select
get_json_object('{'orderId':4,'PayInfo':{'pay_id':1,'money':1650}}','$.orderId'),
get_json_object('{'orderId':4,'PayInfo':{'pay_id':1,'money':1650}}','$.PayInfo.money')
输出：
4
1650
md5 ⭐️
用途：计算输入字符串value的md5值，在需要对手机号码加密时用到
select md5('13X13697810')
输出：faf3cae4ba50be8b85ae80203f6dba96
regexp_replace⭐️⭐️
用途：将字符串匹配的子串更换为指定字符串，位置设为0时表示替换所有匹配子串
regexp_replace('ab12cd123', '[0-9] ', '\\1-', 0)#\\1对应第1个括号中的匹配
输出：ab-cd-regexp_extract ⭐️⭐️
用途：将字符串匹配的子串按照指定位置提取出来，位置设为0时返回满足pattern的全部字符串
select
regexp_extract('www-baidu-com', 'www.(.*?)(.com)', 1),
regexp_extract('www-baidu-com', 'www.(.*?)(.com)', 2),
regexp_extract('www-baidu-com', 'www.(.*?)(.com)', 0)
输出：
baidu
-com
www-baidu-com
split_part ⭐️
用途：按照分隔符对字符串进行拆分，选取指定开始和结束位置的子串
select
split_part('www-baidu-com', '-', 2),
split_part('www-baidu-com', '-', 1, 2)
输出：
baidu
www-baidu
substr ⭐️
用途：按照指定开始位置和长度截取字符串，字符的位置从1开始数
select substr('2019-04-10 10:10:10 ',1,10)
输出：2019-04-10
coalesce⭐️⭐️
用途：在几个变量中取第一个非空的值
select coalesce(null,123,'abc')
输出：123
场景：有2个流量表，分别是一个产品在APP、第三方H5页面的用户访问情况，要求合并流量表得到当天登录的用户ID（我们可以使用union方法，除此之外，还可以使用outer join）
select
coalesce(t1.dt,t2.dt) as dt,
coalesce(t1.user_id,t2.user_id) as user_id
from apppv_tb t1
outer join h5pv_tb t2
on t1.user_id=t2.user_id
and t1.dt=t2.dt
greatest⭐️⭐️
least ⭐️⭐️
用途：若干变量中的最大最小值
select
greatest(1,4,3,9),
least(1,4,3,9)
输出：
9
1
trans_cols⭐️⭐️⭐️
用途：根据关键字将一行的拆成多行
场景：某个表中mobile1和 mobile2字段分别记录用户的主用手机号码和备用手机号码，要求将mobile1和 mobile2放入一个字段，且分行显示
输入：
输入：
user_id mobile1 mobile2
小明 139XXXX0101 138XXXX0111
小王 135XXXX0101 135XXXX0111
select trans_cols(1, user_id, mobile1, mobile2) as (user_id, mobile)
输出：
user_id mobile1
小明 139XXXX0101
小明 138XXXX0111
小王 135XXXX0101
小王 135XXXX0111
trans_array⭐️⭐️⭐️
用途：根据关键字将一行的拆成多行，跟trans_cols不一样的地方是，需要拆成多行的字段只有一个，值之间用分隔符分开。
场景：某个表中mobile字段记录用户的使用过的手机号码，要求每个手机号码分行显示
输入：
user_id mobile recent_calltime
小明 139XXXX0101;138XXXX0111 20190120;20190220
小王 135XXXX0101;135XXXX0111 20180120;20190323
select trans_array(1, ';',user_id, mobile, recent_calltime) as (user_id, mobile, recent_calltime)
输出：
user_id mobile recent_calltime
小明 139XXXX0101 20190120
小明 138XXXX0111 20190220
小王 135XXXX0101 20180120
小王 135XXXX0111 20190323
-The End-
如有谬误烦请不吝指正，感谢！
如果觉得有帮助的话，可以点击“在看”哦
联系客服
