Cognos报表开发中实现Gaps and Islands分析的方法咨询

你写的这套间隙与岛屿分组逻辑完全可以在Cognos内实现，不需要申请数据库管理员在服务端创建对象，针对你提的两个问题和完整实现方式说明如下：

问题1：minimum/maximum函数附加自定义参数的方法 #

Cognos原生的普通聚合版minimum()、maximum()仅支持传入字段和基础聚合范围配置，不支持直接传入窗口分区、排序、行偏移范围参数，但你可以直接使用Cognos的窗口计算版聚合函数实现自定义窗口范围的极值计算，和你SQL里的窗口逻辑完全对齐：

• 对应你SQL中MIN(ARMBegin) OVER (PARTITION BY SRID ORDER BY ARMBegin, ARMEnd ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED FOLLOWING)的写法：

  minimum([ARMBegin] at 0 following, unbounded following within set [SRID] order by [ARMBegin], [ARMEnd])
  

• 对应你SQL中MAX(ARMEnd) OVER (PARTITION BY SRID ORDER BY ARMBegin, ARMEnd ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING)的写法：

  maximum([ARMEnd] at unbounded preceding, 0 preceding within set [SRID] order by [ARMBegin], [ARMEnd])
  

问题2：非自连接方式实现LEAD/LAG功能 #

Cognos 11.1.7版本已经原生内置lag()、lead()窗口函数，完全不需要通过行号自连接的方式实现跨行取值，直接按业务逻辑传参即可：

• 取排序后上一行的WindowStart字段：

  lag([WindowStart],1) within set order by [SRID], [WindowStart], [WindowEnd]
  

• 取排序后上一行的WindowEnd字段：

  lag([WindowEnd],1) within set order by [SRID], [WindowStart], [WindowEnd]
  

完整实现步骤 #

和你原SQL的CTE分层逻辑完全一致，在Cognos中分层创建查询主题即可，不需要修改核心逻辑：

1. 第一层查询（对应CTE的ranges层）：基于你已经合并完成的combined查询，拖入SRID、ARMBegin、ARMEnd字段，新增两个计算项WindowStart、WindowEnd，表达式用上面给出的窗口极值写法即可。
2. 第二层查询（对应CTE的groups层）：引用第一层查询的结果，拖入SRID、WindowStart、WindowEnd字段，新增两个计算项PreviousStart、PreviousEnd，表达式用上面给出的lag函数写法即可。
3. 第三层查询（对应CTE的islands层）：引用第二层查询的结果，拖入已有字段，新增两个计算项：
   • IslandStartInd：

     case when [PreviousEnd] >= [WindowStart] then 0 else 1 end
     

   • IslandId：这里不需要写自定义窗口sum，直接用Cognos内置的累计求和函数即可，逻辑和你原SQL的有序求和完全一致：

     running-total([IslandStartInd] order by [SRID], [WindowStart], [WindowEnd])
     

4. 最终汇总查询：引用第三层查询的结果，按SRID、IslandId分组，分别计算minimum([WindowStart])作为IslandStart、maximum([WindowEnd])作为IslandEnd，最后按SRID、IslandStart、IslandEnd排序即可，输出结果和你原SQL完全一致。

注意：所有窗口函数的分区、排序字段必须和你原SQL保持完全一致，否则会出现窗口范围错位导致结果异常。整套逻辑全部在Cognos查询层实现，Cognos会自动将支持的计算下推到数据源执行，数据源不支持的函数会由Cognos本地查询引擎完成计算，性能和你原生写CTE没有明显差异。

内容的提问来源于stack exchange，提问作者dougp