我有一個如下資料集：
| transaction_id | store_id | product_id | 產品名稱 |
|---|---|---|---|
| 1 | 100 | p001 | 產品_1 |
| 1 | 100 | p002 | 產品_2 |
| 1 | 100 | p003 | 產品_3 |
| 4 | 100 | p002 | 產品_2 |
| 4 | 100 | p003 | 產品_3 |
| 5 | 100 | p002 | 產品_2 |
| 5 | 100 | p003 | 產品_3 |
| 7 | 100 | p001 | 產品_1 |
| 7 | 100 | p003 | 產品_3 |
| 8 | 101 | p002 | 產品_2 |
| 8 | 101 | p003 | 產品_3 |
| 9 | 101 | p001 | 產品_1 |
| 9 | 101 | p002 | 產品_2 |
| 2 | 101 | p001 | 產品_1 |
| 3 | 101 | p002 | 產品_2 |
| 3 | 101 | p003 | 產品_3 |
| 6 | 101 | p001 | 產品_1 |
| 6 | 101 | p002 | 產品_2 |
我正在嘗試找到一個查詢以提供如下輸出。
| 店鋪 | freq_prod_ids | count_of_transactions |
|---|---|---|
| 100 | p002, p003 | 3 |
| 100 | p001, p003 | 2 |
| 101 | p001, p002 | 2 |
| 101 | p002, p003 | 2 |
這基本上應該在每個商店的單次交易中給出前 2 [N=2] 個經常購買的產品組合。
請幫助進行 SQL 查詢以獲取此回應。
uj5u.com熱心網友回復：
您可以嘗試以下方法，在基于商店和產品對進行聚合之前執行自聯接。row_number用于檢索每個商店的前 2 個產品對。
SELECT
store_id, freq_prod_ids,count_of_transactions
FROM (
SELECT
t1.store_id,
CONCAT(t1.product_id,', ',t2.product_id) as freq_prod_ids,
COUNT(1) as count_of_transactions,
ROW_NUMBER() OVER (PARTITION BY t1.store_id ORDER BY COUNT(1) DESC) as rn
FROM my_table t1
INNER JOIN my_table t2 on t1.store_id = t2.store_id and
t1.product_id < t2.product_id and
t1.transaction_id = t2.transaction_id
GROUP BY t1.store_id,CONCAT(t1.product_id,', ',t2.product_id)
) t3 WHERE rn <=2
查看作業演示 db fiddle
讓我知道這是否適合您。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/415058.html
標籤：