Exemple réel, quand les utiliser à l'EXTÉRIEUR / CROIX APPLIQUER dans SQL

J'ai été à la recherche à CROSS /OUTER APPLY avec un collègue et nous avons du mal à trouver de la vie réelle des exemples de cas où les utiliser.

J'ai passé beaucoup de temps à regarder Quand dois-je utiliser de la Croix s'Appliquent à la Jointure Interne? et googler mais le principal (seul) exemple semble assez bizarre (en utilisant le nombre de lignes d'une table pour déterminer le nombre de lignes à sélectionner à partir d'une autre table).

J'ai pensé à ce scénario peut bénéficier de OUTER APPLY:

Table de Contacts (contient 1 enregistrement pour chaque contact)
La Communication des Entrées de la Table (peut contenir n de téléphone, fax, e-mail bof chaque contact)

Mais à l'aide de sous-requêtes, les expressions de table communes, OUTER JOIN avec RANK() et OUTER APPLY semblent tous aussi. Je suppose que cela signifie que le scénario n'est pas applicable aux APPLY.

Merci de partager quelques exemples concrets et de contribuer à expliquer la fonction!

"top n " par groupe" ou l'analyse de XML est commun. Voir certaines de mes réponses, stackoverflow.com/...
mssqltips.com/sqlservertip/1958/...
explainextended.com/2009/07/16/inner-join-vs-cross-apply
double possible de Quand dois-je utiliser de la Croix s'Appliquent à la Jointure Interne?
Consultez ici aussi stackoverflow.com/questions/27838045/where-to-use-outer-apply

InformationsquelleAutor Lee Tickett | 2012-02-14

169

Quelques usages pour APPLY sont...

1) Top N par groupe de requêtes (peut être plus efficace pour certains les 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) l'Appel d'une Fonction à valeur de Table 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) La 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) Unpivoting plus d'un groupe de colonnes

