MySQL (MariaDB): Tabellen „pivotieren“ ohne UNION?

C

canju

Erfahrenes Mitglied
Hallo liebe Forum-Profis,

ich bin auf der Suche nach einer möglichst eleganten Lösung, um ein Performance Reporting zu erstellen, welches mir aus eeeinigen Tabellen (15+) die Werte auf Monatsbasis von Links nach rechts ausgibt. Das auf regelmäßiger Basis in Excel aufzubereiten ist extremst aufwändig bis kaum zeitlich machbar, daher suche ich jetzt eine MySQL-Lösung hierfür.

Bisher habe ich sämtliche (Unter-)Abfragen mit UNION untereinandergeschrieben und um das Vergleichsjahr zu berücksichtigen mit SQL-Variablen (@ReportingYear, @PreviousReportingYear) gearbeitet. Die Variablen können dann im Visualisierungstool so genutzt werden, dass diese auf die Nutzereingabe des gewünschten Jahres reagiert.

Ihr könnt euch sicherlich vorstellen, dass die Lösung mit UNION ein sehr langes Abfragen-Konstrukt ergibt. Ich versuche jetzt dieses Konstrukt "code"-mäßig einzukürzen.

Die Anforderungen wären:

  • MySQL only
  • Auswertung auf Monatsbasis (lesbar von links nach rechts)
  • Jahresvergleich.

Da das extrem viele Daten wären, um ein Beispiel bereitzustellen evtl. erstmal die Frage in der Theorie, ob ihr mir ggf. ein paar Tipps auf den Weg geben könnt, wie man das ohne UNION lösen könnte (Wenn das überhaupt geht).

Grüße,
Canju
 
Ich hatte mal sowas, was ich dann mit nem Haufen CASE WHEN-Klauseln gelöst habe.
War aber für DB2.
Müsste aber auch für MySQL gehen.
 
Ich würde bei der UNION bleiben. Aber das ganze in 2 Schritten.
1) Mittel seiner UNION View von allen Tabellen die relevanten Spalten auslesen. Nix weiter.
2) EIne Abfrage, welche die Daten dann zusammenrechnet. Da kommen dann die ganzen CASE WHEN etc. zum Zuge.
 
Erstmal danke für eure Antworten.

CASE WHEN erscheint mir genauso viel Zeilen (oder sogar noch mehr) zu erfordern als UNION.

Vielleicht ein anderer Angang der zwar nicht von UNION wegkommt, aber zumindest etwas übersichtlicher lesbar ist:

Sagen wir ich habe 20 Abfragen (die auch Unterabfragen enthalten), jede hat ca. 30-50+ Zeilen. Kann ich ganze Abfragen in MySQL in Variablen packen, sodass das MainSheet im prinzip nur die Variablen ausführt. Die einzelnen Abfragen müssten dann irgendwie / irgendwo gespeichert werden.

Aber so hätte ich wenigstens ein bisschen mehr übersicht indem ich dann nur Änderungen an der jeweiligen Einzelabfrage vornehmen kann, ohne das Mainsheet mit den gefühlt 10.000 Zeilen anfassen zu müssen.
 
Oder hier vielleicht doch mal ein Auszug:

