如何从十亿行数据中制作数据透视表

谢谢 Yifeng Wu 贡献了关于渲染输出的部分。

Sigma 的主要功能之一是能够将数据从工作簿导出为各种格式，例如 CSV、Excel 和 Google 表格。除了按需导出数据之外，Sigma 还可以轻松构建在满足给定条件时运行计划导出的自动化，并通过电子邮件、Slack 甚至网络挂钩交付报告，从而为用户的业务工作流程提供支持。

支持数据导出的基础架构的一个关键组件是我们的后端服务，它使用来自用户云数据仓库的查询结果并将其转换为各种格式。该服务是用 Rust 编写的，旨在处理大量数据 使用 Apache Arrow 内存格式 .让我们看看结果转换服务是如何工作的。特别是，我们将看看 Sigma 如何导出数据透视表。

什么是数据透视表？

Sigma 提供了多种类型的表格和可视化，使业务用户能够从他们的数据中获得洞察力。数据透视表是一种特殊类型的可视化，它显示来自基础表的聚合值，这些值已被分组为各种类别。

例如，假设我们从下表开始，其中包含电子商店连锁店的订单数据。

假设我们想找出每个城市和州、每个产品类别的所有订单的总价格。以下数据透视表为我们提供了该信息。

几个参数决定了数据透视表的布局：

• 这 行尺寸 是基础表中的列，用于对沿数据透视表的垂直轴显示的数据进行分组。在这里，行维度是 Store State 和 Store City。
• 这 列尺寸 ，与行维度一样，指定基础表中的列，用于对沿数据透视表的水平轴显示的数据进行分组。在此示例中，我们有一个列维度，即产品类型。
• 这 价值 （或度量）是我们正在聚合的基础表中的列。在这种情况下，它是每个分组内的价格总和。

通常，数据透视表的布局可以任意复杂，具有任意数量的行维度、列维度和度量。我们的数据透视表还支持显示 小计 ，它为我们提供了不同分组级别的聚合值。例如，上面的数据透视表显示了每个城市、州和产品类型的总价以及总计。

计算数据透视表导出

构建数据透视表并将其导出为 Excel 或 CSV 等格式需要几个步骤：

1. 使用 SQL 聚合基础数据，
2. 计算一个表示数据透视表网格布局的数据透视索引，以及
3. 以请求的格式呈现输出。

第一步是根据指定的维度对数据进行分组并计算聚合值。此步骤的计算实际上并未在 Sigma 的后端服务中运行。相反，它作为 Sigma 生成的 SQL 查询运行，该查询在存储数据的云数据仓库中执行。

对于上面的示例，我们的查询编译器将生成一个 SQL 查询，该查询产生以下输出，其中包含分组维度（商店状态、商店城市和产品类型）、聚合的枢轴值列（价格总和）以及计算的小计在不同的分组级别。

生成 SQL 以计算数据透视表中的值由我们的查询编译器服务处理，这是一个值得深入研究的主题。现在，我们将特别关注如何将我们从数据仓库接收到的分组和聚合表转换为所需输出格式的数据透视表。

一旦我们有了一个包含分组和聚合数据的表，下一步就是以数据透视表的形式排列这些值，并沿垂直轴和水平轴显示分组标签。为此，我们构建了一个透视索引数据结构，它以一种高效且独立于目标文件类型或样式的方式对透视表的布局进行编码。

让我们来看一个小例子。假设我们从数据仓库中的一个聚合表开始，其中包含两个行维度、一个列维度和一个值列。

第一步是为数据计算一对排序索引：一个用于行维度，一个用于列维度。排序索引是数组的数据索引的排列，它让我们可以遍历数组，就好像它是根据特定字段的值排序的一样，而无需实际对基础数据进行排序。

在这个例子中，数据已经根据第一行维度排序，第二行维度打破了平局，所以行排序索引只是恒等排列 [0, 1, 2, 3, 4, 5] .另一方面，列排序索引将是 [0, 2, 4, 1, 3, 5] ，即包含所有记录的索引 X 在列维度中，然后是列维度值为 是 .下面，我们有与上面相同的表，但将排序索引添加为列。

一旦我们有了排序索引，我们就计算一个枢轴索引。这是一种有效总结数据透视表布局的数据结构。枢轴索引由每个行和列维度的一组标签索引以及一个值索引组成。值索引非常简单：它将水平和垂直偏移对（生成的数据透视表中的坐标）映射到基础表中可以找到其对应值的行。

标签索引表示我们为每个分组维度显示的标签。对于每个维度，我们存储一个标签条目列表，其中每个条目存储以下信息：

1. 可以找到标签值的基础数据中的索引，
2. 标签的平面偏移量，即标签沿水平轴或垂直轴的位置，
3. 重复计数，这样我们就不必存储大量重复条目。

对于上表，我们可以使用下图可视化数据透视索引：

在此图中，蓝色框表示标签索引，而绿色框表示值索引。例如，包含值的标签条目 乙 对于第一行维度是指数据索引为 4（其中的第一行 乙 出现在第一行维度列中），平面偏移量为 2，重复计数为 1，因为它只有一个子组。另一方面，为 一个 重复计数为 2，因为有两个与其值对应的子组。

为了减少我们需要做的复制量，数据透视索引不存储数据中出现的实际值，而是使用对基础表内存的引用。

我们的结果转换服务是使用 Rust 的异步构建的 东京 运行时，它使服务的单个副本能够使用少量线程处理数千个并发请求。

尽管我们已尝试使枢轴索引计算尽可能高效，但为大型表计算枢轴索引仍然是一个相当 CPU 密集型的过程。因为我们只分配少量线程来处理传入的请求，所以我们需要确保计算大的枢轴索引不会阻塞其他任务的运行。为了确保其他任务能够取得进展，我们使用 Tokio 的 spawn_blocking 原语在与负责处理传入 API 请求的线程池不同的线程池上执行索引计算任务。

渲染输出

一旦我们有了数据透视表，我们就拥有了创建数据透视表所需的一切。下一步是使用透视索引和聚合表以用户选择的格式呈现输出。

为了支持多种导出格式，我们构建了几个使用枢轴索引的枢轴序列化程序以及上面的聚合表。他们输出 CSV 或 Excel 文件，或者通过 API 将数据上传到 Google 表格。为了促进我们的枢轴序列化模块中的代码重用，我们在 访客设计模式 构建可以使用各种输出格式呈现在数据透视索引中编码的数据的序列化程序。这是由几部分组成的。

第一个是访问者接口，它指定方法，例如 visit_boolean , 访问字符串 等，可以实现以给定格式写入数据透视表的单元格。我们有针对 CSV、Excel 和 Google 表格的此访问者的单独实现。

第二件是一个 export_pivot() 接受访问者和数据透视索引的方法。此方法遍历数据透视表中的每个单元格。在每次访问期间，我们都会执行以下步骤：

1. 使用数据透视索引和聚合表检索单元格的值。
2. 从枢轴索引中检索单元格上下文。单元格上下文描述了渲染特定单元格所需知道的一切，除了它的值。这包括单元格在表格中的位置（即，它是标题还是度量值）、其重复计数以及对聚合表中值所在列的引用。
3. 使用单元格的值、其单元格上下文及其在生成的数据透视表中的位置调用适当的访问者方法。

在渲染步骤中，我们还应用用户指定的任何格式选项。例如，Sigma 支持多种可应用于数据透视表单元格的数字格式选项。对于 Excel，我们还利用 Excel 提供的各种格式选项，例如合并单元格、文本格式和对齐方式。

Google 表格的渲染步骤与 CSV 和 Excel 略有不同，主要是因为我们不是生成用户下载的文件，而是使用他们的 API .因此，渲染步骤的输出包含一个批量更新 API 调用列表，用于执行创建工作表、写入数据和合并单元格等任务，然后在用户的 Google 表格帐户上执行这些任务。

结论

这篇文章只是皮毛，但我们希望这能让您了解 Sigma 的工程团队所面临的一些激动人心的挑战！如果您觉得这很有趣，我们很乐意为您 查看或打开职位，并申请与我们合作 ！

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明

本文链接：https://www.qanswer.top/10890/07300208