交叉表/数据透视

概要

通过本章学习，您将掌握数据透视的基本原理，交叉表的使用，表面快速计算(包含下面知识点):

• 交叉表样式配置
• 隐藏维度、隐藏指标
• 行/列占比(子分组)
• 总占比
• RANK/DENSE RANK
• ROW_NUMBER
• 累加/累加占比
• 高级统计
  • 环比/滞后比较
  • 同比
• 行/列汇总
• 行/列小计
• 表达式

数据透视模型^v1.8

v1.8之后原来仅交叉表支持的值类型变化操作，拓展到所有图形，这样带来的好处是:

• 可以更方便的以交叉表为数据模型设计数据，然后把数据映射到其他图形
• 避免了原来在交叉表配置了值类型变化之后，切换到其他图之后又没有变化操作，两种图形产生值内容不一致的问题

一个普通的交叉表包含行表头, 列表头与中间的汇总指标数据

提示

所谓行表头的行是相对于数据来说的, 一行数据的表头, 而不是表头本身以行形式排列

当我们有了原始的交叉表表格之后，可以对表格上的数据(视图数据)进行二次加工，快速值类型转换计算行列占比、总占比等

隐藏维度、隐藏指标

使用场景 参与计算又不需要展示, 如:

• 同比(需要去年的数据，但是不需要展示去年的数据)
• 值类型表达式(两个指标的运算，但是不需要展示原始指标)

交叉表数据计算

1. 交叉表所有值类型计算与转换都是在表格已有值之上进行，不会对数据原进行额外的查询

默认隐藏的单元格不参与快速计算，如果需要让隐藏的单元格参与计算，需要开启对应配置中包含隐藏配置

维度隐藏

隐藏的数据不会在数据源层过滤, @1表示展开之后的第1个值, 常用于同环比隐藏

指标隐藏

指标不设置隐藏条件开启隐藏，所有指标列均会隐藏

维度条件隐藏指标

从v1.13开始支持配置带维度条件的指标列隐藏

值类型变化(快速计算)

在原始值基础之上, 只需简单对指标进行值类型设置之后原始数值将进行相应换算成占比, 同比, 环比等;

列占比

原始指标列可以重复使用, 为了展示效果, 下图对开销指标使用了两次, 第一次列展示原始值, 第二列换算成列占比

行占比

行占比子分组

行列占比统计子分组占比

如: 统计每年统计每月成本占比, 操作步骤如下:
1 值类型选择更多

2 跳出弹出框, 值类型依旧选择列占比, 然后子分组选择年对应的字段year

下图 USA 的列占比分成了按销售年份分成了 2 组, 为了让结果更加直观, 提前开启了列汇总功能, 显示 200%

总占比

Rank/Dense Rank/Row Number

改变值类型为排序

• Rank 为不排除重复元素排序，有两个并列第一，则无第二
• Dense Rank 为排序重复元素排序
• 可在调整排序的行粒度(子分组)
  

没有值则不参与计算

累加、累加占比

高级统计

环比, 同比统计属于高级统计操作, 交互稍微复杂些

环比/滞后比较

点击值类型里面更多操作, 弹出高级对话框, 值类型选择滞后比较

1. 先展示滞后值类型为原始值(值类型一栏不选即可), 从下图可以看出, 原来的成本列相对于原始值在列上有滞后量为 1 行的错位(滞后量可自行调节, 可正可负)

2. 计算环比的差异量 调整值类型改成差值

3. 计算变化率 调整值类型改成变化率

4. 列滞后/环比支持指定匹配列
   指定了比较列之后，比较列必须相等
   

同比

同比一般情况下是今年第 n 月与去年第 n 月比。设计时需要有一列包含年信息的列. 在高级值类型操作框, 选择值类型为同比, 日期选择包含年信息的列 year, 日期格式与实际日期格式保持一致, 便于日期解析, 同样先可以选值类型有,值, 差值, 变化率三个选项:

差值
调整值类型改成差值

变化率
调整值类型改成变化率

最后演示一个极端的例子, 有时候用户的表格里面可能包含完整日期列the_date如下:

