SQL – Zählen und Zusammenfassen (in Bereichen) die Anzahl der Instanzen einer gegebenen Transaktion

Ich habe eine Vorkommenstabelle, die eine Zeile für jedes Mal enthält, wenn ein Benutzer eine Aktion erledigt hat. Ein Benutzer nimmt mehrmals pro Tag eine Aktion ein. Es sieht aus wie das:

Date Username ------ -------- 1/1/9 User1 1/1/9 User1 1/1/9 User2 1/2/9 User1 1/2/9 User3 1/3/9 User1 1/3/9 User1 1/3/9 User1 1/3/9 User2 1/3/9 User3 1/4/9 User1 1/5/9 User1 1/6/9 User1 1/7/9 User1 

Für jeden Tag im Sortiment möchte ich die Anzahl der Leute zeigen, die die Aktion mehrmals zwischen zwei und 5 Mal, zwischen 6 und 10 Mal, und mehr als 10 Mal in die Hand genommen haben. Allerdings betrachte ich nur die Aktion an einem anderen Tag, um als eine andere Instanz dieser Handlung zu zählen. Zum Beispiel, wenn ein Benutzer das Ding 3 Mal am ersten Tag und dann wieder irgendwelche Male am nächsten Tag, ich sehe, dass Benutzer hat die Aktion 2 mal getan und sollte daher in der 2-bis-5-mal sein Säule.

Die Ergebnismenge, die den obigen Beispieldaten entspricht, wäre:

  #_of_people #_of_people #_of_people who_did_action who_did_action who_did_action Date 2to5_times 6to10_times more_than_10 Total ----- -------------- -------------- -------------- ----- 1/1/9 0 0 0 0 1/2/9 1 0 0 1 1/3/9 3 0 0 3 ... 1/7/9 0 1 0 1 

Beachten Sie, dass jede Zeile des Ergebnisses die Anzahl der Wiederholungsaktionen für diesen bestimmten Tag zählt – nicht kumulativ.

  • Die 1/1/9 Zeile ist alle Nullen, da es der erste Tag ist und alle Aktionen als die ersten gelten.
  • Die 1/2/9 Zeile ist 1, 0, 0, 1 weil nur User1 wiederholt hat – es ist User3's erstes Mal.
  • Die Zeile 1/3/9 ist 3, 0, 0, 3, da User1 sich zweimal wiederholt hat, hat User2 einmal wiederholt und User3 hat sich auch einmal wiederholt.
  • Die Zeile 1/7/9 ist 0, 1, 0, 1, weil User1 6 mal wiederholt hat.

Am besten konnte ich kommen, ungetestet:

 DECLARE @username VARCHAR(40) DECLARE @date DATETIME DECLARE @counter INT SET @counter = 1 WITH occur AS ( SELECT DISTINCT CONVERT(VARCHAR(10), o.date, 101) AS dt, o.username FROM OCCURRENCES o ORDER BY o.username, dt), occur_rank AS ( SELECT x.dt, @username = x.username, @date = CAST(x.dt AS DATETIME), CASE WHEN @username = x.username AND @date + 1 = x.date THEN @counter = @counter + 1 ELSE @counter = 1 END AS rank FROM occur x ORDER BY x.username, x.dt) SELECT or.dt, SUM(CASE WHEN or.rank BETWEEN 2 AND 5 THEN 1 ELSE 0 END) AS 2_to_5, SUM(CASE WHEN or.rank BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS 6_to_10 FROM occur_rank or GROUP BY or.dt 

Wenn du die Nummerierung auf eine dateslücke zurücksetzen kannst, ist der Rest einfach. Aber keiner der SQL server-Ranking-functionen unterstützt das . So verlässt man manuell eine Variable.

Der erste CTE, occur einfach zurück eine list der Benutzernamen mit data verbunden. Der zweite CTE baut darauf auf und fügt das Ranking für die endgültige Ausgabe hinzu.

Ich habe eine abgeleitete Tabelle mit einer korrelierten Unterabfrage verwendet.

Die korrelierte Unterabfrage führt zu einer Spalte ActionCount, die die Anzahl der verschiedenen data bis zu dem date der Zeile für den Benutzernamen dieser Zeile zählt.

Die resultierende abgeleitete Tabelle listet jedes date, Benutzername und ActionCount auf.

Die äußere Abfrage gruppiert dann die Ergebnisse nach date und zählt die Anzahl der Aktionszählungen zwischen 2 und 5 zwischen 6 und 10, größer als 10 und für die Summe größer als 1 (eine ActionCount gleich 1 ist keine "Wiederholung" ).

 select [Date], sum(case when [ActionCount] between 2 and 5 then 1 else 0 end) [#_of_people_who_did_action_2to5_times], sum(case when [ActionCount] between 6 and 10 then 1 else 0 end) [#_of_people_who_did_action_6to10_times], sum(case when [ActionCount] > 10 then 1 else 0 end) [#_of_people_who_did_action_more_than_10], sum(case when [ActionCount] > 1 then 1 else 0 end) [Total] from ( select distinct dt.[Date], dt.[Username], (select count(distinct [Date]) from OCCURRENCES cd where cd.[Username] = dt.[Username] and cd.[Date] <= dt.[Date]) [ActionCount] from OCCURRENCES dt ) a group by [Date]