同一个表的MySQL查询

Question

我希望我这篇文章没有违反任何规则。我得到了一个名为order_products的表(在instacart数据库中)，其中有order_id和product_id列，描述了每个订单中购买的产品。

下面是CREATE TABLE

-- Name: order_products; Type: TABLE; Schema: public; Owner: -
--
CREATE TABLE public.order_products (
order_id integer NOT NULL,
product_id integer NOT NULL,
add_to_cart_order integer,
reordered integer
);

下面是请求的数据示例

----------+------------+
| Order_id | Product_id |
+----------+------------+
| 123      | 741        |
+----------+------------+
| 123      | 742        |
+----------+------------+
| 123      | 852        |
+----------+------------+
| 234      | 852        |
+----------+------------+
| 234      | 963        |
+----------+------------+
| 456      | 741        |
+----------+------------+
| 456      | 742        |
+----------+------------+
| 456      | 201        |
+----------+------------+
| 456      | 202        |
+----------+------------+
| 567      | 741        |
+----------+------------+
| 567      | 742        |
+----------+------------+
| 567      | 201        |
+----------+------------+
| 789      | 963        |
+----------+------------+
| 789      | 201        |
+----------+------------+
| 789      | 202        |
+----------+------------+
| 789      | 203        |
+----------+------------+
| 789      | 204        |
+----------+------------+
| 100      | 741        |
+----------+------------+
| 100      | 741        |
+----------+------------+
| 100      | 201        |
+----------+------------+

我想查询在一个单次排序中排序最多的对。在上面的例子中，如果它们在一个订单中一起订购了4次，那么它将是产品id 741和742对。

我尝试了以下基于自连接

的操作

SELECT p1.product_id, count(p1.product_id), p2.product_id, count(p2.product_id)
FROM order_products AS p1, order_products AS p2
WHERE p1.order_id = p2.order_id
GROUP BY p1.product_id, p2.product_id
ORDER BY 2 DESC

没有运气，因为它只返回购买最多的产品及其计数两次，而我需要购买最多的产品对。我想不出这个问题的答案，有人能帮我吗?谢谢你

Answer 1

您可以使用自连接和聚合，如下所示:(这将返回一起购买的所有产品对)

Select OP1.product_id As Product1,
OP2.product_id As Product2,
Count(*) As NumberOfOrders
From order_products OP1 Join order_products OP2
On OP1.order_id = OP2.order_id And 
OP1.product_id > OP2.product_id
Group By OP1.product_id, OP2.product_id
Order By Count(*) Desc

如果您想返回购买次数最多的对产品，您可以使用DENSE_RANK函数如下:

Select Product1, Product2, NumberOfOrders
From
(
Select OP1.product_id As Product1,
OP2.product_id As Product2,
Count(*) As NumberOfOrders,
DENSE_RANK() Over (Order By Count(*) Desc) rnk
From order_products OP1 Join order_products OP2
On OP1.order_id = OP2.order_id And 
OP1.product_id > OP2.product_id
Group By OP1.product_id, OP2.product_id
) T
Where rnk = 1

查看演示