而同比的值搜索定义为

查找上一年份所在行, 该行需要求除年份外其他维度列相同, 这个时候显然日期列会干扰同比行搜索, 考虑到这种情景, 我增加了忽略列选项,用户只需在刚才的高级搜索框里面设置 the_date(日期) 为忽略比较列即可:

计算方向

除了三个占比(行占比、列占比、总占比)之外，其他的几种值类型变化都可以设置计算方向，默认计算方向为列

其他配置

包含隐藏

考虑到之前维度隐藏设置，默认被隐藏的值不参与计算，如果需要隐藏的单元格参与运算可以打开包含隐藏配置

显示原值

值类型变化之后，如果需要显示原值可以开打显示原值配置，此外还可以配置原值格式化

举个栗子

分类别统计销售开销topN前五的产品类别

1. 行维：产品类别 + 子类别
2. 指标：
   • 开销(排名)
     • 值类型选择为Rank + 子分组产品类别 + 排序为降序,
     • 值过滤设置<= 5
     • 设置指标隐藏
   • 开销(原值)

值类型表达式

值类型表达式可以实现上述所有类型的值变化，但是常用于依赖计算，逻辑判断，或者输出文本值

提示

表达式依赖的指标列必须在表格中原始存在
可以设置隐藏

逻辑表达式

数据集表达式vs值类型表达式

• 计算粒度
  • 数据集表达式仅支持自身粒度内汇总计算
  • 值类型表达式可以在值类型变化(任意粒度)之上二次计算
• 原始值依赖
  • 数据集表达式不需要原始汇总提前配置
  • 值类型表达式需要依赖的汇总在表格中存在

汇总(总计)

表格汇总功能, 可以对行/列上进行: 求和sum, 平均avg, 最大max, 最小min统计，企业版 v0.6 新增表达式高级汇总

汇总表头名设置可在图表微调配置中修改

列汇总表达式

单个聚合函数语法： agg('指标', ['列维度'...])， 可通过聚合栏辅助输入

• agg 代表聚合类型
• 第一个参数为统计指标名称，可通过指标辅助输入，需要用单引号包裹输入, 交叉表最终输出的指标表头保持一致
• 第二个参数为列维度表头名称数组，可指定多个维度，用于改变统计粒度，可留空(或者输入null), 如: sum('开销')则统计粒度为默认粒度
• 列汇总不对应任何行维度，故行维度粒度无法调整
• 维度数组输入为空数组[]代表统计所有行/列维层级对应数据
• 如果用户输入的维度数组在表格中找不到对应维度定义，测默认匹配所有维度
• 多个聚合函数直接可以进行算数运算，如：sum('开销', ...)/sum('销售额', ...)
• 支持单行注释 // 与多行注释 /\* \*/
• 表达式书写完毕之后可以进行语法检查

提示

语法检测只能检查表达式书写是否有明细的语法错误，如：不支持的聚合函数、需要英文括号的地方输入了中文括号, 不能够检测输入的维度数组成员是否正确，也有可能语法检测正确之后发生运行时异常

行汇总表达式

单个聚合函数语法： agg('指标', ['行维度'...])， 可通过聚合栏辅助输入

• 行汇总表达式与列汇总表达式基本一致，唯一的区别在于列汇总不对应任何行维度，故列维度粒度无法调整

总计

总计既没有行维，也没有列为，总计只有在同时存在行列汇总时才会显示

列小计

简单列小计

点击指标编辑按钮，弹出下拉框选择列小计，进入列小计设计：

• 小计级别：指定需要做小计的层级，可多选，
  • 下列演示：开销指标上选定了在年-性别级别之上进行小计；销售额指标上选定了在年级别之上进行小计
  • 小计级别选定之后就确定了默认的聚合粒度，如：下例中年-性别小计级别对应的粒度为： 行维度年-性别 + 固定列维度会员等级[Golden]
