一千萬個為什麽

搜索

查找與最小/最大值相關聯的行,無內循環

我有一個與T-SQL和SQL Server相關的問題。

比方說,我有一個表2列的訂單:

  • ProductId int
  • CustomerId int
  • 日期日期時間

我想每個產品的第一個訂單的日期,所以我執行這種類型的查詢:

SELECT ProductId, MIN(Date) AS FirstOrder 
FROM Orders
GROUP BY ProductId

我有一個關於 ProductId 的索引,包括用於加速查詢( IX_Orders )的列 CustomerIdDate 。查詢計劃看起來像在 IX_Orders 上的非聚簇索引掃描,後面是一個流聚集(沒有歸功於索引)。

現在我的問題是,我還想要檢索與每個產品的第一個訂單相關聯的 CustomerId (產品26是在25日周二由客戶12首次訂購的)。棘手的部分是,我不希望執行計劃中有任何內部循環,因為這意味著表中會額外讀取每個 ProductId ,這非常低效。

這應該是可能的使用相同的非聚集索引掃描,其次是流聚合,但我似乎無法找到這樣做的查詢。任何想法?

謝謝

最佳答案

這將處理重復日期的產品:

DECLARE @Orders table (ProductId int
                      ,CustomerId int
                      ,Date datetime
                      )

INSERT INTO @Orders VALUES (1,1,'20090701')
INSERT INTO @Orders VALUES (2,1,'20090703')
INSERT INTO @Orders VALUES (3,1,'20090702')
INSERT INTO @Orders VALUES (1,2,'20090704')
INSERT INTO @Orders VALUES (4,2,'20090701')
INSERT INTO @Orders VALUES (1,3,'20090706')
INSERT INTO @Orders VALUES (2,3,'20090704')
INSERT INTO @Orders VALUES (4,3,'20090702')
INSERT INTO @Orders VALUES (5,5,'20090703')  --duplicate dates for product #5
INSERT INTO @Orders VALUES (5,1,'20090703')  --duplicate dates for product #5
INSERT INTO @Orders VALUES (5,5,'20090703')  --duplicate dates for product #5

;WITH MinOrders AS
(SELECT
     o.ProductId, o.CustomerId, o.Date
         ,row_number() over(partition by o.ProductId order by o.ProductId,o.CustomerId) AS RankValue
     FROM @Orders o
     INNER JOIN (SELECT
                     ProductId
                         ,MIN(Date) MinDate 
                     FROM @Orders 
                     GROUP BY ProductId
                ) dt ON o.ProductId=dt.ProductId AND o.Date=dt.MinDate
 )
SELECT
    m.ProductId, m.CustomerId, m.Date
    FROM MinOrders  m
    WHERE m.RankValue=1
    ORDER BY m.ProductId, m.CustomerId

這將返回相同的結果,只需使用與上面的代碼相同的聲明並插入即可:

;WITH MinOrders AS
(SELECT
     o.ProductId, o.CustomerId, o.Date
         ,row_number() over(partition by o.ProductId order by o.ProductId,o.CustomerId) AS RankValue
     FROM @Orders o
 )
SELECT
    m.ProductId, m.CustomerId, m.Date
    FROM MinOrders  m
    WHERE m.RankValue=1
    ORDER BY m.ProductId, m.CustomerId

你可以嘗試每個版本,看看哪個更快運行...

轉載註明原文: 查找與最小/最大值相關聯的行,無內循環