SQL: 
SELECT   
    CONCAT('# Nutzer Gesamt') AS 'Metrik',
    EXTRACT(YEAR FROM u.created) AS 'Jahr',
   COUNT(u.id) AS 'total_user',
       (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-01-31') AND activated = 1) AS 'Jan',   
        (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-02-28') AS 'Feb',   
        (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-03-31') AS 'Mrz',   
        (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-04-30') AS 'Apr',
        (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-05-31') AS 'Mai',   
        (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-06-30') AS 'Jun',   
        (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-07-31') AS 'Jul',   
        (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-08-31') AS 'Aug',
        (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-09-30') AS 'Sept',
        (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-10-31')  AS 'Okt',
        (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-11-30')  AS 'Nov',
        (SELECT COUNT(id) FROM user WHERE 1 AND created <= CONCAT(@ReportingYear,'-12-31')  AS 'Dez'
FROM user AS u

Davon habe ich echt einen Haufen die ich UNION'n muss.
Da das Unterabfragen sind sicherlich auch nicht wirklich performant.

Seht ihr Kürzungspotenzial im code?
 
SQL: 
-- Abfrage erstellen, welche die Werte sauber vorbereit
CREATE VIEW vw_usercount_by_yearmonth
SELECT
    COUNT(id) AS cnt,
    MONTH(created) AS created_month,
    YEAR(created) AS created_year
FROM user
WHERE activated = 1
GROUP BY MONTH(created), YEAR(created);

-- Die Daten auswerten
SELECT
    u.created_year AS jahr
    SUM(u.cnt) AS total_user,
    SUM(CASE created_month WHEN 1 THEN cnt ELSE 0) AS jan,
    SUM(CASE created_month WHEN 2 THEN cnt ELSE 0) AS feb,
    SUM(CASE created_month WHEN 3 THEN cnt ELSE 0) AS mrz
    -- TODO: Weitere Monate Ausprogrammieren
FROM vw_usercount_by_yearmonth u
WHERE u.created_year = @ReportingYear
GROUP BY u.created_year;
 
Huh?
Sieht mir nach ner seltsamen Syntax aus
hätte ein
SQL: 
CASE WHEN IrgendeinFeld=1 THEN 1 ELSE 0 END
erwartet
 
ok, sorry auf die views bin ich gar nicht eingegangen.
scheint aber echt die beste lösung zu sein die "komplexen" abfragen zuvor in eine view zu packen und dann von dort aus auszuwerten. von der performance her um länge schneller als die ganzen unterabfragen.


@Zvoni:
Es fehlte nur das WHEN und END in den case when bedingungen.

Für alle die evtl. auf der gleichen Suche sind hier nochmal mit END:

SQL: 
-- Abfrage erstellen, welche die Werte sauber vorbereit
CREATE VIEW vw_usercount_by_yearmonth
SELECT
    COUNT(id) AS cnt,
    MONTH(created) AS created_month,
    YEAR(created) AS created_year
FROM user
WHERE activated = 1
GROUP BY MONTH(created), YEAR(created);

-- Die Daten auswerten
SELECT
    u.created_year AS jahr
    SUM(u.cnt) AS total_user,
    SUM(CASE created_month WHEN 1 THEN cnt ELSE 0 END) AS jan,
    SUM(CASE created_month WHEN 2 THEN cnt ELSE 0 END) AS feb,
    SUM(CASE created_month WHEN 3 THEN cnt ELSE 0 END) AS mrz
    -- TODO: Weitere Monate Ausprogrammieren
FROM vw_usercount_by_yearmonth u
WHERE u.created_year = @ReportingYear
GROUP BY u.created_year;


Das gleiche etwas besser lesbar ist dann:

SQL: 
SELECT
    u.created_year AS 'jahr',
    SUM(CASE WHEN created_month = 1 THEN cnt ELSE 0 END) AS jan,
    SUM(CASE WHEN created_month = 2 THEN cnt ELSE 0 END) AS feb,
    SUM(CASE WHEN created_month = 3 THEN cnt ELSE 0 END) AS mrz,
    SUM(CASE WHEN created_month = 4 THEN cnt ELSE 0 END) AS apr,
    SUM(CASE WHEN created_month = 5 THEN cnt ELSE 0 END) AS mai,
    SUM(CASE WHEN created_month = 6 THEN cnt ELSE 0 END) AS jun,
    SUM(CASE WHEN created_month = 7 THEN cnt ELSE 0 END) AS jul,
    SUM(CASE WHEN created_month = 8 THEN cnt ELSE 0 END) AS aug,
    SUM(CASE WHEN created_month = 9 THEN cnt ELSE 0 END) AS sept,
    SUM(CASE WHEN created_month = 10 THEN cnt ELSE 0 END) AS okt,
    SUM(CASE WHEN created_month = 11 THEN cnt ELSE 0 END) AS nov,
    SUM(CASE WHEN created_month = 12 THEN cnt ELSE 0 END) AS dez
FROM vw_usercount_by_yearmonth u
WHERE u.created_year = @ReportingYear
GROUP BY u.created_year
 
Zuletzt bearbeitet:
Stimmt, das END fehlte. Habe schon viel zu lange keine kpmplexe SQL mehr programmiert.
 
stehe auf dem schlauch. die abfrage oben gibt mir einwandfrei die nutzer hinzu die sich im jeweiligen monat registriert haben.

Am beispiel januar 2020:
ich brauche darüber die zeile der gesamten nutzer bis einschließlich Januar 2020

Sprich:
Nutzer gesamt bis Jan 20: 10.000 <-- ?
Nutzer NEU im Jan 20: 100 <-- check

ich vermute die view nochma anpassen?
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück