Пример реальной жизни, когда использовать OUTER/CROSS APPLY в SQL

Я смотрел на CROSS / OUTER APPLY с коллегой, и мы изо всех сил пытаемся найти реальные примеры того, где их использовать.

Я потратил довольно много времени на Когда мне следует использовать Cross Apply for Inner Join? и googling, но основной (только) пример кажется довольно странным (используя rowcount из таблицы, чтобы определить, сколько строк выбрать из другой таблицы).

Я думал, что этот сценарий может выиграть от OUTER APPLY:

Таблица контактов (содержит 1 запись для каждого контакта) Таблица протоколов связи (может содержать n телефон, факс, электронную почту для каждого контакта)

Но использование подзапросов, общие выражения таблиц, OUTER JOIN с RANK() и OUTER APPLY все, похоже, работают одинаково. Я предполагаю, что это означает, что сценарий не применим к APPLY.

Поделитесь некоторыми примерами реальной жизни и помогите объяснить эту функцию!

Ответы

Ответ 1

Некоторые использования для APPLY являются...

1) Наибольшее количество запросов по каждой группе (может быть более эффективным для некоторых мощностей)

SELECT pr.name,
       pa.name
FROM   sys.procedures pr
       OUTER APPLY (SELECT TOP 2 *
                    FROM   sys.parameters pa
                    WHERE  pa.object_id = pr.object_id
                    ORDER  BY pr.name) pa
ORDER  BY pr.name,
          pa.name

2) Вызов функции с таблицей для каждой строки внешнего запроса

SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

3) Повторное использование псевдонима столбца

SELECT number,
       doubled_number,
       doubled_number_plus_one
FROM master..spt_values
CROSS APPLY (SELECT 2 * CAST(number AS BIGINT)) CA1(doubled_number)  
CROSS APPLY (SELECT doubled_number + 1) CA2(doubled_number_plus_one)

4) Невозможно выделить несколько групп столбцов

Предполагает 1NF, нарушающую структуру таблицы....

CREATE TABLE T
  (
     Id   INT PRIMARY KEY,

     Foo1 INT, Foo2 INT, Foo3 INT,
     Bar1 INT, Bar2 INT, Bar3 INT
  );

Пример использования синтаксиса 2008+ VALUES.

SELECT Id,
       Foo,
       Bar
FROM   T
       CROSS APPLY (VALUES(Foo1, Bar1),
                          (Foo2, Bar2),
                          (Foo3, Bar3)) V(Foo, Bar);

В 2005 году можно использовать UNION ALL.

SELECT Id,
       Foo,
       Bar
FROM   T
       CROSS APPLY (SELECT Foo1, Bar1 
                    UNION ALL
                    SELECT Foo2, Bar2 
                    UNION ALL
                    SELECT Foo3, Bar3) V(Foo, Bar);

Ответ 2

Существуют различные ситуации, когда вы не можете избежать CROSS APPLY или OUTER APPLY.

У вас есть две таблицы.

МАСТЕР-ТАБЛИЦА

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

ТАБЛИЦА ДЕТАЛЕЙ

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

&nbsp CROSS APPLY

Существует много ситуаций, когда нам нужно заменить INNER JOIN на CROSS APPLY.

1. Если мы хотим присоединиться к 2 таблицам на TOP n результатах с INNER JOIN функциональностью

Рассмотрим, нужно ли нам выбирать Id и Name из Master и последние две даты для каждого Id из Details table.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

Вышеприведенный запрос генерирует следующий результат.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

См., он сгенерировал результаты для последних двух дат с последними двумя датами Id, а затем присоединил эти записи только к внешнему запросу на Id, что неверно. Для этого нам нужно использовать CROSS APPLY.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

и формирует его результат.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

Вот работа. Запрос внутри CROSS APPLY может ссылаться на внешнюю таблицу, где INNER JOIN не может этого сделать (выдает ошибку компиляции). При нахождении последних двух дат, соединение выполняется внутри CROSS APPLY т.е. WHERE M.ID=D.ID.

