
同一个数据分析的需求,不同人的SQL代码效率上会差别很大!本文给大家梳理集中效率优化方法,这也是数据岗面试的高频问题哦!快学起来~
💡 作者:韩信子@ShowMeAI
📘 数据分析实战系列:https://www.showmeai.tech/tutorials/40
📘 本文地址:https://www.showmeai.tech/article-detail/391
📢 声明:版权所有,转载请联系平台与作者并注明出处
📢 收藏ShowMeAI查看更多精彩内容
所有的数据相关工作人员,包括数据开发、数据分析师、数据科学家等,多多少少会使用数据库,我们很多的业务数据也是存放在业务表中。但即使是同一个需求,不同人写出的 SQL 效率上也会有很大差别,而我们在数据岗位面试的时候,也会考察相关的技能和思考,在本篇文章中,ShowMeAI将给大家梳理 SQL 中可以用于优化效率和提速的核心要求。
关于 SQL 的基础技能知识,欢迎大家查阅ShowMeAI制作的速查表:
📘 编程语言速查表 | SQL 速查表
💡 1)使用正则regexp_like
代替LIKE
如下例所示,当我们要进行模糊匹配的时候(尤其是匹配项很多的时候),我们使用regexp_like
代替LIKE
可以提高效率。
💦 低效代码
| SELECT * |
| FROM phones |
| WHERE |
| lower(name) LIKE '%samsing&' OR |
| lower(name) LIKE '%apple&' OR |
| lower(name) LIKE '%htc&' OR |
💦 高效代码
| SELECT * |
| FROM phones |
| WHERE |
| REGEXP_LIKE(lower(name),'samsung|apple|htc') |
类似的,使用regexp_extract
代替Case-when Like
可以提高效率。
💦 低效代码
| SELECT * |
| CASE |
| WHEN concat(' ', name, ' ') LIKE '%acer%' then 'Acer' |
| WHEN concat(' ', name, ' ') LIKE '%samsung%' then 'Samsung' |
| WHEN concat(' ', name, ' ') LIKE '%dell%' then 'Dell' |
| AS brand |
| FROM laptops |
💦 高效代码
| SELECT |
| regexp_extract(name,'(acer|samsung|dell)') |
| AS brand |
| FROM laptops |
💡 3)IN子句转换为临时表
但我们进行数据选择时候,有时候会用到in
作为条件选择,如果我们的候选项非常多,那利用临时表可能会带来更好的效率。
💦 低效代码
| SELECT * |
| FROM table1 as t1 |
| WHERE |
| itemid in (3363134, 5343, 5555555) |
💦 高效代码
| SELECT * |
| FROM table 1 as t1 |
| JOIN ( |
| SELECT |
| itemid |
| FROM ( |
| SELECT |
| split('3363134, 5343, 5555555') as bar |
| ) |
| CROSS JOIN |
| UNNEST(bar) AS t(itemid) |
| ) AS table2 as t2 |
| ON |
| t1.itemid = t2.itemid |
💡 4)将 JOIN 的表从大到小排序
当我们要进行表关联(join)的时候,我们可以对表基于大小进行一个排序,把大表排在前面,小表排在后面,也会带来效率的提升。
💦 低效代码
| SELECT * |
| FROM small_table |
| JOIN large_table |
| ON small_table.id = large_table.id |
💦 高效代码
| SELECT * |
| FROM large_table |
| JOIN small_table |
| ON small_table.id = large_table.id |
💡 5)使用简单的表关联条件
如果我们要基于条件对两个表进行连接,那条件中尽量不要出现复杂函数,如果一定需要使用,那我们可以先用函数对表的数据处理产出用于连接的字段。
如下例中,我们对a
和b
表进行连接,条件是b
表的「年」「月」「日」拼接后和a
表的日期一致,那粗糙的写法和优化的写法分别如下:
💦 低效代码
| SELECT * |
| FROM table1 a |
| JOIN table2 b |
| ON a.date = CONCAT(b.year, '-', b.month, '-', b.day) |
💦 高效代码
| SELECT * |
| FROM table1 a |
| JOIN ( |
| SELECT name, CONCAT(b.year, '-', b.month, '-', b.day) as date |
| FROM table2 b |
| ) new |
| ON a.date = new.date |
💡 6)分组的字段按照类别取值种类数排序
如果我们需要对数据按照多个字段分组,尤其是字段中有id类这种取值非常多的类别字段,我们应当把它排在最前面,这也可以对效率有一些帮助。
💦 低效代码
| SELECT |
| main_category, |
| sub_category, |
| itemid |
| sum(price) |
| FROM |
| table1 |
| GROUP BY |
| main_category, sub_category, itemid |
💦 高效代码
| SELECT |
| main_category, |
| sub_category, |
| itemid |
| sum(price) |
| FROM |
| table1 |
| GROUP BY |
| itemid, sub_category, main_category |
💡 7)避免 WHERE 子句中的子查询
当我们要查询的语句的where
条件中包含子查询时,我们可以通过with
语句构建临时表来调整连接条件,提升效率,如下:
💦 错误代码
| SELECT sum(price) |
| FROM table1 |
| WHERE itemid in ( |
| SELECT itemid |
| FROM table2 |
| ) |
💦 好代码
| WITH t2 |
| AS (SELECT itemid |
| FROM table2) |
| SELECT Sum(price) |
| FROM table1 AS t1 |
| JOIN t2 |
| ON t1.itemid = t2.itemid |
💡 8)取最大直接用Max而非Rank后取第1
这一条很好理解,如果我们要取某字段最大取值,我们直接使用 max,而不要用 rank 排序后取第 1,如下代码所示:
💦 低效代码
| SELECt * |
| FROM ( |
| SELECT userid, rank() over (order by prdate desc) as rank |
| FROM table 1 |
| ) |
| WHERE ranking = 1 |
💦 高效代码
| SELECT userid, max(prdate) |
| FROM table1 |
| GROUP BY 1 |
💡 9)其他优化点
- 对于大表,利用
approx_distinct()
代替count(distinct)
来计数。
- 对于大表,利用
approx_percentie(metric,0.5)
代替median
。
- 尽可能避免使用
UNION
。
参考资料
推荐阅读

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版