SQL心法：从基础到进阶

对于数据分析师而言，把数取对是一项最基本的能力，因为所有的数据加工、分析工作都依赖于这一步。在互联网公司笔试中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