数据科学家需要了解的所有-SQL

数据科学家需要了解的所有 SQL

原文链接

使用 Grok 2 生成的图像。

简介

在我看来，SQL 是数据专业人士应该具备的最重要技能之一。无论您是数据分析师、数据科学家还是软件开发人员，您都可能每天都会使用 SQL 与数据库交互。

从数据科学家的角度来看，您不需要成为 SQL 专家。能够使用 SQL 提取、操作和分析数据应该足以完成大多数数据科学家的任务。您会发现，您通常只在将数据加载到 Jupyter Notebook 中之前使用 SQL，然后使用 Pandas 实施一些探索性数据分析（EDA）。

本文的目的是讨论 SQL 语法的基础知识，讨论 SQL 最佳实践，以及您可用的练习 SQL 技能的资源。

什么是 SQL？

SQL 是一种为管理和操作关系型数据库而创建的特定领域语言。SQL 已被数据科学家以及大多数数据专业人士广泛采用，作为与数据库交互时的首选语言。

SQL 的缩写代表：

结构化： 数据以有组织的状态存储，与未结构化数据（例如音频、视频、文本）不同。
查询： 用户通过编写 SQL 查询与数据库对话，提取他们所需的信息。
语言： SQL 是一种编程语言，设计得非常用户友好且易于阅读，与一些传统的编程语言不同。

SQL 有许多不同的风味，比较不同风味的主要区别在于它们是付费服务还是免费服务。多年来，已经发布了几个开源的 SQL 风味，其中最受欢迎的是 MySQL 和 PostgreSQL。

根据我的经验，Transact-SQL（通过 MS SQL Server）、GoogleSQL（通过 BigQuery）和 PostgreSQL 是最受欢迎的。如果我从零开始，我会专注于 Transact-SQL（通过 MS SQL Server），因为大多数教程都涵盖这种 SQL 风格。

有关 SQL 的更多信息，请参阅此处。

SQL 基础知识

一些职业，如数据工程师和数据库管理员（DBA），需要具备高级的 SQL 知识，但对于数据科学家来说并非如此。随着经验的积累，你会发现编写 SQL 脚本变得相当重复，大多数时候你只是在复制之前的脚本并进行一些小的修改。

大多数数据科学家在将数据导入 Python 环境之前，会使用 SQL 进行基本的数据转换。我将为你提供所有必需的基本命令，以完成作为数据科学家 90%的日常 SQL 相关任务。

选择数据

最重要的 SQL 命令是SELECT，这个命令允许你定义从查询中指定的表中要选择的列。

select
    order_date,
    product_sku,
    order_quantity
from
    my_store.ecommerce.orders

列可以单独声明，或者你可以使用星号（*）表示你想要选择该表中所有列。

上述查询将选择my_store.ecommerce.orders表中的所有行，无论是否存在重复行。为了防止这种情况发生，你可以使用DISTINCT命令来仅返回唯一的行。

select distinct
    order_date,
    product_sku,
    order_quantity
from
    my_store.ecommerce.orders

数据工程

有时候你的表中没有你想要列，但它确实包含了创建该列的底层数据。使用类似CASE命令的东西，你可以在 SQL 查询中创建自己的特征。

select distinct
    order_date,
    product_sku,
    order_quantity,
    case when order_quantity >= 5 then "High"
         when order_quantity between 3 and 5 then "Medium"
         else "Low" end as order_quantity_status
from
    my_store.ecommerce.orders

在上述查询中，我们根据order_quantity列中的值创建了order_quantity_status列。CASE命令充当IF-ELSE语句，类似于你可能在其他编程语言中遇到的东西。

注意：除了使用CASE之外，还有许多其他方法可以用来创建新特征。关于这些方法的更多信息，请参阅本文底部的学习资源。

分组和排序数据

这些子句非常直观，GROUP BY子句用于聚合列，而ORDER BY子句用于在输出中排序列。

select
    order_date,
    count(distinct product_sku) as distinct_product_count
from
    my_store.ecommerce.orders
group by
    order_date
order by
    count(distinct product_sku) desc

在上述查询中，我们按order_date分组，并计算每天售出的独特产品数量。在计算完这个聚合后，我们按新创建的distinct_product_count列降序返回输出。

过滤数据

遇到规模达到数 TB 的数据库表并不罕见。为了降低处理成本和时间，在查询中包含过滤条件是必不可少的。

select
    order_date,
    product_sku,
    order_quantity
from
    my_store.ecommerce.orders
where
    order_date >= "2024-12-01"

在查询中包含WHERE子句允许你利用分区和/或索引。通过减少查询需要处理的数据量，你的查询将以极低的成本运行得更快。你的数据工程师和 DBA 会感谢你的！

WHERE子句不仅适用于过滤日期，还可以应用于表中的任何列。例如，如果我们只想包括 SKU100、SKU123 和 SKU420，并且只想看到这些产品的数量少于 3 的订单，我们可以使用以下查询：

select
    order_date,
    product_sku,
    order_quantity
from
    my_store.ecommerce.orders
where
    order_date >= "2024-12-01"
    and product_sku in ("SKU100", "SKU123", "SKU420")
    and order_quantity < 3

注意：也要花些时间看看HAVING子句，这是使用聚合列值进行过滤的另一种方法。下面的查询通过只返回每日总和大于或等于 100 的订单日期和订单总数来演示这一点。

select
    order_date,
    sum(order_quantity) as total_orders
from
    my_store.ecommerce.orders
where
    order_date >= "2024-12-01"
group by
    order_date
having
    sum(order_quantity) >= 100

数据连接

最常用的数据库设计模式是星型模式，它使用事实表和维度表。事实表包含定量数据，如指标和测量值，而维度表提供更多描述性信息，为事实表中的信息提供进一步上下文。

作为数据科学家，你的责任是确定所需数据所在的表。其次，你必须执行正确的连接来合并这些表。

select
    o.order_date,
    o.product_sku,
    o.order_quantity,
    p.product_name,
    p.product_weight
from
    my_store.ecommerce.orders o
inner join
    my_store.ecommerce.product_details p
on
    o.product_sku = p.product_sku
where
    o.order_date >= "2024-12-01"

在上述查询中，我们正在对product_sku列执行INNER JOIN。INNER JOIN将返回所有成功在product_details表中识别出product_sku的订单行。

注意到分配给每个表的别名很重要，多个表具有相同的列名并不罕见。通过使用别名，你可以明确指出你引用的是哪个特定列。

注意：确保花时间研究替代的连接方式，例如LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN。对于视觉学习者，可以查看这个链接了解 SQL 连接。

聚合数据

在使用 SQL 时，你应该非常熟悉聚合列。你将最频繁使用的命令是COUNT()、SUM()、MIN()、MAX()和AVG()。

select
    count(product_sku) as product_count,
    sum(order_quantity) as total_orders,
    min(order_quantity) as minimum_orders,
    max(order_quantity) as maximum_orders,
    avg(order_quantity) as average_orders
from
    my_store.ecommerce.orders
where
    order_date >= "2024-12-01"

这些聚合函数用于从你的数据中生成描述性统计。虽然这可以使用 Python 完成，但我发现使用 SQL 来完成这项任务更有效率，尤其是在即时回答利益相关者问题时。

下一步

