超越基础:迈向高级SQL技能之路(sql高级技巧)

yumo6661周前 (07-23)技术文章6

在当今数据驱动的世界中,SQL 不仅是一门必须掌握的技术,更是数据分析师、工程师和商业决策者与数据对话的语言。许多学习者在掌握了 SELECT、FROM、WHERE 等基础语句之后,便陷入瓶颈,难以向更复杂的数据分析场景迈进。本文将带你从SQL的“六大基础语句”出发,逐步探索连接、函数、正则表达式、窗口函数、执行效率分析、子查询与CTE等高级话题,帮助你构建一套实用而系统的SQL技能体系。


一、六大基础语句回顾:SQL的地基

学习任何技能都应从根基打起。SQL 的六大基本语句(Big Six)包括:

  • SELECT: 选择要显示的列;
  • FROM: 指定数据来源的表;
  • WHERE: 筛选满足条件的行;
  • GROUP BY: 将数据按照某列分组;
  • HAVING: 筛选分组后的结果;
  • ORDER BY: 按某列排序。

以下是一个典型的例子:找出 2010 年以来上映的 Pixar 电影,并按照上映时间倒序排列。

SELECT * FROM films 
WHERE release_date >= '2010-01-01' 
ORDER BY release_date DESC;

如果我们想按电影评级统计数量,可以添加 GROUP BY 与 HAVING:

SELECT film_rating, COUNT(id) AS num_movies 
FROM films 
GROUP BY film_rating 
HAVING num_movies > 14;

六大语句的标准执行顺序有助于理解查询逻辑:SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY。掌握这些基础语法是迈向高级的前提。


二、函数的力量:提取、转换与分类

SQL 中的函数按处理数据类型可分为三类:日期函数、数值函数和字符串函数。合理运用这些函数可以显著提升分析能力。

1. 提取十年区间:日期与数值函数联用

我们希望统计每个“年代”发行的电影数量。可通过年份除以 10 并下取整后再乘以 10 实现:

SELECT FLOOR(YEAR(release_date) / 10) * 10 AS decade, 
  COUNT(id) AS num_movies 
FROM films 
GROUP BY decade;

通过 YEAR() 提取年份,FLOOR() 下取整,再还原回“年代”的方式,可将电影聚合为“1990年代”、“2000年代”等区间。

2. 系列识别:用正则表达式清洗片名

许多电影属于系列,如《Toy Story》《Cars》。若要统计每个系列的电影数量,需去除片名中结尾的编号,例如“Toy Story 2”应归为“Toy Story”。可使用正则表达式实现:

