sql - 查找重复订单(按时间接近度)

Question

我有一个订单表，我知道这些订单有重复项

    customer   order_number   order_date
   ----------  ------------   -------------------
          1             1     2012-03-01 01:58:00
          1             2     2012-03-01 02:01:00
          1             3     2012-03-01 02:03:00
          2             4     2012-03-01 02:15:00
          3             5     2012-03-01 02:18:00
          3             6     2012-03-01 04:30:00
          4             7     2012-03-01 04:35:00
          5             8     2012-03-01 04:38:00
          6             9     2012-03-01 04:58:00
          6            10     2012-03-01 04:59:00

我想找到所有重复项(同一客户在 60 分钟内订购的订单)。包含“重复”行的结果集或包含重复项计数的所有客户的集合。

这是我尝试过的

SELECT
   customer,
   count(*)
FROM
   orders
GROUP BY
   customer,
   DATEPART(HOUR, order_date)
HAVING (count(*) > 1)

当重复项彼此间隔在 60 分钟内但处于不同时间(即 1:58 和 2:02)时，此功能不起作用

我也尝试过这个

SELECT
  o1.customer,
  o1.order_number,
  o2.order_number,
  DATEDIFF(MINUTE,o1.order_date, o2.order_date) AS [diff]
FROM
  orders o1 LEFT OUTER JOIN
  orders o2 ON o1.customer = o2.customer AND o1.order_number <> o2.order_number
WHERE
  ABS(DATEDIFF(MINUTE,o1.order_date, o2.order_date)) < 60

现在，这给了我所有的重复项，但它也给了我每个重复订单多行。即 (o1, o2) 和 (o2, o1) 如果没有一些具有多个重复项的订单，情况不会那么糟糕。在这些情况下，我得到 (o1, o2)、(o1,o3)、(o2, o1)、(o2, o3)、(o3, o1)、(o3, o2) 等。我得到所有排列。

有人有一些见解吗？我不一定要在这里寻找性能最好的答案，而只是寻找一个有效的答案。

Answer 1

SELECT
  *,
  CASE WHEN EXISTS (SELECT *
                      FROM orders AS lookup
                     WHERE customer    = orders.customer
                       AND order_date <  orders.order_date
                       AND order_date >= DATEADD(hour, -1, order_date)
                   )
       THEN 'Principle Order'
       ELSE 'Duplicate Order'
  END as Order_Status
FROM
  orders

使用EXISTS和相关子查询，您可以检查过去一小时内是否有任何先前的订单。