[SQLSERVER]split et jointure ?

split et jointure ? [SQLSERVER] - SQL/NoSQL - Programmation

Marsh Posté le 17-08-2010 à 11:36:52    

Bonjour,
 
j'ai dans une table une colonne qui représente des valeurs séparées par des ";"
du style : 3000;10;4000;30;6000;60
3000 4000 et 6000 représentent des ids (de catégories) dans une autre table.
 
Il faudrait que je puisse spliter ces ids, les remplacer par le nom de leur catégorie et les mettre dans 3 colonnes.
 
Est-ce possible ?
 
A noter que j'ai trouver une fonction dbo.fn_split qui fait ceci:
 

Code :
  1. USE [E_LOOKUP]
  2. GO
  3. /****** Object:  UserDefinedFunction [dbo].[fn_split]    Script Date: 08/17/2010 11:35:32 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER FUNCTION [dbo].[fn_split] (@separator char(1), @str VARCHAR(1024))
  9. RETURNS table
  10. AS
  11. RETURN (
  12. WITH parts(c, b, e) AS (
  13.  SELECT 1, 1, CHARINDEX(@separator, @str)
  14.  UNION ALL
  15.  SELECT c + 1, e + 1, CHARINDEX(@separator, @str, e + 1)
  16.  FROM parts
  17.  WHERE e > 0
  18. )
  19. SELECT SUBSTRING(@str, b, CASE WHEN e > 0 THEN e-b ELSE 1023 END) AS [value], c
  20. FROM parts
  21. )


 
mais je ne sais pas comment l'utiliser :d

Reply

Marsh Posté le 17-08-2010 à 11:36:52   

Reply

Marsh Posté le 17-08-2010 à 12:19:08    

ben c'est pas compliqué, tu l'appelles comme n'importe quelle fonction standard de SQL Server en lui fournissant 2 paramètres : le séparateur (char(1)) et la chaine de caractères <= 1024 caractères (varchar(1024))


---------------
J'ai un string dans l'array (Paris Hilton)
Reply

Marsh Posté le 17-08-2010 à 14:58:21    

certes. Mais comment tu fais l'appel ?
Ca me dit: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_split", or the name is ambiguous.


Message édité par Profil supprimé le 18-08-2010 à 13:57:39
Reply

Marsh Posté le 17-08-2010 à 15:24:29    

Code :
  1. SELECT * FROM fn_split(';','3000;10;4000;30;6000;60')

Reply

Marsh Posté le 17-08-2010 à 16:15:38    

ça ne marche pas

Reply

Marsh Posté le 17-08-2010 à 16:16:32    

ça ne marche pas  [:clooney19]  
j'ai même tenté de préfixer le nom avec [NOMDB].[dbo].fn_split()
et ça veut pas

Reply

Marsh Posté le 17-08-2010 à 16:31:35    

ha ça ne marche pas parce que je le mettais dans le select, pas dans le from  [:cerveau du chaos]  
 
mais alors, du coup comment je fais ma jointure  [:pingouino]

Reply

Marsh Posté le 17-08-2010 à 16:55:01    

j'ai trouvé ça  [:pingouino]  

Code :
  1. SELECT * FROM (
  2. SELECT
  3.  visitor_interests.visitor_id,
  4.  visitor_interests.score,
  5.  interests.value,
  6.  'C' + CAST(ROW_NUMBER() OVER (PARTITION BY visitor_interests.visitor_id order by interests.c) as VARCHAR) sequence
  7. FROM (
  8.   SELECT
  9.    v.visitor_id,
  10.    v.score,
  11.    v.interests,
  12.    ROW_NUMBER() OVER (PARTITION BY v.visitor_id ORDER BY v.last_request DESC) row
  13.   FROM DB1.dbo.visitors v
  14.   INNER JOIN DB2.dbo.contacts c ON v.visitor_id = c.nm_visitor_id
  15.   WHERE
  16.     v.interests IS NOT NULL
  17.    AND v.interests <> ''
  18.    AND v.last_request > ISNULL(c.nm_date_modified, '1970-01-01 00:00:00')
  19. ) visitor_interests
  20. cross apply DB3.dbo.fn_split(';', interests) interests
  21. WHERE
  22.  visitor_interests.row = 1
  23. ) result
  24. pivot
  25. (
  26. max(value)
  27. for sequence
  28. in (C1, C2, C3, C4, C5, C6)
  29. ) as p


 
Ca marche mais je ne comprends absolument rien  [:cerveau rockstopper]  
Et un SELECT TOP 1 * met 5 secondes à s'exécuter  [:clooney3]

Reply

Marsh Posté le 17-08-2010 à 18:24:54    

Bon, je me suis fait une fonction scalar. Y en a pleins sur le net.
 

Code :
  1. ALTER FUNCTION splitt (@separator varchar(1), @position INT, @str VARCHAR(1024))
  2. RETURNS VARCHAR(1024)
  3. AS
  4. BEGIN
  5.   DECLARE @retVal       VARCHAR(1024)
  6.   DECLARE @varFldDel    VARCHAR(1024) SET @varFldDel = @str + ','
  7.   DECLARE @i            int SET @i = 0
  8.   DECLARE @varExit      VARCHAR(5) SET @varExit = 'FALSE'
  9.   DECLARE @intLastLen   int SET @intLastLen = 1
  10.   IF @position < 1
  11.     SET @retVal = 'Not Valid Option, 1 is the first occurance of an item'
  12.   ElSE
  13.    --> loop through till the correct column is found
  14.     WHILE @varExit = 'FALSE'
  15.       BEGIN
  16.         SET @i = @i + 1
  17.  SET @varFldDel = SUBSTRING(@varFldDel,@intLastLen,1024)
  19.         If CHARINDEX(@separator,@varFldDel) < 1
  20.         BEGIN
  21.           RETURN NULL
  22.         END
  23.        --
  24.         SET @retVal = SUBSTRING(@varFldDel,1,(CHARINDEX(@separator,@varFldDel)-1))
  25.         SET @intLastLen = LEN(@retVal) + 2
  26.         If @i = @position
  27.           BREAK
  28.       END
  29. --
  30.   RETURN @retVal
  32. END
  33. GO
  34. print dbo.splitt(';',1,'3000;10;4000;30;6000;60');
  35. print dbo.splitt(';',2,'3000;10;4000;30;6000;60');
  36. print dbo.splitt(';',3,'3000;10;4000;30;6000;60');
  37. print dbo.splitt(';',4,'3000;10;4000;30;6000;60');
  38. GO


 
Soucis, ça ne marche pas non plus dans un select (??)
On fait comment alors ?
Quel casse tête ce truc
 

Reply

Marsh Posté le 18-08-2010 à 07:56:50    

Ca marche tres bien quand c'est correctement utilisé :)
Tu peux nous donner l'exemple que tu es en train d'essayer?
C'est peut etre juste un détail qui bloque.
 
Maintenant coté perf ce sera toujours horrible, ce que tu essayes de faire fonctionnera toujours en row by row (tres tres lent).
L'ideal est de normaliser un peu la db et de ne plus avoir plusieurs colonnes en une.


Message édité par Oliiii le 18-08-2010 à 07:57:05
Reply

Marsh Posté le 18-08-2010 à 07:56:50   

Reply

Marsh Posté le 18-08-2010 à 10:21:55    

Bon j'ai redémarré le SQL studio et ça marche  [:cerveau du chaos]

 

Je me retrouve avec un truc comme ça:

 
Code :
  1. interest1 prc1 interest2 prc2 interest3 prc3
  2. 30014 56 300000178 NULL NULL NULL
  3. 300000178 50 30014 NULL NULL NULL
  4. 30014 50 300000178 NULL NULL NULL
  5. 30014 68 300000178 NULL NULL NULL
  6. 30016 81 311000000 NULL NULL NULL
  7. 30016 81 311000000 18 300000033 NULL
 

Maintenant je ne veux pas les id mais les valeurs associées qui sont dans une autre table.
Comment je fais ma jointure ?


Message édité par Profil supprimé le 18-08-2010 à 10:22:20
Reply

Marsh Posté le 18-08-2010 à 10:46:38    

Bon alors,  
de mon esprit démoniaque est sorti ceci:
 
Je passe de ça: (pour avoir les ids)

Code :
  1. SELECT 
  2. c.nm_visitor_id
  3. ,c.email AS cust_id
  4. ,c.nm_date_modified
  5. ,v.interests
  6. ,dbo.splitt(';',1,v.interests) AS interest1
  7. ,dbo.splitt(';',2,v.interests) AS prc1
  8. ,dbo.splitt(';',3,v.interests) AS interest2
  9. ,dbo.splitt(';',4,v.interests) AS prc2
  10. ,dbo.splitt(';',5,v.interests) AS interest3
  11. ,dbo.splitt(';',6,v.interests) AS prc3
  12.   FROM [DB1].[dbo].[contacts] c
  13.   LEFT JOIN [DB2].[dbo].[visitors] v ON c.nm_visitor_id = v.visitor_id
  14.   WHERE c.nm_date_modified > DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
  15.   AND v.interests IS NOT NULL


 
à ça: (pour avoir les valeurs)

Code :
  1. SELECT nm_visitor_id
  2. ,cust_id
  3. ,nm_date_modified
  4. ,t1.value AS category1
  5. ,prc1 AS category1_perc
  6. ,t2.value AS category2
  7. ,prc2 AS category2_perc
  8. ,t3.value AS category3
  9. ,prc3 AS category3_perc
  10.  FROM(
  11. SELECT 
  12.     c.nm_visitor_id
  13.     ,c.email AS cust_id
  14.     ,c.nm_date_modified
  15.     ,v.interests
  16.     ,dbo.splitt(';',1,v.interests) AS interest1
  17.     ,dbo.splitt(';',2,v.interests) AS prc1
  18.     ,dbo.splitt(';',3,v.interests) AS interest2
  19.     ,dbo.splitt(';',4,v.interests) AS prc2
  20.     ,dbo.splitt(';',5,v.interests) AS interest3
  21.     ,dbo.splitt(';',6,v.interests) AS prc3
  22.       FROM [DB1].[dbo].[contacts] c
  23.       LEFT JOIN [DB2].[dbo].[visitors] v ON c.nm_visitor_id = v.visitor_id
  24.       WHERE c.nm_date_modified > DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
  25.       AND v.interests IS NOT NULL
  26.   ) p 
  27.   
  28. LEFT JOIN [DB1].[dbo].tags t1 ON p.interest1 = t1.tag_id 
  29. LEFT JOIN [DB1].[dbo].tags t2 ON p.interest2 = t2.tag_id 
  30. LEFT JOIN [DB1].[dbo].tags t3 ON p.interest3 = t3.tag_id


 
C'est correct ou y a mieux à faire ?

Reply

Marsh Posté le 18-08-2010 à 13:08:19    

A vue de nez (j'ai verifié que la syntax) ca devrai aussi marcher comme ca:

Code :
  1. SELECT 
  2.     c.nm_visitor_id
  3.     ,c.email AS cust_id
  4.     ,c.nm_date_modified
  5.     ,v.interests
  6.     ,t1.value AS category1
  7.     ,dbo.splitt(';',2,v.interests) AS prc1
  8.     ,t2.value AS category2
  9.     ,dbo.splitt(';',4,v.interests) AS prc2
  10.     ,t3.value AS category3
  11.     ,dbo.splitt(';',6,v.interests) AS prc3
  12. FROM [DB1].[dbo].[contacts] c
  13.     LEFT JOIN [DB2].[dbo].[visitors] v ON c.nm_visitor_id = v.visitor_id
  14.     LEFT JOIN [DB1].[dbo].tags t1 ON dbo.splitt(';',1,v.interests) = t1.tag_id 
  15.     LEFT JOIN [DB1].[dbo].tags t2 ON dbo.splitt(';',3,v.interests) = t2.tag_id 
  16.     LEFT JOIN [DB1].[dbo].tags t3 ON dbo.splitt(';',5,v.interests) = t3.tag_id 
  17. WHERE c.nm_date_modified > DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
  18.     AND v.interests IS NOT NULL


 
Maintenant je me demande si la solution avec la table valued function était pas mieux point de vue performance, ici tu vas lire le record 6x (6x split) alors qu'avec la table valued function tu ne le lis qu'une seule fois.
 
Idéalement tu devrais avoir 6 colonnes en plus avec le contenu déja splité (soit tu le remplis a l'insert ou au pire tu rajoutes un trigger on insert/update qui met a jour les 6 colonnes).


Message édité par Oliiii le 18-08-2010 à 13:11:07
Reply

Marsh Posté le 18-08-2010 à 13:57:20    

Ça marche oui  [:boisse]  
 
Je ne peux pas modifier la table donc je suis obligé de faire le split moi-même.
 
la table valued tu veux dire un split qui me retourne une table et non un varchar ? Ca me pose problème aussi de faire 6 fois un split sur le même champ. Je vais voir ce que je peux faire

Reply

Marsh Posté le 18-08-2010 à 14:50:30    

J'ai une fonction split qui donne une table comme ça:
select * from dbo.fn_split(';','10;20;30;40;50;60') v
value   c
---------
10 1
20 2
30 3
40 4
50 5
60 6
 
y a moyen (pivot ?) de la transformer comme ça ? :
c1 c2 c3 c4 c5 c6
------------------
10 20 30 40 50 60

Reply

Marsh Posté le 18-08-2010 à 14:59:59    

Oui le pivot te fera exactement ca.
Si ca peu attendre demain je pourrais te donner un exmemple, sinon les exemples dans les books online ne sont pas trop mal.

Reply

Marsh Posté le 18-08-2010 à 15:49:21    

bon alors le pivot ça se passe comme ça:

Code :
  1. SELECT 
  2. [1] AS interest1
  3. ,[2] AS prc1
  4. ,[3] AS interest2
  5. ,[4] AS prc2
  6. ,[5] AS interest3
  7. ,[6] AS prc3
  8.  FROM
  9.  ( SELECT value,c FROM dbo.fn_split(';','10;20;30;40;50;60')) AS SourceTable
  10. pivot
  11. (
  12. max(value)
  13. FOR c IN ([1],[2],[3],[4],[5],[6])
  14. ) AS pivotTable


 [:charles k:5]  
 
et maintenant faut que je foute ça dans ma grosse query avec des join partout  [:charles k:5]  
 [:canardeur]

Reply

Marsh Posté le 19-08-2010 à 12:50:48    

Bon j'abandonne l'idée de faire un spilt en une fois c'est vraiment trop le bordel.  [:clooney9]  
 
Par contre ma fonction split ne marche pas. Elle ne donne pas le dernier élément  [:clooney19]  
 
Donc j'en ai fait une moi-même.
Pour ceux que ça intéresse:

Code :
  1. ALTER FUNCTION [dbo].[fn_strSplit] 
  2. (
  3.     @input nvarchar(1024),
  4.     @separator char(1),
  5.     @position int
  6. )
  7. RETURNS varchar(1024)
  8. AS
  9. BEGIN
  10.     DECLARE @output varchar(1000)
  11.     DECLARE @count INT 
  12.     DECLARE @temp varchar(1000)
  13.     SET @count = 1
  14.     SET @temp = @input
  15.     WHILE (@count<=@position)
  16.     BEGIN
  17.         
  18.         IF (CHARINDEX(@separator,@temp)= 0)
  19.         BEGIN
  20.             IF(@count<@position)
  21.             BEGIN
  22.                 SET @output = NULL
  23.                 BREAK
  24.             END
  25.             ELSE
  26.             BEGIN
  27.                 SET @output = @temp
  28.                 BREAK
  29.             END
  30.         END
  31.         SET @output = SUBSTRING(@temp,0,CHARINDEX(@separator,@temp))
  32.         SET @temp = SUBSTRING(@temp,CHARINDEX(@separator,@temp)+1,LEN(@temp))
  33.         SET @count= @count +1
  34.         
  35.     END
  36.     
  37.     RETURN @output
  38.  
  39. END

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed