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



## canju (8. Juni 2020)

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


----------



## Zvoni (8. Juni 2020)

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.


----------



## Yaslaw (8. Juni 2020)

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.


----------



## canju (8. Juni 2020)

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.


----------



## canju (8. Juni 2020)

Oder hier vielleicht doch mal ein Auszug:


```
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?


----------



## Yaslaw (8. Juni 2020)

```
-- 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;
```


----------



## Zvoni (9. Juni 2020)

Huh?
Sieht mir nach ner seltsamen Syntax aus
hätte ein

```
CASE WHEN IrgendeinFeld=1 THEN 1 ELSE 0 END
```
 erwartet


----------



## canju (9. Juni 2020)

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`:


```
-- 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:


```
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
```


----------



## Yaslaw (9. Juni 2020)

Stimmt, das END fehlte. Habe schon viel zu lange keine kpmplexe SQL mehr programmiert.


----------



## canju (9. Juni 2020)

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?


----------



## Yaslaw (9. Juni 2020)

```
SELECT
    u.created_year AS 'jahr',
    (SELECT COUNT(id) FROM user WHERE YEAR(created) < @ReportingYear) AS last_year,
    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,
    ...
```


----------



## canju (9. Juni 2020)

ok, danke dir. das mutze ich dann um die Jahressumme anzuzeigen.
um den jeweiligen stand zum stichtag des monats zu bekommen, habe ich die view angepasst und werte die summen dann so aus:


```
...
SUM(CASE WHEN yearmonth <= CONCAT(@ReportingYear,'-01') THEN cnt ELSE 0 END) AS 'Jan',
...
```

Ich habe noch eine andere Frage:

Es geht darum die utm_source, die wir bei registrierung eines nutzers abspeichen zu normalisieren und in ein neues Feld `utm_source_norm` zu speichern (soll dann via cron regelmäßig gemacht werden).
leider wird die source nicht immer einheitlich vergeben, sodass ich wiederum mit case when die entsprechenden werte zusammenfasse.

Beispiel:


```
WHEN     u.utm_source LIKE '%facebook%' OR
    u.utm_source = 'fbbeitrag' OR
    u.utm_source = 'FB' OR
    u.utm_source = 'FB2' OR
    u.utm_source = 'FB1' OR
    u.utm_source LIKE 'fbbeitrag'
THEN 'facebook'
```

Davon gibt es auch wieder einen Haufen...

Da wir dieses "Problem" in mehreren Entitäten haben, würde ich gerne eine Tabelle anlegen, die sozusagen als zentrale Legende der zu normalisieren Werte dient, damit ich das lange CASE WHEN Konstrukt nicht in jede Abfrage mit einbauen muss.
Bin mir aber nicht sicher welchen Spaltenaufbau ich nutzen soll und wie ich den update befehl dann am besten schreibe. Vor allem da auch unterschiedliche operatoren notwendig sind (`=` / `LIKE`).

Darf ich nochmal um Tipps / Beispiele betteln?


----------



## Yaslaw (9. Juni 2020)

Unbedingt in eine eigene Tabelle. LIKE reicht, LIKE ohne % verhält sich wie = (ich nehme an, dass die Performance ein bischen schlechter ist)
Solche Datenkorrekturen gehören nie direkt in das SQL rein. 

```
TBL_MAP_UTM_SOURCE
ID | FINAL_UTM_SOURCE | UTM_SOURCE 
----------------------------------
 1 | facebook         | %facebook% 
 2 | facebook         | fbbeitrag  
 3 | facebook         | FB         
 4 | facebook         | FB2        
 5 | facebook         | FB1        
 6 | facebook         | fbbeitrag
```


```
select 
    u.*,
    (SELECT MAX(m.final_utm_source) FROM tbl_map_utm_source m WHERE u.utm_source LIKE m.utm_source) AS final_utm_source
from 
    user u
```


----------



## canju (9. Juni 2020)

top  danke dir.


----------



## canju (9. Juni 2020)

wenn `NULL` den festen wert "nA" bekommen soll?

hab einen NULL eintrag hinzugefügt, aber scheint mysql nicht zu berücksichtigen.


----------



## canju (9. Juni 2020)

manchmal schieß ich zu schnell, sorry. habs mit case when im update command lösen können:


```
UPDATE user AS u
    SET utm_source_norm =
                        CASE
                            WHEN u.utm_source IS NOT NULL    THEN (SELECT m.utm_source_norm FROM int_map_utm_source AS m WHERE u.utm_source LIKE m.utm_source)
                            WHEN u.utm_source IS NULL THEN 'nA'
                            ELSE 0
                        END
