如何在SQL中筛选最小订单日期处于指定区间的商品-供应商组合

需求说明 #

提取商品与供应商组合的最早订单日期，但仅保留该组合的全局最早订单日期处于指定区间（示例：2024年8月1日至2024年10月30日）的组合。即使某组合有订单落在该区间内，只要其全局最早订单日期不在此区间，就不纳入结果。

数据集 #

期望结果（日期范围：2024年8月1日至2024年10月30日） #

当前SQL语句及问题 #

当前使用的SQL：

SELECT ITEM, VENDOR, MIN(ORDERDATE::timestamp) AS "Earliest Order"
FROM ORDERS
WHERE
ORDERDATE between date_trunc('month', CURRENT_DATE) - interval '1 month' and  date_trunc('month', CURRENT_DATE) + interval '2 month' -interval '1 day'
Group by 1,2

问题分析：该SQL先过滤了订单日期在区间内的记录，再分组取最小值，得到的是组合在区间内的最早订单，而非全局最早订单。这会导致两种错误：

1. 错误纳入全局最早日期不在区间，但有订单落在区间内的组合（比如商品1-ABC）；
2. 逻辑本质错误，没有基于组合的全局最早日期做筛选判断。

正确解决方案 #

方案1：子查询先计算全局最早日期，再筛选 #

SELECT item, vendor, global_min_orderdate AS "最早订单日期"
FROM (
    SELECT 
        item, 
        vendor, 
        MIN(orderdate::timestamp) AS global_min_orderdate
    FROM orders
    GROUP BY item, vendor
) AS combo_min_dates
WHERE global_min_orderdate BETWEEN '2024-08-01'::timestamp AND '2024-10-30'::timestamp;

方案2：使用窗口函数+QUALIFY子句（适用于PostgreSQL 13+、BigQuery等支持QUALIFY的数据库） #

SELECT DISTINCT
    item,
    vendor,
    MIN(orderdate::timestamp) OVER (PARTITION BY item, vendor) AS "最早订单日期"
FROM orders
QUALIFY MIN(orderdate::timestamp) OVER (PARTITION BY item, vendor) BETWEEN '2024-08-01'::timestamp AND '2024-10-30'::timestamp;

动态日期区间适配 #

如果需要基于当前日期动态计算区间（如当前月份前1个月到后2个月的最后一天），可替换WHERE中的固定日期为动态表达式：

WHERE global_min_orderdate BETWEEN 
    date_trunc('month', CURRENT_DATE) - interval '1 month' 
    AND date_trunc('month', CURRENT_DATE) + interval '2 months' - interval '1 day';

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