Spotfire：表透视后深度范围对比及完成区段计算优化

针对你的需求，推荐以下几种更高效、易维护的实现方式，避免大量CASE语句带来的性能问题和维护成本：

1. 区段维度表关联法（最通用、易维护） #

步骤： #

• 创建区段维度表：把所有区段的深度范围定义成独立表（比如命名为Section_Dim），结构示例：

  Section_Name	Min_Depth	Max_Depth
  Section 1	0	50
  Section 2	51	100
  Section 3	101	150
  ...	...	...

• 关联匹配+聚合：将合并后的Table A+B与维度表关联，匹配Item覆盖的所有区段，再通过字符串聚合生成结果。

示例SQL（以SQL Server为例）： #

SELECT 
    ab.*,
    STRING_AGG(sd.Section_Name, ', ') AS [Sections Completed]
FROM 
    [Table_A+B] ab
LEFT JOIN 
    Section_Dim sd ON ab.In_Depth <= sd.Max_Depth AND ab.Out_Depth >= sd.Min_Depth
GROUP BY 
    ab.Item, ab.In_Depth, ab.Out_Depth -- 需包含合并表所有非聚合列

不同数据库聚合语法略有差异：MySQL用GROUP_CONCAT，PostgreSQL用STRING_AGG，逻辑一致。

优势： #

• 新增/修改区段只需更新维度表，无需修改主查询代码
• 维度表可加索引，关联查询性能远优于大量CASE判断

2. 动态数值计算法（适合区段间隔固定的场景） #

如果你的区段是按固定深度间隔划分（比如每50深度一个区段），可以直接通过数值计算生成覆盖的区段，无需额外维度表。

示例SQL（假设每50深度为一个区段）： #

SELECT 
    Item,
    In_Depth,
    Out_Depth,
    STRING_AGG('Section ' + CAST(n AS VARCHAR), ', ') AS [Sections Completed]
FROM 
    [Table_A+B] ab
CROSS JOIN 
    (SELECT TOP (SELECT MAX((Out_Depth + 49)/50) FROM [Table_A+B]) 
     n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
     FROM sys.all_columns) nums
WHERE 
    n >= CEILING(In_Depth / 50.0) 
    AND n <= FLOOR(Out_Depth / 50.0)
GROUP BY 
    Item, In_Depth, Out_Depth

优势： #

• 无需维护维度表，动态计算覆盖区段
• 适合深度区间规律的场景，代码简洁

3. 自定义函数封装法（简化主查询逻辑） #

将区段匹配逻辑封装成表值函数，主查询直接调用函数获取结果，避免重复代码。

示例（SQL Server表值函数）： #

CREATE FUNCTION dbo.GetCompletedSections(@InDepth INT, @OutDepth INT)
RETURNS TABLE
AS
RETURN
(
    SELECT Section_Name
    FROM Section_Dim
    WHERE @InDepth <= Max_Depth AND @OutDepth >= Min_Depth
)

调用查询： #

SELECT 
    ab.*,
    (SELECT STRING_AGG(Section_Name, ', ') FROM dbo.GetCompletedSections(ab.In_Depth, ab.Out_Depth)) AS [Sections Completed]
FROM [Table_A+B] ab

优势： #

• 逻辑封装，主查询更简洁
• 后续修改区段规则只需调整函数或维度表

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