如何查询不同日期重复购买同一商品的客户及相关信息

我需要找出在不同日期多次购买同一商品的客户，现有SQL代码已实现部分功能，但无法在不添加至GROUP BY子句的情况下获取客户FIRST_NAME、LAST_NAME和PRODUCT_NAME，还需统计该商品在不同日期的购买次数。怀疑GROUP BY并非最优方案，想知道用自连接（self JOIN）或LEAD函数是否更合适？

相关表结构及当前查询代码 #

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Abby', 'Katz' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Jerry', 'Torchiano' FROM DUAL;

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME) AS
SELECT 100, 'Black Shoes' FROM DUAL UNION ALL
SELECT 101, 'Brown Shoes' FROM DUAL UNION ALL
SELECT 102, 'White Shoes' FROM DUAL;

CREATE TABLE purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 100, 1, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-11 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2,101,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 3,102,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL;

With CTE as (
 SELECT customer_id
                ,product_id 
                ,trunc(purchase_date)
  FROM purchases
  GROUP BY customer_id 
                    ,product_id
                   ,trunc(purchase_date)
)
SELECT  customer_id, product_id
FROM CTE
GROUP BY customer_id                  ,product_id 
HAVING COUNT(1)>1

解决方案 #

方法一：优化现有GROUP BY方案（最直观易维护） #

不需要重构逻辑，只要在筛选出符合条件的客户-商品组合后，关联客户表和商品表获取名称，同时直接统计不同日期的购买次数：

WITH customer_product_dates AS (
    SELECT 
        customer_id,
        product_id,
        TRUNC(purchase_date) AS purchase_day
    FROM purchases
    GROUP BY customer_id, product_id, TRUNC(purchase_date)
), qualified_pairs AS (
    SELECT 
        customer_id,
        product_id,
        COUNT(*) AS purchase_days_count -- 统计不同日期的购买次数
    FROM customer_product_dates
    GROUP BY customer_id, product_id
    HAVING COUNT(*) > 1
)
SELECT 
    q.customer_id,
    c.first_name,
    c.last_name,
    q.product_id,
    i.product_name,
    q.purchase_days_count
FROM qualified_pairs q
JOIN customers c ON q.customer_id = c.customer_id
JOIN items i ON q.product_id = i.product_id;

逻辑说明：先按「客户-商品-日期」去重，再筛选出购买日期数>1的组合，最后通过关联表直接拿到客户和商品名称，无需在主GROUP BY中额外添加非聚合字段。

方法二：自连接实现 #

通过自连接匹配同一客户、同一商品但不同日期的记录，再去重得到目标组合，最后统计日期数：

WITH qualified_pairs AS (
    SELECT DISTINCT
        p1.customer_id,
        p1.product_id
    FROM purchases p1
    JOIN purchases p2 
        ON p1.customer_id = p2.customer_id
        AND p1.product_id = p2.product_id
        AND TRUNC(p1.purchase_date) != TRUNC(p2.purchase_date)
)
SELECT 
    q.customer_id,
    c.first_name,
    c.last_name,
    q.product_id,
    i.product_name,
    (SELECT COUNT(DISTINCT TRUNC(purchase_date)) 
     FROM purchases p 
     WHERE p.customer_id = q.customer_id AND p.product_id = q.product_id) AS purchase_days_count
FROM qualified_pairs q
JOIN customers c ON q.customer_id = c.customer_id
JOIN items i ON q.product_id = i.product_id;

注意点：必须加DISTINCT，否则同一客户-商品对会因多次配对产生重复结果。

方法三：LEAD窗口函数实现 #

用窗口函数按「客户-商品」分组、日期排序，检查下一条记录的日期是否与当前不同，标记符合条件的组合后统计：

WITH purchase_with_next_day AS (
    SELECT 
        customer_id,
        product_id,
        TRUNC(purchase_date) AS purchase_day,
        LEAD(TRUNC(purchase_date)) OVER (PARTITION BY customer_id, product_id ORDER BY purchase_date) AS next_purchase_day
    FROM purchases
), qualified_pairs AS (
    SELECT DISTINCT
        customer_id,
        product_id
    FROM purchase_with_next_day
    WHERE next_purchase_day IS NOT NULL 
        AND purchase_day != next_purchase_day
)
SELECT 
    q.customer_id,
    c.first_name,
    c.last_name,
    q.product_id,
    i.product_name,
    (SELECT COUNT(DISTINCT TRUNC(purchase_date)) 
     FROM purchases p 
     WHERE p.customer_id = q.customer_id AND p.product_id = q.product_id) AS purchase_days_count
FROM qualified_pairs q
JOIN customers c ON q.customer_id = c.customer_id
JOIN items i ON q.product_id = i.product_id;

优势：如果后续需要扩展分析（比如查看相邻购买的日期间隔），这种写法更容易调整。

方案对比 #

• GROUP BY优化方案：逻辑最清晰，性能稳定，适合绝大多数场景，也是最易维护的写法。
• 自连接：适合需要复杂关联条件的场景，但容易产生重复数据，大数据量下性能可能不如其他两种方法。
• 窗口函数：适合序列数据分析场景，代码稍复杂，但扩展性更强。

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