Laufende Häufigkeitsverteilung aus den vorherigen N Zeilen der MySQL-Datenbank abrufen

Question

Ich habe eine MySQL-Datenbank, in der eine Spalte Statuscodes enthält. Die Spalte ist vom Typ int und die Werte werden immer nur 100, 200, 300, 400 sein. Es sieht wie unten aus; andere Spalten wurden aus Gründen der Übersichtlichkeit entfernt.

id   |  status
----------------
 1      300
 2      100
 3      100
 4      200
 5      300
 6      300
 7      100
 8      400
 9      200
10      300
11      100
12      400
13      400
14      400
15      300
16      300

Das Feld id wird automatisch generiert und ist immer fortlaufend. Ich möchte eine dritte Spalte haben, die eine kommagetrennte Zeichenfolge der Häufigkeitsverteilung der Statuscodes der vorherigen 10 Zeilen anzeigt. Sie sollte wie folgt aussehen.

id   |  status  |  freq
-----------------------------------
 1      300
 2      100
 3      100
 4      200
 5      200
 6      300
 7      100
 8      400
 9      300
10      300
11      100       300,100,200,400    -- from rows 1-10
12      400       100,300,200,400    -- from rows 2-11
13      400       100,300,200,400    -- from rows 3-12
14      400       300,400,100,200    -- from rows 4-13
15      300       400,300,100,200    -- from rows 5-14
16      300       300,400,100        -- from rows 6-15

Ich möchte, dass der häufigste Code zuerst aufgeführt wird. Und wenn zwei Statuscodes gleich häufig vorkommen, ist es für mich egal, welcher zuerst aufgeführt wird, aber im Beispiel habe ich den kleineren Code vor dem größeren aufgeführt. Schließlich sollte ein Code, der in den vorangegangenen zehn Zeilen überhaupt nicht vorkommt, auch nicht in der Spalte freq aufgeführt werden.

Die Zeilennummer, in der die Häufigkeitszeichenfolge erscheint, muss eindeutig sein NICHT den Statuscode dieser Zeile berücksichtigen; es sind nur die vorherigen Zeilen.

Was habe ich also getan? Ich bin ziemlich unerfahren mit SQL. Ich bin Programmierer und finde diese SQL-Sprache ein wenig gewöhnungsbedürftig. Ich habe die folgende Self-Join-Select-Anweisung erstellt.

select *, avg(b.status) freq
from sample a
join sample b
on (b.id < a.id) and (b.id > a.id - 11)
where a.id > 10
group by a.id;

Mit Hilfe der Aggregatfunktion avg kann ich das Konzept zumindest demonstrieren. Die abgeleitete Tabelle b liefert die richtigen Zeilen für die Funktion avg, aber ich komme einfach nicht aus dem mehrstufigen Prozess des Zählens und Gruppierens von Zeilen aus b heraus, um eine Häufigkeitsverteilung zu erhalten und dann die Häufigkeitszeilen zu einem einzigen Zeichenfolgenwert zusammenzufassen.

Ich habe auch versucht, standardmäßig gespeicherte Funktionen und Prozeduren anstelle der integrierten Aggregatfunktionen zu verwenden, aber es scheint, dass die abgeleitete Tabelle b außerhalb des Anwendungsbereichs liegt oder so. Ich kann anscheinend nicht darauf zugreifen. Und soweit ich weiß, ist das Schreiben einer benutzerdefinierten Aggregatfunktion für mich nicht möglich, da es anscheinend die Entwicklung in C erfordert, etwas, wofür ich nicht ausgebildet bin.

Hier ist sql, um das Beispiel zu laden.

create table sample (
    id int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    status int
);

insert into sample(status) values(300),(100),(100),(200),(200),(300)
  ,(100),(400),(300),(300),(100),(400),(400),(400),(300),(300),(300)
  ,(100),(400),(100),(100),(200),(500),(300),(100),(400),(200),(100)
  ,(500),(300);

Das Beispiel hat 30 Datenzeilen, mit denen man arbeiten kann. Ich weiß, es ist eine lange Frage, aber ich wollte so detailliert wie möglich sein. Ich arbeite schon seit ein paar Tagen daran und würde es wirklich gerne fertig bekommen.

Vielen Dank für Ihre Hilfe.

Answer 1

SELECT id, GROUP_CONCAT(status ORDER BY freq desc) FROM
    (SELECT a.id as id, b.status, COUNT(*) as freq
    FROM 
        sample a
    JOIN 
        sample b ON (b.id < a.id) AND (b.id > a.id - 11)
    WHERE 
        a.id > 10
    GROUP BY a.id, b.status) AS sub
GROUP BY id;

SQL-Gefiedel

Answer 2

Der einzige Weg, den ich kenne, um das zu tun, worum Sie bitten, ist die Verwendung einer BEFORE INSERT Auslöser. Es muss sein BEFORE INSERT weil Sie einen Wert in der eingefügten Zeile aktualisieren wollen, was nur in einer BEFORE Auslöser. Leider bedeutet das auch, dass ihm noch keine ID zugewiesen wurde, so dass Sie hoffentlich davon ausgehen können, dass zum Zeitpunkt des Einfügens eines neuen Datensatzes die letzten 10 Datensätze in der Tabelle diejenigen sind, an denen Sie interessiert sind. Ihr Trigger muss die Werte der letzten 10 IDs abrufen und die GROUP_CONCAT Funktion, um sie zu einer einzigen Zeichenkette zusammenzufügen, geordnet nach dem COUNT . Ich habe hauptsächlich SQL Server verwendet und habe im Moment keinen Zugang zu einem MySQL-Server, um dies zu testen, aber ich hoffe, dass meine Syntax nahe genug ist, um Sie zumindest in die richtige Richtung zu bringen:

create trigger sample_trigger BEFORE INSERT ON sample 
FOR EACH ROW
BEGIN
    DECLARE _freq varchar(50);

    SELECT GROUP_CONCAT(tbl.status ORDER BY tbl.Occurrences) INTO _freq
    FROM (SELECT status, COUNT(*) AS Occurrences, 1 AS grp FROM sample ORDER BY id DESC LIMIT 10) AS tbl
    GROUP BY tbl.grp

    SET new.freq = _freq;
END