SparkSQL中PARTITION BY搭配RANGE BETWEEN时的二次排序实现问题

你的问题核心是要在user_id+product_id分区下，筛选出当前行create_date前后3天的行，再按NEW_DATE排序取LAST_VALUE，但窗口函数的ORDER BY不能同时指定两列分别用于范围和排序，以下是可行的解决方案：

方法一：自连接筛选日期范围后排序（兼容所有Spark版本） #

通过自连接先筛选出符合日期范围的行，再按NEW_DATE倒序取第一行（等价于LAST_VALUE）：

WITH filtered_data AS (
    SELECT 
        t1.*,
        t2.target_col  -- 替换成你要取LAST_VALUE的列
    FROM your_table t1
    INNER JOIN your_table t2
        ON t1.user_id = t2.user_id
        AND t1.product_id = t2.product_id
        AND t2.create_date BETWEEN t1.create_date - INTERVAL 3 DAYS AND t1.create_date + INTERVAL 3 DAYS
),
ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY t1.user_id, t1.product_id, t1.create_date
            ORDER BY t2.NEW_DATE DESC
        ) AS rn
    FROM filtered_data
)
SELECT DISTINCT
    user_id, product_id, create_date, NEW_DATE,
    target_col AS last_value_by_new_date
FROM ranked_data
WHERE rn = 1;

方法二：使用FILTER子句（Spark 3.0+推荐） #

利用Spark 3.0及以上支持的窗口函数FILTER特性，直接在窗口内筛选符合日期范围的行，再按NEW_DATE排序取LAST_VALUE，效率更高：

WITH temp_data AS (
    SELECT 
        *,
        create_date AS current_create_date
    FROM your_table
)
SELECT 
    *,
    LAST_VALUE(target_col) OVER (
        PARTITION BY user_id, product_id
        ORDER BY NEW_DATE
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) FILTER (
        WHERE create_date BETWEEN current_create_date - INTERVAL 3 DAYS AND current_create_date + INTERVAL 3 DAYS
    ) AS last_value_by_new_date
FROM temp_data;

方法三：调整ORDER BY列顺序（仅适用于特定场景） #

如果你的业务场景可以接受先按create_date确定窗口范围，再按NEW_DATE排序窗口内的行，可以用这种简化写法，但注意LAST_VALUE会优先按create_date排序，再取NEW_DATE最大的行：

SELECT 
    *,
    LAST_VALUE(target_col) OVER (
        PARTITION BY user_id, product_id
        ORDER BY create_date, NEW_DATE
        RANGE BETWEEN INTERVAL 3 DAYS PRECEDING AND INTERVAL 3 DAYS FOLLOWING
    ) AS last_value
FROM your_table;

错误原因说明 #

你之前写两个ORDER BY的语法是不合法的，Spark窗口函数的OVER子句中只能有一个ORDER BY，它同时承担两个作用：

1. 定义RANGE/ROWS BETWEEN的基准列（决定窗口范围）
2. 定义窗口内行的排序顺序（决定LAST_VALUE的取值逻辑）

因此必须通过拆分逻辑来同时满足日期范围和自定义排序的需求。

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