如何基于特定条件提取Transaction_Data表中的重复订单

问题场景 #

需要从Transaction_Data表中检索重复订单，表包含字段：Guest_ID、Order_ID、Name、Quote_Date、Arrival_Date、Sale_Location、Product_Code、Deposit_Amount。

尝试过的无效查询 #

第一种查询 #

SELECT Guest_ID,Order_ID,COUNT(Order_ID) AS Quantity,Name,Quote_Date,Arrival_Date,Sale_Location,Product_Code,Deposit_Amount 
FROM Transaction_Data WHERE Quote_date >='2022-11-01' and Deposit_Amount NOT LIKE '-%'
GROUP BY Guest_ID,Order_ID,Name,Quote_Date,Arrival_Date,Sale_Location,Product_Code,Deposit_Amount
HAVING COUNT (Order_ID) >1
ORDER BY Order_ID,Guest_ID

第二种查询 #

SELECT Guest_ID,Order_ID,Name,Quote_Date,Arrival_Date,Sale_Location,Product_Code,Deposit_Amount 
INTO #TempOrder
FROM Transaction_Data WHERE Quote_Date >='2022-11-01' and Deposit_Amount NOT LIKE '-%'

SELECT Guest_ID,Order_ID,
(SELECT MAX (Order_ID)
FROM Transaction_Data TD
WHERE TD.Order_ID < TO.Order_ID
) AS Prev_Order,
(SELECT MIN(Order_ID)
FROM Transaction_Data TD
WHERE TD.Order_ID > TO.Order_ID
) As Nxt_Order, TO.Name,TO.Quote_Date,TO.Arrival_Date,TO.Sale_Location,TO.Product_Code,TO.Deposit_Amount
FROM #TempOrder

问题说明 #

以上两种查询均未得到预期结果：仅返回了前后相邻的Order_ID，未按Guest维度筛选重复记录。预期提取的是Guest_ID为1的三条重复订单：

最终解决方法 #

通过创建临时表结合窗口函数的SQL语句实现需求，正确统计了重复数量并完成分组，得到预期结果：

DROP TABLE IF EXISTS #TempRes;

SELECT rth.ip_number,
       ror.reservation_id,
       COUNT(ror.reservation_id) OVER (PARTITION BY rth.ip_number,
                                                    ror.quote_date,
                                                    rdd.deposit_amount
                                       ORDER BY ror.reservation_id
                                      ) AS quantity,
       MIN(ror.reservation_id) OVER (PARTITION BY rth.ip_number, ror.quote_date, rdd.deposit_amount) AS min_order,
       ror.name,
       ror.quote_date,
       ror.arrival_date,
       rth.sale_location_code,
       rdd.deposit_amount
INTO #TempRes
FROM dbo.r_order_reservation ror
    JOIN dbo.r_transaction_header rth
        ON rth.reservation_id = ror.reservation_id
    JOIN dbo.r_transaction_detail rtd
        ON rtd.reservation_id = ror.reservation_id
           AND rtd.product_header_code != '7777777'
    JOIN dbo.r_deposit_detail rdd
        ON rdd.reservation_id = ror.reservation_id
WHERE ror.quote_date > '2022-12-01'
      AND ror.operator_id = 'freeride'
      AND rdd.deposit_amount NOT LIKE '-%';

SELECT *
FROM #TempRes tr
WHERE tr.quantity > 1
ORDER BY tr.reservation_id,
         tr.ip_number;

DROP TABLE #TempRes;

内容的提问来源于stack exchange，提问作者SunBrnt