SQL查询：按Customer_ID合并符合条件的用户购买记录

需求：获取过去5年内购买过Milk或Yogurt，且在该购买行为之后15个月内购买过Milk、Yogurt或Apple的所有用户记录，并按Customer_ID合并结果。

原始数据 #

目标输出（两种形式均可） #

形式一：关联合并式 #

形式二：明细式 #

尝试代码 #

with t1 as (
   select 
      customer_id
      , date
      , item
      , sum(volume)
   from table
   where item in ('milk', 'yogurt')
     and date between to_date(20181030) and to_date(2023-10-30)
  ),

t2 as (
      select 
      customer_id
      , date
      , item
      , sum(volume)
   from table
   where item in ('milk', 'yogurt','apple')
    and date between to_date(20181030) and to_date(2023-10-30)
  )

select 
   a.customer_id
   , a.date as day1
   , a.item as item1
   , a.volume as v1
   , b.date as day2
   , b.item as item2
   , b.volume as v2
from t1 
    inner join t2 
         on t1.customer_id = t2.customer_id
            and (t1.date < t2.date and t1.date - t2.date < 456)

当前问题 #

返回结果关联混乱，日期计算错误，同一用户出现多条重复记录，且用户后续15个月内多次购买时仅返回一行记录。

问题分析与解决方案 #

问题点拆解 #

• 日期逻辑错误：t1.date - t2.date < 456 完全无效，因为t1.date < t2.date时差值为负，必然小于456，应该用t2.date - t1.date <= 456或更准确的月份差函数（如MONTHS_BETWEEN）。
• 聚合语法错误：CTE中使用sum(volume)但未添加GROUP BY customer_id, date, item，会导致聚合结果错误甚至SQL无法执行。
• 关联重复问题：直接t1 join t2会产生笛卡尔积，若用户有多个初始/后续购买记录，会生成大量重复关联结果。
• 日期格式错误：to_date(2023-10-30)会被解析为数值计算（2023-10-30=1983），应该用字符串格式to_date('2023-10-30', 'YYYY-MM-DD')。

解决方案代码 #

以下以Oracle SQL为例，其他引擎（MySQL/BigQuery等）可调整日期函数适配：

方案一：获取符合条件的用户所有相关记录（明细式）

WITH initial_purchases AS (
    -- 筛选近5年购买Milk/Yogurt的记录
    SELECT 
        customer_id,
        date AS initial_date
    FROM your_table
    WHERE item IN ('Milk', 'Yogurt')
      AND date BETWEEN ADD_MONTHS(SYSDATE, -60) AND SYSDATE -- 动态计算过去5年
),
eligible_records AS (
    -- 筛选这些用户在初始购买后15个月内的目标商品记录
    SELECT DISTINCT
        t.customer_id,
        t.date,
        t.item,
        t.volume
    FROM your_table t
    JOIN initial_purchases ip 
        ON t.customer_id = ip.customer_id
        AND t.date BETWEEN ip.initial_date AND ADD_MONTHS(ip.initial_date, 15)
        AND t.item IN ('Milk', 'Yogurt', 'Apple')
)
SELECT *
FROM eligible_records
ORDER BY customer_id, date;

方案二：关联初始购买与后续购买（合并式）

WITH initial_purchases AS (
    SELECT 
        customer_id,
        date AS initial_date,
        item AS initial_item,
        volume AS initial_volume
    FROM your_table
    WHERE item IN ('Milk', 'Yogurt')
      AND date BETWEEN ADD_MONTHS(SYSDATE, -60) AND SYSDATE
),
subsequent_purchases AS (
    SELECT 
        customer_id,
        date AS sub_date,
        item AS sub_item,
        volume AS sub_volume
    FROM your_table
    WHERE item IN ('Milk', 'Yogurt', 'Apple')
      AND date BETWEEN ADD_MONTHS(SYSDATE, -60) AND SYSDATE
)
SELECT 
    ip.customer_id,
    ip.initial_date AS "Date 1",
    ip.initial_item AS "Item",
    ip.initial_volume AS "Volume",
    sp.sub_date AS "Date 2",
    sp.sub_item AS "Item",
    sp.sub_volume AS "Volume"
FROM initial_purchases ip
JOIN subsequent_purchases sp
    ON ip.customer_id = sp.customer_id
    AND sp.sub_date > ip.initial_date
    AND MONTHS_BETWEEN(sp.sub_date, ip.initial_date) <= 15
ORDER BY ip.customer_id, ip.initial_date, sp.sub_date;

关键优化说明 #

• 用ADD_MONTHS和MONTHS_BETWEEN替代天数计算，避免不同月份天数差异导致的误差。
• 动态计算时间范围（ADD_MONTHS(SYSDATE, -60)）比硬编码日期更灵活，无需定期修改。
• 添加DISTINCT避免重复记录，确保结果准确性。

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