iData

计算字段

2024-8-30 Guide
  • 分析师
  • 普通用户
  • 图表设计
 About 34 min

在数据分析时，如果基础数据没有包括回答问题所需的所有字段，可以增加计算字段，增加的计算字段将作为原始明细数据字段的一部分参与运算

场景

  1. 普通字段类型转换
  • 日期格式转换: 将一个日期字段从字符串格式转换为日期格式。例如，将”20230809”转换为”2023-08-09”。
  • 数值转换: 将文本格式的数值转换为实际数值类型，如将”1000”转换为整数1000，或将”3.14”转换为浮点数。
  • 货币转换: 将一个货币值从一种货币单位转换为另一种货币单位，例如从美元转换为欧元。
  1. 字段间的运算
  • 销售金额计算: 通过乘法计算单价和数量，得出销售金额(Sales Amount = Unit Price * Quantity)。
  • 折扣后的价格: 计算应用折扣后的最终价格，如Final Price = Original Price - (Original Price * Discount)。
  • 时间差计算: 计算两个日期或时间字段之间的差值，例如计算一个项目的完成时间或订单的处理时间(Processing Time = End Date - Start Date)。
  1. 逻辑判断
  • 分类判断: 基于特定条件对数据进行分类，如Case When Age >= 18 Then 'Adult' Else 'Minor' End。
  • 评分计算: 根据多条件对记录进行评分，如计算客户的信用评分或产品的质量等级。
  • 状态标记: 基于某些业务规则为记录添加状态标记，例如，如果库存量低于某个阈值，标记为“缺货”。
  1. 汇总字段

累计总和: 计算一个列的累计总和，如SUM(Sales) OVER (ORDER BY Date)，用以分析销售趋势。 排名: 使用ROW_NUMBER()或RANK()函数对记录进行排名，如根据销售额对销售人员排名。 分区求和: 使用SUM(Sales) OVER (PARTITION BY Region)按区域对销售进行分组汇总。

# 使用须知

使用须知

对于jdbc数据源，汇总类型计算字段需要数据库支持窗口函数, 常用数据除mysql8.0之前的版本均已支持窗口函数
数据源大类上目前支持关系型数据库、文本、Http、在线表格，ES和MongoDB暂不支持，此外关系型数据库如果使用汇总类型的计算字段对数据库版本也有一定要求，
再次特别提示：MySQL需要8.0以上版本 mysql版本查询语法如下：

SELECT VERSION()
1

# 添加计算字段

在字段列表下方的计算字段栏，点击加号，添加计算字段

  • 图形化定义普通计算字段与窗口函数计算字段

计算字段编辑器如下：

  • 字段命名：必填相当于数据库表字段名，命名支持中文
  • 字段列表：为原始字段列表，点击字段自动插入字段定义编辑器
  • 计算列段列表：为其他计算字段，如果自身是汇总类型计算字段则不能引用其他汇总类型计算字段
  • 字段定义：计算字段定义表达式
  • 汇总字段开关：切换字段是否需要进行汇总
  • 汇总函数列表：常用汇总函数列表，点击函数自动插入字段定义编辑器
  • 分组字段栏：可拖拽原始0个或多个字段作为分组栏，不拖入字段则为全表汇总
  • 组内排序字段：可拖拽原始字段到组内排序，注意：如果使用的是sum、count、avg，排序之后计算值会变成累计值
  • 模式切换：普通模式、高级模式，高级模式下可以相当于用纯sql脚本定义计算字段可以是普通计算字段，也可以是带窗口函数的汇总计算字段
  • 查看字段值：计算字段定义完成之后，点击确认关闭窗口，在数据集编辑的预览查询中查看计算字段值，汇总计算字段开销比较大，当数据量比较大，而且有多个汇总字段时预览会比原始数据预览要慢

# 添加计算字段到模型

添加到模型

计算字段定义完成之后与普通字段列表一样，需要拖拽添加到数据集模型

  • 拖拽到维度之后，可以编辑维度节点设置原始数据类型
  • 日期类型计算字段拖拽到模型之后，需要编辑维度节点，设置原始数据类型为日期

# 计算字段间引用

CASE 
     WHEN #{首次下单时间} = `order date` THEN '新客'
     ELSE '老客'
END
1
2
3
4

# 动态计算

只有在分析中用到的计算字段才会在查询中构造，避免无用的运算开销

# 配套支持

过滤组合、维度成员获、指标字段统计取均已支持使用计算字段

Q&A

  1. 问: 我使用SQL数据集，这些计算字段能分分钟设计出来，还需要使用计算字段吗？
    答: 如果是非汇总类型字段(不需要使用窗口函数)，可以直接在数据集sql中添加，但是如果是汇总类型计算字段，BI引擎会根据分析中是否使用到该字段动态决定是否构造查询，相较直接在sql中增加汇总运算字段在性能上会比有好一些. 此外如果多个计算之间有运算，利用计算字段间的引用也能减少一些sql编写工作量, 比如上面案例中case when中使用了汇总字段。

  2. 问: 计算字段与数据集表达式字段有什么区别？
    答: 数据集表达式使用场景是针对汇总结果之后的字段间运算，而计算字段运行在汇总之前明细数据级别，相当于给原始表新增了列。

  3. 问: 哪些类型数据源可以使用计算字段?
    答: 数据源大类上目前支持关系型数据库、文本、Http、在线表格，ES和MongoDB暂不支持，此外关系型数据库如果使用汇总类型的计算字段对数据库版本也有一定要求，特别提示：MySQL需要8.0以上版本。

对照Tableau<15 大详细级别表达式>，进行案例挑战，

提示

本篇中使用的测试数据和Tableau的中的数据略有差异，以分析与解决问题的过程为主。

# 0. 分析数据集

超市销售订单明细表: 包含有关产品、销售和利润的信息，您可以使用这些信息来确定该虚构公司中需要改进的关键领域。

  • Row ID
  • Order ID
  • Order Date
  • Ship Date
  • Ship Mode
  • Customer ID
  • Customer Name
  • Segment
  • Country/Region
  • City
  • State/Province
  • Postal Code
  • Region
  • Product ID
  • Category
  • Sub-Category
  • Product Name
  • Sales
  • Quantity
  • Discount
  • Profit

# 1. 客户订单频率

找出每个客户订购的订单数相对简单，但是如果我们想了解订购过一个订单、两个订单、三个订单（依次类推）的客户数目，该怎么办呢？
要生成相应视图，我们必须按订购的订单数划分客户数。这是一个简单的问题，但是如果没有详细级别表达式(Tableau Lod)，按照某一度量划分另一度量将非常困难。

增加计算字段[客户下单数量]， 因为我们要按客户汇总订单数量，

  1. 首先打开[汇总字段]开关
  2. 拖拽【customer id】字段到分组字段栏
  3. 在【字段定义】中输入count(`order id`)

自助分析中配置如下

  • 拖拽上面新增的【客户下单数量】到行维，
  • 拖拽【order id】到指标，并设置汇总函数为count，
  • 图表类型选择为柱线图

# 2. 阵列分析

合作时间越长的客户对销售额的贡献越大吗？
下面的视图按照客户首次购买的年份将客户分组，以便对比各个阵列的年度销售贡献额。
每个客户的最早订单日期将体现出首次购买日期。不过，由于视图中的数据为订单明细，没有客户信息，我们需要新增一个客户【首次下单时间】字段

增加计算字段【首次下单时间】, 因为我们要按客户汇总订单数量，

  1. 首先打开【汇总字段】开关
  2. 拖拽【customer id】字段到分组字段栏
  3. 拖拽【order date】字段到组内排序字段栏，并设置升序
  4. 在【字段定义】中输入first_value(`order date`), 取组内第一个下单时间做为字段值

图表设计配置如下：

  • 拖拽【order date】到行维，并设置日期粒度为年
  • 拖拽【首次下单时间】到列维，并设置原始字段类型为日期，日期格式化为年
  • 拖拽【order id】到指标，并设置汇总函数为count
  • 图表类型选择为柱线图, 展示样式为堆叠柱状

下图中横轴为销售年份，分类为用户首次下单年份，可以看到每年中按用户首次购买年份做了分类

# 3. 每日利润 KPI

我们可以查看利润随时间的变化趋势，但如果我们想按照每个工作日的总利润来衡量成功，该怎么办？
我们可能想要了解每个月或每年的盈利天数，尤其是在我们想了解季节影响时。
下面的视图显示了在以交易级别记录基础数据的情况下，我们如何利用详细级别表达式轻松根据聚合数据创建分级（例如每日利润）。

增加计算字段【每日利润】字段，配置如下比较简单，不在赘述

图表配置如下：

  • 行维：【order date】设置日期粒度为月
  • 列维：【order date】设置日期粒度为年，【每日利润】并设置维度分组
  • 指标：【order date】汇总函数为count
  • 图表类型选择为柱线图，面积折线，
  • 样式配置：布局->拆分图表，取消图列显示

# 4. 总额百分比

每个国家/地区的收入对全球销售额有何贡献？
如果我们按照贡献百分比着色，即可看出美国对全球销售收入的贡献最大。

图表配置如下：

  • 行维：国家
  • 指标：[sales], 第二个指标也是[Sales]，值类型设置为【列占比】

# 5. 新客户争取率

各个市场总客户争取率的每日趋势如何？
了解这一数据趋势后，可帮助我们了解地区营销和销售组织在发展新业务方面的表现。线越陡，则争取率趋势越好。如果线逐渐变平，则必须采取一些措施来增加潜在客户流。 详细级别表达式可确保回头客不会误计入新客户，这是因为虽然数据按照市场和天数直观显示，但是必须在客户层面上进行评估。

增加计算字段【是否为新客】字段，该字段使用了前面定义的字段字段【首次下单时间】

CASE
    WHEN #{首次下单时间} = `order date` THEN '新客'
    ELSE '老客'
END
1
2
3
4

小技巧

case when输入有快捷模板, 输入case选case-when snippet即可

图表配置如下：

  • 行维：【order date】
  • 列维：【region】
  • 过滤：【是否新客】选择新客
  • 指标：distinct【customer id】，值类型配置为累加
  • 图表类型：柱线图，样式配置，线条样式-> 空值处理->connect连接

# 6. 对比销售额分析

找出与平均值之间的差异相对简单，但是如果您想找出与选定类别的差异，该怎么办？

增加计算字段【指定类别销售额】，开启汇总，但是不选分组字段，即全体汇总，汇总定义中使用了条件汇总，只有在【sub-category】等于环境变量${subcate}传入值时才计入汇总

sum(CASE
    WHEN `sub-category` = '${subcate}' THEN `Sales`
    ELSE 0
END)
1
2
3
4

增加数据集表达式, 因为新增字段【指定类别差额】，所有会全体汇总字段，所有数据值都相同，所以选择max或者min维持聚合后原值输出即可

sum(`sales`) - max(`指定类别销售额`)
1

新建看板如下：
左图为按类别排序的柱线图，行维【sub-category】不排序，指标选择【sales】求和升序 图表选择柱线图-主轴选柱图，样式微调->轴样式->值轴选择水平轴

右图为按类别排序的柱线图，行维【sub-category】不排序，指标选择【指定类别差额】 图表选择柱线图-主轴选柱图，样式微调->轴样式->值轴选择水平轴

看板中左图增加联动，行维联动到环境变量subcate，

Last update: August 30, 2024 11:50

概述