基于Over窗口函数计算同产品工序坐标匹配行数的公式优化请求

数据背景 #

• 数据集包含字段：Product Number（产品编号）、Process Number（工序编号，文本类型，值为1000、2000、3000等）、X坐标、Y坐标

需求说明 #

仅针对Process Number = '2000'的行，统计同一Product Number下满足以下条件的行数（目标列为Matching Count）：

• Process Number = '1000'
• X坐标处于当前行X±2范围内
• Y坐标处于当前行Y±2范围内

现有问题 #

当前使用的公式存在多处错误，无法返回预期结果：

if([Process Number]<>'2000',Null, Sum(if([Process Number]='1000' and [X]<(?+20) AND [X]>(?+20) AND [Y]<(?+20) AND [Y]>(?+20) ,1,0) OVER ([Product Number])

错误点：

1. 容差逻辑错误：需求是±2，但公式误写为±20，且判断方向逻辑混乱（比如[X] > (?+20)完全不符合范围要求）
2. 行值引用错误：窗口函数上下文无法直接通过?引用当前行X/Y值
3. 语法不完整：缺少闭合括号，导致公式无法执行

示例数据 #

修正后的公式方案 #

方案1：Tableau计算字段（LOD表达式） #

IF [Process Number] = '2000' THEN
    {FIXED [Product Number] : 
        SUM(
            IF [Process Number] = '1000' 
                AND ABS([X] - {EXCLUDE : [X]}) <= 2 
                AND ABS([Y] - {EXCLUDE : [Y]}) <= 2 
            THEN 1 ELSE 0 END
        )
    }
ELSE NULL
END

或者采用自关联后的窗口计算（需先复制数据集生成Process Number (copy)、X (copy)、Y (copy)字段）：

IF [Process Number] = '2000' THEN
    SUM(
        IF [Process Number (copy)] = '1000' 
            AND ABS([X (copy)] - [X]) <= 2 
            AND ABS([Y (copy)] - [Y]) <= 2 
        THEN 1 ELSE 0 END
    ) OVER (PARTITION BY [Product Number])
ELSE NULL
END

方案2：SQL语句（数据库层面执行） #

SELECT 
    t1.*,
    CASE 
        WHEN t1.Process_Number = '2000' THEN 
            (SELECT COUNT(*) 
             FROM your_table t2 
             WHERE t2.Product_Number = t1.Product_Number 
               AND t2.Process_Number = '1000' 
               AND ABS(t2.X - t1.X) <= 2 
               AND ABS(t2.Y - t1.Y) <= 2)
        ELSE NULL 
    END AS Matching_Count
FROM your_table t1;

关键修正点 #

1. 修正容差逻辑：使用ABS(目标坐标 - 当前坐标) <= 2，简洁准确地实现±2范围判断
2. 解决行值引用问题：通过LOD上下文或自关联，实现当前行与同产品下1000工序行的坐标对比
3. 补全语法：确保所有括号闭合，逻辑分支完整

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