Metabase 数据透视表Pivot Table完整实战指南从查询构建器配置到 GROUPING SETS 源码原理【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabase数据透视表Pivot Table是 Metabase 中唯一一种能同时沿多个维度展示多个指标的表格类可视化它可以把行与列互换、对数据分组、并在分组内自动计算小计与总计。本指南以 官方文档 为主线完整讲解透视表的适用前提、创建步骤、行/列/度量三桶配置、小计与总计行为、条件格式与热力图玩法、导出注意事项并结合src/metabase/query_processor/pivot.clj等源码深入剖析透视表背后多子查询拼接与原生 GROUPING SETS两条执行路径的实现原理。读完本文你既能熟练地在 Metabase 中搭建并调优一张透视表也能理解它为何只支持查询构建器、为何需要额外的子查询来计算小计。适用范围提醒透视表目前仅支持使用查询构建器创建的问题Question不支持 MongoDB 等 NoSQL 数据库也不支持直接基于 SQL 编写的问题。透视表 vs 普通表格核心差异普通表格是一个由单元格组成的网格每个维度dimension也称作属性、group by分组字段对应一列每条记录对应一行。例如下表把Source、Plan、Created at作为维度列Sum of Seats和Count作为指标列而透视表的特点是行和列上都放维度单元格里放指标值并且可以附带维度的小计subtotal汇总行之所以叫透视pivot是因为你可以把一个维度列旋转 90 度让该列中的取值本身变成新的列标题。把取值透视成列标题在跨多个属性如时间、地区、品类分析数据时非常有用。你可以把多个维度从行透视到列也可以反过来甚至一个都不透视。透视表是 Metabase 中除普通表格之外唯一可以同时沿多个维度展示多个指标的可视化类型——例如在同一张表里同时看Count和Average of Total并分别按Category和Month分组。如何创建一张数据透视表创建透视表的前提是使用查询构建器。目前不能用 SQL 问题直接生成透视表因为 Metabase 需要改写你的 SQL 才能计算小计。如果必须用 SQL变通方案是把问题拆成两步详见文末SQL 用户的绕行方案一节。创建步骤在查询构建器中创建一个**带汇总summary且至少含一个分组breakout**的问题例如Countof orders byCategoryandMonth按类别和月份统计订单数。你还可以在一个查询里放多个指标例如Count和Average of Totalof ordersbyCategoryandMonth。点击Visualize进行可视化。点击左下角的Visualization可视化图标在侧边栏中选择Pivot table。点击齿轮图标进入配置把字段分配到三个桶bucket之一rows行、columns列或measures度量。Rows 和 Columns放维度/分组字段breakouts即你用来分组的字段如Category、Created at。Measures放汇总/指标字段metrics如Count、Average of Total。rows 和 columns 桶里都可以放多个字段但需要注意字段的排列顺序会改变表格的显示方式——每新增一个字段都会嵌套在前一个字段之下形成层级分组。另外有一个使用约束查询中的所有维度和指标必须全部出现在透视表的 rows、columns 或 measures 中你可以在小计与总计一节通过折叠行来隐藏明细但字段本身不能从透视表中隐藏。如果不想展示某个分组或指标只能把它从查询里移除无法在透视表配置里藏起来。小计与总计Totals and grand totals只要语义合理Metabase 会自动为分组行生成小计。例如在上面的配置图中行先按Source分组、再按Plan分组那么 Metabase 会为每个Source下列出各个Plan然后汇总该Source的指标值交互细节点击分组标题旁的**减号–**可折叠该组**加号**可展开保存透视表后Metabase 会记住哪些组被展开、哪些被折叠。如果想去掉总计进入透视表设置齿轮图标关闭Show row/column totals显示行/列总计开关即可。从源码看这个开关对应pivot.show_row_totals与pivot.show_column_totals两个可视化设置键见 pivot.clj默认值均为true。后端在生成查询组合时会根据这两个开关决定要不要生成行总计列总计大总计等子查询详见下文源码解析。透视表中的条件格式你可以基于条件或数值范围给透视表单元格上色需要注意Metabase 不会对小计和总计行应用格式。透视表条件格式的使用方式与普通表格完全一致可参考表格的条件格式说明。把透视表当作热力图Heatmap用利用条件格式你可以让透视表呈现热力图效果用颜色深浅表示数值强度创建一个带汇总块的查询构建器问题其中一个指标用于定义热力图单元格的强度两个分组分别定义热力图的横向与纵向坐标。将查询可视化为透视表。添加一个Color range颜色范围类型的条件格式。例如要构建一张一周内各小时活跃度热力图用按**小时hour of day和星期几day of week**分组的问题即可再配合条件格式渲染成透视表透视表的导出Export导出透视表为 XLSX 文件时有一些特殊注意事项详见导出透视表一节默认情况下 Metabase 导出透视后的结果但你也可以选择导出未透视的原始结果。导出的透视结果在 Excel 中会以平面表格呈现不是Excel 原生的数据透视表PivotTable。如果你需要 Excel 原生数据透视表应当先撤销问题中的汇总与分组导出原始未聚合的行数据再在 Excel 里自行透视。之所以不直接导出为 Excel 原生透视表是因为 Excel 并不支持 Metabase 的全部聚合函数缺少这些函数时重构出的透视表可能产生正确性问题。后端还提供了enable-pivoted-exports设置开关用于控制透视导出与透视订阅是否启用见 settings.clj。透视表的限制仅适用于 SQL 数据库需要驱动支持透视所需的特性。查询中指定的所有指标和维度都会显示在透视表中。仅适用于查询构建器创建的问题。查询构建器的问题必须包含汇总块summary block。源码级解析Metabase 是如何算出透视表的理解透视表在查询处理器Query Processor中的实现能帮你更准确地预判性能与行为。两条执行路径多子查询拼接 vs 原生 GROUPING SETS透视查询的入口是run-pivot-query它按条件在两条路径之间派发见 pivot.clj多查询路径multi-query这是经典实现。它根据行/列维度的组合把一个大透视查询拆成一系列子查询逐个在数据仓库上执行再把结果行拼接起来——类似clojure.core/lazy-cat逐段拼接。源码注释里还自嘲式地吐槽过为什么不干脆生成一个大 UNION 查询之所以不这么做是因为每个子查询对应不同的分组组合直接 UNION 反而更复杂。原生路径native pivot在开启use-native-pivot-tables设置、且驱动支持:native-pivot-tables特性时透视查询会翻译为 MBQL5 的:pivot子句最终由 SQL 编译器生成一条使用GROUPING SETS的原生 SQL 查询执行不再逐个子查询拼接。两条路径在clojure.test测试中会默认同时运行并进行对等性校验parity check一旦结果不一致会在测试里报失败用于防止两条路径的回归差异见 pivot.clj。子查询组合与小计的生成逻辑多查询路径的核心是breakout-combinations函数见 pivot.clj。它根据分组数量、行维度、列维度以及是否显示行列总计枚举出需要生成的全部子查询组合。例如分组数为 4、行维度为[0 1 2]、列维度为[3]时会生成主数据查询包含全部分组[0 1 2 3]各层行小计嵌套在列里[0 _ _ 3]、[0 1 _ 3]右侧行总计[0 1 2 _]行总计内部的行小计[0 _ _ _]、[0 1 _ _]列总计即大总计行[_ _ _ 3]右下角的大总计[_ _ _ _]当没有任何行/列配置如公共链接、嵌入端点时则会用powerset生成全部分组组合。每个子查询通过keep-breakouts-at-indexes只保留对应下标的分组见 pivot.clj。pivot-grouping标记每行属于哪个分组组合拼接起来的结果行如何区分这是明细行还是某个层级的小计答案在group-bitmask见 pivot/common.clj与后处理中间件 pivot/middleware.clj 中结果会附加一个pivot-grouping列其值是一个位掩码bitmask按当前行包含了哪些分组下标逐位置位语义与 PostgreSQL 等数据库对GROUPING的处理方式一致。前端拿到该列后即可判断某行属于哪一级汇总进而渲染折叠/展开结构。特性检查为什么只支持 SQL 数据库查询处理器中间件 check_features.clj 会遍历查询的阶段stage一旦发现存在:pivot子句就要求驱动支持:native-pivot-tables特性否则抛出unsupported feature错误。因此透视表只对实现了该特性的 SQL 驱动可用这也是 NoSQL 数据库如 MongoDB无法使用透视表的根本原因。子查询行数上限为防止拼接过多子查询导致内存膨胀*pivot-max-result-rows*将每个子查询的结果行数上限设为 200000并且会按聚合数量进一步均分floor(pivot-max-result-rows / num-aggregations)见 pivot.clj。当结果被截断时响应数据中会带上pivot_rows_truncated标记。SQL 用户的绕行方案如果你必须使用 SQL且 SQL 查询不含参数可以按以下方式借助查询构建器生成透视表先保存这个 SQL 查询然后以它的结果作为起点新建一个查询构建器问题。关键是在查询构建器里完成聚合与分组让 SQL 问题只负责取出你想要的原始数据也可以先把它存成模型 Model再在查询构建器的新问题里做筛选、汇总与分组。这样既保留了 SQL 的灵活性复杂取数又能享受到查询构建器为你自动计算小计、生成透视表的能力。【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabase创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考