Spark SQL中使用LEAD函数获取下一个唯一订单日期的问题

在Spark SQL中计算每个订单的next_order_date时，当同一客户同一天有多笔订单，计算结果不准确。需要确保next_order_date对应同一客户的下一个唯一订单日期。

示例数据 #

原始交易记录：

预期结果 #

每个订单对应的next_order_date：

尝试的错误代码 #

WITH ranked_transactions AS (
        SELECT
        t.*,
        DENSE_RANK() OVER (PARTITION BY t.customer_key ORDER BY t.order_date) AS order_rank
        FROM transaction_records t
    )
SELECT
    customer_key,
    order_id,
    order_date,
    quantity,
    amount,
    order_rank,
    LEAD(order_date) OVER (PARTITION BY customer_key, order_rank ORDER BY order_date) AS next_order_date
FROM ranked_transactions

错误输出 #

错误原因：按customer_key, order_rank分区使用LEAD，只会在同排名内取后续订单的日期，无法跨日期获取下一个唯一的订单日期。

核心思路是先提取每个客户的唯一订单日期，计算每个唯一日期的下一个日期，再将结果关联回原始交易表。

方法一：先聚合唯一日期再关联 #

WITH unique_dates AS (
    SELECT 
        customer_key,
        order_date,
        LEAD(order_date) OVER (PARTITION BY customer_key ORDER BY order_date) AS next_order_date
    FROM (
        SELECT DISTINCT customer_key, order_date 
        FROM transaction_records
    ) t
)
SELECT 
    tr.customer_key,
    tr.order_id,
    tr.order_date,
    tr.quantity,
    tr.amount,
    ud.next_order_date
FROM transaction_records tr
JOIN unique_dates ud 
    ON tr.customer_key = ud.customer_key 
    AND tr.order_date = ud.order_date
ORDER BY tr.order_id;

方法二：利用DENSE_RANK匹配下一个排名的日期 #

基于原思路优化，先给每个唯一日期分配排名，再找到当前排名+1对应的日期：

WITH ranked_dates AS (
    SELECT 
        t.*,
        DENSE_RANK() OVER (PARTITION BY customer_key ORDER BY order_date) AS date_rank
    FROM transaction_records t
),
next_date_map AS (
    SELECT 
        customer_key,
        date_rank,
        LEAD(order_date) OVER (PARTITION BY customer_key ORDER BY date_rank) AS next_order_date
    FROM (
        SELECT DISTINCT customer_key, date_rank, order_date 
        FROM ranked_dates
    ) t
)
SELECT 
    rd.customer_key,
    rd.order_id,
    rd.order_date,
    rd.quantity,
    rd.amount,
    ndm.next_order_date
FROM ranked_dates rd
JOIN next_date_map ndm 
    ON rd.customer_key = ndm.customer_key 
    AND rd.date_rank = ndm.date_rank
ORDER BY rd.order_id;

说明 #

• 方法一逻辑最直观：先去重得到每个客户的唯一订单日期，计算每个日期的下一个日期，再关联回原始表，确保同一天的所有订单都拿到相同的next_order_date。
• 方法二基于原有的DENSE_RANK思路，通过提取唯一的排名-日期映射，修正了原代码的分区错误，保证同一排名（同一日期）的订单都关联到同一个下一个日期。

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