Loading...
墨滴

BulletTech官方微信

2021/09/11  阅读:31  主题:自定义主题1

sql-tips

Sql tips

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

BulletTech官方微信

2021/09/11  阅读:31  主题:自定义主题1

作者介绍

BulletTech官方微信