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

Question

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

Patrzyłem na CROSS / OUTER APPLY z kolegą i staramy się znaleźć prawdziwe przykłady, gdzie ich użyć.

Spędziłem sporo czasu patrząc na Kiedy powinienem użyć Cross Apply over Inner Join? i googling, ale główny (jedyny) przykład wydaje się dość dziwaczny(użycie liczby wierszy z tabeli do określenia, ile wierszy wybrać z innej tabeli).

Myślałem, że ten scenariusz może skorzystać z OUTER APPLY:

Tabela kontaktów (zawiera 1 rekord dla każdy kontakt) Tabela wpisów komunikacji (może zawierać n telefonu, faksu, e-maila dla każdego kontaktu)

Ale używając zapytań podrzędnych, popularnych wyrażeń tabel, OUTER JOIN z RANK() i OUTER APPLY wszystkie wydają się działać jednakowo. Zgaduję, że to oznacza, że scenariusz nie dotyczy APPLY.

Podziel się przykładami z prawdziwego życia i pomóż wyjaśnić tę funkcję!

94

sql sql-server sql-server-2005 sql-server-2008

Author: johnnyRose, 2012-02-14

Source

4 answers

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

Weź pod uwagę, że masz dwa stoliki.

TABELA GŁÓWNA

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

SZCZEGÓŁY TABELA

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

CROSS APPLY

Jest wiele sytuacji, w których musimy zastąpić INNER JOIN CROSS APPLY.

1. Jeśli chcemy dołączyć 2 tabele na TOP n wyniki z INNER JOIN funkcjonalność

Zastanów się, czy musimy wybrać Id i Name z Master i dwie ostatnie daty dla każdej Id z 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 dwóch ostatnich dat z dwiema ostatnimi datami Id, a następnie dołączył do tych rekordów tylko w zewnętrznym zapytaniu na Id, co jest złe. 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 formularze on po wyniku.

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

Tutaj jest praca. Zapytanie wewnątrz {[21] } może odwoływać się do zewnętrznej tabeli, gdzie INNER JOIN nie może tego zrobić (wyrzuca błąd kompilacji). Po znalezieniu dwóch ostatnich dat, łączenie odbywa się wewnątrz CROSS APPLY ie, WHERE M.ID=D.ID.

2. Kiedy potrzebujemy INNER JOIN funkcjonalności za pomocą funkcji.

CROSS APPLY Może być używany jako zamiennik z INNER JOIN, gdy musimy uzyskać wynik z Master tabeli i 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ące 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

ZASTOSOWANIE ZEWNĘTRZNE

1. Jeśli chcemy połączyć 2 tabele na TOP n wyniki z LEFT JOIN funkcjonalnością

Zastanów się, czy musimy wybrać Id i nazwę z Master i dwie ostatnie daty dla każdego Id z tabeli 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

, który 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

Przyniesie to błędne wyniki, tzn. przyniesie tylko dane z dwóch ostatnich dat z tabeli Details niezależnie od Id mimo że łączymy się z 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

, który tworzy 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. Gdy potrzebujemy LEFT JOIN funkcjonalności za pomocą functions.

OUTER APPLY może być używany jako zamiennik z LEFT JOIN, gdy musimy uzyskać wynik z Master tabeli i function.

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

I funkcja idzie tutaj.

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

Który wygenerował następujące 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 APPLY i OUTER APPLY

CROSS APPLY lub OUTER APPLY mogą być używane do zachowania wartości NULL podczas nieujawniania, które są wymienne.

Rozważ, ż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

Gdy użyjesz UNPIVOT do wprowadzenia FROMDATE I TODATE do jednej kolumny, domyślnie wyeliminuje ona NULL wartości.

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

Który generuje poniższy wynik. Zauważ, że przegapiliśmy zapis o Id liczba 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

W takich przypadkach przyda się CROSS APPLY lub OUTER APPLY

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

Który tworzy następujący wynik i zachowuje Id gdzie jego wartość wynosi 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

66

Author: Sarath Avanavu,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-02-28 14:21:45

Prawdziwym przykładem byłoby, gdybyś miał scheduler i chciał zobaczyć, jaki jest najnowszy wpis dziennika dla 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

7

Author: BJury,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-14 13:43:09

Aby odpowiedzieć na powyższy punkt wpisz 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

Widać, że zewnętrzne zapytanie zastosuj jest bardziej wydajne. (Nie mogłem dołączyć planu, ponieważ jestem nowym użytkownikiem... Doh.)

4

Author: BJury,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-14 16:27:12

score 137 · Accepted Answer

Niektóre zastosowania dla APPLY są...

1) Top N per group queries (może być bardziej wydajne dla niektórych cardinalities)

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 wartej tabeli dla każdego wiersza w zewnętrznym zapytaniu

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

3) Reusing a column alias

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

Zakłada 1NF strukturę tabeli....

CREATE TABLE T
  (
     Id   INT PRIMARY KEY,

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

Przykład użycia 2008 + VALUES składnia.

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

W 2005 UNION ALL może być stosowany 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);