Exemple concret, quand utiliser OUTER / CROSS APPLY en SQL

Question

J'ai regardé CROSS / OUTER APPLY avec un collègue et nous avons du mal à trouver des exemples concrets de leur utilisation.

J'ai passé pas mal de temps à regarder Quand dois-je utiliser l'application croisée plutôt que la jointure interne ? et en cherchant sur Google, mais le principal (seul) exemple semble assez bizarre (utiliser le nombre de lignes d'une table pour déterminer le nombre de lignes à sélectionner dans une autre table).

J'ai pensé que ce scénario pourrait bénéficier OUTER APPLY :

Tableau des contacts (contient 1 enregistrement pour chaque contact) Tableau des entrées de communication (peut contenir n téléphone, fax, email pour chaque contact)

Mais en utilisant des sous-requêtes, des expressions de table communes, OUTER JOIN con RANK() y OUTER APPLY semblent tous avoir les mêmes performances. Je suppose que cela signifie que le scénario n'est pas applicable à APPLY .

Veuillez partager des exemples concrets et aider à expliquer cette fonctionnalité !

Answer 1

Quelques utilisations de APPLY sont...

1) Top N des requêtes par groupe (peut être plus efficace pour certaines cardinalités)

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) Appel d'une fonction à valeur de tableau pour chaque ligne de la requête externe.

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

3) Réutilisation d'un alias de colonne

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) Dépivoter plus d'un groupe de colonnes

Suppose une structure de table violant 1NF....

CREATE TABLE T
  (
     Id   INT PRIMARY KEY,

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

Exemple utilisant 2008+ VALUES la syntaxe.

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

En 2005 UNION ALL peut être utilisé à la place.

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

Answer 2

Il y a plusieurs situations où vous ne pouvez pas éviter CROSS APPLY o OUTER APPLY .

Considérons que vous avez deux tables.

TABLE MAÎTRE

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

TABLEAU DE DÉTAILS

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

Il y a de nombreuses situations où nous devons remplacer INNER JOIN con CROSS APPLY .

1. Si nous voulons joindre 2 tables sur TOP n les résultats avec INNER JOIN fonctionnalité

Considérons si nous devons sélectionner Id y Name de Master et les deux dernières dates pour chaque Id de 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

La requête ci-dessus génère le résultat suivant.

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

Tu vois, ça a généré des résultats pour les deux dernières dates avec les deux dernières dates. Id puis a joint ces enregistrements uniquement dans la requête externe sur Id ce qui est faux. Pour ce faire, nous devons utiliser 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

et forme le résultat suivant.

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

Voici le fonctionnement. La requête à l'intérieur CROSS APPLY peut faire référence à la table externe, où INNER JOIN ne peut pas le faire (erreur de compilation). Lors de la recherche des deux dernières dates, la jonction est faite à l'intérieur de CROSS APPLY ie, WHERE M.ID=D.ID .

2. Lorsque nous avons besoin INNER JOIN la fonctionnalité à l'aide de fonctions.

CROSS APPLY peut être utilisé en remplacement de INNER JOIN quand nous avons besoin d'obtenir le résultat de Master et une function .

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

Et voici la fonction

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

qui a généré le résultat suivant

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

                                                             APPLIQUER À L'EXTÉRIEUR

1. Si nous voulons joindre 2 tables sur TOP n les résultats avec LEFT JOIN fonctionnalité

Considérons que nous avons besoin de sélectionner Id et Name à partir de Master et les deux dernières dates pour chaque Id de Details table.

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

ce qui donne le résultat suivant

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

Cela donnera des résultats erronés, c'est-à-dire que seules les données des deux dernières dates seront affichées. Details indépendamment de Id même si nous nous joignons à Id . La bonne solution est donc d'utiliser 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

ce qui donne le résultat souhaité suivant

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. Lorsque nous avons besoin LEFT JOIN fonctionnalité utilisant functions .

OUTER APPLY peut être utilisé en remplacement de LEFT JOIN quand nous avons besoin d'obtenir le résultat de Master et une function .

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

Et la fonction va ici.

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

qui a généré le résultat suivant

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

                              Caractéristique commune de CROSS APPLY y OUTER APPLY

CROSS APPLY o OUTER APPLY peut être utilisé pour conserver NULL lors du dépivotage, qui sont interchangeables.

Considérons que vous avez le tableau ci-dessous

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

Lorsque vous utilisez UNPIVOT d'apporter FROMDATE ET TODATE à une colonne, cela éliminera NULL par défaut.

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

qui génère le résultat ci-dessous. Notez que nous avons manqué l'enregistrement de Id numéro 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

Dans ce cas, un CROSS APPLY o OUTER APPLY sera utile

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

qui forme le résultat suivant et retient Id où sa valeur est 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

Answer 3

Un exemple concret serait de disposer d'un planificateur et de vouloir voir quelle est l'entrée de journal la plus récente pour chaque tâche planifiée.

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

Answer 4

Pour répondre au point ci-dessus, donnez un exemple :

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

Et maintenant, exécutez les deux requêtes avec un plan d'exécution.

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

Vous pouvez voir que la requête d'application externe est plus efficace. (Je n'ai pas pu joindre le plan car je suis un nouvel utilisateur... Doh.)