wake-up-neo.com

Beispiel aus der Praxis, wenn OUTER / CROSS APPLY in SQL verwendet werden soll

Ich habe mir CROSS / OUTER APPLY Mit einem Kollegen angesehen und wir haben Mühe, Beispiele aus dem wirklichen Leben zu finden, wo wir sie verwenden können.

Ich habe ziemlich viel Zeit damit verbracht, Wann sollte ich Cross Apply over Inner Join verwenden? und googeln, aber das (einzige) Hauptbeispiel scheint ziemlich bizarr (anhand der Zeilenanzahl aus einer Tabelle zu bestimmen, wie viele Zeilen zur Auswahl aus einer anderen Tabelle).

Ich dachte, dieses Szenario könnte von OUTER APPLY Profitieren:

Kontakttabelle (enthält 1 Datensatz für jeden Kontakt) Kommunikationseintragstabelle (kann n Telefon, Fax, E-Mail für jeden Kontakt enthalten)

Unter Verwendung von Unterabfragen scheinen die allgemeinen Tabellenausdrücke OUTER JOIN Mit RANK() und OUTER APPLY Alle gleich zu funktionieren. Ich vermute, dies bedeutet, dass das Szenario nicht auf APPLY anwendbar ist.

Bitte teilen Sie einige Beispiele aus der Praxis mit und helfen Sie dabei, die Funktion zu erklären!

sqlsql-serversql-server-2008sql-server-2005
119
Lee Tickett

Einige Verwendungen für APPLY sind ...

1) Top N pro Gruppenabfragen (kann für einige Kardinalitäten effizienter sein)

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) Aufrufen einer Tabellenwertfunktion für jede Zeile in der äußeren Abfrage

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

3) Wiederverwenden eines Spaltenalias

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) Aufheben der Drehung mehrerer Spaltengruppen

Nimmt an, dass 1NF die Tabellenstruktur verletzt ....

CREATE TABLE T
  (
     Id   INT PRIMARY KEY,

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

Beispiel mit der Syntax 2008+ VALUES.

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

Im Jahr 2005 UNION ALL kann stattdessen verwendet werden.

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

Es gibt verschiedene Situationen, in denen Sie CROSS APPLY Oder OUTER APPLY Nicht vermeiden können.

Angenommen, Sie haben zwei Tabellen.

MASTER TABLE

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

DETAILS TABLE

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

Es gibt viele Situationen, in denen wir INNER JOIN Durch CROSS APPLY Ersetzen müssen.

1. Wenn wir 2 Tabellen zu TOP n - Ergebnissen mit INNER JOIN - Funktionalität verbinden möchten

Überlegen Sie, ob wir Id und Name aus Master und die letzten beiden Daten für jedes Id aus Details table Auswählen müssen.

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

Die obige Abfrage generiert das folgende Ergebnis.

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

Siehe, es hat Ergebnisse für die letzten beiden Daten mit dem Id der letzten beiden Daten generiert und diese Datensätze dann nur in der äußeren Abfrage für Id verknüpft, was falsch ist. Um dies zu erreichen, müssen wir CROSS APPLY Verwenden.

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

und bildet das folgende Ergebnis.

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

Hier ist die Arbeit. Die Abfrage in CROSS APPLY Kann auf die äußere Tabelle verweisen, wobei INNER JOIN Dies nicht kann (löst einen Kompilierungsfehler aus). Wenn Sie die letzten beiden Daten gefunden haben, erfolgt der Beitritt innerhalb von CROSS APPLY, Dh WHERE M.ID=D.ID.

2. Wenn wir die Funktion INNER JOIN Mit Funktionen benötigen.

CROSS APPLY Kann als Ersatz für INNER JOIN Verwendet werden, wenn das Ergebnis aus der Tabelle Master und einem function abgerufen werden soll.

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

Und hier ist die Funktion

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

was zu folgendem Ergebnis führte

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



OUTER APPLY

1. Wenn wir 2 Tabellen zu TOP n - Ergebnissen mit LEFT JOIN - Funktionalität verbinden möchten

Überlegen Sie, ob wir ID und Name aus Master und die letzten beiden Daten für jede ID aus der Tabelle Details auswählen müssen.

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

was das folgende Ergebnis ergibt

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

Dies führt zu falschen Ergebnissen, dh es werden nur die letzten zwei Datumsdaten aus der Tabelle Details unabhängig von Id übertragen, obwohl wir uns mit Id verbinden. Die richtige Lösung ist also die Verwendung von 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

was das folgende gewünschte Ergebnis ergibt

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. Wenn wir die Funktion LEFT JOIN Mit functions benötigen.

OUTER APPLY Kann als Ersatz für LEFT JOIN Verwendet werden, wenn das Ergebnis aus der Tabelle Master und einem function abgerufen werden soll.

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

Und die Funktion geht hier.

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

was zu folgendem Ergebnis führte

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



Gemeinsames Merkmal von CROSS APPLY Und OUTER APPLY

CROSS APPLY Oder OUTER APPLY Können verwendet werden, um NULL Werte beim Deaktivieren beizubehalten, die austauschbar sind.

Angenommen, Sie haben die folgende Tabelle

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

Wenn Sie UNPIVOT verwenden, um FROMDATE UND TODATE in eine Spalte zu verschieben, werden standardmäßig NULL Werte entfernt.

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

was das folgende Ergebnis erzeugt. Beachten Sie, dass wir den Datensatz von Id number 3 Verpasst haben.

  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

In solchen Fällen ist ein CROSS APPLY Oder OUTER APPLY Nützlich

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

dies ergibt das folgende Ergebnis und behält Id bei, wobei sein Wert 3 ist.

  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
81
Sarath Avanavu

Ein Beispiel aus der Praxis wäre, wenn Sie einen Scheduler hätten und sehen möchten, wie der letzte Protokolleintrag für jede geplante Aufgabe lautete.

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
8
BJury

Um den obigen Punkt zu beantworten, schlagen Sie ein Beispiel vor:

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

Führen Sie nun die beiden Abfragen mit einem Ausführungsplan aus.

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

Sie sehen, dass die äußere Anwenden-Abfrage effizienter ist. (Konnte den Plan nicht anhängen, da ich ein neuer Benutzer bin ... Doh.)

5
BJury