Spark SQL实现排除含早于指定日期记录的整组订单

我有一张订单表，包含order Id、country、order date、product、quantity字段，同一个order Id对应多条不同日期的商品记录。需求是仅保留所有记录的order date均晚于'6/11/2022'的订单的全部记录——比如订单222和111因为存在早于该日期的记录要完全排除，只保留订单333的所有记录。

我尝试用下面的Spark SQL代码，按order Id和country分组并用HAVING子句过滤，但结果只排除了单条不符合日期的记录，而非整组订单：

select order Id, order date, product, quantity from Orders table
group by order Id, country
HAVING MIN(order date) > '6/11/2022'

原订单表 #

期望查询结果 #

当前非期望结果 #

错误原因 #

你的写法逻辑有误：Spark SQL中，当SELECT包含非聚合字段（如order date、product）却使用GROUP BY时，默认会启用自动模式匹配（若spark.sql.generator.unsafe.enabled=true），这会随机返回分组内的某一条记录，而非过滤整个分组后返回所有记录。GROUP BY是用来聚合数据的，不是用来筛选完整订单的。

正确解法 #

要实现需求，需先筛选出符合条件的order Id（即该订单下所有记录的order date都晚于指定日期），再关联原表获取这些订单的全部记录，有两种常用写法：

方法1：子查询+IN子句 #

SELECT o.*
FROM Orders o
WHERE o.`order Id` IN (
    SELECT `order Id`
    FROM Orders
    GROUP BY `order Id`, country
    HAVING MIN(`order date`) > '6/11/2022'
)

方法2：窗口函数（大数据场景更高效） #

WITH order_date_check AS (
    SELECT 
        *,
        MIN(`order date`) OVER (PARTITION BY `order Id`, country) AS min_order_date
    FROM Orders
)
SELECT `order Id`, country, `order date`, product, quantity
FROM order_date_check
WHERE min_order_date > '6/11/2022'

两种方法都能正确返回订单333的所有记录，排除订单222和111的全部数据。窗口函数的方式无需两次扫描表（Spark优化器处理得当的话），数据量较大时性能更优。

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