每日分享 – 私藏!资深数据专家SQL效率优化技巧 ⛵

同一个数据分析的需求,不同人的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')

💡 2)使用regexp_extract代替 Case-when Like

类似的,使用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)使用简单的表关联条件

如果我们要基于条件对两个表进行连接,那条件中尽量不要出现复杂函数,如果一定需要使用,那我们可以先用函数对表的数据处理产出用于连接的字段。

如下例中,我们对ab表进行连接,条件是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

参考资料

推荐阅读

正文完