FineBI常用函数实战：聚合粒度、空值与DEF同环比排查

上周有人在群里问我FineBI 里算同比到底该用DATEDIF还是DEF我翻出自己那份记了快两年的函数笔记发现最早一条写的是“SUM和明细字段别混着写”旁边画了个大大的感叹号。那份笔记现在有六七十条按聚合、文本、日期、逻辑、分析函数分了类每条后面都跟着一行“踩坑备注”。这篇就把这份笔记里最常用的那部分整理出来顺带把每条函数背后的口径逻辑和实操细节讲清楚。这是一份给“能看懂字段、但写公式就发怵”的同学准备的常用函数记录也是一份给已经在写公式、却经常被“数字对不上”折磨的从业者的排查清单。里面不会有函数大全式的罗列只会保留真正在报表里高频出现的那几十个以及它们最容易翻车的地方。FineBI 的公式体系跟 SQL 思路很像但并不完全一致很多坑不是语法错而是粒度错、空值错、口径错语法检查一路绿灯数字出来是另一回事。1. 先说清楚这份记录是怎么攒出来的1.1 一个让我彻底重视函数的下午最早接手的一个销售看板指标很简单销售额、订单数、客户数、客单价。四个指标写完只花了半小时验收时业务方指着客单价说“这个数不对比我自己算的高”。我一看公式SUM(销售额)/COUNTD(订单编号)逻辑上没毛病。问题出在数据集里有一批“退货单”金额是负数销售额被冲减了但订单编号还在分母没跟着减客单价自然偏高。改法很简单把分母换成COUNTD(IF(销售额0, 订单编号, NULL))数字立刻对上了。这件事之后我养成了一个习惯每写一个函数先在纸上写清楚它“数的是什么、不数什么”。SUM不数空值但数负数COUNT数非空行COUNTD数非空去重值AVG的分母是非空行数而不是总行数。这些规则单看都是常识凑在一个公式里互相嵌套的时候就特别容易出错。所以我那份笔记的每一条后面都强制要求自己写一行“不包含什么”。1.2 这份记录适合谁、怎么用如果你刚开始用 FineBI 做自助数据集和仪表板建议按“聚合 → 逻辑 → 日期 → 文本 → DEF”的顺序看前四类是日常八成场景的覆盖DEF 属于进阶等你遇到“占比”“排名”“同环比”“去重后求和”这类问题时再回头翻。如果你已经能独立写公式可以直接跳到第 3、7、8 章那里集中了粒度、空值和排查的内容是我认为最容易出问题也最省时间的部分。需要提前说明一点FineBI 不同版本之间个别函数的参数顺序、单位缩写、返回值类型会有微调比如日期差函数里“月”的单位到底是大写M还是小写m不同版本文档写法不完全一致。我的处理办法是写的时候盯着公式编辑器左侧的语法提示它会把你当前版本支持的参数格式直接列出来比翻文档快得多。下面所有写法都是我在实际版本里跑通的但你在自己环境里用之前还是建议先用一行两行数据验一下。2. 函数体系先摸清六类函数各管什么2.1 六类函数的分工与选择顺序我把 FineBI 里常用的函数按“它解决什么问题”分成六类这个分类跟官方文档的排布不完全一样但更贴近写报表时的思考顺序类别典型函数解决什么问题使用频率聚合类SUMAVGCOUNTCOUNTDMAXMINMEDIAN把多行压成一行极高逻辑类IFANDORNOTISNULL分支判断、空值兜底极高日期类TODAYNOWYEARMONTHDATEDIFDATEDELTADATE时间口径、同环比、账龄高文本类LEFTRIGHTMIDFINDREPLACESUBSTITUTETRIMSPLIT脏数据清洗、编码拆解高数学类ROUNDINTABSMODCEILINGFLOOR精度控制、取整口径中分析类DEFDEF_ADDDEF_SUBEARLIER跨粒度计算、占比、排名中高选择顺序上我遵循一个原则能用聚合解决的不用 DEF能用简单IF解决的不嵌套三层能在数据集里算的不放到组件里算。原因很实际越靠前的写法越容易被人看懂也越容易在换人维护的时候不出事。一个用DEF写出来的占比公式三个月后自己都得盯着看半分钟而一个SUM(IF(...))/SUM(IF(...))的结构谁接手都能秒懂。2.2 公式编辑器里三个容易被忽略的规则第一个规则是聚合函数自动带分组。在 FineBI 里写SUM(销售额)它不是一个全局总和而是跟着组件当前的维度走。你拖了“门店”进来它就是每个门店的销售额你把“门店”换成“月份”它自动变成每月销售额。很多人以为是函数变了其实是维度变了。理解这一点就不会再问“为什么同一个公式在两个组件里结果不一样”。第二个规则是明细字段混进聚合公式会改变分组。比如你写销售额 / SUM(销售额)想算占比语法上可能过得去但销售额是个明细字段它会把每一行都当成一个独立分组结果要么全是 1要么行数暴涨。正确写法是SUM(销售额) / DEF(SUM(销售额))或者用聚合套聚合的方式把分母固定住。这条我在第 3 章会展开。第三个规则是空值参与运算会传染。NULL 1还是NULLNULL 100还是NULLIF判断里NULL既不等于真也不等于假。所以任何涉及“可能为空”的字段都要先用ISNULL兜一层。这个习惯能挡掉后面八成的诡异问题尤其是从数据库同步过来的宽表外键关联不上就是空业务上叫“未分类”公式里叫“炸弹”。3. 聚合函数粒度对了数字才对3.1 五个高频聚合函数的边界SUM是最没脾气的但它有两个特性要记住空值不参与计算负数照算不误。所以算“销售总额”时如果有退货冲减结果是净额这是对的但如果业务方要的是“毛销售额”就得写成SUM(IF(金额0, 金额, 0))。AVG的坑在分母。它除以的是非空行数不是总行数。如果某个门店某天没有数据这一行可能压根不存在也可能存在但值为空两种情况算出来的平均值完全不同。做“日均销售额”的时候我一般不用AVG而是用SUM(销售额)/COUNTD(日期)显式指定分母这样口径写在明面上谁都能复核。COUNT数非空行数COUNTD数非空去重值MAX和MIN除了求极值还经常被拿来“取最新状态”比如MAX(更新时间)找到每个客户最近一次下单时间。MEDIAN用得少但在处理金额分布时很好用平均值容易被大客户拉高中位数更能反映普通客户的真实水平做客户分层阈值的时候我会两个都看一眼。3.2 去重计数 COUNTD 的三个真实坑第一个坑是空值。COUNTD(客户编号)不会把空值算进去这通常是对的但如果你要统计“包含未识别客户的总记录数”就得用COUNT而不是COUNTD。第二个坑是性能。COUNTD底层要做去重数据量上到千万行级别时明显比SUM慢。我做过一个对比同一个明细表SUM秒出COUNTD要等几秒到十几秒。所以如果一个指标能用“标记位求和”替代我会优先替代比如“是否下单”这个 0/1 字段SUM(是否下单)和COUNTD(IF(是否下单1, 订单号, NULL))在语义上接近但前者快得多——前提是“一笔订单一行”这个粒度成立如果存在一单多行SUM就会重复计数这时候只能老老实实用COUNTD。第三个坑是条件去重。统计“复购客户数”写法是COUNTD(IF(订单数2, 客户编号, NULL))关键在于不满足条件时返回NULL而不是0或空字符串。返回0的话所有客户编号都会被计成一个“编号为 0 的客户”结果永远多 1。3.3 聚合与明细混用的报错根源前面提过销售额 / SUM(销售额)这种写法的问题这里展开说清楚。FineBI 的计算字段大致分两种聚合计算和明细计算。聚合计算返回的是“按当前维度汇总后的一个值”明细计算返回的是“每一行一个值”。两者混在一个公式里引擎必须做一次隐式转换转换规则是“把明细字段也加入分组维度”于是你本来想看“每个门店的占比”结果变成了“每一行占它自己的 100%”。正确的占比写法有三种我按推荐顺序列一下写法一推荐结构最清晰 SUM(销售额) / DEF(SUM(销售额)) 写法二维度固定时更稳 SUM(销售额) / SUM(DEF(SUM(销售额), [门店])) 写法三总量不变的场景最简单 SUM(销售额) / SUM(销售额) 配合快速计算-占比写法一里DEF(SUM(销售额))不带维度参数表示“在整个数据集范围内求总和”作为分母固定不动分子跟着组件维度变化占比就出来了。写法二适合你只想在门店这个层级上算占比、不想被其他筛选维度干扰的场景。写法三是很多人不知道的省事办法FineBI 的快速计算里直接有“占比”选项能点就别写公式。注意任何百分比指标算完之后记得在组件里设置显示格式为百分比并保留一位小数别让它显示成0.3456789业务方看到长串小数会本能地不信任这个数。4. 文本与脏数据处理报表可信度的第一道闸门4.1 截取与定位的组合拳现实里的编码字段基本都是“拼接型”的比如门店编码SH001-华东-直营你要拆出区域。这时候靠LEFT和RIGHT硬切是不行的因为每段长度不一样得用FIND定位分隔符再配合MID截取。取区域第二段 MID(门店编码, FIND(门店编码, -) 1, FIND(门店编码, -, FIND(门店编码, -) 1) - FIND(门店编码, -) - 1)上面这行写法能跑但可读性极差维护的人看到会骂人。我的建议是分两步走先在自助数据集里加一个“第一分隔符位置”的辅助字段再基于它算第二段虽然多一列但三个月后还能看懂。如果分隔符结构固定是三段用SPLIT会舒服很多直接按分隔符切开取第几段就行具体参数顺序看编辑器提示。4.2 替换、去空格与不可见字符脏数据里最隐蔽的是空格。用户从别处复制过来的名称前后可能带普通空格、全角空格、换行符肉眼看不出来但分组的时候华东和华东会被当成两个不同的值报表里就出现两个“华东”一个有数一个没数。标准处理是TRIM去首尾空格但TRIM对全角空格和换行符不一定管用我会再加一层SUBSTITUTE把常见脏字符替换掉SUBSTITUTE(SUBSTITUTE(TRIM(区域名称), , ), \n, )替换类函数里REPLACE是按位置换SUBSTITUTE是按内容换两个别搞混。脱敏场景我用REPLACE比较多比如手机号中间四位LEFT(手机号,3) **** RIGHT(手机号,4)用拼接比CONCATENATE更短FineBI 里两种都支持。4.3 类型转换与大小写统一从数据库同步过来的字段类型经常不是你想要的。数字被存成文本排序就变成字典序10会排在9前面日期被存成文本DATEDIF直接报错。这时候要用TOSTRING或DATEVALUE之类的转换函数统一类型具体函数名各版本略有差异在编辑器里输入TO一般就能看到候选列表。大小写统一用LOWER或UPPER看着不起眼但在做关联匹配的时候特别重要。我有一次做客户表关联两边一个是ABC一个是abc关联不上查了半小时才发现是大小写问题。从那以后凡是文本型主键做关联我都会在数据集层面先统一一次大小写再去掉首尾空格宁可多算一个字段也不在组件里反复排查。提示文本函数尽量在自助数据集阶段算完不要在组件里对明细字段做文本运算。组件里的计算是每次查询都重算的数据集里算完是物化的性能差好几倍。5. 日期函数口径对齐比函数本身更重要5.1 基准日与日期构造TODAY()返回当天日期NOW()返回当前时间戳这两个函数最常用于“距今”类指标。但要注意TODAY()是跟随系统时间的如果报表是自动刷新后发出去看的第二天打开数据就变了这在做月报的时候会引起困惑。我的做法是凡是需要固定基准日的报表都建一个“基准日”参数默认值取TODAY()但允许手动改这样复盘历史数据的时候不会乱。构造日期用DATE(年, 月, 日)。它有个很好用的特性月或日超出正常范围时会自动进位比如DATE(2024, 13, 1)会得到 2025 年 1 月 1 日。这个特性拿来找月末特别方便当月最后一天 DATEDELTA(DATE(YEAR(日期), MONTH(日期) 1, 1), -1, d) 当月第一天 DATE(YEAR(日期), MONTH(日期), 1)比用DAYSOFMONTH之类的函数更直白也不依赖版本支持。5.2 DATEDIF 与 DATEDELTA 的区别这两个函数名字像用途完全不同我见过不少人用混。DATEDIF(开始日期, 结束日期, 单位)算的是两个日期之间的差值返回一个数字DATEDELTA(日期, 偏移量, 单位)算的是日期加减偏移量之后得到的新日期返回一个日期。算账龄天 DATEDIF(下单日期, TODAY(), d) 算7天前的日期 DATEDELTA(TODAY(), -7, d)单位缩写是个高频出错点。年用y月用M日用小写d小时h分钟m。注意月和分钟在某些版本里是大小写区分的月大写M、分钟小写m写错一个字母结果差 12 倍。我现在的习惯是写完日期差公式先用一行边界数据验一下比如算一个跨年的日期差看结果对不对。DATEDIF还有一个特性值得记它算“整月”时是向下取整的。1 月 31 日到 2 月 28 日按M算出来是 0 个月不到一个月。做账龄分档的时候如果不注意会把一批“快到期”的客户算成“未到期”。处理办法是改用天数分档或者自己定义分档规则别直接依赖整月差值。5.3 周、月、季度口径的对齐业务方说“本周”可能是周一到周日也可能是周日到周六说“本月”可能是自然月也可能是财月。这类分歧没有技术解法只能事先问清楚。技术上要做的是把口径固定成字段而不是每次写公式现算。我的做法是在数据集里加三个字段年月、年周、年季。年月用YEAR(日期)*100 MONTH(日期)生成一个整数排序天然正确比字符串拼接的2024-03更好用年周用周数函数生成注意年初那几天可能被算到上一年的最后一周具体归属规则看函数参数年季用YEAR(日期)*10 CEILING(MONTH(日期)/3.0, 0)生成注意除完要向上取整MONTH/3直接整除会把 3 月算成第 1 季度。这些字段一次算好放在数据集里后面所有组件都复用口径就不会在各处漂移。5.4 同比环比的标准写法同环比是问得最多的。FineBI 的图表里有快速计算可以直接出“同比增长率”“环比增长率”能用快速计算就别手写省事且不易错。但快速计算有个限制它对维度的顺序有要求维度一多就可能算错层级。需要精确控制的时候我用DEF手工构造上期值本期销售额 SUM(销售额) 上年同期销售额 DEF(SUM(销售额), [门店], [年月]) 配合日期维度偏移 环比增长率 (SUM(销售额) - 上期值) / 上期值实际写的时候更稳妥的路子是在数据集里先把“上年同月”这个字段算出来然后用它作为维度做一次自关联或者用DEF按它聚合。这样虽然多一步但每一层的口径都是显式的出错能定位到具体哪一步。我吃过亏一个用快速计算做出来的同比看板换了个维度组合之后数字整体偏大查了半天发现是比较基准被算成了“上一个有数据的月份”而不是“上一个月”中间断月导致基准错位。注意算增长率一定要处理分母为 0 和分母为空的情况否则报表里会出现无穷大或者空白。标准兜底写法是IF(ISNULL(上期值) OR 上期值 0, NULL, (本期 - 上期) / 上期)。6. 逻辑函数与空值让报表不翻车6.1 IF 嵌套与分支写法取舍IF(条件, 真值, 假值)是最基础也最常用的。两三层嵌套还好超过四层就开始难读了括号对不上是常事。这时候有两个选择换成多分支的判断函数不同版本叫法不一编辑器里输入IF一般会带出候选或者干脆拆成多个计算字段每个字段负责一层判断最后再拼。我更倾向拆字段。比如客户分层与其写一个巨大的嵌套IF一次分出五档不如先算“近12个月消费额”“近12个月订单数”两个字段再用一个较短的IF组合出分层。拆开之后每一层都能单独拖到表里验证出错范围小别人接手也能看懂逻辑。分层打标的模板我一般是这么写的IF(ISNULL(消费额), 未消费, IF(消费额 10000, 高价值, IF(消费额 3000, 中价值, IF(消费额 500, 低价值, 潜力))))注意最外层先把空值处理掉否则NULL 10000返回空整个分层结果就是空这类客户在报表里会直接消失看数的人以为没这批人。6.2 零除、空值与隐式转换零除是报表里最常见的报错来源。SUM(金额)/SUM(数量)某个维度组合下数量是 0结果要么报错要么显示异常值。标准处理是分母兜底IF(SUM(数量) 0 OR ISNULL(SUM(数量)), NULL, SUM(金额) / SUM(数量))返回NULL而不是0是有讲究的。返回0会被当成“这个品类的单价是 0”参与后续平均值计算时把整体拉低返回NULL表示“没有数据”在图表里显示为空缺看数的人知道这是缺口而不是真实值。这个区别在做汇总分析时影响很大。隐式转换的坑在数值和文本混用。有些字段在数据库里是varchar存的是1,234这种带千分位的字符串直接SUM会报错或者算出奇怪结果。得先用替换函数去掉逗号再转成数值。我一般会在数据集里专门做一个“清洗后数值”字段命名上带前缀区分避免后面误用原始字段。6.3 AND、OR 与条件组合AND和OR组合多个条件时一定要加括号明确优先级。A AND B OR C在不同引擎里的结合顺序可能不同虽然大多遵循AND优先但写清楚总没坏处。我的写法是把每组条件都括起来(A AND B) OR (C AND D)。NOT用得少一般用或者!更直观。ISNULL是必须记住的它在IF里配合使用是空值处理的主力。另外有个细节ISNULL只判断空不判断空字符串。文本字段里和NULL是两回事从 Excel 导入的数据经常是空字符串得用LEN(TRIM(字段)) 0来判断。这个区别我调了两回才记住。7. DEF 函数族跨粒度计算的主力7.1 DEF 到底在做什么一句话概括DEF让你在当前的聚合粒度之外另外指定一套维度重新算一遍聚合。它等价于 SQL 里的子查询或者窗口函数但在 FineBI 里写起来更短。语法结构大致是DEF(聚合表达式, 维度1, 维度2, ...)。比如DEF(SUM(销售额), [客户])意思是“不管当前组件按什么维度分组都按客户这个维度重新算一遍销售额总和”。每个明细行拿到的是它所属客户的总销售额拿这个值再和当前行做除法占比就出来了。理解DEF的关键是分清两个粒度组件粒度你拖进图表的维度和DEF 粒度你写在函数里的维度。两者可以完全不同这正是它有价值的地方。没有DEF的时候想做“明细行对比所属分组汇总”只能靠自关联写起来麻烦且容易重复。7.2 DEF_ADD 与 DEF_SUB 的使用场景DEF_ADD是在当前维度基础上追加维度DEF_SUB是减少维度。这两个在写多层占比的时候特别顺手。举个例子组件里拖了“区域”和“城市”两个维度你想算“每个城市销售额占所属区域的比例”。用DEF_SUB的思路是在当前粒度基础上把“城市”这个维度去掉剩下“区域”粒度得到区域总额城市占区域比例 SUM(销售额) / DEF_SUB(SUM(销售额), [城市])反过来如果组件里只有“区域”你想算“该区域占全国的比例”用DEF_ADD把“全国”这个更高层级补进来或者直接用不带维度的DEF(SUM(销售额))拿全局总额。实际用下来DEF_SUB的场景比DEF_ADD多因为大部分占比计算是“往下看往上算”。7.3 去重、排名与 TopN 的写法DEF组合COUNTD能做“每个客户的去重商品数”这类指标DEF(COUNTD(商品编号), [客户])。这类指标如果不用DEF在明细粒度上直接写COUNTD会得到全局去重数不是每个客户各自的。排名用DEF加上排名函数或者用组件自带的排序功能。我倾向后者因为排名涉及“并列怎么处理”“空值排第几”这些细节函数参数的默认行为不一定符合预期在图表里点排序更直观。真要用函数算记得先明确“按降序排、并列取相同名次还是跳过名次”。TopN 场景我一般不用公式直接用组件的筛选功能设置“按销售额取前 10”。公式实现 TopN 需要在DEF里嵌套排名判断可读性差而且筛选条件一变就得改公式。提示DEF本质上是一次额外的聚合计算数据量大时会明显拖慢查询。我的经验是明细行数在百万级以内可以放心用超过千万行就要评估一下能提前在数据集里算好落表的就不要放在组件里实时算。8. 报错与数字对不上的排查路径8.1 常见报错对照表现象大概率原因处理办法公式报“聚合函数使用错误”聚合字段和明细字段直接混用两边都改成聚合或用DEF固定粒度结果全是 1 或全是 100%分母跟着行粒度走了分母改用不带维度的DEF报“除数为零”分母存在 0 值IF兜底返回NULL数字比预期大好几倍关联产生笛卡尔积行数膨胀检查关联键是否唯一先做去重日期差报错字段类型是文本不是日期用转换函数统一类型分组出现两个同名值隐藏空格或全角字符TRIM加SUBSTITUTE清洗分层字段出现空白空值没在最外层处理ISNULL前置判断COUNTD结果偏小空值不计入或条件返回了0条件分支返回NULL而不是08.2 数字对不上的四步排查法我的固定排查顺序是先查行数再查关联再查粒度最后查空值。第一步查行数。把明细表拖到最细粒度看总行数是多少和数据库里COUNT(*)对一下。行数对不上后面的都不用查了问题在数据接入环节。第二步查关联。如果用了多表关联先单独看每张表的行数和主键去重数判断关联键是不是唯一。一对多关联后行数会膨胀这是“数字偏大”的头号原因。我遇到过一次客户表和订单表关联客户表里同一个客户有三条地址记录关联后订单翻了三倍销售额自然虚高。第三步查粒度。把指标拖到不同维度组合下看看哪个组合开始出现异常。如果全局汇总是对的、拆到明细就错基本可以确定是粒度问题去检查公式里有没有明细字段混进聚合。第四步查空值。把待查字段单独拿出来COUNT一下和总行数比看缺多少。缺失的部分是不是被IF分支吞掉了是不是让SUM少算了。这一步最容易被跳过但我统计下来至少三成的“数字对不上”最后都归到空值上。9. 让公式更好维护的几个习惯写公式这件事写出来只是开始维护才是成本大头。我在几个项目里慢慢养成了几个习惯成本很低但收益明显。第一个是命名带语义。计算字段别叫“新建公式1”“指标A”就叫“销售额_净额”“客户数_去重_近12月”。名字长一点没关系搜索的时候能搜到、看名字知道口径比什么都强。我现在的命名规则是“指标_口径_时间范围_特殊说明”四段式不够就省略后面几段。第二个是把复杂公式拆成字段链。一个五层嵌套的公式拆成三个中间字段每层单测一次最后拼起来。中间字段可以隐藏掉不展示但保留在数据集里后面改口径的时候改一层就行不用推倒重来。第三个是记录口径来源。每个容易引起歧义的指标在描述里写清楚“含不含退货”“按哪个时间字段”“去重按什么键”。这话写给自己看是备忘写给别人看是防扯皮。第四个是优先用组件自带能力。占比、排名、同环比、累计这些 FineBI 的快速计算里基本都有点两下就能出还自带格式处理。自己写公式的唯一理由是快速计算口径不满足比如需要自定义分组或者跨层级计算。别为了“显得专业”去手写维护成本是自己的。最后一个习惯是定期回头看。我那份函数笔记每季度翻一次把三个月没用过的划掉把新踩的坑补进去。函数常用的其实就那么二十来个剩下的都是为了解决某个特定问题临时学的不常用的记了也忘不如保持笔记精简只留高频和易错的那部分。这套东西没有多高深都是被一个个报错和一次次对数逼出来的。真要说有什么心得就是每次写完公式别急着发布先用两三个边界值验一下空值、零值、负数。这三个值能过八成场景就没问题了。剩下两成等它出问题的时候你自然会想起来该查哪里因为排查路径已经刻在脑子里了。