在不使用内部循环的情况下找到与Min/Max相关联的行

Question

我有一个与T-SQL和SQL Server相关的问题。

假设我有一张名为Orders的表格，其中有两列：

• ProductId int
• CustomerId int
• Date datetime

我想要每个产品的第一个订单日期，因此我执行以下查询：

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

我在ProductId上创建了索引，包括列CustomerId和Date以加速查询（IX_Orders）。查询计划看起来像是在IX_Orders进行非聚集索引扫描，然后进行流式聚合操作（由于有索引，无需排序）。

现在我的问题是，我还想检索与每个产品的第一笔订单相关联的CustomerId（Product 26在星期二25日由客户12首次订购）。棘手的部分是我不想在执行计划中有任何内部循环，因为这将意味着在表中每个ProductId的额外读取，这非常低效。

应该可以使用相同的非聚集索引扫描，紧接着的是流式聚合操作，但我似乎找不到可以实现这一点的查询。有什么想法吗？

谢谢

- Fcharlon

Answer 1

这将处理具有重复日期的产品：

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

这将返回相同的结果，只需使用与上述代码相同的declare和inserts：

;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

您可以尝试每个版本，以查看哪个运行速度更快...

- KM.

---

很好，只有一个索引扫描，但是在查询执行计划中它会进行排序。 - Fcharlon

Answer 2

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')

select O.* from @Orders O inner join 
(
    select ProductId,
    MIN(Date) MinDate 
    from @Orders 
    group by ProductId
) FO
on FO.ProductId = O.ProductId and FO.MinDate = O.Date

由于我正在使用表变量进行模拟，因此此查询的估计查询计划是无用的，但匿名内连接应该优化为子查询。

- butterchicken

---

您的选择需要包括 FO.MinDate。 - pjp

我从来没听说过“匿名”连接，我总是用“派生表”这个术语。 - KM.

这将无法正常工作，如果相同产品的最小日期有多行。试一下，将此代码添加到示例中： insert into @Orders values (5,1,'20090703'); insert into @Orders values (5,5,'20090703') 你将会在结果集中获得多个产品5。 - KM.

我认为关键在于OP想要的不仅仅是每个产品的第一个订单日期：“我还想检索与每个产品的第一个订单相关联的CustomerId”。在这种情况下，OP需要确定在两个答案相同的情况下如何选择单个答案; 可以简单地选择具有最低CustomerId的那个。 - butterchicken

只有 OP 能回答他们想要什么。但是他们没有说他们想要每个客户的第一个日期。在我的解决方案中，我每个产品只返回一行，并且通过使用最低的 CustomerId 来打破关系。 - KM.

Answer 3

在SQL Server 2005及以上版本中：

SELECT  oo.*
FROM    (
        SELECT  DISTINCT ProductId
        FROM    Orders
        ) od
CROSS APPLY
        (
        SELECT  TOP 1 ProductID, Date, CustomerID
        FROM    Orders oi
        WHERE   oi.ProductID = od.ProductID
        ORDER BY
                Date DESC
        ) oo

名义上，查询计划包含嵌套循环。

但是，外部循环将使用索引扫描与流聚合，而内部循环将包含对ProductID的索引查找和Top。

实际上，第二个操作几乎是免费的，因为在内部循环中使用的索引页面很可能已经驻留在缓存中，因为它刚刚被用于外部循环。

以下是在1,000,000行（其中有100个DISTINCT ProductID）上的测试结果：

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

(строк обработано: 100)
Table 'Orders'. Scan count 103, logical reads 6020, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 125 ms.

虽然这只是一个简单的SELECT DISTINCT查询的结果：

SELECT  od.*
FROM    (
        SELECT  DISTINCT ProductId
        FROM    Orders
        ) od

还有统计数据：

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

(строк обработано: 100)
Table 'Orders'. Scan count 3, logical reads 5648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 250 ms,  elapsed time = 125 ms.

正如我们所看到的，性能相同，而CROSS APPLY只需要额外的400个逻辑读取（很可能永远不会是物理读取）。

不知道如何进一步改进这个查询。

此查询的好处是可以很好地并行化。您可能会注意到CPU时间是经过时间的两倍：这是由于在我旧的Core Duo上并行处理。

4核CPU将在半小时内完成此查询。

使用窗口函数的解决方案无法并行化：

SELECT  od.*
FROM    (
        SELECT  ProductId, Date, CustomerID, ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Date DESC) AS rn
        FROM    Orders
        ) od
WHERE   rn = 1

这里是统计数据：

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(строк обработано: 100)
Table 'Orders'. Scan count 1, logical reads 5123, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 406 ms,  elapsed time = 415 ms.

- Quassnoi

---

需要将where更改为：WHERE oi.ProductID = od.ProductID。我的查询计划显示了排序和嵌套循环。 - Shannon Severance

我收到了“_Msg 4104，Level 16，State 1，Line 2 The multi-part identifier "oo.ProductID" could not be bound._”的错误信息。按照@Shannon Severance的建议，将WHERE更改为正确的语法即可解决问题。 - KM.

好的，忘记修复了。@Shannon：你按照 @op 的说法创建了索引吗？CREATE INDEX IX_orders_pdc ON Orders (ProductID, Date, CustomerID)。 - Quassnoi

我的索引是（ProductId，Date，CustomerId）。我将使用（ProductId，Date，CustomerId）进行检查。 - Shannon Severance

@Quassnoi：在索引中移动列，我确实失去了排序。还是像你在回答中指出的那样，使用带有Seek的循环。 - Shannon Severance

Answer 4

SELECT
    o1.productid, 
    o1.date, 
    o1.customerid
FROM
    Orders o1
JOIN
    (select productid, min(date) as orderDate
     from Orders
     group by productid
    ) firstOrder
ON o1.productid = firstOrder.productid

这是我能想到的最好方案，但说实话，我不知道这个查询的性能特征如何。如果不好，我建议运行两个查询来获取所需信息。

- Josh Smeaton

---

+1：我认为你需要在匿名连接中为min(date)定义一个别名；否则，这正是我得到的结果。如果有更好的方法，那就好了解一下。 - butterchicken

此查询得到错误答案，因为在o1和firstOrder之间的连接中没有包括日期。 - Martin Brown

在连接中，您不需要包括日期。 您只需要子查询的产品ID，因为它具有与之关联的最小日期。 返回实际结果的选择返回日期。 - Josh Smeaton

Answer 5

IX_Orders按ProductId、然后是CutomerId、最后是Date排序，还是按ProductId、然后是Date、最后是CustomerId排序？如果是前者，请将其更改为后者。

换句话说，不要使用这个：

create index IX_Orders on Orders (ProductId, CustomerId, Date) 

请使用以下内容：

create index IX_Orders on Orders (ProductId, Date, CustomerId)

那么如果你这样做：

SELECT o1.* 
FROM [Order] o1
JOIN
    (
        SELECT ProductID, Min(Date) as Date
        FROM [Order]
        GROUP BY ProductID
    ) o2
    ON o1.ProductID = o2.ProductID AND o1.Date = o2.Date
ORDER BY ProductID

最终只有一个 IX_Orders 的索引扫描，但如果两个客户在同一时间订购相同的产品，则每个产品可能会获得多个行。您可以通过使用以下查询来解决此问题，但效率不如第一个查询：

WITH cte AS
(
    SELECT ProductID, CustomerID, Date, 
        ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY Date ASC) AS row
    FROM [Order]
)
SELECT ProductID, CustomerId, Date
FROM cte
WHERE row = 1
ORDER BY ProductID

- Martin Brown

Answer 6

我认为没有一种优雅的方式可以做到这一点，除非使用子查询或窗口函数（例如row_number、rank），因为最大值仅查看其中一个列。

但是你可以采用不太优雅的方式。

SELECT
    productid, 
    min(date), 
cast(
    substring( 
        min(convert(varchar(23),date,21) + cast(customerid as varchar(20)))
              , 24, 44)
    as int) customerid
from 
    orders
group by
    productid 

只有当您的客户ID少于20位数字时，此方法才有效。

编辑： 添加了group by子句

- David Raznick

---

Msg 8120，级别16，状态1，行51 因为它没有包含在聚合函数或GROUP BY子句中，所以选择列表中的列“@orders.ProductId”无效。 - KM.

哎呀，忘记加上 group by 子句了。 - David Raznick