2. Когда нам нужны функции INNER JOIN, используя функции.

CROSS APPLY может использоваться как замена INNER JOIN, когда нам нужно получить результат из таблицы Master и function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

И вот функция

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE [email protected]
)

который породил следующий результат:

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x

&nbsp ВНЕШНЕЕ ПРИМЕНЕНИЕ

1. Если мы хотим объединить 2 таблицы в TOP n с помощью LEFT JOIN функциональности

Рассмотрим, нужно ли нам выбирать Id и Name из Master и последние две даты для каждой таблицы Id из Details.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
LEFT JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

который образует следующий результат:

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     |   NULL       |  NULL |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

Это приведет к неправильным результатам, т.е. приведет к появлению только последних двух данных даты из таблицы Details независимо от Id, хотя мы присоединяемся к Id. Поэтому правильное решение использует OUTER APPLY.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
OUTER APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

который образует следующий желаемый результат

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

2. Когда нам нужны функции LEFT JOIN, используя functions.

OUTER APPLY можно использовать в качестве замены LEFT JOIN, когда нам нужно получить результат из таблицы Master и function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
OUTER APPLY dbo.FnGetQty(M.ID) C

И функция здесь.

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE [email protected]
)

который породил следующий результат:

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

&nbsp Общая особенность CROSS APPLY и OUTER APPLY

CROSS APPLY или OUTER APPLY можно использовать для сохранения значений NULL, когда они не могут быть взаимозаменяемыми.

У вас есть таблица ниже

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   |    
|   3  |   NULL      |   NULL       | 
x------x-------------x--------------x

Когда вы используете UNPIVOT, чтобы принести FROMDATE AND TODATE в один столбец, по умолчанию будут удалены значения NULL.

SELECT ID,DATES
FROM MYTABLE
UNPIVOT (DATES FOR COLS IN (FROMDATE,TODATE)) P

который генерирует приведенный ниже результат. Обратите внимание, что мы пропустили запись Id number 3

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 |
  x------x-------------x

В таких случаях полезно использовать CROSS APPLY или OUTER APPLY

SELECT DISTINCT ID,DATES
FROM MYTABLE 
OUTER APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

который формирует следующий результат и сохраняет Id, где его значение 3

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 |
  |  3   |     NULL    |
  x------x-------------x

Ответ 3

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

select t.taskName, lg.logResult, lg.lastUpdateDate
from task t
cross apply (select top 1 taskID, logResult, lastUpdateDate
             from taskLog l
             where l.taskID = t.taskID
             order by lastUpdateDate desc) lg

Ответ 4

Чтобы ответить на вышеописанный пример, выполните следующие действия:

create table #task (taskID int identity primary key not null, taskName varchar(50) not null)
create table #log (taskID int not null, reportDate datetime not null, result varchar(50) not null, primary key(reportDate, taskId))

insert #task select 'Task 1'
insert #task select 'Task 2'
insert #task select 'Task 3'
insert #task select 'Task 4'
insert #task select 'Task 5'
insert #task select 'Task 6'

insert  #log
select  taskID, 39951 + number, 'Result text...'
from    #task
        cross join (
            select top 1000 row_number() over (order by a.id) as number from syscolumns a cross join syscolumns b cross join syscolumns c) n

И теперь запустите два запроса с планом выполнения.

select  t.taskID, t.taskName, lg.reportDate, lg.result
from    #task t
        left join (select taskID, reportDate, result, rank() over (partition by taskID order by reportDate desc) rnk from #log) lg
            on lg.taskID = t.taskID and lg.rnk = 1

select  t.taskID, t.taskName, lg.reportDate, lg.result
from    #task t
        outer apply (   select  top 1 l.*
                        from    #log l
                        where   l.taskID = t.taskID
                        order   by reportDate desc) lg

Вы можете видеть, что внешний запрос применяется более эффективно. (Не удалось прикрепить план, поскольку я новый пользователь... Doh.)