Suppose 1FN violer structure de la table....
```
CREATE TABLE T
  (
     Id   INT PRIMARY KEY,

     Foo1 INT, Foo2 INT, Foo3 INT,
     Bar1 INT, Bar2 INT, Bar3 INT
  ); 
```
Exemple à l'aide de 2008+ VALUES 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);
```
- Une belle liste d'utilisations là, mais la clé est la vie réelle des exemples - j'aimerais voir un pour chacun.
- Pour le #1 ceci peut être réalisé également à l'aide de rang, les sous-requêtes ou d'expressions de table communes? Pouvez-vous donner un exemple, quand ce n'est pas vrai?
- Veuillez lire le lien. Il dispose d'un 4 à la page de discussion au sujet de quand vous préférez l'un à l'autre.
- désolé manquer les liens - avoir une lecture merci
- lors de nos tests, nous avons toujours trouvé de jointure avec la fonction de fenêtre le plus efficace pour le top n (je pensais que ce serait toujours vrai que et la sous-requête sont à la fois à l'écriture cursive/besoin de boucles imbriquées)
- Mais ROW_NUMBER peut mettre fin à la recherche à travers une charge de lignes inutiles alors que APPLY pouvez juste obtenir le TOP N ensuite chercher dans le prochain groupe. Voir l'exemple dans le lien de 39 lectures logiques pour APPLY comparé avec 28 000 lectures pour ROW_NUMBER
- Assurez-vous de visiter le lien inclus dans l'exemple n ° 1. J'ai utilisé ces deux approches (LIGNE PLUS de et CROIX supplément) avec à la fois performant en fonction de divers scénarios, mais je n'ai jamais compris pourquoi ils fonctionnent différemment. Cet article a été envoyé du ciel!! L'accent mis sur une bonne indexation correspondant à la commande par les directions contribué dans une grande manière pour les requêtes qui ont "bonne" structure mais des problèmes de performances lorsqu'il est interrogé. Merci pour dont il!!
- Top N par groupe de requêtes lien mort?
- ressemble, c'est maintenant au itprotoday.com/software-development/...
InformationsquelleAutor Martin Smith

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

Considérer que vous avez deux tables.

DE LA TABLE DE MAÎTRE

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

TABLE 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

CROIX APPLIQUER

Il y a beaucoup de situation où nous avons besoin de remplacer INNER JOIN avec CROSS APPLY.

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

Examiner si nous avons besoin de sélectionner Id et 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

Voir, il a généré des résultats pour les deux dernières dates avec les deux derniers date du Id et ensuite joint les documents à l'extérieur de la requête sur Id, ce qui est faux. Pour ce faire, nous avons besoin d'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 des formes, il a 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

Ici, c'est le travail. La requête à l'intérieur CROSS APPLY peut faire référence à la table externe, où INNER JOIN ne peut pas le faire(jette erreur de compilation). Lors de la recherche de la dernière des deux dates, l'assemblage est fait à l'intérieur CROSS APPLY c'est à dire, WHERE M.ID=D.ID.

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

CROSS APPLY peut être utilisé comme un remplacement avec INNER JOIN quand nous en avons besoin pour obtenir le résultat de Master table et un 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é les résultats suivants

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

EXTÉRIEURE APPLIQUER

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

Examiner si nous avons besoin de sélectionner l'Id et le Nom 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

qui 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     |   NULL       |  NULL |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

Cela apportera de mauvais résultats c'est à dire, il ne vous apportera que plus tard deux dates données à partir de Details table, indépendamment de Id même si nous nous joignons à Id. Donc, la bonne solution est 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

qui forme le suivant résultat souhaité

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 de LEFT JOIN fonctionnalités à l'aide de functions.

OUTER APPLY peut être utilisé comme un remplacement avec LEFT JOIN quand nous en avons besoin pour obtenir le résultat de Master table et un function.

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

Et la fonction passe ici.

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

qui a généré les résultats suivants

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

la caractéristique Commune de CROSS APPLY et OUTER APPLY

CROSS APPLY ou OUTER APPLY peut être utilisé pour conserver NULL des valeurs lors de l'unpivoting, qui sont interchangeables.

Considérer 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 à apporter FROMDATE ET TODATE à une colonne, il permettra d'éliminer les NULL valeurs 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 raté le record de Id nombre 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 de tels cas, un CROSS APPLY ou OUTER APPLY sera utile

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

qui forme le résultat suivant et conserve 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

Au lieu de poster la même réponse à deux questions, pourquoi pas un drapeau comme un double?
Je trouve cette réponse sera plus applicable pour répondre à la question d'origine. Ses exemples montrent "vie réelle" des scénarios.
Donc, pour clarifier. Le "top n" scénario; peut-on le faire avec la gauche/inner join, mais en utilisant un "row_number sur partition par id", puis en sélectionnant "OÙ M. RowNumber < 3" ou quelque chose comme ça?

InformationsquelleAutor Sarath Avanavu

8

Un exemple concret serait si vous aviez un planificateur et je voulais voir ce que le journal le plus récent entrée a été 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
```
- lors de nos tests, nous avons toujours trouvé de jointure avec la fonction de fenêtre le plus efficace pour le top n (je pensais que ce serait toujours vrai que et la sous-requête sont à la fois à l'écriture cursive/besoin de boucles imbriquées). même si je pense que j'ai peut-être maintenant craqué... merci à Martin du lien qui suggère si vous n'êtes pas de retour de l'intégralité de la table, et il n'y a pas optimale index sur la table, alors le nombre de lectures serait beaucoup plus petites à l'aide de la croix-appliquer (ou une sous-requête si haut n où n = 1)
- J'ai, en substance, que la requête et à son certainement pas toute la sous-requête avec des boucles imbriquées. Compte tenu de la table du journal a un PK de l'id de la tâche et lastUpdateDate, son très rapide de l'opération. Comment feriez-vous de la réforme que la requête à utiliser une fonction de fenêtre?
- sélectionnez * à partir de la tâche t inner join (select id de la tâche, logresult, lastupdatedate, rank() over(partition by taskid commande par lastupdatedate desc) _rank) lg lg.taskid = t.taskid et lg._rank = 1
InformationsquelleAutor BJury

Pour répondre à la point au-dessus de frapper 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écuter 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 l'extérieur s'appliquent requête est plus efficace. (Ne pouvait pas joindre le plan que je suis un nouvel utilisateur... Doh.)

le plan d'exécution m'intéresse - savez-vous pourquoi le rang() solution ne un scan index cher trier par opposition à l'extérieur, qui ne un index de recherche et ne semble pas faire une sorte (même si elle doit, parce que vous ne pouvez pas faire un top sans une sorte?)
L'extérieur s'applique n'a pas besoin d'effectuer un tri, car il peut utiliser l'index sur la table sous-jacente. Sans doute la requête avec le rang() la fonction a besoin pour traiter l'ensemble de la table pour s'assurer de son classement sont corrects.
vous ne pouvez pas faire un top sans une sorte. bien que votre point de vue sur le traitement de l'ensemble de la table PEUT-être vrai, ce serait une surprise pour moi (je sais que l'optimiseur sql/compilateur peut décevoir de temps en temps, mais ce serait de la folie de comportement)
Vous pouvez le dessus un top sans une sorte lorsque les données de votre groupement est par contre un indice, comme l'optimiseur sait déjà trié, littéralement juste besoin de tirer la première (ou la dernière) entrée hors de l'index.

InformationsquelleAutor BJury

Vous devez vous connecter pour publier un commentaire.