SELECT REGEXP_REPLACE(film, ' \\d+#39;, '') AS series, COUNT(id) AS num_movies 
FROM films 
GROUP BY series 
HAVING num_movies > 1;

这里使用了 REGEXP_REPLACE,将结尾的空格加数字(如“ 2”)移除,成功将系列统一命名。


三、连接与合并:JOIN 与 UNION 深入理解

理解 JOIN(表连接)与 UNION(结果合并)是迈入中高级SQL的关键一步。

1. JOIN:将数据横向拼接

假设我们有两个表:films 和 reviews,想把它们按 film 字段拼接:

SELECT * FROM films AS f JOIN reviews AS r 
ON f.film = r.film;

若数据存在缺失或拼写差异导致未成功连接,可通过 LEFT JOIN 与 RIGHT JOIN 排查缺失项:

-- films 中有而 reviews 中没有的记录

SELECT f.film 
FROM films f 
LEFT JOIN reviews r ON f.film = r.film 
WHERE r.film IS NULL;

2. UNION:将结果纵向叠加

若想查看两边都未匹配的记录,可用 UNION 合并两个方向的查找结果:

SELECT f.film FROM films f LEFT JOIN reviews r ON f.film = r.film 
WHERE r.film IS NULL 
UNION 
SELECT r.film FROM reviews r LEFT JOIN films f ON r.film = f.film 
WHERE f.film IS NULL;

四、查询效率评估与正则性能陷阱

虽然使用 REGEXP_REPLACE 可以解决拼写差异问题,但正则计算代价较高,可能导致查询效率低下。推荐仅在探索阶段使用,在生产环境应通过预处理标准化数据。

为了验证效率,可使用如下语句评估执行计划:

EXPLAIN ANALYZE 
SELECT * 
FROM films f 
JOIN reviews r ON REGEXP_REPLACE(f.film, '[[:punct:]]', '') = r.film;

若查询响应明显变慢,可考虑优化正则表达式或提前清洗数据。


五、窗口函数:超越单行的计算

窗口函数可实现跨行计算,是SQL的强大功能之一。它与普通函数不同,不局限于单行,而能在“窗口”范围内执行聚合、排序、排名等操作。

1. 生成新编号:ROW_NUMBER()

SELECT id, film, ROW_NUMBER() OVER (ORDER BY release_date) AS new_id FROM films;

该语句为所有电影重新生成编号,顺序基于上映时间。

2. 计算评分差值:LAG()

WITH score_diff AS ( 
    SELECT film, imdb_score, LAG(imdb_score) OVER (ORDER BY release_date) AS prev_score 
    FROM films ) 
SELECT *, ROUND(imdb_score - prev_score, 1) AS score_diff 
FROM score_diff 
WHERE prev_score IS NOT NULL;

该方法可计算一部电影与上一部作品的评分差异。例如,《Toy Story 2》与《Toy Story》的评分差异就可由此得出。


六、子查询与CTE:构建分步逻辑链条

在 SQL 中,由于语句执行顺序原因,SELECT 中定义的别名不能直接在 WHERE 中使用。此时可用子查询或 CTE(公共表达式)解决。

子查询方式:

SELECT * FROM ( 
     SELECT film, imdb_score, 
           LAG(imdb_score) OVER (ORDER BY release_date) AS prev_score 
     FROM films ) AS sub 
WHERE prev_score IS NOT NULL;

CTE方式(推荐):

WITH score_cte AS (
  SELECT film, imdb_score,
         LAG(imdb_score) OVER (ORDER BY release_date) AS prev_score
  FROM films
)
SELECT *,
       imdb_score - prev_score AS score_diff
FROM score_cte
WHERE prev_score IS NOT NULL;

CTE 的优势在于可读性强、可多次引用,并能嵌套构建多层逻辑。对于复杂查询,使用CTE远胜子查询。


七、总结与建议

学习SQL不仅仅是记住语法,更重要的是理解每一步的目的和逻辑。以下是本文的几点核心总结:

  • 打牢基础: 熟练掌握六大基本语句是向高级迈进的前提;
  • 函数加持: 善用内置函数,尤其是日期、数值、字符串类型;
  • 逐步推进: 借助CTE与子查询分阶段构建复杂逻辑;
  • 思路优先: 写出可运行的查询比一次写对更重要,优化可以之后再做;
  • 善用工具: ChatGPT 是高效编写和调试SQL的有力助手,尤其在正则表达式和优化建议上非常实用;
  • 不断实践: 在工作中多尝试 JOIN、UNION、窗口函数与性能分析,才能真正把知识转化为能力。
<script type="text/javascript" src="//mp.toutiao.com/mp/agw/mass_profit/pc_product_promotions_js?item_id=7528322771336675855"></script>

相关文章

如何利用VLOOKUP函数光速计算个人所得税?

说到VLOOKUP这个查找引用函数,相信从事财务工作的亲们都对它倍感亲切,因为它是咱们财务圈的“大众情人”,在财会工作中运用相当普遍。我们都知道,VLOOKUP函数的第4个参数包括两种查找方式,即精确...

Excel常用函数介绍(excel常用函数详细讲解)

Excel 函数是一种非常强大的工具,可以帮助我们更加高效地处理和分析数据。下面介绍一些 Excel 函数使用技巧,帮助你更加灵活地使用 Excel。IF 函数IF 函数是 Excel 中非常常用的一...

Excel中的常用数学函数(excel表格数学函数)

Excel中的常用数学函数包含了ROUND(四舍五入函数)、ABS(取绝对值函数)、INT(取整函数);MOD(取余函数)、RANDBETWEEN(随机函数)、PRODUCT(乘积函数)等等,这些函数...

办公文员常用几大Excel函数,建议收藏

文章最后有彩蛋!好礼相送!Excel秘籍大全,正文开始一、trunc函数功能:将数字的小数部分截去(不进行舍入或四舍五入)。结构:=TRUNC(数值,小数位数)二、int函数功能:将数字向下舍入到最接...

Excel函数公式大全(图文详解)(excel函数公式大全2021)

Excel函数公式大全(图文详解)我把每个函数的中文名都制作成了目录,通过目录能够快速定位到相应的函数。如果这篇文章对你有帮助,记得点赞关注哟。全文为Excel干货实操指南,为方便学习求和SUM函数是...

Excel常用函数使用说明(excel 常用函数介绍)

一、条件判断函数 IF 功能:根据条件返回不同结果。示例:输入 =IF(A1>80,"合格","不合格"),若A1数值>80,显示“合格”,否则“不合格”。 注...