Pivot table（数据透视表（Pivot table））¶
A pivot table is used to display aggregated data over an object set in a table. Object properties are chosen as row and column properties; the resulting data is grouped by these object properties and aggregated based on the configuration you set in the Editor panel. Pivot tables are mainly configured in three ways: row properties, column properties, and aggregations.
Row properties¶
Row properties are the properties with values that will be the row headers of the table. Rows will only appear in the table for sets of values with data.
Column properties¶
Column properties are the properties with values that will be the column headers of the table. Columns will only appear in the table for sets of values with data.
Aggregations¶
Aggregation configurations allow you to set the way you want the data to be aggregated for each cell, grouped by row and column properties. The pivot table card provides numerous configuration options for aggregations, such as:
- Count
- Sum of a numeric property
- Min of a numeric property
- Max of a numeric property
- Approximate unique count of a property
Alternatively, you can select Switch to formula metric to write your own metric composed of the aggregations listed above. You can also select +Add Series to configure as many aggregations as you want. The aggregations will appear next to each other with their own column headers below the headers representing column property values.
For example, suppose you have a dataset of daily precipitation by city in the United States. If you select city as a row property, year as a column property, and sum of precipitation as an aggregation, then the column headers will be New York, Chicago, and Los Angeles, the row headers will be 2015, 2016, and 2017, and the values will be the total precipitation in that city during that year.
The only difference between row and column properties is on which edge of the table they appear. For example, if you have configured row properties A and B with no column properties, the data will be the same as if you had row property A and column property B, or column properties A and B with no row properties. Only the layout of the data will be different.
You can use the pivot table to filter data. Select individual cells or entire rows and columns by clicking on the headers. Clicking Drill down to selection will filter the data to those with the property values for the rows and columns you selected. Hold down the shift or command key to select multiple regions of the table.
You can also activate the toggle to Show grand totals for rows or columns to show aggregates on the right side (for rows) or bottom (for columns). Be aware that these aggregates apply over all of the data for that set of row or column properties; it is not an aggregate of aggregates. Select the info icon next to this option for a deeper explanation of how grand totals are computed.
If you would like to do further processing on the columns of a pivot table, or use advanced formatting options like conditional coloring, you can convert a pivot table to a transform table.
Input type¶
Object set
Output type¶
Pivot table, object selection
Usage information¶
| Functionality | Availability |
|---|---|
| Standard Quiver card | Supported |
| Transform table transform | Unsupported |
中文翻译¶
数据透视表（Pivot table）¶
数据透视表用于在表格中展示对象集（object set）的聚合数据。选择对象属性作为行（row）和列（column）属性后，系统会根据这些对象属性对数据进行分组，并按照您在编辑器面板中设置的配置进行聚合。 数据透视表主要通过三种方式进行配置：行属性、列属性和聚合设置。
行属性（Row properties）¶
行属性是指其值将作为表格行标题的属性。只有包含数据的值组合才会在表格中显示为行。
列属性（Column properties）¶
列属性是指其值将作为表格列标题的属性。只有包含数据的值组合才会在表格中显示为列。
聚合设置（Aggregations）¶
聚合配置允许您设置每个单元格的数据聚合方式，并按行和列属性进行分组。数据透视表卡片提供了多种聚合配置选项，例如：
- 计数（Count）
- 数值属性求和（Sum of a numeric property）
- 数值属性最小值（Min of a numeric property）
- 数值属性最大值（Max of a numeric property）
- 属性近似唯一计数（Approximate unique count of a property）
此外，您也可以选择切换为公式指标（Switch to formula metric）来编写由上述聚合组合而成的自定义指标。您还可以点击+添加系列（+Add Series）来配置任意数量的聚合。这些聚合将并排显示，并在代表列属性值的标题下方拥有各自的列标题。
例如，假设您有一个美国各城市每日降水量的数据集。如果您选择 city 作为行属性，year 作为列属性，sum of precipitation 作为聚合，那么列标题将是 New York、Chicago 和 Los Angeles，行标题将是 2015、2016 和 2017，而值则是该城市在该年份的总降水量。
行属性和列属性之间的唯一区别在于它们在表格中的显示位置。例如，如果您配置了行属性 A 和 B，而没有列属性，那么数据结果将与配置行属性 A 和列属性 B，或者配置列属性 A 和 B 而没有行属性时完全相同。只是数据的布局会有所不同。
您可以使用数据透视表来筛选数据。通过点击标题，可以选择单个单元格或整行整列。点击向下钻取至所选内容（Drill down to selection）将筛选数据，仅显示您所选行和列属性值对应的数据。按住 Shift 或 Command 键可选择表格的多个区域。
您还可以激活显示总计（Show grand totals）开关，为行或列在右侧（针对行）或底部（针对列）显示聚合总计。请注意，这些总计是针对该组行或列属性的所有数据计算的，而非聚合值的再聚合。点击此选项旁边的信息图标，可获取关于总计计算方式的更详细说明。
如果您希望对数据透视表的列进行进一步处理，或使用条件着色等高级格式选项，可以将数据透视表转换为转换表。
输入类型¶
对象集（Object set）
输出类型¶
数据透视表，对象选择（Object selection）
使用信息¶
| 功能 | 可用性 |
|---|---|
| 标准 Quiver 卡片 | 支持 |
| 转换表转换 | 不支持 |