Skip to content

SQL tips

Info

作者：Jeremy，发布于2021-08-27，阅读时间：约5分钟，微信公众号文章链接：

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 去重

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 去重

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就是:

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自身的递归查询。与函数不同的的是，递归查询没有显示的终止条件，当第二个递归查询返回结果为空时即停止递归。

常见的递归形式如下：

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或经验分享话题，也欢迎多多给我们留言评论~

Viewed times

Comments