MSSql: Использование оператора APPLY в TSql

    Недавно, реализуя некоторый код доступа к данным, я столкнулся с задачей выбора последних N записей для каждой сущности. Пользователь kuda78 подсказал вместо многоэтажной выборки использовать метод SelectMany.
    Исследуя, какой SQL код создает LinqToSQL, я натолкнулся на интересный SQL оператор APPLY.


    Как гласит MSDN эта команда выполняет следующее:
    http://technet.microsoft.com/en-us/library/ms175156.aspx
    Оператор APPLY позволяет вызывать возвращающую табличное значение функцию для каждой строки, возвращаемой внешним табличным выражением запроса. Возвращающая табличное значение функция выступает в роли правого входа, а внешнее табличное выражение — в роли левого входа. Правый вход оценивается для каждой строки из левого входа, а созданные строки объединяются для конечного вывода. Список столбцов, созданных оператором APPLY, является набором столбцов в левом входе, за которым следует список столбцов, возвращенный правым входом.

    Как оказалось APPLY очень хорошо подходит к решению поставленной задачи.

    Давайте рассмотрим на примере:
    Задача: Выбрать 10 последних заказов для каждого заказчика.

    Пускай имеем следующую простую структуру БД:
    
    CREATE TABLE Customer
    (
    	CustomerID INT PRIMARY KEY,
    	CustomerName NVARCHAR(30) NOT NULL
    )
    
    CREATE TABLE Nomenclature
    (
    	NomenclatureID INT PRIMARY KEY,
    	NomenclatureName NVARCHAR(30) NOT NULL,
    	Price MONEY  NOT NULL
    )
    
    CREATE TABLE Deal
    (
    	DealID INT IDENTITY(1, 1) PRIMARY KEY,
    	CustomerID INT NOT NULL,
    	NomenclatureID INT NOT NULL,
    	[Count] DECIMAL(8,2) NOT NULL,
    	DealDate DATETIME NOT NULL
    )
    


    Теперь нам надо выбрать 10 последних заказов для каждого заказчика. Раньше мы пользовались следующим подходом: сначала для каждого заказчика выбирал дату, начиная с которой у него было 10 заказов, а потом выбирал все заказы с этой даты.
    
    SELECT
    	d.DealDate,
    	c.CustomerName,
    	n.NomenclatureName,
    	n.Price,
    	d.Count
    FROM
    	Customer c JOIN Deal d ON
    		d.CustomerID = c.CustomerID
    	JOIN (SELECT  c.CustomerID,
    		(SELECT MIN(lastDeals.DealDate) FROM (SELECT TOP 10 d1.DealDate FROM Deal d1 WHERE
     d1.CustomerID = c.CustomerID ORDER BY d1.DealDate DESC) LastDeals) LastDealDate
    		FROM Customer c) ld ON
    		ld.CustomerID = c.CustomerID
    	JOIN Nomenclature n ON
    		n.NomenclatureID = d.NomenclatureID
    WHERE 
    	d.DealDate >= ld.LastDealDate 
    ORDER BY c.CustomerName, d.DealDate DESC
    

    * Для простоты я специально сделал допущение, что 2 заказа в один и тот же момент времени быть не могут.

    С использованием APPLY SQL код приобрел большую читаемость:
    
    SELECT 
    	d.DealDate,
    	c.CustomerName,
    	n.NomenclatureName,
    	n.Price,
    	d.Count
    FROM
    	Customer c 
    	OUTER APPLY (SELECT TOP 10 d1.* FROM Deal d1 Where d1.CustomerID = c.CustomerID ORDER BY d1.DealDate DESC) d
    	INNER JOIN Nomenclature n ON
    		n.NomenclatureID = d.NomenclatureID
    ORDER BY c.CustomerName, d.DealDate DESC
    

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

    Файл создания бд с индексами: CreateDB.txt
    Файл с SQL запросами: Queries.txt

    Средняя зарплата в IT

    110 000 ₽/мес.
    Средняя зарплата по всем IT-специализациям на основании 8 355 анкет, за 2-ое пол. 2020 года Узнать свою зарплату
    Реклама
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее

    Комментарии 16

      +3
      Сравните планы выполнения, мне кажется, так будет оптимальней:
      select t.rn
      	 , t.dealid deal_id
      	 , t.customerid cust_id, c.customername cust_name
      	 , t.nomenclatureid nom_id, nom.nomenclaturename nom_name
      	 , t.dealdate, t.qty
      from (
      	select d.DealID, d.customerid, nomenclatureid
      		 , [COUNT] qty -- нехорошо :(
      		 , dealdate 
      		 -- нумеруем отдельно заказы каждого клиента с сортировкой по дате
      		 , ROW_NUMBER() over( partition by customerid order by dealdate desc ) rn
      	from Deal d
      	) t
      	join customer c on( c.customerid = t.customerid )
      	join nomenclature nom on( nom.nomenclatureid = t.nomenclatureid )
      where t.rn <= 10
      order by cust_id, dealdate
      Или же я не правильно понял задачу.
        0
        Все правильно.
        Большое спасибо, ваш вариент работает быстрее.
          +1
          Сравнил план выполнения.

          В таблице customer 5000 записей, в таблице Deal около 10 миллионов.

          Используя OUTER APPLY:
          Время исполнения: 1 сек
          Estimated subtree cost: 0,17.
          Profiler — CPU: 765
          Profiler — Reads: 34973
          Profiler — Duration: 783

          Используя ROW_NUMBER estimated subtree cost 450.00.
          Время исполнения: 48 сек
          Estimated subtree cost: 450,00.
          Profiler — CPU: 125979
          Profiler — Reads: 41707
          Profiler — Duration: 47346
            +1
            CREATE INDEX I_Deal ON Deal(CustomerID, DealDate, NomenclatureID)
            Индекс построен по возрастанию даты, а везде используется сортировка по убыванию. Предположу, что партишену более западло просматривать 10 миллионов строк в столь непотребном порядке.
              0
              Попробовал использовать 4 разных индекса:

              CREATE INDEX I_Deal1 ON Deal (CustomerID, DealDate)
              CREATE INDEX I_Deal2 ON Deal (CustomerID, DealDate desc)
              CREATE INDEX I_Deal3 ON Deal (DealDate desc, CustomerID)
              CREATE INDEX I_Deal3 ON Deal (DealDate, CustomerID)
              


              Разницы никакой не обнаружил.
              По результатам изучения Execution Plan для скриптов, которые приложены к примеру, в любом случае при использовании ROW_NUMBER сначала производится расставление номеров для всех строк, т.е. до фильтра «t.rn <= 10» доходит больше миллиона строк, а уже только после фильтрации получается 30 строк.
          0
          Сгенерировал 1M записей в табличку Deal следующим кодом:
          USE Claims 
          GO
          
          SET NOCOUNT ON
          GO
          
          DECLARE @i INT
          
          SET @i = 1
          
          WHILE @i < 1000000
          BEGIN
          	INSERT INTO Deal(CustomerID, NomenclatureID, [Count], DealDate)
          	SELECT (@i % 3) + 1, (@i % 3) + 1, 250, DateADD(n, -@i, '2010-01-01')
          	
          	SET @i = @i + 1
          END
          

          Результат сравним с результатом kuda78
          Если судить по ExecutionPlan, cервер сначала нумерует ВСЕ строки таблицы DEALS и только потом начинает перемножать её с другими таблицами.
          • НЛО прилетело и опубликовало эту надпись здесь
              +7
              Люди, которые считают что они занют все, сильно раздражают нас, людей которые действительно знают все.

              0
              согласен с предыдущим товарищем.

              к аффтару: а вы чего такой поверхностный? хоть бы написали, чем cross apply от outer apply отличается.

              еще могу порекомендовать прочитать книжку:
              www.amazon.com/Microsoft%C2%AE-Server%C2%AE-T-SQL-Fundamentals-PRO-Developer/dp/0735626014

              в интернете полно где лежит.
                +1
                Может быть, но я не ставил целью дать описание оператора, это можно прочитать в MSDN, ссылку я дал.
                Просто хотел поделиться с сообществом интересной находкой. Лично я про этот оператор не знал, и он помог мне элегантно решить проблему, которую мы раньше решали неудобно и громоздко.

                Если комунибудь из коллег, этот оператор (семейство операторов) поможет решать их задачи, значит я писал этот пост не зря.

                Вот, например, решение от abyrvalg я так же считаю интресным, и возму его себе на заметку.

                За книжку большое спасибо, на досуге почитаю.
                0
                Вы попали пальцем в небо, преподносите давно существующую и относительно известную фичу как открытие Америки. Например, кто работает с xml столбцами знает, что без cross apply xml.nodes никуда…

                Из примеров кода, которые видел в последнее время, больше всего восхитила остроумная выборка значений поля в таблице в строку с разделителями в один запрос

                select (
                select [column] + [delimeter] as 'data()'
                from [table] as A
                for xml path(''))
                  0
                  Никакой Америки. Просто поиск оптимального решения конкретной задачи.

                  P.S Коллеги, давайте вопрос известности/неизвестности оператора APPLY оставим вне рамок этой темы, тем более, как показывает статистика, далеко не все, кто применяет TSql, знакомы со всеми его возможностями.

                    0
                    Ну в таком случае, давайте весь BOL сюда скопируем, ведь не все знакомы со всеми возможностями, кому то может пригодится.
                  0
                  > Правый вход оценивается

                  Имелось в виду вычисляется

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое