15年数据分析专业人士Josh Berry从Python转向SQL时遇到的5个最困难的事情提供示例、SQL代码和定制SQL到您自己的项目的资源
我们中的许多人已经体验到了在云数据仓库内集中计算带来的速度和效率的核心力量。虽然这是真的，但我们中的许多人也意识到，像任何事物一样，这个价值也带有自己的缺点。
这种方法的主要缺点之一是，您必须学习并执行不同语言的查询，特别是 SQL。尽管编写 SQL 比建立第二基础设施来运行 python（在您的笔记本电脑或办公室服务器上）更快、更便宜，但它随着数据分析师想要从云仓库中提取什么信息而带来了许多不同的复杂性。转换到云数据仓库增加了复杂 SQL 与 Python 的效用。通过自己的经验，我决定记录学习和执行 SQL 中最痛苦的特定转换，并提供读者所需的实际 SQL，以减轻一些痛苦。
为了帮助您的工作流程，您会注意到我在执行转换之前和之后提供数据结构的示例，这样您就可以跟随并验证您的工作。我还提供了执行这 5 个最难的转换所需的实际 SQL。您需要新的 SQL 来执行跨多个项目的转换，因为您的数据会发生变化。我们为每个转换提供了动态 SQL 的链接，因此您可以根据需要继续捕获所需的分析 SQL！
日期脊柱
不清楚日期脊柱这个术语是从哪里来的，但即使不知道这个术语的人可能也熟悉它是什么。
想象一下，您正在分析每日销售数据，它看起来像这样：
| sales_date | product | sales |
| 2022-04-14 | A | 46 |
| 2022-04-14 | B | 409 |
| 2022-04-15 | A | 17 |
| 2022-04-15 | B | 480 |
| 2022-04-18 | A | 65 |
| 2022-04-19 | A | 45 |
| 2022-04-19 | B | 411 |
16日和17日没有销售，所以行完全缺失。如果我们试图计算平均每日销售额，或构建时间序列预测模型，这种格式将是一个重大问题。我们需要做的是插入缺少的日子的行。
这是基本概念：
- 生成或选择唯一日期
- 生成或选择唯一产品
- 交叉连接（笛卡尔积）1和2的所有组合
- 将#3外连接到您的原始数据
可定制的日期脊柱 SQL
最终结果将如下所示：
| sales_date | product | sales |
| 2022-04-14 | A | 46 |
| 2022-04-14 | B | 409 |
| 2022-04-15 | A | 17 |
| 2022-04-15 | B | 480 |
| 2022-04-16 | A | 0 |
| 2022-04-16 | B | 0 |
| 2022-04-17 | A | 0 |
| 2022-04-17 | B | 0 |
| 2022-04-18 | A | 65 |
| 2022-04-18 | B | 0 |
| 2022-04-19 | A | 45 |
| 2022-04-19 | B | 411 |
轴旋转/逆旋转
有时，在进行分析时，您想要重新构造表格。例如，我们可能有学生、学科和成绩的列表，但我们想将学科分列到每个列中。我们都知道和喜欢 Excel 因为它的数据透视表。但是你有没有试过在 SQL 中做它呢？不仅每个数据库都有不同的支持 PIVOT 的烦人差异，而且语法也不直观，容易被遗忘。
之前的表格：
| 学生 | 学科 | 成绩 |
| Jared | 数学 | 61 |
| Jared | 地理 | 94 |
| Jared | 体育 | 98 |
| Patrick | 数学 | 99 |
| Patrick | 地理 | 93 |
| Patrick | 体育 | 4 |
可定制的用于 Pivot 的 SQL
结果：
| 学生 | 数学 | 地理 | 体育 |
| Jared | 61 | 94 | 98 |
| Patrick | 99 | 93 | 4 |
一位有效编码
这个并不一定困难，但是很费时间。大多数数据科学家不考虑在 SQL 中进行一位有效编码。尽管语法很简单，但他们宁愿将数据传输出数据仓库，而不是编写 26 行 CASE 语句的繁琐任务。我们不怪他们！
但是，我们建议利用数据仓库及其处理能力。这里是使用 STATE 作为一位有效编码列的示例。
之前的表格：
| 姓名 | 州 | 数量 |
| Alice | AL | 156 |
| Alice | AK | 146 |
| Alice | PA | 654 |
| … | … | … |
| Zelda | NY | 417 |
| Zelda | AL | 261 |
| Zelda | CO | 321 |
可定制的SQL One-Hot编码
结果:
| 宝宝名字 | 州 | 州_AL | 州_AK | … | 州_CO | 数量 |
| 爱丽丝 | AL | 1 | 0 | … | 0 | 156 |
| 爱丽丝 | AK | 0 | 1 | … | 0 | 146 |
| 爱丽丝 | PA | 0 | 0 | … | 0 | 654 |
| … | … | … | … | |||
| 塞尔达 | NY | 0 | 0 | … | 0 | 417 |
| 塞尔达 | AL | 1 | 0 | … | 0 | 261 |
| 塞尔达 | CO | 0 | 0 | … | 1 | 321 |
购物篮分析
进行购物篮分析或关联规则挖掘时，第一步通常是将数据格式化以将每个交易聚合到单个记录中。 这对于您的笔记本电脑来说可能很具有挑战性，但是您的数据仓库旨在高效地处理此类数据。
典型的交易数据:
| 销售订单号 | 客户关键字 | 英文产品名称 | 定价 | 重量 | 下单日期 |
| SO51247 | 11249 | Mountain-200 Black | 2294.99 | 23.77 | 2013年1月1日 |
| SO51247 | 11249 | 水瓶 – 30 盎司。 | 4.99 | 2013年1月1日 | |
| SO51247 | 11249 | 山地瓶笼 | 9.99 | 2013年1月1日 | |
| SO51246 | 25625 | 运动-100头盔 | 34.99 | 2012年12月31日 | |
| SO51246 | 25625 | 水瓶 – 30 盎司。 | 4.99 | 2012年12月31日 | |
| SO51246 | 25625 | 公路瓶笼 | 8.99 | 2012年12月31日 | |
| SO51246 | 25625 | 旅行-1000蓝色 | 2384.07 | 25.42 | 2012年12月31日 |
适用于市场篮子的可定制SQL
结果：
| NUMTRANSACTIONS | ENGLISHPRODUCTNAME_LISTAGG |
| 207 | 山地车水壶架，30盎司水瓶 |
| 200 | 山地车轮胎内胎，补丁套件/8块补丁 |
| 142 | LL公路车轮胎，补丁套件/8块补丁 |
| 137 | 补丁套件/8块补丁，公路车轮胎内胎 |
| 135 | 补丁套件/8块补丁，旅行车轮胎内胎 |
| 132 | HL山地车轮胎，山地车轮胎内胎，补丁套件/8块补丁 |
时间序列聚合
时间序列聚合不仅被数据科学家使用，还被用于分析。它们之所以难处理是因为窗口函数需要正确格式化数据。
例如，如果要计算过去14天的平均销售额，窗口函数要求将所有销售数据分解成每天一行。不幸的是，任何曾经处理过销售数据的人都知道，它通常是以交易级别存储的。这就是时间序列聚合的用处所在。您可以创建聚合的历史指标，而无需重新格式化整个数据集。如果我们要同时添加多个指标，它也很有用：
- 过去14天的平均销售额
- 过去6个月中最大的购买
- 过去90天中不同产品类型的计数
如果您想使用窗口函数，则每个指标都需要独立构建，并进行多个步骤。
更好的处理方法是使用公共表达式（CTEs）来定义每个历史窗口，进行预聚合。
例如：
| 交易ID | 客户ID | 产品类型 | 购买金额 | 交易日期 |
| 65432 | 101 | 杂货 | 101.14 | 2022-03-01 |
| 65493 | 101 | 杂货 | 98.45 | 2022-04-30 |
| 65494 | 101 | 汽车 | 239.98 | 2022-05-01 |
| 66789 | 101 | 杂货 | 86.55 | 2022-05-22 |
| 66981 | 101 | 药房 | 14 | 2022-06-15 |
| 67145 | 101 | 杂货 | 93.12 | 2022-06-22 |
可定制的时序聚合 SQL
结果：
| 交易 ID | 客户 ID | 产品类型 | 购买金额 | 交易日期 | 过去 14 天平均销售额 | 过去 6 个月最大购买金额 | 过去 90 天不同产品类型数量 |
| 65432 | 101 | 杂货 | 101.14 | 2022-03-01 | 101.14 | 101.14 | 1 |
| 65493 | 101 | 杂货 | 98.45 | 2022-04-30 | 98.45 | 101.14 | 2 |
| 65494 | 101 | 汽车用品 | 239.98 | 2022-05-01 | 169.21 | 239.98 | 2 |
| 66789 | 101 | 杂货 | 86.55 | 2022-05-22 | 86.55 | 239.98 | 2 |
| 66981 | 101 | 药房 | 14 | 2022-06-15 | 14 | 239.98 | 3 |
| 67145 | 101 | 杂货 | 93.12 | 2022-06-22 | 53.56 | 239.98 | 3 |
结论
我希望这篇文章能够帮助解决数据从业者在现代数据堆栈中遇到的不同问题。当在云数据仓库中查询时，SQL 是一把双刃剑。虽然将计算集中在云数据仓库中可以提高速度，但有时需要一些额外的 SQL 技能。希望这篇文章有助于回答问题，并提供所需的语法和背景，以解决这些问题。
Josh Berry（@Twitter）是 Rasgo 的客户面向数据科学负责人，自 2008 年以来一直从事数据和分析工作。Josh 在 Comcast 工作了 10 年，他建立了数据科学团队，并是公司内部开发的 Comcast 功能商店的重要拥有者之一，这是市场上最早的功能商店之一。在 Comcast 之后，Josh 是在 DataRobot 中建立客户面向数据科学的关键领导者。在业余时间，Josh 对诸如棒球、F1 赛车、房地产市场预测等有趣的主题进行复杂分析。