在掌握基础知识之后，你应该扩展你的知识并专注于中级 SQL。在我日常工作中经常出现的一些常见过程包括[公用表达式表（CTEs）](https://www.atlassian.com/data/sql/using-common-table-expressions#:~:text=A Common Table Expression (CTE,focus on non-recurrsive CTEs)）和窗口函数。

CTEs

由于我大部分的 SQL 操作都是通过 GCP 的BigQuery进行的，因此我在几乎所有的查询中都使用 CTE。CTE 允许你创建临时表，这些表可以在更广泛的、更大的 SQL 查询中作为一部分被引用。

with total_product_orders_daily as 
(
  select
    order_date,
    product_sku,
    sum(order_quantity) as total_orders
  from  
    my_store.ecommerce.orders
where
    order_date >= "2024-12-01"
)

select
    tpod.order_date,
    tpod.product_sku,
    p.product_name,
    tpod.total_orders
from
    total_product_orders_daily tpod
inner join
    my_store.ecommerce.product_details p
on
    tpod.product_sku = p.product_sku

上述查询首先创建了一个 CTE（公用表表达式），计算total_orders，然后将其与total_product_orders_daily表和my_store.ecommerce.product_details表进行连接。此外，请注意，WHERE子句在 CTE 中尽可能早地出现，你应该始终尽量减少你正在处理的数据量。

窗口函数

窗口函数在一系列与当前行相关的行上执行计算，每一行保持一个独立的身份。例如，如果你想对你的数据进行排名或识别重复记录，你可以通过实现窗口函数来完成。

select
    order_date,
    product_sku,
    order_quantity,
    rank() over (partition by order_date, product_sku order by order_quantity desc) as daily_sku_order_rank
from
    my_store.ecommerce.orders
where
    order_date >= "2024-12-01"

上述查询创建了一个名为daily_sku_order_rank的列，该列按order_date对每个product_sku进行降序排名。

要使用窗口函数来识别和删除重复记录，可以使用以下代码：

with base_table as 
(
  select
    order_date,
    product_sku,
    order_quantity,
    row_number() over (partition by order_date, product_sku) as daily_sku_row_num
  from
    my_store.ecommerce.orders
  where
    order_date >= "2024-12-01"
)

select
    order_date,
    product_sku,
    order_quantity,
from
    base_table
where
  daily_sku_order_rank = 1

对于daily_sku_order_rank大于 1（重复记录）的情况，这些记录将在 CTE 执行并生成输出时被删除。

注意：在执行窗口函数（如 DENSE_RANK）时，还有更多可用的函数，更多信息请在此处查看。

SQL 最佳实践

与其他编程脚本类似，在编写 SQL 脚本时，你应该始终考虑其他人可能会重用你的代码。为了使这个过程更容易，最好遵循一些 SQL 最佳实践。一些突出的最佳实践包括：

使用有意义的命名约定：拥有更长且描述性更强的列/表命名约定会更好。
代码格式化：在整个脚本中使用一致的缩进。关于文本的大小写，没有正确或错误之分，选择一个并坚持下去。
避免选择所有列：选择你想要包含在输出中的特定列，在从表中选择时不要使用星号。
索引列：在WHERE、JOIN或ORDER BY子句中频繁使用的列应该被索引，因此可以优化查询性能。
函数的使用位置：与你应该索引列的位置类似，你也不应该在WHERE、JOIN或ORDER BY子句中使用任何函数（例如CAST()、LEN()）。这也适用于通配符。

注意：除了上述提到的之外，还有更多的 SQL 最佳实践，这有时可能取决于你使用的 SQL 版本。我鼓励你向公司内部咨询，看看是否已经建立了任何内部 SQL 最佳实践，你可以将其应用于你的工作中。

SQL 实践资源

当你在专业环境中使用真实数据开发 SQL 时，你的 SQL 开发将始终以更快的速度进步。对于尚未找到第一份工作的有志于成为数据科学家的人来说，有许多在线替代方案可以帮助你保持和提升你的 SQL 技能。

我发现的一些学习 SQL 的顶级资源是：

W3Schools.com

解决 SQL 代码挑战

掌握数据科学编码

SQLZoo

SQLBolt – 学习 SQL – SQL 简介

DataLemur – 通过 SQL 和数据科学面试

个人而言，我认为 StrataScratch 是最好的，因为它允许你选择不同的 SQL 版本，有很好的问题选择，并且有一个良好的用户界面（类似于 LeetCode）。

对于更理论化的学习，我会选择 W3Schools。我在刚开始学习 SQL 时就开始阅读这个资源，它总是存在于我的书签中，以便我需要刷新对特定主题的记忆。

我建议的一点是不要花太多时间试图找到正确的资源，选择一个，然后开始解决挑战。从入门任务开始，逐步提高，保持耐心和学习的连贯性。在你被认为面试准备就绪之前，你不需要完成所有困难的挑战，随着你的进步，你的信心将会增长。

注意：这些资源中的一些是免费的，而其他一些有免费层，但其中一些内容位于付费墙后。

最后的想法

所有数据科学家都应该至少具备 SQL 的基础知识。不幸的是，SQL 在学术水平上没有得到应有的认可，这通常导致毕业生在尝试获得第一份数据科学家职位时缺乏技能。

语言可能不是最吸引人的，与学习 Python 相比，它通常被描述为相当无聊。只有当你开始在专业环境中工作时，你才会理解 SQL 在你的职业生涯中有多么重要。

不仅网上有大量的免费资源可以教你，而且还有一个由工程师和科学家组成的优秀社区，他们在网上讨论 SQL 最佳实践。

抽出时间学习 SQL，在职业生涯早期就掌握这项技能无疑会使你在竞争中脱颖而出。

免责声明：我对本文中讨论的任何公司、软件或产品都没有任何关联。此外，除非另有说明，本文中包含的所有图片均为作者所有。

如果您喜欢阅读这篇文章，请关注我在 Medium 上的账号，X，以及 GitHub 以获取更多与数据科学、人工智能和工程相关的内容。

祝您学习愉快！🚀

posted @ 2026-03-28 09:59 布客飞龙III 阅读(2) 评论(0) 收藏举报

刷新页面返回顶部

龙哥盟