课程

SQL 中的数据处理

基础技能水平

更新时间 2025年12月

掌握复杂的 SQL 查询，解答各种数据科学问题，并在 PostgreSQL 中准备稳健的数据集用于分析。

免费开始课程

SQLData Manipulation

4小时

15 视频

55 道练习

4,700 XP

320K+

成就证明

深受数千家公司学习者的喜爱

需要团队培训？

企业版试用

课程描述

您已经学会了如何在数据库中对表进行聚合与连接——接下来该怎么做？如何整理、转换并更好地理解您的数据？本中级课程将讲解用于清洗、筛选与分类关系型数据库信息的关键函数，扩展您的 SQL 工具箱，并帮助您回答更复杂的问题。您将系统学习 CASE 语句、子查询与窗口函数的用法，同时借助 European Soccer Database 发掘一些有趣的足球数据事实。视频提供实时字幕，您可以点击视频左下角的 "Show transcript" 展开查看。课程术语表位于右侧的资源部分。若要获得 CPE 学分，您需要完成本课程并在合格测评中达到 70% 的得分。您可以点击右侧的 CPE 学分提示进入测评。

先决条件

Joining Data in SQL

1

We'll take the CASE

In this chapter, you will learn how to use the CASE WHEN statement to create categorical variables, aggregate data into a single column with multiple filtering conditions, and calculate counts and percentages.

We'll take the CASE

Basic CASE statements

CASE statements comparing column values

CASE statements comparing two column values part 2

In CASE things get more complex

In CASE of rivalry

Filtering your CASE statement

CASE WHEN with aggregate functions

COUNT using CASE WHEN

Conditional selection and summation with CASE WHEN

Using CASE and AVG to derive fractions

2

Short and Simple Subqueries

In this chapter, you will learn about subqueries in the SELECT, FROM, and WHERE clauses. You will gain an understanding of when subqueries are necessary to construct your dataset and where to best include them in your queries.

WHERE are the Subqueries?

Filtering using scalar subqueries

Filtering using a subquery with a list

Filtering with more complex subquery conditions

Subqueries in FROM

Joining Subqueries in FROM

Building on Subqueries in FROM

Subqueries in SELECT

Add a subquery to the SELECT clause

Subqueries in Select for Calculations

Subqueries everywhere! And best practices!

ALL the subqueries EVERYWHERE

Add a subquery in FROM

Add a subquery in SELECT

3

Correlated Queries, Nested Queries, and Common Table Expressions

In this chapter, you will learn how to use nested and correlated subqueries to extract more complex data from a relational database. You will also learn about common table expressions and how to best construct queries using multiple common table expressions.

Correlated subqueries

Getting started with correlated subqueries

Correlated subquery with multiple conditions

Nested subqueries

Getting started with nested subqueries

Nest a subquery in FROM

Common Table Expressions

Clean up with CTEs

Organizing with CTEs

CTEs with nested subqueries

Deciding on techniques to use

Get team names with a subquery

Get team names with correlated subqueries

Get team names with CTEs

Which technique to use?

4

Window Functions

You will learn about window functions and how to pass aggregate functions along a dataset. You will also learn how to calculate running totals and partitioned averages.

The match is OVER

What's OVER here?

Flip OVER your results

OVER with a PARTITION

PARTITION BY a column

PARTITION BY multiple columns

Sliding windows

Slide to the left

Slide to the right

Bringing it all together

Setting up the home team CTE

Setting up the away team CTE

Putting the CTEs together

Add a window function

SQL 中的数据处理

课程完成

获得成就证明

将此证书添加到您的 LinkedIn 档案、简历或履历中
在社交媒体和绩效评估中分享立即注册

加入超过19百万学习者，今天就开始SQL 中的数据处理！

通过 DataCamp for Mobile 提升您的数据技能

随时随地通过我们的移动课程和每日 5 分钟编程挑战提升技能。