Calculer le pourcentage de la racine appartenant à ses parents
en termes simplifiés, j'essaie de calculer le pourcentage de la racine d'un arbre appartenant à ses parents, plus haut dans l'arbre. Comment puis-je faire cela en SQL seul?
Voici mon (exemple) schéma. Veuillez noter que même si la hiérarchie elle-même est assez simple, il y a un holding_id, ce qui signifie qu'un parent seul peut "posséder" les différentes parties de leur enfant.
create table hierarchy_test (
id number -- "root" ID
, parent_id number -- Parent of ID
, holding_id number -- The ID can be split into multiple parts
, percent_owned number (3, 2)
, primary key (id, parent_id, holding_id)
);
Et quelques exemples de données:
insert all
into hierarchy_test values (1, 2, 1, 1)
into hierarchy_test values (2, 3, 1, 0.25)
into hierarchy_test values (2, 4, 1, 0.25)
into hierarchy_test values (2, 5, 1, 0.1)
into hierarchy_test values (2, 4, 2, 0.4)
into hierarchy_test values (4, 5, 1, 1)
into hierarchy_test values (5, 6, 1, 0.3)
into hierarchy_test values (5, 7, 1, 0.2)
into hierarchy_test values (5, 8, 1, 0.5)
select * from dual;
La requête suivante retourne le calcul je voudrais faire. En raison de la nature de SYS_CONNECT_BY_PATH il ne peut pas, à ma connaissance, effectuer le calcul lui-même.
select a.*, level as lvl
, '1' || sys_connect_by_path(percent_owned, ' * ') as calc
from hierarchy_test a
start with id = 1
connect by nocycle prior parent_id = id
il y a des relations cycliques dans les données, mais pas dans cet exemple.
pour le moment je vais utiliser une fonction assez simple pour tourner la chaîne dans le calc colonne dans un certain nombre
create or replace function some_sum ( P_Sum in varchar2 ) return number is
l_result number;
begin
execute immediate 'select ' || P_Sum || ' from dual'
into l_result;
return l_result;
end;
/
cela semble être une façon ridicule d'aller a ce sujet et je préfère éviter le temps supplémentaire qui sera pris parsing le SQL dynamique1.
en théorie, je pense que je devrais pouvoir utiliser la clause modèle pour calculer ceci. Mon problème est causé par la non-unicité de l'arbre. L'une de mes tentatives pour utiliser la clause type est la suivante:
select *
from ( select a.*, level as lvl
, '1' || sys_connect_by_path(percent_owned, ' * ') as calc
from hierarchy_test a
start with id = 1
connect by nocycle prior parent_id = id
)
model
dimension by (lvl ll, id ii)
measures (percent_owned, parent_id )
rules upsert all (
percent_owned[any, any]
order by ll, ii = percent_owned[cv(ll), cv(ii)] * nvl( percent_owned[cv(ll) - 1, parent_id[cv(ll), cv(ii)]], 1)
)
Ce, à juste titre, échoue avec le code suivant:
ORA-32638: adresse Non unique dans le modèle dimensions
en utilisant RÉFÉRENCE UNIQUE échoue pour une raison similaire, à savoir que L'ordre Par clause n'est pas unique.
tl;dr
Existe-t-il un moyen simple de calculer le pourcentage de la racine d'un arbre appartenant à ses parents en utilisant seulement le SQL? Si je suis sur la bonne voie avec MODEL, où est-ce que je vais mal?
1. Je voudrais également éviter le PL/SQL SQL context-switch. Je me rends compte que c'est une quantité minuscule de temps mais cela va être assez difficile à faire rapidement sans ajouter quelques minutes supplémentaires par jour.
2 réponses
En 11g, Probablement quelque chose comme:
SELECT a.*, LEVEL AS lvl
,XMLQuery( substr( sys_connect_by_path( percent_owned, '*' ), 2 ) RETURNING CONTENT).getnumberval() AS calc
FROM hierarchy_test a
START WITH id = 1
CONNECT BY nocycle PRIOR parent_id = id;
Ou, selon votre '1'|| astuce-
SELECT a.*, LEVEL AS lvl
, XMLQuery( ('1'|| sys_connect_by_path( percent_owned, '*' )) RETURNING CONTENT).getnumberval() AS calc
FROM hierarchy_test a
START WITH id = 1
CONNECT BY nocycle PRIOR parent_id = id;
malheureusement en 10g,XMLQuery ne peut pas accepter les fonctions et attend toujours une chaîne littérale pour l'évaluation par exemple-
select XMLQuery('1*0.25' RETURNING CONTENT).getnumberval() as val
from dual;
fonctionne et renvoie 0.25, mais
select XMLQuery(substr('*1*0.25',2) RETURNING CONTENT).getnumberval() as val
from dual;
donne ORA-19102: XQuery string literal expected.
la requête peut devenir plus lente que le nombre de niveaux sur un arbre augmente avec un au-dessus de la création interne de l'arbre par XMLQuery lui-même. La méthode la plus optimale pour obtenir le résultat serait encore une fonction PL/SQL qui fonctionnerait d'ailleurs à la fois en 10g et 11g.
cela mérite une réponse; mais attention, nous opérons dans des circonstances spéciales.
la première chose à mentionner est que la meilleure façon possible de le faire est avec recursive sub-query factoring / a recursive CTE selon Daniel Hilgarth et jonearles dans les commentaires:
with temp (id, parent_id, percent_owned, calc) as (
select a.id, a.parent_id, a.percent_owned, percent_owned as calc
from hierarchy_test a
where id = 1
union all
select a.id, a.parent_id, a.percent_owned, a.percent_owned * t.calc as calc
from temp t
join hierarchy_test a
on t.parent_id = a.id
)
select *
from temp
malheureusement, la complexité de la requête et la taille des données sur lesquelles nous travaillons sont telles que cela s'est avéré impossible. être impossible. Il n'y avait aucun moyen de le faire sans balayage complet de certaines tables trop grandes à chaque fois.
Cela ne veut pas nécessairement dire que nous sommes de retour à CONNECT BY. Il est possible de calculer les hiérarchies en vrac. Malheureusement, cela s'est avéré impossible; une heure dans la base de données s'est écrasé. Trois fois. Nous utilisions presque 100 Go D'UNDO et le serveur ne pouvait pas faire face.
ce sont les circonstances spéciales; nous devons calculer des centaines de des milliers de hiérarchies dans quelques heures, tout au plus. La moyenne est d'environ 1,5 niveaux de profondeur avec peut-être 5-10 feuilles et 8-12 noeuds au total. Cependant, les valeurs aberrantes ont des noeuds de 90k, 27 niveaux et de multiples relations cycliques. Les valeurs aberrantes ne sont pas assez rares.
Donc, CONNECT BY. Benchmarking la solution D'Annjawn contre le PL / SQL EXECUTE IMMEDIATE suggéré dans la question a indiqué que pour un arbre au-dessus de la moyenne XMLQuery() était jusqu'à 4 fois plus lent. Excellent, had la réponse; pas d'autre option; Laissez tomber.
Non.
parce que nous calculons tellement de hiérarchies avec autant de noeuds nous avons fini par obtenir des attentes trop longues de serrures de NIP de cache de bibliothèque causé par l'analyse constante de centaines de milliers de fonctions mathématiques en EXECUTE IMMEDIATE.
pas de réponse évidente à cela, donc revenir en arrière trop la solution D'Annjawn finit 3 fois plus vite! serrures de NIP de cache de bibliothèque disparaître complètement, et nous sommes de retour sur le droit chemin.
Non.
malheureusement, il semble y avoir un bug Oracle dans 11.2 qui apparaît lorsque vous combinez CONNECT BY,XMLQuery() et DBMS_SCHEDULER. Dans certaines circonstances, normalement dans les plus grandes hiérarchies, il fuit d'énormes quantités de mémoire. Perdu la base de données et le serveur trouvant celui-là. Un rapport a été rédigé avec Oracle et nous testons en 12c; bien que les fuites de mémoire montrent moins, ils apparaissent toujours tellement 12c.
la solution? Envelopper le XMLQuery() dans une fonction PL/SQL. Fuite de mémoire résolu, malheureusement cela a causé une grande quantité de controverse pour cette fonction, et nous avons commencé à obtenir de plusieurs heures cache de bibliothèque: mutex x attend.. Interrogeant x$kglob confirmé que c'était XMLTYPE qui a été martelé.
Andrey Nikolaev recommande soit modifier le système; plutôt ne pas le faire quand tout le reste fonctionne bien, ou en utilisant le DBMS_POOL.MARKHOT procédure pour dire à Oracle que vous accéderez beaucoup à cet objet. Pour l'oeil occasionnel, cela peut avoir résolu le problème, cependant, après environ 10 minutes, et en passant par ce qui semblait être chaque serrure que Oracle a, nous avons fini avec 5 processus se disputant pour CPU. Apparemment, il n'y en avait pas assez (54 Go et 24 cœurs sur la boîte de test)...
nous avons alors commencé à obtenir curseur pin: s attend. Burleson recommande plus de paramètre caché finangling,Jonathan Lewis suggère que c'est à cause de SGA resizing. Comme le DB utilisait le dimensionnement automatique SGA, nous avons essayé d'augmenter graduellement le piscine partagée, jusqu'à 30 Go et seulement sorti de vieux ami le cache de bibliothèque: mutex x attendre.
Alors, quelle est la solution? Qui sait est la réponse honnête, mais une procédure Java stocké fonctionne brillamment jusqu'à présent, pas de fuites de mémoire, pas d'attente et considérablement plus rapide que tout le reste.
je suis sûr qu'il y en a plus dehors... et je voudrais vraiment obtenir le MODEL clause de travailler si quelqu'un a des idées?
P.S. Je ne peux pas revendiquer le mérite de tout cela; c'est le travail d'environ 3 personnes pour nous amener à ce stade...