Mélange de Connecter par, jointure interne et de la somme avec Oracle

J'ai besoin d'aide avec un oracle de la requête.

Voici ma configuration:

J'ai 2 tables, appelés respectivement "tâches" et "feuilles de temps". Les "tâches" de la table est récursive, de cette façon, chaque tâche peut avoir plusieurs sous-tâches. Chaque feuille de temps est associé à une tâche (pas nécessairement la "racine" de la tâche) et contient le nombre d'heures travaillées.

Exemple:

Tâches

id:1 | nom: Tâche | parent_id: NULL

id:2 | nom: Groupe A1 | parent_id: 1

id:3 | nom: Groupe A1.1 | parent_id: 2

id:4 | nom: la Tâche B | parent_id: NULL

id:5 | nom: Tâche B1 | parent_id: 4

Feuilles de temps

id:1 | task_id: 1 | nombre d'heures: 1

id:2 | task_id: 2 | nombre d'heures: 3

id:3 | task_id:3 | nombre d'heures: 1

id:5 | task_id:5 | nombre d'heures:1
...

Ce que je veux faire:

Je veux une requête qui retourne la somme de toutes les heures travaillées sur une "hiérarchie de tâche". Si nous prenons un coup d'oeil à l'exemple précédent, Cela signifie que je voudrais avoir les résultats suivants:

tâche A - 5 heure(s) | tâche B - 1 heure(s)

J'ai d'abord essayé cette

SELECT TaskName, Sum(Hours) "TotalHours" 
FROM (
    SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName, 
    ts.hours as hours
    FROM tasks t INNER JOIN timesheets ts ON t.id=ts.task_id
    START WITH PARENTOID=-1
    CONNECT BY PRIOR t.id = t.parent_id
    )
GROUP BY TaskName Having Sum(Hours) > 0 ORDER BY TaskName

Et c'est presque un travail. Le seul problème est que si il n'y a pas de feuille de temps pour de la racine de la tâche, il va sauter dans l'ensemble de la hiérarchie... mais il y a peut être des feuilles de temps pour l'enfant de lignes et c'est exactement ce qui se passe avec la Tâche B1. Je sais que c'est le "inner join" la partie qui est à l'origine de mon problème, mais je ne suis pas sûr de savoir comment puis-je me débarrasser de lui.

Une idée de comment résoudre ce problème?

Merci

Je pense que si vous inversez l'ordre des tables et utiliser une jointure externe gauche, vous obtiendrez de tâches, même si il n'y a pas de feuille de temps, de sorte que travail... peut-être?
Merci pour votre aide. Faire un left join dans mon cas, est à l'origine de certains des principaux problèmes de performance... même si les tables sont bien indexés et tous.

OriginalL'auteur François | 2010-05-27

connect-by oracle

Serait quelque chose comme ce travail? J'ai eu des cas similaires à la vôtre, et j'ai simplement supprimé la jointure de la requête hiérarchique et l'a appliqué seulement par la suite pour éviter de perdre des lignes.

SELECT TaskName, Sum(ts.hours) "TotalHours" 
FROM (
    SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName, t.id
    FROM tasks t
    START WITH PARENTOID=-1
    CONNECT BY PRIOR t.id = t.parent_id
    ) tasks
INNER JOIN timesheets ts ON tasks.id=ts.task_id
GROUP BY TaskName Having Sum(ts.hours) > 0 ORDER BY TaskName

Un million de mercis! Je pense que c'est en train de faire le tour. Des tests un peu plus à faire et je vais marquer ce que la réponse officielle.

OriginalL'auteur aw crud

1

Avez-vous essayé?
```
SELECT TaskName, Sum(Hours) "TotalHours" 
FROM (
    SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName, 
    ts.hours as hours
    FROM timesheets ts  LEFT OUTER JOIN tasks t  ON t.id=ts.task_id
    START WITH PARENTOID=-1
    CONNECT BY PRIOR t.id = t.parent_id
    )
GROUP BY TaskName Having Sum(Hours) > 0 ORDER BY TaskName
```
Merci pour votre aide! J'ai déjà essayé la jointure externe gauche option, mais il ralentit la requête énormément. Peut-être que je devrais prendre un autre coup d'oeil à l'index dans les tables... peut-être qu'il manque un quelque part. Mais pour le moment la solution proposée par RenderLn est ce que je suis à la recherche d'. Encore une fois, merci pour votre aide.
L'index que je pense (juste en regardant cette requête) est sur timesheets, l'indice de l' task_id (en Supposant que les tâches.l'id est déjà un indice). Mais oui, en général, les jointures externes sont plus lents que d'intérieur.

OriginalL'auteur FrustratedWithFormsDesigner
0

Si vous utilisez une jointure externe gauche au lieu de la normale rejoindre, vous pouvez obtenir la sortie.
```
SELECT TaskName, Sum(Hours) "TotalHours"  
FROM ( 
    SELECT replace(sys_connect_by_path(decode(level, 1, t.name), '~'), '~') As TaskName,  
    ts.hours as hours 
    FROM tasks t,timesheets ts where t.id=ts.task_id(+) 
    START WITH PARENTOID=-1 
    CONNECT BY PRIOR t.id = t.parent_id 
    ) 
GROUP BY TaskName Having Sum(Hours) > 0 ORDER BY TaskName 
```
Je pense qu'il faudrait inverser les commandes de la tables de trop, parce qu'il veut les tâches qui n'ont pas de correspondant des feuilles de temps. Actuellement, si il change, à gauche, à l'extérieur, il faudrait obtenir des feuilles de temps qui n'ont pas de tâches correspondantes.
FrustratedWithFormsDesigner: Vous êtes correct ou il peut aussi utiliser jointure externe droite

OriginalL'auteur Bharat

Vous devez vous connecter pour publier un commentaire.