Requête SQL avec du join et des conditions imbriquées

sqlserver
sql

#1

Hello les copains

Pour simplifier, j’ai 2 tables dans une base MS SQL Server 2012: une avec des requêtes (pas sql :p) faites par des utilisateurs, et une avec les actions liées à ces requêtes. Cela vient d’un logiciel de service manager (genre pour les helpdesks):

Table REQUESTS            Table ACTIONS
+------+-------+---+      +---------+------+--------+
|Req_ID|RFC_Num|...|      |Action_ID|Req_ID|Group_ID|
+------+-------+---+      +---------+------+--------+
| 1    | I01   |...|      |  a      | 1    | 10     |
+------+-------+---+      +---------+------+--------+
| 2    | I02   |...|      |  b      | 1    | 20     |
+------+-------+---+      +---------+------+--------+
| 3    | I03   |...|      |  c      | 1    | 38     |
+------+-------+---+      +---------+------+--------+
| 4    | I04   |...|      |  d      | 1    | 38     |
+------+-------+---+      +---------+------+--------+
                          |  e      | 2    | 10     |
                          +---------+------+--------+
                          |  f      | 2    | 38     |
                          +---------+------+--------+
                          |  g      | 2    | 20     |
                          +---------+------+--------+
                          |  h      | 3    | 38     |
                          +---------+------+--------+
                          |  i      | 4    | 10     |
                          +---------+------+--------+

Je veux pouvoir sélectionner toutes les requêtes pour lesquelles la dernière action a comme group_id 38, donc avec les tables exemples le résultat serait les req_id = 1 et req_id = 3, car la dernière action pour ces requêtes a group_id =38, mais ça ne renverrait pas les req_id = 2 (car la dernière action est faite par un autre groupe) ni req_id = 4 (car c’est un autre groupe également)

J’ai essayé des trucs tels que

select *
from REQUEST r
inner join ACTION a 
    on (
        (r.REQUEST_ID = a.REQUEST_ID ) 
        and (select group_id 
            from ACTION a2 
            where a2.action_id = a.action_id
            ).GROUP_ID = 38
        )

mais là je ne vois pas trop comment placer un “max(action_id)” (ou select top 1 with an order by desc) et je ne sais pas vraiment comme utiliser la clause HAVING qui aurait peut-être pu m’aider (sauf si je ne la comprends vraiment pas).

J’ai également essayé des requêtes comme celle-ci:

select *
from REQUEST r
inner join ACTION a 
    on (
        (r.REQUEST_ID = a.REQUEST_ID ) 
        and (select top 1 a2.action_id, a2.group_id 
            from ACTION a2 
            where a2.action_id = a.action_id
            group by a2.ACTION_ID
            order by a2.action_id desc
            ).GROUP_ID = 38
        ) 

Mais j’obtiens 2 erreurs:

Msg 8120, Level 16, State 1, Line 6
La colonne ‘ACTION.GROUP_ID’ n’est pas valide dans la liste de sélection parce qu’elle n’est pas contenue dans une fonction d’agrégation ou dans la clause GROUP BY.
Msg 116, Level 16, State 1, Line 11
Une seule expression peut être spécifiée dans la liste de sélection quand la sous-requête n’est pas introduite par EXISTS.

ou encore

select *
from REQUEST r
inner join ACTION a on (r.REQUEST_ID = a.REQUEST_ID) 
inner join ACTION a2 on (select top 1 a2.action_id, a2.group_id where a2.request_id = a.request_id order by a2.action_id desc).group_id = 38

Mais là encore j’ai une erreur car il ne veut pas du (select …).group_id = 38:
Une seule expression peut être spécifiée dans la liste de sélection quand la sous-requête n’est pas introduite par EXISTS.

Merci d’avance, je galère là


#2

Je n’ai pas MS SQL mais Postgres sur mon poste, mais je te propose une solution standard SQL.

Hop :slight_smile:

SELECT * 
FROM public."REQUESTS" R
inner join public."ACTIONS" A2
on R."Req_Id"=A2."Req_ID"
inner join  
(
  select a."Req_ID", max(a."Action_Id") max_action_id
  from public."ACTIONS" a
  group by a."Req_ID"
   
) M
on A2."Action_Id"=M.max_action_id
where A2."Group_ID"=38

(Je ne sais pas pourquoi mon Pg Windows exige des guillemets).


#3

Ceci fonctionne :

SELECT a1.req_id
  ,a1.group_id
  ,r1.rfc_num
 FROM actions as a1
   inner join (select max (action_id) action_id
      ,req_id
      from Actions
      group by req_id) as a2 on a1.action_id = a2.action_id
   inner join request as r1 on r1.req_id = a1.req_id
where a1.group_id = 38

edit : la meme que phili_b je crois :slight_smile:
Et le fiddle qui va bien : http://sqlfiddle.com/#!18/d5601/17


#4

Du coup, ça fait une différence de syntaxe avec SQL Server qui n’acceptera pas les doubles quotes.


#5

Oui j’ai fait cela à l’ancienne mais j’oubliais que Fiddle existe aussi pour SQL.

oui, tout à fait. Je ne me souviens pas que sous linux il m’ait causé ce souci.


#6

En fait si, il n’accepte pas que les chaines de caractères soient entre doubles quotes mais ça passe pour les colonnes.
Public c’est le nom du schéma par défaut sur PG ?


#7

oui on dirait :slight_smile: , j’ai fait cela à l’arrache sur mon poste perso.

Mais Coldfire est un grand garçon :stuck_out_tongue: il ne va pas bêtement copier-coller.

Tiens pendant que j’y pense @ColdFire, le postulat de départ disant que l’identifiant le plus élevé est le dernier est imparfait, pour bien faire faudrait une colonne d’horodatage :stuck_out_tongue:


#8

Oui oui, c’était plus pour la culture après !

Concernant le schéma je comptais y venir aussi, il ne faut pas perdre les bonnes habitudes mais j’espère bien que ce n’est que pour la question et que la colonne utilisée pour le tri est en vrai au pire un entier, au mieux un datetime. Mais dans le dernier cas il vaudrait mieux dissocier la pk, ça ne semble pas une bonne idée non plus.


#9

Il y a une autre solution, à priori elle n’est pas idéale dans ton cas mais je la met car tout le monde ne connait pas les fonctions analytiques.

select * from requests
inner join 
(
  select * 
  from 
  ( select req_id, group_id, rank() over(partition by req_id order by action_id desc) rk from actions ) ranked_actions
  where rk=1 
  and group_id=38
) last_actions
on last_actions.req_id = requests.req_id
 ;

#10

Les fonctions analytiques sont très performantes mais très consommatrice de puissance : pour cette raison à n’utiliser que si on n’a pas trouvé la solution en SQL standard.

:slight_smile:


#11

Ouais, ici c’est clairement pas idéal.

Par contre je ne serais pas aussi catégorique que toi : ici on tape deux fois sur la table actions, implicitement il s’agit de deux fois le même set de lignes. Avec la fonction analytique on ne calcule le set qu’une seule fois.

Imagine que pour calculer ce set de ligne il faut beaucoup de ressources, dans ce cas, si le set est suffisamment petit, ça peut être mieux de passer par une requête analytique.


#12

Pour info il y a plus d’un million d’enregistrements dans la table actions.
Je suis en train de jouer avec vos propositions, sauf que je me rends compte que déjà mon postulat sur l’identification des tickets d’une équipe est incomplet, mais déjà un gros merci pour l’aide!


#13

Sur des millions de lignes tu risques d’avoir des problèmes de performances avec des jointures comme ça. Si c’est le cas tu peux utiliser une table temporaire pour la requête la plus imbriqué qui fait le MAX.


#14

Bon au final, une fois qu’on a réellement compris le schéma du bordel ça va bien au-delà de ce que j’avais pensé, et le résultat final est ceci:

Select  SD_REQUEST.RFC_NUMBER AS "N°", 
		AM_ASSET.ASSET_TAG AS "N° inventaire", 
		SD_REQUEST.AVAILABLE_FIELD_2 AS "Nbre fils", 
		AM_REQUESTOR.LAST_NAME AS "Demandeur", 
		AM_RECIPIENT.LAST_NAME AS "Bénéficiaire", 
		AM_LOCATION_MATERIEL.LOCATION_FR AS "Localisation matériel", 
		AM_GROUP.GROUP_FR AS "Groupe", 
		SD_CATALOG.SD_CATALOG_ID AS "SD_CATALOG.SD_CATALOG_ID_0", 
		SD_REQUEST."COMMENT" AS "Description", 
		SD_STATUS.STATUS_FR AS "Statut", 
		SD_REQUEST.SUBMIT_DATE_UT AS "SD_REQUEST.SUBMIT_DATE_UT_1", 
		AM_SUBMITTED_BY.LAST_NAME AS "Enregistré par", 
		AM_ACTION.ACTION_ID AS "PK", 
		AM_ACTION.DONE_BY_ID AS "Fait par" 
