我試圖找到在不同日子多次購買同一商品的客戶。我讓它部分作業。如果不將客戶的名字/姓氏和 item_name 添加到 group by 子句中，我將無法獲得它。另外，我想包括一個計數，如果在不同的日子里購買了多少次相同的 uten。
我懷疑 group by 可能不是最好的解決方案。使用 self JOIN 或潛在客戶會更好地解決這個問題嗎？
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
uj5u.com熱心網友回復：
我會在這里使用存在邏輯：
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)
);
用簡單的英語來說，上面的查詢表示要查找購買相同產品但在不同日期的所有客戶。
uj5u.com熱心網友回復：
這可能是一種選擇：使用count分析形式的函式和計數大于 1 的獲取行；根據你發布的資料，是麗莎在兩個不同的日期買了棕色鞋子。
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>
uj5u.com熱心網友回復：
您不需要“自我加入”或“不存在”。甚至還有決議計數功能。您只需要一組。
with 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),
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),
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)
-- SQL Query
select p.product_id, p.customer_id, c.first_name, c.last_name, i.product_name,
count(*) cnt_purchases, count(distinct trunc(purchase_date)) cnt_dist_days
from purchases p
left join customers c on p.customer_id = c.customer_id
left join items i on p.product_id = i.product_id
group by p.product_id, p.customer_id, c.first_name, c.last_name, i.product_name
having count(distinct trunc(purchase_date)) > 1;
PRODUCT_ID CUSTOMER_ID FIRST_NAME LAST_NAME PRODUCT_NAME CNT_PURCHASES CNT_DIST_DAYS
---------- ----------- ---------- --------- ------------ ------------- -------------
101 2 Lisa Saladino Brown Shoes 2 2
https://dbfiddle.uk/nqGdtxAY
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/522694.html