Comptage analytique sur partition avec et sans clause ORDER BY

Question

Je ne comprends pas pourquoi les résultats sont différents lorsque l'on utilise une carte à puce. ORDER BY clause dans une analyse COUNT fonction.

Prenons un exemple simple :

with req as
 (select 1 as n, 'A' as cls
    from dual
  union
  select 2 as n, 'A' as cls
    from dual)
select req.*, count(*) over(partition by cls) as cnt from req;

donne le résultat suivant :

N   CLS CNT
2   A   2
1   A   2

Alors que, lors de l'ajout d'un ORDER BY dans la clause analytique, le résultat est différent !

with req as
 (select 1 as n, 'A' as cls
    from dual
  union
  select 2 as n, 'A' as cls
    from dual)
select req.*, count(*) over(partition by cls order by n) as cnt from req;

La colonne CNT a changé :

N   CLS CNT
1   A   1
2   A   2

Quelqu'un peut-il m'expliquer ?

Merci

Answer 1

Tout d'abord, un lien aux docs. C'est un peu obscur, cependant.

La clause analytique consiste en query_partition_clause , order_by_clause y windowing_clause . Et, une chose vraiment importante à propos windowing_clause est

Vous ne pouvez pas spécifier cette clause à moins d'avoir spécifié l'option order_by_clause . Certaines limites de fenêtres définies par le RANGE clause vous permettent de spécifier une seule expression dans la order_by_clause . Reportez-vous à "Restrictions sur la clause ORDER BY".

Mais non seulement vous ne pouvez pas utiliser windowing_clause sans le order_by_clause ils sont liés ensemble.

Si vous omettez entièrement la clause windowing_clause, alors la valeur par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW .

La clause de fenêtrage par défaut produit quelque chose comme le total courant. COUNT renvoie à 1 pour la première ligne, car il n'y a qu'une seule ligne entre le haut de la fenêtre et la ligne actuelle, 2 pour la deuxième ligne et ainsi de suite.

Donc, dans votre première requête, il y a pas de Il n'y a pas de fenêtrage du tout, mais il y a le fenêtrage par défaut dans la deuxième.

Et vous pouvez simuler le comportement de la première requête en spécifiant une fenêtre entièrement non délimitée.

with req as
 (select 1 as n, 'A' as cls
    from dual
  union
  select 2 as n, 'A' as cls
    from dual)
select req.*, count(*) over(partition by cls order by n RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as cnt from req;

Yep

N   CLS CNT
1   A   2
2   A   2

Answer 2

La façon la plus simple de penser à cela - en laissant les ORDER BY out équivaut à "ordonner" de manière à ce que toutes les lignes de la partition soient "égales" les unes aux autres. En effet, vous pouvez obtenir le même effet en ajoutant explicitement la balise ORDER BY clause comme celle-ci : ORDER BY 0 (ou "commander par" cualquier expression constante), ou encore, de manière plus insistante, ORDER BY NULL .

Pourquoi vous obtenez le COUNT() o SUM() etc. pour l'ensemble de la partition a à voir avec la clause de fenêtrage par défaut : RANGE between unbounded preceding and current row . "Range" (par opposition à "ROWS") signifie que toutes les rangées "liées" à la rangée actuelle sont également incluses, même si elles ne la précèdent pas. Comme toutes les lignes sont liées, cela signifie que toute la partition est incluse, quelle que soit la ligne "courante".