Автор: agorkov

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

ID	Phone	SMSType
Уникальный монотонно возрастающий идентификатор SMS.	Номер телефона на который отправлено сообщение	Тип сообщения

Первое решение, которое пришло мне в голову — использовать cross apply, чтобы для каждого уникального номера телефона подзапросом найти соответствующий тип:

;WITH [cte] 
     AS (SELECT [S].[phone] 
         FROM   [dbo].[smslog] AS [S] 
         GROUP  BY [S].[phone]) 
SELECT [ca].[id], 
       [cte].[phone], 
       [ca].[smstype] 
FROM   [cte] 
       CROSS apply (SELECT TOP 1 [ca_s].[id], 
                                 [ca_s].[smstype] 
                    FROM   [dbo].[smslog] AS [ca_s] 
                    WHERE  ( 1 = 1 ) 
                           AND ( [ca_s].[phone] = [cte].[phone] ) 
                    ORDER  BY [ca_s].[id] DESC) AS [ca] 

Довольно очевидным шагом является избавление от CROSS APPLY и замена его на JOIN:

;WITH [cte] 
     AS (SELECT [S].[phone], 
                Max ([S].[id]) AS ‘maxID’ 
         FROM   [dbo].[smslog] AS [S] 
         GROUP  BY [S].[phone]) 
SELECT [S].[id], 
       [S].[phone], 
       [S].[smstype], 
       [cte].[phone], 
       [cte].[maxid] 
FROM   [dbo].[smslog] AS [S] 
       INNER JOIN [cte] 
               ON ( 1 = 1 ) 
                  AND ( [S].[id] = [cte].[maxid] ) 

Даже без использования индексов второй вариант гораздо быстрее. Но, оказывается, есть ещё интересный вариант, который позволяет вообще избежать соединений. Для этого нам придётся прибегнуть к оконной функции:

;WITH [cte] 
     AS (SELECT [S].[id], 
                [S].[phone], 
                [S].[smstype], 
                Row_number () 
                  OVER ( 
                    partition BY [S].[phone] 
                    ORDER BY [S].[id] DESC ) AS ‘rn’ 
         FROM   [dbo].[smslog] AS [S]) 
SELECT [cte].[id], 
       [cte].[phone], 
       [cte].[smstype] 
FROM   [cte] 
WHERE  ( 1 = 1 ) 
       AND ( [cte].[rn] = 1 ) 

Все представленные выше планы запросов получены, когда на таблице нет никаких индексов, но важно понимать, что их добавление может значительно изменить план. Например, после добавления некластеризованного индекса по полям (Phone;ID) и включённым полем smstype в третьем запросе вообще исчезает этап сортировки, а время тратится практически исключительно на просмотр индекса.