SQL-Abfrage, um die längste Folge von 7 aufeinanderfolgenden Werten einer Person zu zählen

797
Zero Cool

Ich habe eine SQL-Tabelle, die folgendermaßen aussieht (mit dem Namen in Spalte A und der Bewertung in Spalte B):

 N. Name Score 1. Jim 7  2. Jim 4  3. Jim 4  4. Jim 7 5. Jim 7 6. Jim 7 7. Jim 7 8. Jim 4 9. Ted 4 10. Ted 4 11. Ted 7 12. Ted 7 . . . n. cont'd

Ich muss jede Person nach der höchsten Anzahl aufeinanderfolgender Ergebnisse von 7 bewerten.

Jims max 7 Ergebnis: 4, weil er viermal in Folge 7 Punkte erzielt hat. Teds max 7 Ergebnis 2; das ist seine maximale Anzahl. Wenn ich nach maximal 4 suchen würde, wären beide zwei Punkte.

Wie erhalte ich die SQL-Abfragefunktion in SSMS, um mir mitzuteilen, welche der längsten Ergebnisse von 7 Punkten (oder einem bestimmten Wert) für jede Person war?

Dies ist ein Beispiel für eine lange Liste mit über 100 Millionen Zeilen mit 1 Million eindeutigen Namen (eigentlich eindeutige Nummern, keine Namen, sondern nur zur Veranschaulichung ...).

Idealerweise gibt die Abfrage eine Liste zurück, die wie folgt aussieht:

Bob 133 Jane 117 Phil 106 ... Jim 4 Ted 2

Ich denke, es wird mit SELECT DISTINCT beginnen, so dass mein Ergebnis alle eindeutigen Namen hat.

0
Wenn Sie einen Moment bekommen, schauen Sie sich [Der Unterschied zwischen ROW_NUMBER (), RANK () und DENSE_RANK ()] an (https://blog.jooq.org/2014/08/12/the-difference-between-row_number- rank-and-dense_rank /) Pimp Juice IT vor 7 Jahren 0
Danke, als ich sagte, dass ich sie nach hoch / niedrig bewerten muss, hätte ich sagen sollen, wie hoch die Anzahl der aufeinander folgenden Punktzahlen für jede Person ist, und ich beabsichtige, diese Werte dann hoch einzustufen zu tief. Dies scheint nur eine Liste zu erstellen, ohne den Wert zu erhalten. Zero Cool vor 7 Jahren 0
Was genau ist deine Frage? Sie sollten "längste Reihe von Punkten von 7", aufeinanderfolgende Zeichenfolge oder nur die Gesamtzahl von Punkten, die über 7 liegen, klarstellen. Ramhound vor 7 Jahren 0
Ich bin auf der Suche nach einer SQL-Abfrage, um die längste Folge aufeinanderfolgender Punktzahlen von 7 Personen zu zählen. Der Titel wurde entsprechend umbenannt. Vielen Dank. Zero Cool vor 7 Jahren 0

1 Antwort auf die Frage

0
Steve Mangiameli

Hier ist der Code, den ich mir ausgedacht habe. Sie könnten dies möglicherweise in eine große Frage stellen, aber ich habe es aus Gründen der Übersichtlichkeit in temporäre Tabellen aufgeteilt, damit Sie es leichter anpassen können. Grundsätzlich benötigen Sie einen mehrstufigen Prozess

  1. Filtern Sie die Daten
  2. Gruppieren Sie die Datensätze extern
  3. Erstellen Sie relationale Gruppen und zählen Sie die Ergebnisse

Code

 --Test data create table #testRank ( RowNum int identity, Name nvarchar(255), Score int )  insert #testRank values ('Jim',7), ('Jim',4), ('Jim',4), ('Jim',7), ('Jim',7), ('Jim',7), ('Jim',7), ('Jim',4), ('Ted',4), ('Ted',4), ('Ted',7), ('Ted',7) -- I've added a second set of Jim data for testing, ('Jim',7), ('Jim',7), ('Jim',7), ('Jim',7)  --minimize the dataset; if your dealing with millions of rows, filter out the scores less than 7 select RowNum, Name  into #smallerDataSet from #testRank where score = 7;  --create groups or strings delineated by NULL values indicating the start and end of the string of scores about 7 select  x.RowNum, x.Name, Case when z.NextRow is null then null else x.RowNum-1 end PrevRow, Case when y.PrevRow is null then null else x.RowNum+1 end NextRow into #strings from #smallerDataSet x Left Join (select RowNum-1 PrevRow, Name from #smallerDataSet) y on y.PrevRow = x.RowNum and y.Name = x.Name Left Join (select RowNum+1 NextRow, Name from #smallerDataSet) z on z.NextRow = x.RowNum and z.Name = x.Name Where PrevRow is not null or NextRow is not null  --visulaize the query above select * from #strings  --use the ROW_NUMBER function to seperate the groups from each other; particularly important when the same name has the same string count --then simply do the math and order the list Select Distinct p.Name, EndStr-StartStr+1 MaxString from ( select ROW_NUMBER() over (order by RowNum) GroupNum, Name, RowNum StartStr  from #strings where PrevRow is null ) p join ( select ROW_NUMBER() over (order by RowNum) GroupNum, Name, RowNum EndStr from #strings where NextRow is null ) n on n.GroupNum = p.GroupNum and n.Name = p.Name order by MaxString desc  drop table #strings drop table #smallerDataSet drop table #testRank
Erstmal vielen Dank, dass Sie das geschrieben haben. Es bedeutet, dass für die Zeilen 53 und 54 für PrevRow, RowNum, Name und NextRow ein ungültiger Spaltenname angegeben ist. Ich kann nicht herausfinden, warum. Ich führe das Skript alle zusammen aus und bin vorher in Einzelteilen gelaufen und habe Erfolg beim Erstellen der #Strings gefunden. Könnte ein fehlendes Komma oder etwas fehlen? Zero Cool vor 7 Jahren 0
Der Code funktioniert mit den von mir geposteten Testdaten. Sie müssen es ändern, um mit Ihrem Tabellenschema zu arbeiten. RowNum repräsentiert die Werte in Ihrem Beispiel für N. Steve Mangiameli vor 7 Jahren 0
Das ist toll. Ich lasse es jetzt laufen. Ich habe einen 100-Zeilen-Lauf gemacht und es wird jede Zeichenfolge größer als zwei zurückgegeben, und nicht die höchste für jede "Person". Dies ist eine Art unerwarteter Vorteil, aber das bedeutet, dass der Name einer Person wahrscheinlich mehrmals angezeigt wird als nur einmal mit dem höchsten Wert. Es wurden auch die temporären Tabellen zurückgegeben. Ich mache mir Sorgen, das bedeutet, dass alles im RAM bleibt und abstürzt, bevor das Schreiben in eine Datei abgeschlossen ist. Ich kann mir vorstellen, dass dies ein sehr langer Job sein wird. Teilen Sie also bitte alle Erkenntnisse mit, falls Sie diese derzeit haben. ansonsten werde ich posten, wenn und wenn dies erfolgreich ist. Zero Cool vor 7 Jahren 0
Egal Steve. Es dauerte nur 20 Minuten, um in eine Datei zu schreiben. Das sind Champagnerprobleme. RIESIG danke! Zero Cool vor 7 Jahren 0
Die Auswahl der temporären Tabelle dient Ihrem Nutzen. Kommentieren Sie sie einfach aus, wenn Sie sie nicht für die Fehlersuche benötigen Steve Mangiameli vor 7 Jahren 0

Verwandte Probleme