Je fais un système qui attribue des analystes aux exigences du client. J'ai écrit une procédure qui recherche l'analyste idéal à attribuer à une exigence.
Les tables principales sont:
Les règles de recherche de l'analyste sont les suivantes:
Voici le code: Comme vous pouvez le voir, j'utilise trois requêtes, mais "de" partie "de" est la même pour les trois (même joindre tables avec les mêmes conditions). Cette procédure est conforme à la règle et que cela fonctionne, mais je souhaite réduire le nombre de requêtes, car il y a du code qui se répète. Merci d'avance!
3 Réponses :
Est-ce que cela aide?
WITH base AS
(
SELECT
a.id,
count(r.id) requirements,
avg (DATEDIFF(DAY,getdate(),r.due_date)) average_due_date
FROM grc_analyst a
JOIN grc_analyststate ea ON a.id_analyststate = ea.id AND ea.code = 'A'
JOIN grc_analyst_category ac ON a.id = ac.id_analyst
JOIN grc_category c ON c.id = ac.id_category AND c.code = 'SOAP'
LEFT JOIN grc_requirement r ON a.id = r.id_analyst
JOIN grc_requirementstate es ON r.id_requirementstate IS NULL OR
(r.id_requirementstate IS NOT NULL
AND r.id_requirementstate = er.id
AND er.code IN ('AS','ER','DL','DC')
)
group by a.id
)
-- etc
Je manque probablement quelque chose, mais comment un CTE vous permet-il d'écrire uniquement le code une fois au lieu de 3 fois?
@Dalek - C'est la manière dont une CTE fonctionne ... Vous interrogez sur le CTE et l'optimiseur / compilateur le stocke en mémoire ou une table Temp en fonction de la taille. Il construit essentiellement une requête matérialisée pour vous (si le compilateur est bon).
@Dalek - Si vous utilisez une vue comme si vous décrivez, vous n'avez pas autant de code, mais que tous les compilateurs vont toujours exécuter 3 requêtes contre les tables. Il est facile de voir si vous regardez le plan d'exécution. Avec un CTE, vous racontez le compilateur de le matérialiser pour que cela ne gère que la requête une fois.
Mais vous ne pouvez pas sélectionner des temps distincts de CTE 3, sauf si vous stockez les résultats dans une table TEMP?
Ou suggérez-vous d'obtenir toutes les valeurs en un coup?
Le CTE peut créer base , comme indiqué, pour la partie commune de la requête globale, alors trois requêtes supplémentaires produisant les trois résultats (possibles) avec une colonne pour les classer et une requête finale. qui sélectionne le résultat non nul le mieux classé. Une des bonnes choses est que chaque requête puisse être débogée en remplaçant la finale SELECT avec une instruction qui sélectionne les résultats de la requête en cours de débogage / testé / inspecté pour comprendre les résultats intermédiaires.
@Dalek - Oui, vous pouvez en choisir 3 fois dans des CTES supplémentaires - c'est comment ils fonctionnent.
Pour éviter de répéter le code, créez une vue de la partie commune de la requête, puis utilisez-la dans vos 3 requêtes, par exemple,
CREATE VIEW dbo.grc_analyst_view
AS
SELECT a.id
, COUNT(r.id) requirements
, AVG(DATEDIFF(DAY,GETDATE(),r.due_date)) average_due_date
FROM grc_analyst a
INNER JOIN grc_analyststate ea ON a.id_analyststate = ea.id AND ea.code = 'A'
INNER JOIN grc_analyst_category ac ON a.id = ac.id_analyst
INNER JOIN grc_category c ON c.id = ac.id_category AND c.code = 'SOAP'
LEFT JOIN grc_requirement r ON a.id = r.id_analyst AND r.id_requirementstate in (
SELECT id
FROM grc_requirementstate er
WHERE er.code IN ('AS','ER','DL','DC')
)
GROUP BY a.id;
GO
-- QUERY 1
SELECT @totalRequirementsAnalysts = COUNT(DISTINCT(requirements))
FROM dbo.grc_analyst_view;
-- QUERY 2
SELECT TOP(1) id
FROM dbo.grc_analyst_view
ORDER BY average_due_date DESC
-- QUERY 3
SELECT TOP(1) id
FROM dbo.grc_analyst_view
ORDER BY requirements ASC;
En fait, je crois que vous pouvez accomplir ce que vous voulez avec une seule requête, en utilisant la fonction de fenêtre Il s'avère que vous ne pouvez pas utiliser et si c'est une condition d'imprimer quel choix a été choisi, attribuez la configuration Comptez sur une variable et utilisez cela dans votre first_value et une sous-requête Vous pouvez obtenir exactement la valeur que vous souhaitez sans répéter la requête du tout. . Cela devrait également fonctionner mieux. distinct dans compter sur () Nous devons donc utiliser un CTE et croix s'appliquer à la place. si instruction.
Vous devrez ajouter une clause de plage aux premières instructions devalue ou ne donnera pas les résultats que vous attendez.
@Hogan Pouvez-vous développer ce commentaire s'il vous plaît. Je crois comprendre que la gamme n'est requise que lorsque vous partitionnez les résultats.
NOPE -> "Les lignes / la plage sont non spécifiées mais l'ordre par est spécifié, la plage précédente non liée et la ligne actuelle est utilisée comme valeur par défaut pour le cadre de fenêtre." De docs.microsoft.com/en-us/sql/t-sql/ Queries / ... Ceci est attendu et la norme. DB2 et Oracle fonctionnent de la même manière.
@Hogan OK, bon appel, dis-tu que plage peut renvoyer plusieurs lignes et que pour first_value Nous devons utiliser " lignes non liées et actuelles LIGNE". Je n'ai pas eu appel à l'utilisation de la fenêtre en cadrage.
Mon problème avec cette solution est "Compter (R.IID distincts)". Cela peut prendre plusieurs valeurs ... Laquelle de ces valeurs sera évaluée dans la phrase de cas? Dans la requête originale, Count (R.ID) pourrait être par exemple 2 pour analyste1 et 3 pour analyste2. Ainsi, lorsque vous évaluez la phrase @totalrequirsanalysts = compte (distinct (A.Requirements)), je reçois la valeur 2 et je sais que les analystes ont une quantité différente de requêtes attribuées.
@Mariotigua Voir mon édition Où je l'ai changé à Case lors du décompte (exigences distinctes) sur () = 1 alors .
Dale - Non, je dis si vous si vous n'utilisez pas illimité précédent et illimité après la première () ne regardera que les rangées jusqu'à présent.
S'il vous plaît essayer ce code et voir la différence: gist.github.com/hoganlong/c9ef8693255918c86DDD72A85212f82b
@Hogan - Merci d'avoir collé avec ceci - je l'apprécie vraiment. Maintenant une fois que je convertit, votre code en SQL Server (est votre message MySQL?) Cela me donne les mêmes résultats de l'une de l'autre sens de l'une des voies dbfiddle
Ce n'est pas MySQL - il devrait être proche de courir sur DB2, Oracle et SQLServer. Autant que je sache, MySQL ne prend pas en charge les valeurs ou les fonctions Windows. Mais c'est vrai que je ne l'ai pas testé - je viens de l'écrire au sommet de ma tête.
Donc, il ressemble à SqlServer is Buggy - vous le voyez ici d'agir correctement en fonction de la norme (et de leur documentation que j'ai montré avant dbfiddle.uk/...
@Hogan je suis avec vous - ce que j'ai oublié de la réponse était top 1 - dans ce cas, seul le résultat de première rangée est requis - pourquoi j'ai été confus - mais parce que je manquais le Top 1 Tout le monde a été confus. Merci encore
De côté: à l'aide de l'alias
A pour une table et une sous-requête dans votre première requête ne le rend pas plus facile à comprendre ou à entretenir.