时间戳

首页 > 解决方案 > 给定一组 ID，返回仅具有这些 ID 的订单子集

sql - 给定一组 ID，返回仅具有这些 ID 的订单子集

问题描述

给定一组product_ids，只有order_ids那些是什么？product_ids

对于下面的示例，我只想要具有某种组合的 order_ids (a,b,c)。我有 2 个如下表：

“交易”表：

order_id | product_id |
---------+-------------
    1    |    a       |
    1    |    b       |
    2    |    a       |
    2    |    X       |
    3    |    a       |
    3    |    b       |
    3    |    c       |
    ...  |    ...     |
    999  |    Y       |

“产品”表：

product_id |
------------
     a     |
     b     |
     c     |
     d     |
     X     |
     Y     |
     ...   |
     ZZZ   |

所需输出有 2 个带有预期表输出的 order_id：

order_id |
----------
    1    |
    3    |

请注意，order_id == 2尽管它已被删除，product_id == a但因为它已product_id == X被删除，所以它应该被删除。

因此，这不是一个简单的：

SELECT DISTINCT(order_id)
FROM transactions
WHERE product_id IN (a, b, c)

标签： sqlpostgresqlamazon-redshiftrelational-division

解决方案

通常，有一张orders桌子与之配套，每个订单只有一行。

如果我们可以进一步假设每个订单总是至少有一个交易，这将完成这项工作：

SELECT o.id
FROM   orders o
WHERE  NOT EXISTS (
   SELECT FROM transactions  -- SELECT list can be empty for EXISTS test
   WHERE  order_id = o.id
   AND    product_id <> ALL ('{a,b,c}')
   );

这对于非常常见的 product_id 或长列表很有用。

对于短名单或稀有产品，首先从正面选择开始会更快。喜欢：

SELECT order_id
FROM  (
   SELECT DISTINCT order_id
   FROM   transactions
   WHERE  product_id = ANY ('{a,b,c}')
   ) t
WHERE  NOT EXISTS (
   SELECT FROM transactions
   WHERE  order_id = t.order_id
   AND    product_id <> ALL ('{a,b,c}')
   );

索引对(product_id)性能至关重要。更好的是，一个多列索引 on (product_id, order_id)，加上另一个 on (order_id, product_id)。看：

关于数组文字的手册：

关于ANYandALL构造：

推荐阅读