• 计算类型：选择简单聚合或复杂聚合，简单聚合只汇总该级别对应小计单元格的值，如下图中性别小计，会统计性别维度下所有该列（Golden+开销）对应的数据
• 格式化：列小计可单独设置格式化，如不设定则使用上级值默认的格式化参数
• 聚合参数：可选 sum/min/max/avg

高级列小计

如上所述简单列小计统计的单元格范围是固定的，用户只能修改聚合类型，但是如果需要改变统计粒度，又或者是统计两个不同指标之间的算数运算值，则需要用到表达式小计
小计计算类型下拉选择复杂聚合，进入小计表达式编辑：

单个聚合函数语法： agg('指标', ['行维度'...], ['列维度'...])， 可通过聚合栏辅助输入

• agg 代表聚合类型
• 第一个参数为统计指标名称，可通过指标辅助输入，需要用单引号包裹输入, 如交叉表最终输出的指标表头保持一致（出现重复指标输入会有后缀）
• 第二个与第三个参数为行维度表头名称数组与列维度表头名称数组，可指定多个维度，用于改变统计粒度，两个参数可留空(或者输入null), 如: sum('开销')则统计粒度为默认粒度

理解默认颗粒度

// 上面的小计单元格 sum('销售额') = sum('销售额', null, null) = sum('销售额', ['年'], ['国家’])

粒度提升

小计粒度改变只能在默认改小计级别默认粒度上放大上卷，不能下钻，如：小计级别['年', '性别']，可以上卷为['年'], 不能下钻到['年','性别','国家']，因为该小计上没有任何对应的国家维度值；

• 维度数组输入为空数组[]代表统计所有行/列维层级对应数据
• 如果用户输入的维度数组在表格中找不到对应维度定义，测默认匹配所有维度
• 多个聚合函数直接可以进行算数运算，如：sum('开销', ...)/sum('销售额', ...)
• 支持单行注释 // 与多行注释 /\* \*/
• 表达式书写完毕之后可以进行语法检查

// 在小计栏统计月占比
sum('开销', ['年', '月']) / sum('开销', ['年']);

Tips

语法检测只能检查表达式书写是否有明细的语法错误，如：不支持的聚合函数、需要英文括号的地方输入了中文括号, 不能够检测输入的维度数组成员是否正确，也有可能语法检测正确之后发生运行时异常

交叉表样式

交叉表布局

非即时生效

交叉表布局配置为非即时生效配置，即变更配置之后需要点击图表预览重新渲染图表

汇总前置

• 列汇总前置
• 行汇总前置
  

转置

• 转置

对齐指标

单个指标不展示值表头

存在列维，且只有一个指标时，如果觉得指标名称重复出现不太好看，可以配置关闭单指标显示值表头开关，下过如下

值样式

值样式包含百分比样式, 分段样式, 当设置值显示百分比的时候自动映射百分比样式;

百分比样式

当指标格式化为百分数之后，使用百分比样式可以快速设置指标样式，包含正负值背景颜色、字体颜色、图标、图标颜色，其中图标配置从2.0版本开始支持

分段样式

• 分段配置中上下限，可是输入常数，也可以通过下拉选择维度值，动态的取指标对应的维度作为动态上下限(前提条件是维度必须为可比较的数值)
• 可以仅配置上限或下限，让分段为开区间
• 其中图标配置从2.0版本开始支持,

热力图

通过表格值样式热力图, 用户可以迅速定位表格中值分布, 热力图配置中, 字体颜色, 单元格底色都有默认值, 最小值与最大值能自动计算获取; 没有特殊需求均可不做设置

热力图效果如下:

• 值样式热力图支持配置计算范围:行、列、全体

• 值样式展示形式增加渐变柱、纯色柱
  

从2.1开始支持区分热力图中正负值颜色配置

条件样式

通过条件样式配置维度条件、指标条件精确定位指标单元格, 如：

• 相同的指标，不同的列维(国家、部门)设置不同的异常阈值

边框

交叉表支持单独配置是否显示左右边框与上下边框，注意上下边框关闭时表头和最后一行数据行下边框不会隐藏，如果需要不显示所有边框，可以将边框线条宽度设置为0

表头

固定前N列

