Spotfire函数应用：跨行计算不同日期列的数值差值

问题分析 #

你之前尝试的MostCommon([Type]) over (Intersect([END_Date],[START_Date]))等表达式不符合需求——Intersect用于集合交集运算，MostCommon用于统计众数，都和跨行日期差计算无关。下面针对常见的两种环境给出正确实现方式：

1. SQL环境下的实现 #

使用窗口函数LEAD()获取下一行的开始日期，再计算与当前行结束日期的差值：

SELECT
    ID,
    Type,
    START_Date AS 当前行开始日期,
    END_Date AS 当前行结束日期,
    -- 获取下一行的开始日期，按Type分组、START_Date排序
    LEAD(START_Date) OVER (PARTITION BY Type ORDER BY START_Date) AS 下一行开始日期,
    -- 计算日期差（以天为单位，可根据需求调整单位，如hour/month）
    DATEDIFF(day, END_Date, LEAD(START_Date) OVER (PARTITION BY Type ORDER BY START_Date)) AS 日期差值
FROM
    你的表名
ORDER BY
    Type, START_Date;

• 若需按ID分组计算，将PARTITION BY Type替换为PARTITION BY ID即可；
• 最后一行无下一行，结果会返回NULL，可通过IFNULL()函数处理空值。

2. Tableau（或类似BI工具）下的实现 #

使用LOOKUP()函数获取下一行的开始日期，创建计算字段：

DATEDIFF('day', [END_Date], LOOKUP([START_Date], 1))

• LOOKUP([START_Date], 1)表示取当前行之后第1行的开始日期；
• 需先在视图中按Type和START_Date升序排序，确保行顺序正确；
• 若需按Type分组计算，可在计算字段中结合PARTITION BY，或在视图中添加Type作为分区字段；
• 空值处理可使用ZN()函数，例如ZN(DATEDIFF('day', [END_Date], LOOKUP([START_Date], 1)))。

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