枢轴表

了解如何在 Metabase 中使用不同数据库创建枢轴表。

什么是枢轴表? 枢轴表是一种以表格形式汇总和分组数据的方法。它们是分析师工具包中的宝贵工具,因为它们是呈现和重新安排大量信息的有效方式。以下是它们的工作原理

在 Metabase 中创建枢轴表时不支持的数据库

Metabase 中目前不支持以下数据库的枢轴表

  • Druid
  • MongoDB

枢轴表适用于所有其他官方支持的数据库

枢轴表 vs. 普通表

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

枢轴表是一种对行和列进行分组的表格,并包含带有这些分组的汇总值的汇总行。这些汇总值通常被称为小计和总计,尽管这些汇总也可以是其他值,例如平均值。

之所以称为枢轴表,是因为您可以将列旋转(“枢轴”)90 度,使该列中的值本身成为列标题。将值旋转为列标题在尝试跨多个属性(如时间、位置和类别)分析数据时非常有用。您可以将多行旋转到列中,反之亦然,或者根本不旋转任何行。

但这听起来很抽象,所以让我们通过一个示例来让您了解枢轴表的工作原理。

如何创建枢轴表:示例

首先,假设我们想知道

  • 订单带来了多少年收入
  • 每个州(即我们的客户住在哪里?)
  • 以及这些订单如何按产品类别细分

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

Query Builder with a preview of the question

在这里,我们从 Orders 表中获取数据并汇总记录。我们按 User → StateCreated At(按年份)和 Product → Category 对订单进行分组。对于每个组(例如 2017 年的阿拉斯加),我们计算订单数量并汇总这些订单的小计。(请注意,即使我们只在数据部分选择了 Orders 表,Metabase 也会自动连接 ProductsPeople 表以获取州和类别数据。)

结果表是一个普通表,每行包含州、年份和产品类别的每种组合。

现在,假设对于每个州,我们还想知道每个州的年度小计总和(例如,阿拉斯加的 Doohickey 产品订单在所有年份中赚了多少钱?)。要找出答案,我们可以自己加总小计,或者使用枢轴表为我们计算该数字。在屏幕左下角,点击 可视化 > 枢轴表

The pivot table is born.

在我们的枢轴表中,Metabase 将行、列和值设置如下

  • 行:User → StateCreated At(按年份)
  • 列:Product → Category
  • 值:CountSum of Subtotal

与普通表一样,枢轴表让我们能够看到,例如,在 2020 年,我们在阿拉斯加 (AK) 的客户总共购买了 11 件 Doohickey 产品,价值 867.63 美元。但现在枢轴表已经将与阿拉斯加相关的行分组,并给出了这些阿拉斯加行的小计,使我们能够看到问题的答案:阿拉斯加人在 2016-2020 年购买了 103 件 Doohickey 产品,总计 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.

与普通表一样,我们有一些排序和格式化选项,并且我们可以点击表中的值以调出钻取菜单,这将让我们钻取数据

Sorting and formatting options, as well as the action menu.

如何创建枢轴表:限制

枢轴表仅适用于支持连接和表达式的关系型数据库,因此您无法将其与 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

请注意,我们在这里只是获取记录;没有汇总或分组。接下来,我们将该 SQL 问题保存(此处为 Raw data for pivot table),然后启动一个使用该问题结果作为其起始数据的新查询构建器问题。

Picking a saved question as your starting data.

现在我们可以对结果进行计数、求和和分组

The Query Builder summarizing data from a saved question.

当我们可视化这个问题时,现在我们将能够使用枢轴表可视化来查看组小计和总计。

延伸阅读

© . All rights reserved.