配置固定前N列实现表头固定，默认前N列包含指标列，如果不需要固定指标列，可以关闭固定指标开关

列宽与换行

表头换行、在行维与指标上设定列宽， 默认情况下单元格内容均不换行，如果配置了列宽之后希望换行可以打开允许换行开关

交互排序

交互排序是指点击值表头，实现排序设定的效果，注意

• 如果行维度有排序的情况下优先根据行维排序
• 指标列排序只能选定一个列进行排序
• 关闭排序交互功能之后可以不显示排序头标

表头字体

列表头与行表头字体可以独立配置

页脚

是否显示页码

默认情况下表格会根据容器高度自动分页，通过是否显示页脚或是否显示页码关闭页码，可以实现表格不分页

页码样式调整

支持调整

• 页码按钮数量
• 页码按钮背景色、激活背景色，字体等

滚动条

在表格不分页的情况下可以配置滚动条颜色，这在大屏样式配置时非常有用

自动滚动

交叉表支持开启自动滚动, 仅需设置滚动条 -> 取消页码显示 -> 打开自动滚动

• 支持首尾停顿时间设置
• 支持鼠标悬停暂停滚动
• 支持配置滚动速度
• 开启自动滚动的情况下，可以配置滚动条颜色为最后一个透明色隐藏滚动条

序号配置

其他配置

• 列汇总表头
• 行汇总表头
• 小计

表格导出 Excel 兼容

除了值样式目前在表格导出的时候不能保持一致之外, 值类型, 汇总在导出的时候都能在 Excel 里面体现出来, 后续值样式也会实现导出兼容.

汇总下推数据源^1.13

汇总计算背景

在此之前行/列汇总、小计、合计汇总计算仅针对表格上已有的数据(视图数据/表面数据)进行二次处理从而实现汇总功能

• 好处是对于求和类计算可以不用多次查询数据源，从而提高了图表性能
• 但是遇到一些必须要在原始数据之上计算的数值就会变得无能为力了

如下图所示在订单表数据上统计用户数，汇总方式为count(distinct userid)，白色单元格的数值为查询出来的精确结果，

select member_card, channel, year, month,
       count(distinct userid)
  from table
 group by member_card, channel, year, month

我们知道一个用户可能在OFFLINE、ONLINE不同渠道下过订单，在一年之中会员等级也可能有升级或降级，因此小计和列汇总计数的时候不能简单做求和运算，而是需要提升汇总颗粒度再此查询

-- 列小计统计
select member_card, year, month,
       count(distinct userid)
  from table
 group by member_card,  year, month
 
-- 列汇总统计
select year, month,
       count(distinct userid)
  from table
 group by year, month

汇总同环比等值类型变化

深入使用过汇总功能的用户知道汇总单元格不能支持占比、同环比之类的值类型变化快速计算，下表中的小计和汇总行的同、环比在之前的单次查询中也无法计算

功能引入

为了解决此类问题，本次更新中新增了汇总下推数据源计算的功能，在遇到count/distinct，数据集表达式等必须下推计算的汇总会自动下推计算，下面是汇总下推数据源实现的功能清单

• 支持行汇总、列汇总
• 支持小计
• 支持数据集表达式下推
• 下推汇总支持值类型配置
• 汇总表达式支持下推计算
• 普通汇总设置强推数据源计算
• 预览查询支持多个查询

支持数据集表达式下推

对于数据集表达式类型汇总，值类型增加原类型，在汇总粒度上沿用原来的汇总计算公式

下推汇总支持值类型配置

• 通过汇总计算值类型配置，轻松配置汇总占比、同环比之类的值类型变化快速计算
• 对于sum类型的汇总计算引擎不会自动下推数据源，可以通过数据源聚合开关下推计算，开启下推之后的即可汇总支持数据类型配置

预览查询支持多个查询

一个交叉表综合行汇总、列汇总，行小计、列小计、列汇总与行小计交叉、列小计与行小计交叉、列小计与行汇总交叉、总计，需要进行9次查询

提示

因此，请在不需要的汇总下推的时候尽量使用表格结果集进行配置