Przykład z życia, kiedy używać OUTER / CROSS APPLY w SQL

Patrzyłem CROSS / OUTER APPLYz kolegą i staramy się znaleźć przykłady z życia, gdzie ich używać.

Spędziłem sporo czasu, zastanawiając się, kiedy należy używać funkcji krzyżowej zamiast łączenia wewnętrznego? i googlowanie, ale główny (jedyny) przykład wydaje się dość dziwny (użycie liczby wierszy z tabeli do określenia, ile wierszy należy wybrać z innej tabeli).

Pomyślałem, że ten scenariusz może skorzystać na OUTER APPLY:

Tabela kontaktów (zawiera 1 rekord dla każdego kontaktu) Tabela wpisów komunikacji (może zawierać n numerów telefonów, faksów, e-maili dla każdego kontaktu)

Ale za pomocą podzapytania, typowych wyrażeń tabeli, OUTER JOINze RANK()i OUTER APPLYwszystkie wydają się wykonać jednakowo. Zgaduję, że to oznacza, że ​​scenariusz nie ma zastosowania do APPLY.

Udostępnij kilka przykładów z życia wziętych i pomóż wyjaśnić tę funkcję!

Niektóre zastosowania APPLYto ...

1) Najczęstsze N ​​zapytań na grupę (może być bardziej wydajne w przypadku niektórych liczebności)

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) Wywołanie funkcji wartościowanej w tabeli dla każdego wiersza w zapytaniu zewnętrznym

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

3) Ponowne użycie aliasu kolumny

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) Cofanie obracania więcej niż jednej grupy kolumn

Zakłada, że ​​1NF narusza strukturę tabeli ....

CREATE TABLE T
  (
     Id   INT PRIMARY KEY,

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

Przykład przy użyciu VALUESskładni 2008+ .

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

W 2005 UNION ALLmożna użyć zamiast.

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

Są różne sytuacje, w których nie można uniknąć CROSS APPLYlub OUTER APPLY.

Rozważ, że masz dwa stoły.

STÓŁ GŁÓWNY

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

TABELA SZCZEGÓŁÓW

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                                       

                                                            ZASTOSUJ KRZYŻ

Istnieje wiele sytuacji, w której musimy wymienić INNER JOINz CROSS APPLY.

1. Jeśli chcemy połączyć 2 tabele TOP nwyników z INNER JOINfunkcjonalnością

Rozważyć, czy musimy wybrać Idi Nameod Mastera ostatnie dwa terminy dla każdego Idz 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

Powyższe zapytanie generuje następujący wynik.

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

Widzisz, wygenerował wyniki dla ostatnich dwóch dat z ostatnimi dwoma datami, Ida następnie dołączył do tych rekordów tylko w zewnętrznym zapytaniu Id, co jest błędne. Aby to osiągnąć, musimy użyć 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

i tworzy następujący wynik.

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

Oto praca. Zapytanie wewnątrz CROSS APPLYmoże odwoływać się do zewnętrznej tabeli, gdzie INNER JOINnie może tego zrobić (zgłasza błąd kompilacji). Podczas wyszukiwania dwóch ostatnich dat, łączenie odbywa się wewnątrz CROSS APPLYtj WHERE M.ID=D.ID.

2. Kiedy potrzebujemy INNER JOINfunkcjonalności za pomocą funkcji.

CROSS APPLYmoże być używany jako zamiennik, INNER JOINgdy musimy uzyskać wynik z Mastertabeli i pliku function.

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

A oto funkcja

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

który wygenerował następujący wynik

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

                                                            APLIKACJA ZEWNĘTRZNA

1. Jeśli chcemy połączyć 2 tabele TOP nwyników z LEFT JOINfunkcjonalnością

Zastanów się, czy musimy wybrać identyfikator i nazwę od Masteroraz ostatnie dwie daty dla każdego identyfikatora z Detailstabeli.

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

co tworzy następujący wynik

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

To przyniesie błędne wyniki, tzn. Przyniesie tylko ostatnie dwie daty z Detailstabeli, niezależnie od Idtego, czy dołączymy do Id. Więc właściwym rozwiązaniem jest użycie 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

co daje następujący pożądany rezultat

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. Kiedy potrzebujemy LEFT JOINfunkcjonalności przy użyciu functions.

OUTER APPLYmoże być używany jako zamiennik, LEFT JOINgdy musimy uzyskać wynik z Mastertabeli i pliku function.

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

I funkcja jest tutaj.

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

który wygenerował następujący wynik

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

                             Wspólna cecha CROSS APPLYiOUTER APPLY

CROSS APPLYlub OUTER APPLYmoże służyć do zachowywania NULLwartości podczas unieruchamiania, które są wymienne.

Weź pod uwagę, że masz poniższą tabelę

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

Jeśli użyjesz UNPIVOTdo przeniesienia FROMDATEAND TODATEdo jednej kolumny, NULLdomyślnie wyeliminuje to wartości.

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

co generuje poniższy wynik. Zauważ, że przegapiliśmy rekord Idliczby3

  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

W takich przypadkach CROSS APPLYlub OUTER APPLYbędzie przydatna

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

który tworzy następujący wynik i zachowuje Idtam, gdzie jest jego wartość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

Przykładem z życia może być planista i chcesz zobaczyć, jaki jest najnowszy wpis dziennika dotyczący każdego zaplanowanego zadania.

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

Aby odpowiedzieć na powyższy punkt, przytocz przykład:

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

A teraz uruchom dwa zapytania z planem wykonania.

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

Jak widać, zewnętrzne zapytanie stosujące jest bardziej wydajne. (Nie można dołączyć planu, ponieważ jestem nowym użytkownikiem ... Doh.)