```


----------



## canju (10. Juni 2020)

Hey @Yaslaw 

Hab nochmal eine Frage zu den Views.

Ich aber relativ viele Datenquellen (bereits gebündelt in einer DB, aber mit separaten Tabellen) aus denen ich meist die gleichen Metriken auswerten und wie oben geschrieben "pivotieren" muss.

Würdest du tatsächlich für jede Datenquelle eine View anlegen oder würdest du die Datenquellen, dessen Metriken "gleich" auszuwerten sind in eine View packen?

Als Beispiel mal zwei Views die ich erstellt habe:


```
CREATE VIEW vw_tba_spendings_yearmonth AS
SELECT
    DATE_FORMAT(t.reporting_start_date, "%Y-%m") AS 'yearmonth',
    MONTH(t.reporting_start_date) AS created_month,
    YEAR(t.reporting_start_date) AS created_year,
    SUM(t.spent) AS 'spent_usd',
    SUM(t.clicks) AS 'clicks',
    SUM(t.impressions) AS 'impressions'

FROM tba_day AS t
GROUP BY MONTH(t.reporting_start_date), YEAR(t.reporting_start_date), DATE_FORMAT(t.reporting_start_date, "%Y-%m")
```

und


```
CREATE VIEW vw_obn_spendings_yearmonth AS
SELECT
    DATE_FORMAT(obn.reporting_start_date, "%Y-%m") AS 'yearmonth',
    MONTH(obn.reporting_start_date) AS created_month,
    YEAR(obn.reporting_start_date) AS created_year,
    SUM(obn.spend) AS 'spent',
    SUM(obn.clicks) AS 'clicks',
    SUM(obn.impressions) AS 'impressions'

FROM obn_report AS obn
GROUP BY MONTH(obn.reporting_start_date), YEAR(obn.reporting_start_date), DATE_FORMAT(obn.reporting_start_date, "%Y-%m")
```

Könnte ich überhaupt beide in einer View vereinen, bspw. in `vw_total_spendings_yearmonth`? Beide Datenquellen haben keinen gemeinsamen identifier. Sodass man vielleicht die View um eine Spalte mit dem Namen der Datenquelle (`tba` / `obn`) erweitert und dann irgendiwe inserted?


----------



## Yaslaw (10. Juni 2020)

Ich würde eine UNION-Abfrgae schreiben, die nur die Daten zusammensammelt die du nachher brauchst. Also nur die Notwendigen Felder plus ein Feld 'SOURCE' in der dann tba oder obn etc. steht.
Diese ist dann die Quelle die vw_spendings_yearmonth.



```
CREATE OR REPLACE vw_all_data AS
select 
    tba.reporting_start_date AS start_date,
    tba.spent AS spent,
    tba.clicks AS clicks,
    tba.impressions AS impressions
    'tba' AS source
FROM tba_day tba
UNION select 
    obn.reporting_start_date AS start_date,
    obn.spent AS spent,
    obn.clicks AS clicks,
    tba.impressions AS impressions
    'obn' AS source
FROM obn_report obn;

SELECT
    DATE_FORMAT(all.reporting_start_date, "%Y-%m") AS 'yearmonth',
    MONTH(all.reporting_start_date) AS created_month,
    YEAR(all.reporting_start_date) AS created_year,
    SUM(all.spend) AS 'spent',
    SUM(all.clicks) AS 'clicks',
    SUM(all.impressions) AS 'impressions'
FROM vw_all_data all
GROUP BY MONTH(all.reporting_start_date), YEAR(all.reporting_start_date), DATE_FORMAT(all.reporting_start_date, "%Y-%m")
```


----------



## canju (10. Juni 2020)

Top, vielen lieben Dank.
Das macht es um einiges angenehmer bei der Auswertung.


----------



## canju (11. Juni 2020)

Nochmal kurz zu der Datenkorrektur:
Das mit der "Legendentabelle" hat super funktioniert:


```
UPDATE user AS u
    SET u.utm_source_norm =
                CASE
                    WHEN u.utm_source IS NOT NULL    THEN (SELECT MAX(m.utm_source_norm) FROM int_map_utm_source AS m WHERE u.utm_source LIKE m.utm_source)
                    WHEN u.utm_source IS NULL THEN 'nA'
                    WHEN u.utm_source = '' THEN 'nA'
                    ELSE u.utm_source
                END
    WHERE u.utm_source_norm IS NULL;
```


Ich möchte jetzt, dass wenn die Eintrage in int_map_utm_source nicht mappen, einfach die nicht normalisierte Source übernommen wird.
Aber der ELSE Zweig `... ELSE u.utm_source ...` wird nicht berücksichtigt. Hast du eine Idee?


----------



## Yaslaw (11. Juni 2020)

Nutze den Rückgabewert von MAX(), wenn keine Daten vorhanden sind


> MIN() und MAX() geben NULL zurück, wenn keine passenden Datensätze vorhanden waren


Also kann man das Resultat noch mit IFNULL() auswerten

```
...
WHEN u.utm_source IS NOT NULL    
THEN 
    IFNULL(
        (
            SELECT MAX(m.utm_source_norm) 
            FROM int_map_utm_source AS m 
            WHERE u.utm_source LIKE m.utm_source
        ),
        u.utm_source
    )
WHEN u.utm_source IS NULL 
THEN 'nA'
...
```


----------



## canju (11. Juni 2020)

funzt .. da wäre ich glaube nicht von alleine drauf gekommen.. so langsam wird es.


----------

