有什么新内容

数据透视表

学习如何在 Metabase 中使用不同数据库创建交叉表。

什么是交叉表？交叉表 是一种汇总和分组数据的表格方式。它们是分析师工具箱中的宝贵工具，因为它们是展示和重新排列大量信息的有效方式。以下是它们的工作原理：

在 Metabase 中创建交叉表时不受支持的数据库

交叉表目前在 Metabase 中以下数据库中不可用

Druid
MongoDB

交叉表适用于所有其他官方支持的数据库。

交叉表与常规表对比

您典型的、基本的表是一个单元格网格。每一列代表单个记录的一个属性，每行有一个记录。

交叉表是一种分组行和列的表，包括具有这些分组汇总值的汇总行。这些汇总值通常被称为小计和总计，尽管这些汇总也可以是其他值，例如平均值。

它们被称为交叉表的原因是因为您可以旋转（“交叉”）一列 90 度，使该列中的值成为列标题。将值旋转到列标题中在尝试分析具有多个属性的数据时非常有用，例如时间、位置和类别。您可以旋转多个行到列，反之亦然，或者根本不旋转。

但这所有的一切都很抽象，所以让我们通过一个例子来向您展示交叉表是如何工作的。

如何创建交叉表：示例

首先，让我们假设我们想了解

订单带来的年度收入总额
对于每个州（即我们的客户住在哪里？）
以及这些订单如何按产品类别细分

这是我们使用 查询构建器 的查询：

Query Builder with a preview of the question

在这里，我们从Orders表中获取数据并对记录进行汇总。我们将订单按User → State、Created At（按年份）和Product → Category进行分组。对于每个组（比如2017年的阿拉斯加），我们计算订单数量，并累加这些订单的小计。（请注意，尽管我们在数据部分只选择了Orders表，但Metabase会自动连接Products和People表以获取状态和类别数据。）

生成的表格是一个常规表格，有行代表状态、年份和产品类别的每一种组合。

现在，假设我们还想了解每个州的年度小计总和（例如，阿拉斯加州所有年份Doohickey产品的订单收入是多少？）。为了找出答案，我们可以自己累加小计，或者使用交叉表来计算这个数字。在屏幕左下角，点击可视化 > 交叉表。

The pivot table is born.

在我们的交叉表中，Metabase已经设置了如下

行：User → State和Created At（按年份）
列：Product → Category
值：Count和Subtotal总和

像平面表一样，交叉表让我们看到，例如，2020年我们的阿拉斯加（AK）客户总共购买了11个Doohickey产品，花费了867.63美元。但现在交叉表已经对阿拉斯加相关的行进行了分组，并给出了这些行的小计，使我们能够看到我们的问题的答案：从2016年到2020年，阿拉斯加人购买了103个Doohickeys，总计6,900.43美元。

除了分组小计外，交叉表还包括行和列的总计

行总计示例：所有州Doohickey订单的总数。
列总计示例：阿拉斯加州所有产品类别的小计总和。

我们可以通过折叠和展开行组来浏览表格

Collapsed rows on a pivot table, showing only the groupings subtotals. Only Colorado

现在，让我们尝试交叉表格。在屏幕左下角，我们将点击设置。要交叉表格，我们将字段在三个桶（行、列和值）之间移动。

单个桶内的顺序很重要，所以让我们先在一个桶内（行桶）简单调整表格：将Created At置于User → State之上，这样表格就会重新排列

Reordering the columns within a single bucket rearranges the table.

现在，表格首先按年份分组，然后按每个产品类别给出了每个州的订单细分。

我们也可以在桶之间切换字段，例如将Product → Category从列移动到行，将User → State从行移动到列。

Pivoting to group rows by product category.

您还可以关闭给定行分组的小计

You can toggle subtotals on or off.

与平面表一样，我们有一些排序和格式化选项，并且我们可以点击表中的值来打开钻取菜单，这将允许我们钻取数据。

如何创建交叉表：局限性

数据透视表仅适用于支持连接和表达式的关系数据库，因此您无法在MongoDB等数据库中使用它们。它们也仅适用于使用查询构建器组合的问题。在这种情况下，如果必须使用SQL来组合问题，可以保存该问题，然后使用其结果作为查询构建器问题的起点来构建问题。这里的技巧是在GUI问题中进行聚合和分组。也就是说，使用SQL问题获取您要处理的原始数据（也许创建一个模型），然后启动一个新的GUI问题来筛选、总结和分组这些数据。

例如，要使用SQL问题构建上面创建的数据透视表，首先编写一个SQL查询来获取您要处理的原始数据

SELECT people.state,
       products.category,
       orders.subtotal,
       orders.created_at
FROM   orders
       INNER JOIN products
               ON orders.product_id = products.id
       INNER JOIN people
               ON orders.user_id = people.id