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

SQL: 
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,
    ...
 
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:

SQL: 
...
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:

SQL: 
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?
 
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.
Code: 
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
SQL: 
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
 
wenn NULL den festen wert "nA" bekommen soll?

hab einen NULL eintrag hinzugefügt, aber scheint mysql nicht zu berücksichtigen.
 
manchmal schieß ich zu schnell, sorry. habs mit case when im update command lösen können:

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

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

SQL: 
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?
 
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.


SQL: 
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")
 
Nochmal kurz zu der Datenkorrektur:
Das mit der "Legendentabelle" hat super funktioniert:

SQL: 
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?
 
Zuletzt bearbeitet:
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück