在观远 BI 中实现数组字段按指定业务顺序排序的方案

在观远 BI 中实现数组字段按指定业务顺序排序的方案

一、背景

在实际业务分析中，经常会遇到这样一类需求：
某个字段经过 COLLECT_SET([服务类型]) 聚合后，会生成一个数组，例如：

[IEX, URG, CAC, EEC]

但业务上并不希望它按默认规则展示，而是希望按照约定好的业务顺序输出，例如：

[URG, IEX, EEC, CAC]

这里的顺序不是字母升序或降序，而是业务上定义好的优先级顺序。

当前在观远 BI 的卡片内计算字段场景下，通常无法直接按“指定字典顺序”对数组元素排序。

因此，更推荐的做法是：在数据集 SQL 或 ETL 处理阶段，先补一个排序权重字段，再基于这个权重完成排序和聚合。

二、适用场景

该方案适用于以下场景：

• 数组元素需要按业务优先级展示，而不是按默认字母顺序展示
• 排序规则固定且清晰，例如：
• URG -> 1
• IEX -> 2
• EEC -> 3
• CAC -> 4
• 希望将排序逻辑沉淀在数据层，便于多个卡片和页面复用

常见业务场景包括：

• 服务类型排序
• 订单状态排序
• 渠道等级排序
• 风险等级排序

三、实现思路

核心思路可以概括为三步：

1. 为原始字段增加一个“排序权重字段”
2. 按排序权重进行排序
3. 再聚合成数组或展示字符串

例如，为 [服务类型] 增加一个权重字段 [服务类型排序值]：

CASE

  WHEN 服务类型 = 'URG' THEN 1

  WHEN 服务类型 = 'IEX' THEN 2

  WHEN 服务类型 = 'EEC' THEN 3

  WHEN 服务类型 = 'CAC' THEN 4

  ELSE 999

END

这样就把原本无法控制的文本排序问题，转成了数值排序问题。

四、方案一：通过数据集 SQL 实现

4.1 适用情况

适合以下情况：

• 当前使用的是 SQL 数据集
• 可以直接编辑数据集 SQL
• 希望在源头一次性完成处理

4.2 实现步骤

步骤 1：进入数据集编辑页面

在观远 BI 中进入：

数据准备 -> 数据集 -> 选择目标数据集 -> 编辑 SQL

进入 SQL 编辑器后，在原 SQL 基础上补充排序逻辑。

步骤 2：新增排序权重字段

在 SQL 中增加一个排序权重字段，例如：

SELECT

  工单ID,

  服务类型,

  CASE

    WHEN 服务类型 = 'URG' THEN 1

    WHEN 服务类型 = 'IEX' THEN 2

    WHEN 服务类型 = 'EEC' THEN 3

    WHEN 服务类型 = 'CAC' THEN 4

    ELSE 999

  END AS 服务类型排序值

FROM demo_service_type

这一步完成后，每种服务类型都会有一个固定的排序值。

步骤 3：按排序值排序后再聚合

如果当前数据库支持先排序再聚合，可以继续处理成：

SELECT

  工单ID,

  collect_list(服务类型) AS 服务类型数组

FROM (

  SELECT

    工单ID,

    服务类型,

    CASE

      WHEN 服务类型 = 'URG' THEN 1

      WHEN 服务类型 = 'IEX' THEN 2

      WHEN 服务类型 = 'EEC' THEN 3

      WHEN 服务类型 = 'CAC' THEN 4

      ELSE 999

    END AS 服务类型排序值

  FROM demo_service_type

  ORDER BY 工单ID, 服务类型排序值

) t

GROUP BY 工单ID

这样就可以尽量保证数组聚合结果按业务顺序输出。

步骤 4：如果数组顺序不稳定，可以改为输出字符串

某些数据库对 collect_list 或数组聚合顺序控制不够稳定，此时可以直接输出字符串：

SELECT

  工单ID,

  GROUP_CONCAT(服务类型 ORDER BY 服务类型排序值) AS 服务类型展示

FROM (

  SELECT

    工单ID,

    服务类型,

    CASE

      WHEN 服务类型 = 'URG' THEN 1

      WHEN 服务类型 = 'IEX' THEN 2

      WHEN 服务类型 = 'EEC' THEN 3

      WHEN 服务类型 = 'CAC' THEN 4

      ELSE 999

    END AS 服务类型排序值

  FROM demo_service_type

) t

GROUP BY 工单ID

最终展示结果可以直接变成：

URG,IEX,EEC,CAC

步骤 5：保存数据集并用于卡片分析

保存数据集后，在卡片中直接使用已经处理好的结果字段：

• 如果需要数组结果，就用 服务类型数组
• 如果需要更稳定的文本展示，就用 服务类型展示

这样卡片层就不需要再额外处理排序逻辑。

五、方案二：通过 ETL 实现

5.1 适用情况

适合以下情况：

• 当前项目主要通过 ETL处理
• 不方便直接改 SQL
• 希望通过观远 BI 可视化流程完成处理

5.2 实现步骤

步骤 1：进入 ETL 流程

在观远 BI 中进入：

数据准备 -> ETL -> 选择目标流程

确认当前流程中已经包含原始字段 [服务类型]。

步骤 2：新增排序权重字段

在 ETL 中增加一个“字段加工”或“新增字段”节点，新增字段：

• 字段名：服务类型排序值
• 字段类型：数值型

逻辑可以写成：

CASE

WHEN [服务类型] = 'URG' THEN 1

WHEN [服务类型] = 'IEX' THEN 2

WHEN [服务类型] = 'EEC' THEN 3

WHEN [服务类型] = 'CAC' THEN 4

ELSE 999

END

如果当前 ETL 表达式节点不支持完整 CASE 写法，也可以通过多层 IF 实现相同效果。

步骤 3：增加排序节点

在新增完 [服务类型排序值] 后，增加一个排序节点，按以下顺序排序：

• 第一排序字段：业务主键，例如 [工单ID]
• 第二排序字段：[服务类型排序值]，升序

这样就能确保同一主键下的服务类型记录按指定顺序排列。

步骤 4：增加聚合节点

在排序之后增加聚合节点，例如：

• 分组字段：[工单ID]
• 聚合字段：[服务类型]

如果当前环境支持数组聚合，可以直接输出数组；
如果更适合展示文本结果，也可以在聚合后输出字符串。

步骤 5：输出 ETL 结果数据集

将 ETL 的最终结果输出成新数据集，供后续卡片直接使用。

这样处理后，排序逻辑就已经沉淀在数据层，后续所有图表都可以复用。

六、方案三：先拼成“带序号值”再还原

6.1 适用情况

适合以下情况：

• 当前数组聚合顺序不稳定
• 需要更强的结果可控性
• 想用一个更直观的方式固定顺序

6.2 实现方式

可以先把排序值和字段值拼接成一个中间字段，例如：

1_URG

2_IEX

3_EEC

4_CAC

示例 SQL：

SELECT

  工单ID,

  CONCAT(

    CASE

      WHEN 服务类型 = 'URG' THEN '1'

      WHEN 服务类型 = 'IEX' THEN '2'

      WHEN 服务类型 = 'EEC' THEN '3'

      WHEN 服务类型 = 'CAC' THEN '4'

      ELSE '999'

    END,

    '_',

    服务类型

  ) AS 服务类型排序中间值

FROM demo_service_type

然后按这个中间字段排序、聚合，最终再把前缀去掉。

例如中间结果可能是：

1_URG,2_IEX,3_EEC,4_CAC

最终可转成：

URG,IEX,EEC,CAC

这种方式虽然比直接排序稍微绕一点，但在很多场景下更稳、更容易控制。

七、最终效果

通过以上方案，可以把原本默认无序或默认排序的结果：

[IEX, URG, CAC, EEC]

稳定调整为：

[URG, IEX, EEC, CAC]

如果输出为字符串，则结果为：

URG,IEX,EEC,CAC

八、总结

当业务需要对 COLLECT_SET([服务类型]) 这样的数组结果按指定顺序展示时，建议不要依赖卡片层默认数组排序，而是在观远 BI 的数据集 SQL或ETL阶段提前增加排序权重字段，再完成排序与聚合。

推荐的标准处理方式是：

1. 给 [服务类型] 增加排序权重字段
2. 按排序权重排序
3. 再做聚合输出
4. 必要时改成字符串展示，保证结果稳定

这样可以稳定实现：

[URG, IEX, EEC, CAC]

而不是默认无序或字母顺序结果。