如果在不同的日子购买相同的商品

Question

我正在寻找在不同的日子里不止一次购买同一商品的顾客。我让它部分起作用了。如果不将客户名/姓和item_name添加到group by子句中，我将无法获得它。此外，我想包括一个计数，如果相同的uten是在不同的日子购买了多少次。

我怀疑小组讨论可能不是最好的解决办法。这是最好的解决办法，用一个自我连接，或可能是一个线索？

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

Answer 1

我会在这里使用存在逻辑：

SELECT DISTINCT c.first_name, c.last_name
FROM customers c
INNER JOIN purchases p
    ON p.customer_id = c.customer_id
WHERE EXISTS (
    SELECT 1
    FROM purchases p2
    WHERE p2.customer_id = p.customer_id AND
          p2.product_id = p.product_id   AND
          TRUNC(p2.purchase_date) <> TRUNC(p.purchase_date)
);

在简单的英语中，上面的查询说要找到所有在不同日期购买相同产品的顾客。

Answer 2

这可能是一种选择:使用解析形式的count函数和计数大于1的提取行；根据您发布的数据，是Lisa在两个不同的日期购买了棕色鞋子。

SQL> WITH
  2     temp
  3     AS
  4        (  SELECT c.first_name,
  5                  i.product_name,
  6                  TRUNC (p.purchase_date),
  7                  COUNT (*) OVER (PARTITION BY c.first_name, i.product_name) cnt
  8             FROM purchases p
  9                  JOIN customers c ON c.customer_id = p.customer_id
 10                  JOIN items i ON i.product_id = p.product_id
 11         GROUP BY c.first_name, i.product_name, TRUNC (p.purchase_date))
 12  SELECT DISTINCT first_name, product_name, cnt
 13    FROM temp
 14   WHERE cnt > 1;

FIRST PRODUCT_NAM        CNT
----- ----------- ----------
Lisa  Brown Shoes          2

SQL>