---
template: overrides/blogs.html
tags:
  - analytics
  - database
---

# SQL tips

!!! info
    作者：Jeremy，发布于2021-08-27，阅读时间：约10分钟，微信公众号文章链接：[:fontawesome-solid-link:](https://mp.weixin.qq.com/s/TtXCCZqp7WBZgn_90wakaw)

## 1 引言

上回Void同学主要聊了聊他对使用过的数据仓库的体验和测评。与Void"花式干饭"不同，本人在工作中主要接触的是Snowflake，以及在不同的平台里连接Snowflake写SQL，也整理一些初入数据分析常踩的坑，希望可以帮助大家绕过这些问题。

## 2 SQL结构篇

### 2.1 选择合适驱动表

进行两表或者多表Join的时候，一般会考虑需求和性能两个方面:

* 业务需求上：选择和结果表粒度相同的表作为驱动表：例如，如果结果表是统计某一天一群目标用户的某个行为指标，一般会选择用一张用户表的快照作为驱动表进行筛选，然后左连接相应的用户行为表。
* 性能上：选择小表作为驱动表连接大表，对大表建立索引：如果两张需要Join的表记录数量差异很大，一般会把小表放在前面。因为在表连接时，只有右连接表里的索引会生效，且索引在大表中效率提升更明显。


### 2.2 join前去重

表连接时，如果没有对两表进行去重，则会因为笛卡尔积出现大量重复记录。一般来说Join前去重方式有以下种：

* distinct 去重
* group by 去重
* 窗口函数 row_number() 去重（注意MySQL 8.0版本以上才可以使用窗口函数）

假设我们目前有两张表，一张员工表dept_emp，包含数据如下：

| emp_no | dept_no | from_date | to_date |
|-------|------|------------|------------|
| 10001 | d001 | 1986-06-26 | 9999-01-01 |
| 10002 | d001 | 1996-08-03 | 2000-01-01 |
| 10002 | d001 | 2001-08-03 | 9999-01-01 |
| 10003 | d002 | 1995-12-03 | 9999-01-01 |

另一张manager表，数据如下：

| dept_no | emp_no | from_date  | to_date    |
|---------|--------|------------|------------|
| d001    | 10001  | 1996-08-03 | 9999-01-01 |
| d002    | 10003  | 1990-08-05 | 9999-01-01 |

我们想要找到所有员工对应的经理，如果员工本身是经理的话则不显示，以上例子如下：

| emp_no | manger |
|--------|--------|
| 10002  | 10001 |

注意第一张表中，emp_no是有重的，不去重直接连接的话会出现两条记录:

| emp_no | manger |
|--------|--------|
| 10002  | 10001  |
| 10002  | 10001  |

上述三种方式都可以实现去重:

#### distinct 去重

``` sql
Select dept.emp_no,mgn.emp_no
From
(
    Select distinct emp_no,dept_no
    From dept_emp
) dept
Left Join
(
    Select distinct emp_no,dept_no
    From dept_manager
) mgn
on dept.dept_no = mgn.dept_no
Where dept.emp_no != mgn.emp_no
```

#### group by 去重

```sql
Select dept.emp_no,mgn.emp_no
From
(
    Select emp_no,dept_no
    From dept_emp
    group by 1
) dept
Left Join
(
    Select emp_no,dept_no
    From dept_manager
    group by 1
) mgn
on dept.dept_no = mgn.dept_no
Where dept.emp_no != mgn.emp_no
```

#### 利用窗口函数去重

以上两种去重方式虽然都能达到效果，但其实他们在选择重复记录时是有随机性的，我们不确定查询最终选择的是哪一条重复的记录。
在实际业务中，往往需要保证运行结果的一致性，这个时候可以借助窗口函数，利用额外字段进行排序，然后取第一条(如取时间字段from_date最近的一条记录)进行去重，转化成sql就是:

``` sql
Select dept.emp_no,mgn.emp_no
From
(
    Select emp_no,dept_no,
    row_number() over(partition by emp_no,dept_no order by from_date desc) as rec_rank
    From dept_emp
    group by 1
    where rec_rank = 1
) dept
Left Join
(
    Select emp_no,dept_no
    From dept_manager
    group by 1
) mgn
on dept.dept_no = mgn.dept_no
Where dept.emp_no != mgn.emp_no
```

### 2.3 善用公用表达式(CTE)

公用表达式（CTE）类似编程语言中的函数，可以用于提炼出本来需要出现多次的子查询语句，有效减少代码行数。

除了让查询语句变得更加清爽整洁以外，公用表达式还可以实现递归这类"高级"查询功能。按照递归三要素：

- 终止条件
- 函数运行状态，每次运行都逐步逼近终止条件
- 调用函数自身

在CTE中需要包含两个查询，第一个为定点查询，即返回以一个有效表的普通查询，再通过union all 叠加一个调用CTE自身的递归查询。与函数不同的的是，递归查询没有显示的终止条件，当第二个递归查询返回结果为空时即停止递归。

常见的递归形式如下：

```sql
WITH CTE AS (
SELECT column1,column2... FROM tablename WHERE conditions
UNION ALL
SELECT column1,column2... FROM tablename
INNER JOIN CTE ON conditions
)

SELECT *
FROM CTE
```

## 3 小结

以上是对SQL知识点的超级不完全总结，但是在实际业务中往往不需要非常fancy的代码，理解并运用好去重，连接，子查询，窗口函数和CTE已经能够覆盖绝大多数业务场景，更多的是需要提高代码的可读性和运行性能。如果大家对SQL在业务或面试中的小技巧感兴趣，推荐以下两本读物：
* SQL必知必会（基础）
* SQL语言艺术（进阶）

如果希望多看到类似的SQL或经验分享话题，也欢迎多多给我们留言评论~