Redshift数据库中计算“Days since last unavailable”列的SQL方案

嘿，这个需求用窗口函数完全可以搞定！我来给你拆解思路，再直接上可运行的Redshift SQL代码。

首先核心思路是按product_id分区、按日期排序，找到每一行之前最近的is_unavailable=1的日期，再计算当前日期与该日期的差值，同时处理两种特殊情况：

• 当前行本身不可用时，天数显示0
• 该产品首次出现不可用记录时，显示“-”

具体实现步骤 #

1. 转换日期格式：你的示例中date是1st Jan这类字符串，需要用Redshift的TO_DATE函数转成可计算的DATE类型，比如TO_DATE(date, 'DDth Mon')。
2. 获取最近的不可用日期：用LAST_VALUE窗口函数结合IGNORE NULLS，精准捕捉每个分区内当前行之前最近的不可用日期。
3. 计算并格式化结果：通过DATEDIFF计算天数差，再用CASE语句处理特殊场景的显示逻辑。

完整SQL代码 #

WITH transformed_data AS (
    SELECT
        product_id,
        date,
        TO_DATE(date, 'DDth Mon') AS actual_date, -- 将字符串日期转为标准日期类型
        is_unavailable
    FROM your_table_name -- 替换成你的实际表名
)
SELECT
    product_id,
    date,
    is_unavailable,
    CASE
        WHEN is_unavailable = 1 THEN '0' -- 当前不可用，直接显示0
        WHEN last_unavailable_date IS NULL THEN '-' -- 无历史不可用记录，显示-
        ELSE CAST(DATEDIFF(day, last_unavailable_date, actual_date) AS VARCHAR) -- 计算间隔天数
    END AS days_since_last_unavailable
FROM (
    SELECT
        *,
        LAST_VALUE(CASE WHEN is_unavailable = 1 THEN actual_date END IGNORE NULLS) 
            OVER (PARTITION BY product_id ORDER BY actual_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_unavailable_date
    FROM transformed_data
) AS subquery
ORDER BY product_id, actual_date;

代码细节解释 #

• LAST_VALUE(CASE WHEN is_unavailable = 1 THEN actual_date END IGNORE NULLS)：在每个product_id分区内，从当前行往前查找最近的不可用日期，IGNORE NULLS会自动跳过is_unavailable=0的行（此时CASE返回NULL）。
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：明确窗口范围是分区第一行到当前行，避免默认RANGE逻辑可能带来的异常。
• CASE分支：完美匹配你想要的输出格式，覆盖所有场景的显示需求。

用你的示例数据测试，输出会和你期望的完全一致！

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