From AM_ACTION 
	 INNER JOIN SD_REQUEST ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID 
	 INNER JOIN SD_CATALOG ON SD_REQUEST.SD_CATALOG_ID = SD_CATALOG.SD_CATALOG_ID 
	 INNER JOIN AM_REFERENCE SD_CATALOG_REF ON SD_CATALOG_REF.REFERENCE_ID = SD_CATALOG.CATEGORY_ID 
	 INNER JOIN AM_GROUP ON AM_ACTION.GROUP_ID = AM_GROUP.GROUP_ID 
	 INNER JOIN AM_EMPLGROUP ON AM_GROUP.GROUP_ID = AM_EMPLGROUP.GROUP_ID 
	 INNER JOIN AM_EMPLOYEE ON AM_EMPLOYEE.EMPLOYEE_ID = AM_EMPLGROUP.EMPLOYEE_ID 
	 INNER JOIN SD_URGENCY ON SD_URGENCY.URGENCY_ID = SD_REQUEST.URGENCY_ID 
	 INNER JOIN V_PRIORITY ON SD_REQUEST.URGENCY_ID = V_PRIORITY.URGENCY_ID AND SD_REQUEST.IMPACT_ID = V_PRIORITY.IMPACT_ID 
	 INNER JOIN AM_ACTION_TYPE ON AM_ACTION_TYPE.ACTION_TYPE_ID = AM_ACTION.ACTION_TYPE_ID 
	 LEFT OUTER JOIN AM_EMPLOYEE AM_RECIPIENT ON SD_REQUEST.RECIPIENT_ID = AM_RECIPIENT.EMPLOYEE_ID 
	 LEFT OUTER JOIN V_SD_STATUS SD_STATUS ON SD_STATUS.STATUS_ID = SD_REQUEST.STATUS_ID 
	 LEFT OUTER JOIN AM_EMPLOYEE AM_REQUESTOR ON AM_REQUESTOR.EMPLOYEE_ID = SD_REQUEST.REQUESTOR_ID 
	 LEFT OUTER JOIN AM_ASSET ON SD_REQUEST.ASSET_ID = AM_ASSET.ASSET_ID 
	 LEFT OUTER JOIN AM_EMPLOYEE AM_SUBMITTED_BY ON SD_REQUEST.SUBMITTED_BY = AM_SUBMITTED_BY.EMPLOYEE_ID
	 LEFT OUTER JOIN AM_LOCATION AM_LOCATION_MATERIEL ON AM_LOCATION_MATERIEL.LOCATION_ID = AM_ASSET.LOCATION_ID 
Where (AM_EMPLOYEE.EMPLOYEE_ID = 23173)
 	 and ((SD_CATALOG_REF.REFERENCE_GUID = '{E9C336A1-F7A7-4145-9F28-FDD883BFF389}' OR SD_CATALOG_REF.REFERENCE_GUID = '{E93064E7-CAA7-4994-A790-49B5F1FE4EA4}' OR SD_CATALOG_REF.REFERENCE_GUID = '{78B158B9-13BC-461B-BFDF-7FFFA1F6F34F}'))
	 and (AM_ACTION_TYPE.IS_SYSTEM <> 1 OR AM_ACTION_TYPE.IS_SYSTEM IS NULL)
	 and AM_GROUP.GROUP_ID = 38 
	 and (( AM_ACTION.END_DATE_UT is null) 
	 AND ( AM_ACTION.DONE_BY_ID is null)) 
Order by 1 ASC

(l’employee_id 23173 c’est moi mais je refiltre sur le groupe car j’appartiens à plusieurs groupes)


#15

y’ a With pour cela :yum:


#16

Côté perf ça va, la requête est exécutée en 0.15 secondes :slight_smile: