编辑器提供的图片
我们许多人都体验过将计算集中在云数据仓库中带来的速度和效率的核心优势。虽然这确实如此，但我们也意识到，就像任何事情一样，这种价值也伴随着自己的缺点。
这种方法的主要缺点之一是你必须学习和执行不同语言的查询，特别是 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 到你的原始数据
最终结果将如下所示：
| 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 |
结果：
| Student | Mathematics | Geography | Phys Ed |
|---|---|---|---|
| Jared | 61 | 94 | 98 |
| Patrick | 99 | 93 | 4 |
这个方法不一定困难，但确实耗时。大多数数据科学家不考虑在 SQL 中做一热编码。虽然语法很简单，他们宁愿将数据从数据仓库中转移出来，也不愿意写 26 行的 CASE 语句。我们不怪他们！
然而，我们建议利用你的数据仓库及其处理能力。这里是一个使用 STATE 作为列进行一热编码的示例。
之前：
| Babyname | State | Qty |
|---|---|---|
| Alice | AL | 156 |
| Alice | AK | 146 |
| Alice | PA | 654 |
| … | … | … |
| Zelda | NY | 417 |
| Zelda | AL | 261 |
| Zelda | CO | 321 |
结果：
| Babyname | State | State_AL | State_AK | … | State_CO | Qty |
|---|---|---|---|---|---|---|
| Alice | AL | 1 | 0 | … | 0 | 156 |
| Alice | AK | 0 | 1 | … | 0 | 146 |
| Alice | PA | 0 | 0 | … | 0 | 654 |
| … | … | … | … | |||
| Zelda | NY | 0 | 0 | … | 0 | 417 |
| Zelda | AL | 1 | 0 | … | 0 | 261 |
| Zelda | CO | 0 | 0 | … | 1 | 321 |
在进行市场篮分析或挖掘关联规则时，第一步通常是将数据格式化以将每笔交易汇总为一个记录。这对你的笔记本电脑来说可能是一个挑战，但你的数据仓库旨在高效地处理这些数据。
典型交易数据：
| 销售订单号 | 客户密钥 | 英文产品名称 | 列表价格 | 重量 | 订单日期 |
|---|---|---|---|---|---|
| SO51247 | 11249 | Mountain-200 黑色 | 2294.99 | 23.77 | 1/1/2013 |
| SO51247 | 11249 | 水瓶 - 30 oz. | 4.99 | 1/1/2013 | |
| SO51247 | 11249 | Mountain 水瓶架 | 9.99 | 1/1/2013 | |
| SO51246 | 25625 | Sport-100 头盔 | 34.99 | 12/31/2012 | |
| SO51246 | 25625 | 水瓶 - 30 oz. | 4.99 | 12/31/2012 | |
| SO51246 | 25625 | Road 水瓶架 | 8.99 | 12/31/2012 | |
| SO51246 | 25625 | Touring-1000 蓝色 | 2384.07 | 25.42 | 12/31/2012 |
结果：
| 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 |
结果：
| 交易 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 赛车、房地产市场预测等有趣的话题进行复杂分析。
1. Google 网络安全证书 - 快速进入网络安全职业生涯。
2. Google 数据分析专业证书 - 提升你的数据分析能力
3. Google IT 支持专业证书 - 支持你组织的 IT