sql - 计算两个项目一起订购次数的有效方法

Question

我目前遇到了一个问题，我必须编写一个 SQL 查询来计算一对项目一起订购的次数。

我可以使用的 table 是这样的:

ORDER_ID | PRODUCT_ID |    QUANTITY    
1               1             10
1               2             20
1               3             10
2               1             10
2               2             20
3               3             50
4               2             10

我希望编写一个 SQL 查询，它可以为每对唯一的元素计算它们一起订购的次数，并告诉我它们在同一订单中的数量。

结果表应如下所示:

   PRODUCT_ID_1 | PRODUCT_ID_2 | NUM_JOINT_ORDERS | SUM_QUANTITY_1 | SUM_QUANTITY__2    
    1               2             2                   20                 40 
    1               3             1                   10                 10
    2               3             1                   20                 10

一些可以利用的东西是:

有些订单只包含 1 个项目，因此与计算成对关系无关(不确定如何排除这些，但可能先过滤它们才有意义)

我们只需要在最终表中列出一次成对关系(所以可能是 WHERE PRODUCT_ID_1 < PRODUCT_ID_2)

有一个类似的帖子 here ，虽然我重新发布了这个问题，因为

我真的很想知道最快的方法，因为我的原始表很大而且我的计算资源有限，而且

在这种情况下，我只有一个表格，没有列出数字的表格。

Answer 1

您可以使用以下方法，它会为您提供如上所示的结果。

select
  PRODUCT1, PRODUCT2, count(*), sum(QUANTITY1), sum(QUANTITY2)
from (
  select
    T1.PRODUCT_ID AS PRODUCT1, 
    T2.PRODUCT_ID AS PRODUCT2, 
    T1.QUANTITY AS QUANTITY1, 
    T2.QUANTITY AS QUANTITY2
  from TABLE as T1, TABLE as T2
  where T1.ORDER_ID=T2.ORDER_ID
  and T1.PRODUCT_ID<T2.PRODUCT_ID
)
group by PRODUCT1, PRODUCT2