课程

SQL 中的探索性数据分析

中级技能水平

更新时间 2026年1月

了解如何探索数据库中的可用内容：表、表之间的关系，以及其中存储的数据。

免费开始课程

SQLExploratory Data Analysis

4小时

16 视频

57 道练习

4,650 XP

180K+

成就证明

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

需要团队培训？

企业版试用

课程描述

您已经可以访问一个数据库。接下来该做什么？在已有的连接表、使用基础函数、分组数据和子查询等技能之上，下一步就是学习如何探索数据库及其中的数据。借助来自 Stack Overflow、Fortune 500 公司，以及伊利诺伊州埃文斯顿市 311 求助请求的数据，您将熟悉数值、字符和日期/时间等数据类型。您会使用函数在数据库内完成聚合、汇总和分析。数据中的错误和不一致也难不倒您！您将了解常见问题及相应的清洗策略。课程结束时，您就可以开始探索自己的 PostgreSQL 数据库并分析其中的数据。视频包含实时字幕，您可点击视频左下角的 "Show transcript" 展开查看。课程术语表位于右侧的资源部分。要获得 CPE 学分，您需要完成课程并在合格评估中取得 70% 的成绩。您可以点击右侧的 CPE 学分提示进入评估。

先决条件

Data Manipulation in SQL

1

What's in the Database?

Start exploring a database by identifying the tables and the foreign keys that link them. Look for missing values, count the number of observations, and join tables to understand how they're related. Learn about coalescing and casting data along the way.

What's in the database?

Explore table sizes

Count missing values

Join tables

The keys to the database

Foreign keys

Read an entity relationship diagram

Column types and constraints

Effects of casting

Summarize the distribution of numeric values

2

Summarizing and Aggregating Numeric Data

You'll build on functions like min and max to summarize numeric data in new ways. Add average, variance, correlation, and percentile functions to your toolkit, and learn how to truncate and round numeric values too. Build complex queries and save your results by creating temporary tables.

Numeric data types and summary functions

Explore with division

Summarize numeric columns

Summarize group statistics

Exploring distributions

Generate series

More summary functions

Correlation

Mean and Median

Creating temporary tables

Create a temp table

Create a temp table to simplify a query

Insert into a temp table

3

Exploring Categorical Data and Unstructured Text

Text, or character, data can get messy, but you'll learn how to deal with inconsistencies in case, spacing, and delimiters. Learn how to use a temporary table to recode messy categorical data to standardized values you can count and aggregate. Extract new variables from unstructured text as you explore help requests submitted to the city of Evanston, IL.

Character data types and common issues

Count the categories

Spotting character data problems

Cases and spaces

Exploring unstructured text

Splitting and concatenating text

Concatenate strings

Split strings on a delimiter

Shorten long strings

Strategies for multiple transformations

Create an "other" category

Group and recode values

Create a table with indicator variables

4

Working with Dates and Timestamps

What time is it? In this chapter, you'll learn how to find out. You'll aggregate date/time data by hour, day, month, or year and practice both constructing time series and finding gaps in them.

Date/time types and formats

Date comparisons

Date arithmetic

Completion time by category

Date/time components and aggregation

Variation by day of week

Date truncation

Aggregating with date/time series

Find missing dates

Custom aggregation periods

Monthly average with missing dates

Time between events

Longest gap

SQL 中的探索性数据分析

课程完成

获得成就证明

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

加入超过19百万学习者，今天就开始SQL 中的探索性数据分析！

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

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