Найдите строку, связанную с Min/Max, без внутреннего цикла

У меня есть вопрос, связанный с T-SQL и SQL Server.

Скажем, у меня есть таблица заказов с двумя столбцами:

ProductId int
CustomerId int
Дата datetime

Я хочу дату первого заказа для каждого продукта, поэтому я выполняю этот тип запроса:

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

У меня есть индекс на ProductId, включая столбцы CustomerId и Date, чтобы ускорить запрос (IX_Orders). План запроса выглядит как некластеризованное сканирование индексов на IX_Orders, за которым следует агрегат потока (без учета индекса).

Теперь моя проблема в том, что я также хочу получить CustomerId, связанный с первым порядком для каждого продукта (продукт 26 был сначала заказан во вторник 25, заказчиком 12). Сложная часть состоит в том, что я не хочу, чтобы внутри плана выполнения не было внутреннего цикла, потому что это означало бы дополнительное чтение в ProductId в таблице, что крайне неэффективно.

Это должно быть возможно с использованием того же некластеризованного сканирования индекса, за которым следуют агрегаты потоков, однако я не могу найти запрос, который бы это сделал. Любая идея?

Спасибо

Ответ 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

это вернет те же результаты, просто используйте те же объявления и вставки, что и указанный выше код:

;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

Вы можете попробовать каждую версию, чтобы увидеть, какая из них будет работать быстрее...

Ответ 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

Оценочный план запроса для этого бесполезен, поскольку я издеваюсь над ним с помощью табличных переменных, но анонимное внутреннее соединение должно быть оптимизировано по подзапрос.

Ответ 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

Номинально, план запроса содержит Nested Loops.

Однако внешний цикл будет использовать Index Scan с Stream Aggregate, а внутренний цикл будет содержать Index Seek для 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 extra logical reads (что, вероятно, никогда не будет physical).

Не вижу, как можно улучшить этот запрос.

Также преимущество этого запроса заключается в том, что он прекрасно распараллеливается. Вы можете заметить, что время CPU в два раза меньше elapsed time: из-за распараллеливания на моем старом Core Duo.

A 4-core 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.

Ответ 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

Это лучшее, что я могу придумать, хотя, если честно, я не знаю, каковы характеристики производительности этого запроса. Если это нехорошо, я бы предположил запустить два запроса, чтобы получить нужную вам информацию.

Ответ 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

Ответ 6

Я не вижу способа сделать это красиво, не делая подзапроса или функцию окон (например, row_number, rank), поскольку max просматривает только один столбец.

Однако вы можете сделать это не красиво.

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

Это работает только в том случае, если ваш идентификатор клиента имеет менее 20 цифр.

EDIT: добавлено предложение group by.