对于数据分析师而言，把数取对是一项最基本的能力，因为所有的数据加工、分析工作都依赖于这一步。在互联网公司笔试中SQL的考察也是不可少的一道坎。
对于产品经理和运营而言，具备基本的取数能力则能够节省很多繁琐的沟通，同时对解读数据看板、沟通数据需求、合理设计项目的KPI也大有裨益。工作中偶尔会有运营同学问有没有SQL的学习链接，其实SQL学起来并不难，这个系列将用3天时间让你上手SQL实操。
全文目录：
--【1】全量表和增量表
--【2】SQL的语法句式
------【2.1】select...from...where...
------【2.2】group by 和 having
------【2.3】case..when..then..else..end
--【3】SQL的连接方法
------【3.1】...left join...
------【3.2】...inner join...
------【3.3】...full join...
--【4】SQL内置函数
------【4.1】聚合函数
------------【场景1】对卖家当日订单进行聚合统计
------【4.2】窗口函数
------------【场景2】显示每个订单中的卖家当日销售情况
------------【场景3】查询每个品类下总实收入排名前10的卖家
------------【场景4】查询连续2天下单的人
------------【场景5】每个品类订单数量月环比
------------【场景6】对各品类下的卖家按GMV分桶、显示排名和累计百分比
------【4.3】日期函数
------【4.4】数学函数
------【4.5】字符串函数
------【4.6】其他函数
------------【场景7】从不同流量表合并当日登录的用户ID
------------【场景8】多列合并到1列
------------【场景9】1行拆分成多行
备注：不同的数据库环境，函数及其使用方法不太一致，总体上大同小异。
【1】全量表和增量表
在企业的数据仓库中，存在着许多从生产表生成的抽表，这些抽表在物理上存放在不同的分区，分区就好像抽屉一样，我们在使用SQL取数时，必须告诉系统，我们要从哪些“抽屉”中取数据。
一般最常见的是按照天为分区，每日刷新数据，分析师在抽表的基础上根据业务需求进行加工提炼出基于某个主题的加工表应用于分析。数仓的抽表可分为两种类型：全量表和增量表。
全量表是截至某个日期的全量状态拍照，每个分区数据量级只增不减；
增量表记录某个日期新增加的业务记录，每个分区数据量级有增有减。
举例而言，用户表是全量表，订单表是增量表。
如果以天为分区，用户表中，最新的分区记录着到目前为止全部的注册用户信息，而订单表最新的分区仅记录当日的订单。
在我们进行查询之前，理清楚所用到的表是全量表还是增量表十分重要，全量表只需要取最新日期的分区即可。如果把全量表误认为是增量表，一次取过去30天的数据，数据会大量重复，加之全量表本身数据量十分庞大，如果再跟其他表做关联查询，则极大可能把系统资源跑崩掉。——这也是新人常见易犯的错误。
备注：本文中的案例使用MySQL，没有涉及分区的概念
从上篇：SQL心法：MySQL安装使用中，我们已经在MySQLWorkbench中导入数据生成了以下表，dt字段代表业务记录生成的日期（格式为："yyyy-mm-ss"）：
一个用户全量表usertb，字段：
dt,user_id
一个订单增量表ordertb，字段：
dt,user_id,order_id,seller_id,cat_id,payment
一个卖家全量表sellertb，字段:
dt,seller_id,cat_id
一个品类全量表categorytb，字段:
dt,category_id
【2】SQL的语法句式
SQL是不分大小写的，select和SELECT都是一样可用，看个人习惯。SQL语法其实非常直白易懂，这里不说建表和写入表操作，只单纯讲解数据查询技巧。查询的语法结构如下：
select 字段1[，字段2...字段N]
from tb
where 条件1
group by 字段a
order by 字段b
having 条件2
【2.1】select...from...where...
这是最简单的查询语句了，表示从表中查找符合条件的记录行。
比如我们要查表usertb中2015年2月注册的所有用户：
select *
from usertb
where dt like '2015-02%'
【2.2】group by 和 having
我们常对周期内的业务数据进行汇总，这时需要用到分组，比如：统计每周订单数，找出其中订单数在1000+的周：
select weekofyear(dt), count(distinct order_id) as order_cnt
from ordertb
group by weekofyear(dt)
having order_cnt>1000
【2.3】case..when..then..else..end
当我们需要根据条件自定义一个字段时，可以用case when 语句。
比如，取2012年高中低消费等级的订单数。
这里高中低消费等级是我们新定义的：
100以上为高消费，30-100为中消费，30以下为低消费。
那么，按照新定义的消费等级字段进行分组，即可得到结果：
select
case when payment>100 then '高消费'
when payment>30 then '中消费'
else '低消费'
end as pay_level,
count(distinct order_id) as order_cnt
from ordertb
where dt between '2012-01-01' and '2012-12-31'
group by
case when payment>100 then '高消费'
when payment>30 then '中消费'
else '低消费'
end
注意：在case when 语句中，前排的语句优先级大于后排，如果其他语句不变，单把“>100”和“>30”换个位置，试试看会发生什么？——“>100”那行语句不会被执行了，输出如下：
【3】SQL的连接方法
网络上各种连接方法都能搜索到，但我个人觉得少即是多，熟练运用以下三种就足够了：
【3.1】...left join...
select * from A left join B on A.uid=B.uid
表A中所有记录行都在结果中，而表B只有uid跟A表匹配的记录行才出现，不能与A.uid匹配的记录行为空。
比如，查询2015-02-05日注册用户当日在品类id:28下单的记录，结果中，左边表的数据会完整保留，而当日没有下单的用户右边表的取值为null：
select
t1.*,
t2.*
from usertb t1
left join ordertb t2
on t1.user_id=t2.user_id
and t2.dt ='2015-02-05'
and t2.cat_id=28
where t1.dt ='2015-02-05'
【3.2】...inner join...
inner join 中的inner可一省掉不写：
select * from A join B on A.uid=B.uid只有两个表的uid相匹配的记录行才出现在结果中。
上面同一个例子，如果把left join换成inner join，则结果仅会保留两个表中都共同存在的用户的记录：
select
t1.*,
t2.*
from usertb t1
inner join ordertb t2
on t1.user_id=t2.user_id
and t2.dt ='2015-02-05'
and t2.cat_id=28
where t1.dt ='2015-02-05'
【3.3】...full join...
select * from A full join B on A.uid=B.uid
表A和表B在uid匹配的记录行合并数据，不能匹配的记录行左右各自保留数据，另一方的记录为空，所以结果里面包含了两个表所有的数据。如果加一条where A.uid is null or B.uid is null，那么就把两个表uid匹配的行删掉，剩下的是仅其中一方有的。
【4】SQL内置函数
SQL提供了很多方便易用的内置函数，来处理数据格式和进行数据加工计算，接下来我们将结合使用场景来研究SQL的基本和进阶用法。五角星⭐️的个数代表难度级别指数。
聚合函数
聚合函数比较简单，因此不用详细介绍，
avg ⭐️
max ⭐️
min ⭐️
count ⭐️
sum ⭐️
【场景1】对卖家当日订单进行聚合统计
查询2012-09-10日订单的均实付客单价、最大/最小实付客单价、总下单人数、总实收入：
select seller_id
avg(payment),
max(payment),
min(payment),
count(distinct user_id),
sum(payment)
from ordertb
where dt='2012-04-10'
group by seller_id
窗口函数
avg ⭐️⭐️
count ⭐️⭐️
max ⭐️⭐️
min ⭐️⭐️
sum ⭐️⭐️
【场景2】显示每个订单中的卖家当日销售情况
2012-09-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),
count(distinct user_id) over(partition by seller_id),
sum(payment) over(partition by seller_id)
from ordertb
where dt='2012-09-10'
row_number ⭐️⭐️⭐️
用途：分组在组内对记录逐行进行排序
备注：窗口函数不能出现在同一个Select查询的where子句下，这时候使用qualify条件语句筛选，而不是having。某些数据库不支持qualify用法，则需要增加一层嵌套进行筛选，我们这里使用嵌套方法。
【场景3】查询每个品类下总实收入排名前10的卖家
use mydata;
select
*
from(
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
where dt between '2013-04-01' and '2013-04-10'
group by cat_id,
seller_id
) a
) b
where rk<=10
上面代码，用row_number的话，如果第10个跟第11个相等的gmv，那么排在第11位的seller_id则不会出现于结果中。
dense_rank ⭐️⭐️⭐️
用途：分组在组内对记录进行排序，用dense_rank的话，如果第10个跟第11个相等的gmv，那么第10、11个都是同样的排名，都出现于结果中
rank ⭐️⭐️⭐️
用途：分组在组内对记录进行排序，用rank的话，如果第10个跟第11个相等的gmv，那么第10、11个都是同样的排名，都出现于结果中，并且第11名置空，后续记录从第12名开始
row_number、dense_rank、rank这三个窗口函数在使用时要结合业务使用场景决定需要用那个，避免搞混淆。
【场景4】查询连续2天下单的人
备注：关键在于怎么识别连续——只要把订单日减按用户ID分组的订单排序，如果一个用户连续下单的话，相减得到的日期相等
select *
from(
select user_id,diff,
count(1) as days
from (
select
user_id, dt,
row_number() over(partition by user_id order by dt asc) as rk,
date_add(dt, interval -(row_number() over(partition by user_id order by dt asc)) day) as diff
from (select
user_id, dt
from ordertb
where dt between '2012-04-01' and '2013-04-10'
group by user_id, dt
)basedata
)orderdt_minus_rownum
group by user_id,
diff
) conti_days
where days=2
lag & lead ⭐️⭐️⭐️⭐️
用途：分组在组内找往过去/未来数第N个数据
【场景5】每个品类订单数量月环比
select
mon,
cat_id,
ordernum,
lag(ordernum,1) over(partition by cat_id order by mon) as ordernum_t7,
ordernum/lag(ordernum,1) over(partition by cat_id order by mon)-1 as rate_t7
from
(select
month(dt) as mon,
cat_id,
count(order_id) as ordernum
from ordertb
where dt between '2012-04-01' and '2013-04-10'
group by
month(dt),
cat_id) t
ntile ⭐️⭐️⭐️⭐️
用途：分组在组内对某列进行分桶
percent_rank ⭐️⭐️⭐️⭐️
用途：分组在组内对某列找某条记录所处的相对排名
cume_dist ⭐️⭐️⭐️⭐️
用途：分组在组内对每列找某条记录所处的累计百分比
【场景6】对各品类下的卖家按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 '2012-04-01' and '2013-04-10'
group by cat_id,
seller_id
) t
日期函数
date_add ⭐️⭐️
用途：日期+时间间隔
date_add('2020-01-01',interval 2 day) # date往后推2天
date_add('2020-01-01',interval -2 day) # date往前推2天
datediff⭐️⭐️
用途：第一个日期减去第二个日期得到的天数，可正可负
datediff('2020-01-01','2008-11-29')
date_format⭐️⭐️
用途：将时间转化成指定格式
datepart('2018-06-08 01:10:00', 'yyyy')
输出：2018-06-08
weekday ⭐️⭐️
用途：返回日期所在当前周的第几天。周一作为一周的第一天，返回值为0,周日返回6。输入必须是日期类型。
select weekday('2020-01-01')
输出：2
weekofyear ⭐️⭐️
用途：返回日期所在周为当年的第几周
weekofyear(to_date('20190421','yyyymmdd'))
weekofyear('2020-01-01')
输出：1
数学运算函数
round ⭐️
用途：四舍五入
floor ⭐️
用途：向下取整
round(118.815,2),
floor(118.815)
输出：
118.82
118
字符串处理函数
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
json_extract⭐️⭐️
用途：从json格式中摘取变量值
备注：hive中用get_json_object()
场景：从{"orderId":"4","PayInfo":"[{"pay_id":1,"money":1650}]"}中获取orderId和money的值。
select
json_extract('{"orderId":4,"PayInfo":{"pay_id":1,"money":1650}}','$.orderId'),
json_extract('{"orderId":4,"PayInfo":{"pay_id":1,"money":1650}}','$.PayInfo.money')
输出：
4
1650
md5 ⭐️
用途：计算输入字符串value的md5值，在需要对手机号码加密时用到
select md5('13X13697810')
输出：faf3cae4ba50be8b85ae80203f6dba96
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
full 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
