Je suis sûr que je manque quelque chose ici.
J'ai un jeu de données comme celui-ci: Je voudrais interroger les 3 meilleurs résultats et Pivotez-les comme colonnes, le jeu de résultats final ressemble à ceci: Comment puis-je accomplir cela dans SQL Server 2005? J'ai essayé de tenter cela en utilisant Pivot , mais je suis toujours très inconnu avec ce mot clé et je ne peux pas le faire fonctionner comme je le veux. mon ensemble de données réel est un peu plus complexe que cela, et j'ai besoin des 10 meilleurs enregistrements, pas le top 3, donc je ne veux donc pas simplement faire J'ai testé toutes les réponses ci-dessous, et la plupart d'entre eux semblent à peu près la même chose sans différence de performance apparente dans des ensembles de données plus petits (environ 3K enregistrements), cependant Il y avait une légère différence lors de la gestion des requêtes contre des ensembles de données plus importants. Voici les résultats de mes tests à l'aide de 80 000 enregistrements et interrogez sur 5 colonnes dans les 10 premières lignes. Mon ensemble de résultat final était de 50 colonnes + la colonne ID . Je vous suggère de les tester seuls pour décider de laquelle on fonctionne le mieux pour vous et votre environnement. La réponse de Bluefoot d'impuche et reproduisez les données la plus rapide à environ 12 secondes. J'ai aussi aimé cette réponse parce que j'ai trouvé plus facile à lire et à entretenir.
La réponse d'Aaron et Réponse de koderoid Les deux suggèrent à l'aide d'un Rodney's Réponse d'utiliser plusieurs et la première moitié de Réponse d'Aaron qui suggère à l'aide d'un CTE et lorsque RWOWNBER = x (code> pour chacun.
max (cas lorsque rownber = x alors ...) et était proche de la moyenne de la moyenne d'environ 13 secondes.
Pivot Les déclarations en moyenne de 16 secondes, bien que Cela pourrait être plus rapide avec moins de déclarations de pivotements (mes tests avaient 5).
externe était le plus lent. Je ne sais pas combien de temps il faudrait pour courir parce que je l'ai annulé après 2 minutes, ce qui était avec environ 3k enregistrements, 3 lignes et 3 colonnes au lieu de 80k enregistrements, 10 lignes et 5 colonnes.
5 Réponses :
Vous pouvez essayer de faire le pivot dans trois déclarations de pivotement distinctes. Veuillez essayer:
SELECT Id
,MAX(S1) [Status 1]
,MAX(T1) [Type1]
,MAX(V1) [Value1]
--, Add other columns
FROM
(
SELECT Id, Value , Type, Status
, 'S' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Status, Type) AS VARCHAR(10)) [Status_RowNumber]
, 'T' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Status, Type) AS VARCHAR(10)) [Type_RowNumber]
, 'V' + CAST(ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Status, Type) AS VARCHAR(10)) [Value_RowNumber]
FROM MyTable
) as T
PIVOT
(
MAX(Status) FOR Status_RowNumber IN ([S1], [S2], [S3],[S4],[S5],[S6],[S7],[S8],[S9],[S10])
)AS StatusPivot
PIVOT(
MAX(Type) FOR Type_RowNumber IN ([T1], [T2], [T3],[T4],[T5],[T6],[T7],[T8],[T9],[T10])
)AS Type_Pivot
PIVOT(
MAX(Value) FOR Value_RowNumber IN ([V1], [V2], [V3],[V4],[V5],[V6],[V7],[V8],[V9],[V10])
)AS Value_Pivot
GROUP BY Id
+1, très intelligent. Mais s'il vous plaît n'utilisez pas varchar sans longueur. sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/...
J'ai mis à jour ma déclaration pour inclure la longueur de Varchark. C'était un article très utile.
Vous pouvez le rendre même tiditier en utilisant une deuxième sous-requête pour dériver le rang_number. Ne devrait être évalué qu'une fois dans les deux cas, mais il semblera plus jolie. :-)
Cela fonctionne réellement bien mieux que je pensais que ce serait! Je l'ai utilisé pour obtenir 4 colonnes des 10 enregistrements TOP 10 pour environ 4000 enregistrements FK, et il n'y avait pas de retard notable dans l'exécution de la requête. J'aimerais toujours essayer de l'exécuter contre un ensemble de résultats plus large et essayer certaines des autres réponses postées ici pour voir s'il y a une différence de performance, mais je suis très heureux d'avoir une solution :) (PS @aaronbertrand Quoi Voulez-vous dire environ une deuxième sous-requête? Ma requête pourrait certainement utiliser n'importe quelle poisson qu'elle peut obtenir)
@Aaronbertrand ahhhh qui a du sens maintenant, merci. J'ai besoin d'y aller, mais je prévois de choisir cette sauvegarde sur le mardi et vous fera savoir comment ça se passe. Merci :)
@Rachel Je ne vois rien de la version spécifique à la version, alors je doute vraiment 2005 vous donne des résultats différents en raison du code, c'est à cause des données.
Bien que j'aime cette solution, il s'est avéré d'imputation / pivotant des données comme Bluefoot suggéré était plus rapide pour ma situation spécifique. J'ai mis à jour ma question avec mes résultats de test si vous êtes intéressé :)
Muli-pivot de Rodney est intelligent, c'est sûr. Voici deux autres alternatives qui sont bien sûr moins attrayantes lorsque vous entrez dans la zone 10x contre 3x. - ou - P> ;WITH a AS
(
SELECT Id, Value, [Type], [Status],
n = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [Status], [Type])
FROM dbo.MyTable
)
SELECT Id,
Value1 = MAX(CASE WHEN n = 1 THEN Value END),
Type1 = MAX(CASE WHEN n = 1 THEN [Type] END),
Status1 = MAX(CASE WHEN n = 1 THEN [Status] END),
Value2 = MAX(CASE WHEN n = 2 THEN Value END),
Type2 = MAX(CASE WHEN n = 2 THEN [Type] END),
Status2 = MAX(CASE WHEN n = 2 THEN [Status] END),
Value3 = MAX(CASE WHEN n = 3 THEN Value END),
Type3 = MAX(CASE WHEN n = 3 THEN [Type] END),
Status3 = MAX(CASE WHEN n = 3 THEN [Status] END)
FROM a
GROUP BY Id
ORDER BY a.Id;
Dans votre premier scénario, savez-vous si la CTE est évaluée pour chaque jointure? Signification Si je crée 10 jointures pour obtenir les 10 meilleurs enregistrements, le CTE sera-t-il évalué 10 fois?
@Rachel impossible à savoir sans tests, désolé. Trop de variables peuvent dicter si une CTE sera évaluée plus d'une fois.
La première requête avec le CTE ne va certainement pas fonctionner pour moi à cause de la performance, mais la seconde a une durée d'exécution décente. J'ai mis à jour ma question avec les résultats de mes tests si vous êtes intéressé :)
Vous pouvez faire un Version statique: version dynamique, cela obtiendra la liste des colonnes à voir SQL Fiddle avec Demo Les deux généreront les mêmes résultats, mais la dynamique est excellente si vous ne connaissez pas le nombre de colonnes à l'avance. La version dynamique fonctionne sous l'hypothèse que la rive est déjà une partie de l'ensemble de données. pivot , puis un pivot des données. Cela peut être fait de manière statique ou dynamique: impublard , puis sur pivot < / code> au moment de l'exécution:
+1 mais varchar (1) va casser la valeur 10 . En outre, je reçois une erreur sur les conflits de type (la démonstration que vous avez connaisse comme une colonne dans la table; il doit être fabriqué au moment de l'exécution, je pense). Donc, je passe des tests de temps en dehors de votre démo.
@Aaronbertrand j'ai utilisé varchar (1) comme exemple car il n'y avait que 1 à 5 numéros de rangée présents, il faudrait être prolongé pour des valeurs plus longues. J'ai basé ma démo sur les données fournies, si le rive-numérique n'est pas présent, alors oui, il faudrait être évalué avant utilisation. Je l'ai basé sur le jeu de données présenté montrant qu'il est possible via un pivot , puis pivot .
Ici et sur le tas, elle a expliqué qu'elle a besoin de 10 ensembles (elle vient d'utiliser 3 dans l'échantillon).
Ok, j'essaie d'obtenir une version dynamique fonctionnant également. Je viens de comprendre que je posterais immédiatement une version statique. :)
+1 - Nice. Je me demandais comment tu n'avais pas encore répondu ;-)
Oh, belle préparation alors. Félicitations!
@Aaronbertrand a ajouté une version dynamique pour obtenir toutes les lignes au moment de l'exécution
J'ai modifié la requête pour travailler avec mon ensemble de données réelle et, bien que l'impulsion apparaisse ok, la requête pivotante finale renvoie toutes les valeurs null même s'il n'y a pas de valeurs nulles dans l'ensemble de données impouvert. Essayant toujours de comprendre pourquoi c'est maintenant
@Rachel Si vous obtenez null valeurs, vous pouvez implémenter un iSnull () autour de chaque colonne, cela peut même être ajouté à la version dynamique de la requête
@Bluefeet Je n'ai aucun NULL Valeurs dans les données, ce qui me confondre. Je peux supprimer le dernier pivot de la requête statique et ajouter où new_col est NULL ou VAL est NULL et ne renvoie aucun enregistrement.
@Rachel Il est difficile de dire sans voir l'échantillon complet des données. Pouvez-vous créer un sqlfiddle avec un échantillon complet?
@bluefeet Je vais essayer ... Beaucoup de données que je travaille sont privées, je ne peux donc pas divulguer les valeurs exactes. Savez-vous s'il y a un paramètre pour SQL Fiddle pour SQL 2005? Je suis curieux de savoir si cela fait une différence car je ne peux voir aucune différence entre mes données et la vôtre
@Rachel je comprends ça. Je vais toujours essayer de jouer avec ce que j'ai
@Bluefeet J'ai saisi certaines données réelles, complétées avec des valeurs NULL et la fonction Row_Number () , mais votre violon fonctionne toujours pendant que ma requête SQL ne fonctionne pas. Les seules différences que je peux penser est la table est en réalité une sous-requête contenant 5 jointures de table et j'exécute une version différente de SQL Server. J'ai besoin d'y aller, mais j'espère que ce sauvegarde le mardi. J'apprécie vraiment que vous preniez le temps de m'aider et je vous ferai savoir si je suppose que mon problème est :)
@Rachel OK, même si vous pouviez frotter un ensemble de données plus grand, je pourrais avoir une meilleure idée du problème.
@Bluefeet en fait, je l'ai compris comme je l'étais sur le point de partir :) J'utilisais mes propres noms de colonne afin que mon impublard était val pour col dans (myvaluecolumn, mytypecolumn, mystatuscolumn) , mais j'ai oublié de Modifiez le pivot sur pivot à l'aide des nouveaux noms de colonne. Une fois que j'ai remplacé mes noms de colonne avec valeur , type et statut , cela fonctionne. Je suis désolé de vous distraire de votre mariage et de vous féliciter! :)
@Rachel heureux que vous l'avez compris.
Merci @Bluefoot, cela s'est effectivement passé pour effectuer le meilleur pour ma situation et mon environnement. J'ai mis à jour ma question avec les résultats des tests si vous êtes intéressé :)
@Rachel content que vous ayez pu obtenir cela pour travailler pour votre situation. :)
Cela pourrait fonctionner pour vous, bien que ce ne soit pas élégant.
select aa.FK_Id
, isnull(max(aa.Value1), '') as Value1
, isnull(max(aa.Type1), '') as Type1
, isnull(max(aa.Status1), '') as Status1
, isnull(max(aa.Value2), '') as Value2
, isnull(max(aa.Type2), '') as Type2
, isnull(max(aa.Status2), '') as Status2
, isnull(max(aa.Value3), '') as Value3
, isnull(max(aa.Type3), '') as Type3
, isnull(max(aa.Status3), '') as Status3
from
(
select FK_Id
, case when RowNumber = 1 then Value else null end as Value1
, case when RowNumber = 1 then [Type] else null end as Type1
, case when RowNumber = 1 then [Status] else null end as Status1
, case when RowNumber = 2 then Value else null end as Value2
, case when RowNumber = 2 then [Type] else null end as Type2
, case when RowNumber = 2 then [Status] else null end as Status2
, case when RowNumber = 3 then Value else null end as Value3
, case when RowNumber = 3 then [Type] else null end as Type3
, case when RowNumber = 3 then [Status] else null end as Status3
from Table1
) aa
group by aa.FK_Id
Oui c'était mon plan de sauvegarde. J'ai besoin de sélectionner 5 colonnes dans les 10 principaux enregistrements, ce qui donne lieu à 50 déclarations de cas
Lorsque vous avez les deux solutions, veuillez nous tenir au courant des performances. Merci :)
Merci beaucoup de Rachel. Bon à savoir. :-)
Essayez quelque chose comme ceci:
declare @rowCount int
set @rowCount = 10
declare @isNullClause varchar(4024)
set @isnullClause = ''
declare @caseClause varchar(4024)
set @caseClause = ''
declare @i int
set @i = 1
while(@i <= @rowCount) begin
set @isnullClause = @isNullClause +
' , max(aa.Value' + CAST(@i as varchar(3)) + ') as Value' + CAST(@i as varchar(3)) +
' , max(aa.Type' + CAST(@i as varchar(3)) + ') as Type' + CAST(@i as varchar(3)) +
' , max(aa.Status' + CAST(@i as varchar(3)) + ') as Status' + CAST(@i as varchar(3)) + ' ';
set @caseClause = @caseClause +
' , case when RowNumber = ' + CAST(@i as varchar(3)) + ' then Value else null end as Value' + CAST(@i as varchar(3)) +
' , case when RowNumber = ' + CAST(@i as varchar(3)) + ' then Type else null end as Type' + CAST(@i as varchar(3)) +
' , case when RowNumber = ' + CAST(@i as varchar(3)) + ' then Status else null end as Status' + CAST(@i as varchar(3)) + ' '
set @i = @i + 1;
end
declare @sql nvarchar(4000)
set @sql = 'select aa.FK_Id ' + @isnullClause + ' from ( select FK_Id '
+ @caseClause + ' from Table1) aa group by aa.FK_Id '
exec SP_EXECUTESQL @sql
+1 pour fournir une manière dynamique de construire le cas lorsque la version de la requête :)
Qu'est-il arrivé à la ligne avec
ddddd eteeeee ?@njk ils ne sont pas inclus dans l'ensemble de résultats final car je ne suis intéressé que par obtenir les documents Top X (dans mon exemple j'utilise 3, mais dans ma requête réelle, j'ai besoin des 10 meilleurs enregistrements)
Quels sont les critères pour sélectionner "Top 10"?
@NJK L'ensemble de résultats final nécessite 10 rangées de données. Le premier jeu de données est réellement obtenu à l'aide du Row_Number () fonction, et peut facilement être trié et filtré pour obtenir les 10 meilleurs enregistrements. Je suis plus préoccupé par l'obtention de l'ensemble de 2e résultats, qui interroge les données de ligne pour chaque colonne
Je suppose que ID ID dans le deuxième ensemble est essentiellement rive du premier ensemble et rien à voir avec ID dans la première série? ID de la première définition devient l'indexeur de la matrice dans le deuxième ensemble? Cela devrait être un joli pivot standard. Pouvez-vous donner au code que vous avez essayé avec pivot?
@Caderoux Le
ID dans les deux ensembles est une fourchette à une autre table et n'est pas la même que celle du 2e ensemble. J'ai mis à jour ma question avec la requête que je teste actuellement.@Rachel mais c'est deux fois plus complètement différents?
@Caderoux Non, c'est le même FK. Je sais que je pourrais écrire la requête avec une tonne de cas
lorsque rownumber = x ALORS VALEUR ... Déclarations, mais je pensais qu'il y aurait une solution facile qui n'impliquait pas 50 déclarations de cas (j'ai besoin de 5 colonnes à partir des 10 lignes TOP 10, donc 50 colonnes de mon ensemble de résultats + la colonne ID)