Benutzer:Rolf b/unconf

Script für Unconference. Wer spickt, ist selber schuld ;-)

== Ablauf

• Tabellen vorstellen
• GROUP BY -> WITH ROLLUP
• Schöner mit IFNULL
• Hinweis auf GROUPING() in MySQL 9.6 Heatwave (Cloud) und GROUPING SET bei anderen SQL Systemen
• Window-Funktionen: ROW_NUMBER
• WHERE auf ROW_NUMBER mit SELECT als Table Expression
• Übergang zu CTE
• Sequences?
• JSON Queries?

Überblick Personen

 SELECT serie, COUNT(*)
 FROM personen
 GROUP BY serie

Auf Ebene Staffel

 SELECT serie, erste_staffel, COUNT(*)
 FROM personen
 GROUP BY serie, erste_staffel

Rollup

Wie bilde ich Totalzeilen pro Serie?

   SELECT serie, erste_staffel, COUNT(*)
   FROM personen
   GROUP BY serie, erste_staffel
 UNION ALL
   SELECT serie, 'Alle Staffeln', COUNT(*)
   FROM personen
   GROUP BY serie
 UNION ALL
   SELECT 'Alle Serien', 'Alle Staffeln', COUNT(*)
   FROM personen
 ORDER BY serie, erste_staffel

Mit Summierzeilen

 WITH ROLLUP

Achtung: ROLLUP und ORDER BY sind nicht gleichzeitig möglich

Bessere Lesbarkeit

 SELECT IFNULL(serie, '── Gesamt ──')       AS Serie,
        IFNULL(erste_staffel, '── alle ──') AS Staffel,
        COUNT(*)                            AS Anzahl
 FROM personen
 GROUP BY serie, erste_staffel
 WITH ROLLUP

Letzte Detailstufe: Wer kommt hinzu

 SELECT IFNULL(serie, '── Gesamt ──')       AS Serie
        IFNULL(erste_staffel, '── alle ──') AS Staffel
        IFNULL(rollenname, '── diese Staffel ──')    AS Figur
        COUNT(*)                            AS Anzahl
 FROM personen
 GROUP BY serie, erste_staffel, rollenname WITH ROLLUP

Hinweis auf GROUPING() in MySQL 9.6 Heatwave (Cloud) und GROUPING SET bei anderen SQL Systemen

Ranking

 SELECT p.name, p.vorname, p.geburtsdatum, s.titel AS Serie, s.start AS Serienstart
 FROM personen p JOIN serien s ON p.serie=s.ident
 ORDER BY s.start DESC, p.geburtsdatum;

Window-Funktion ROW_NUMBER für die Nummerierung nach Geburtsdatum

 SELECT p.name, p.vorname, p.geburtsdatum, s.titel AS Serie, s.start AS Serienstart,
       ROW_NUMBER() OVER (ORDER BY geburtsdatum) AS Nr
 FROM personen p JOIN serien s ON p.serie=s.ident

Mit Nummer pro Serie

 SELECT p.name, p.vorname, p.geburtsdatum, s.titel AS Serie, s.start AS Serienstart,
       ROW_NUMBER() OVER (PARTITION BY serie ORDER BY geburtsdatum) AS Nr
 FROM personen p JOIN serien s ON p.serie=s.ident

Eingrenzen auf die ältesten drei pro Serie

So nicht:

 SELECT p.*, s.start,
       ROW_NUMBER() OVER (PARTITION BY serie ORDER BY geburtsdatum) AS Nr
 FROM personen p JOIN serien s ON p.serie=s.ident
 WHERE Nr < 4
 ORDER BY start desc, geburtsdatum;

So geht es (unschön)

 SELECT * 
 FROM (SELECT p.*, s.start,
              ROW_NUMBER() OVER (
                PARTITION BY serie 
                ORDER BY geburtsdatum) AS Nr
       FROM personen p 
            JOIN serien s ON p.serie=s.ident) x
 WHERE Nr < 4
 ORDER BY start desc, geburtsdatum;

Andere Window-Funktionen: LAG, LEAD, NTH_VALUE

Startrek-Personenliste, nächstjüngerer Darsteller pro Serie

SELECT serie, NAME, vorname, rollenname, geburtsdatum,

      LEAD(geburtsdatum, 1) OVER (PARTITION BY serie ORDER BY geburtsdatum ) AS ny_birth,
      LEAD(concat(NAME, ',', vorname), 1) OVER (PARTITION BY serie ORDER BY geburtsdatum ) AS ny_name

FROM personen ORDER BY serie, geburtsdatum

Nächstälterer mit LAG

Drittältester Darsteller pro Serie

SELECT serie, NAME, vorname, rollenname, geburtsdatum,

      NTH_VALUE(geburtsdatum, 3) OVER (PARTITION BY serie ORDER BY geburtsdatum ) AS ny_birth,
      NTH_VALUE(concat(NAME, ',', vorname), 3) OVER (PARTITION BY serie ORDER BY geburtsdatum ) AS ny_name

FROM personen ORDER BY serie, geburtsdatum

Nanu?

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Rows/Range!

CTE

 WITH darsteller AS (
    SELECT p.name, p.vorname, p.geburtsdatum, s.titel AS Serie, s.start AS Serienstart,
       ROW_NUMBER() OVER (PARTITION BY p.serie ORDER BY geburtsdatum) AS Nr
    FROM personen p JOIN serien s ON p.serie=s.ident
 )
 SELECT * from darsteller
 WHERE Nr < 4
 ORDER BY Serienstart DESC, geburtsdatum;

Anderes Beispiel: clerk/sales, wertvollster Abschluss mit Datum, Betrag und Kundennummer

 WITH maxAmount AS (
     SELECT clerk.id, clerk.name, sales.amount, sales.sale_date, sales.customer_id,
            RANK() OVER V(PARTITION by clerk.id ORDER BY sales.amount DESC) AS Rang
     FROM clerk LEFT JOIN sales ON clerk.id = sales.clerk_id 
 )
 SELECT *
 FROM maxAmount
 WHERE Rang = 1;

CTE rekursiv

 Ordner-Tabelle: Rekursive Schachtelung
   id         INT
   parent_id  INT
   name       VARCHAR

 Rekursives Auflisten mit stumpfen JOINs: 

 SELECT id, CONCAT('/', `Name`) AS pfad
 FROM Ordner o1 WHERE o1.parent_id IS NULL
 UNION ALL
 SELECT o2.id, CONCAT('/', o1.`Name`, '/', o2.`Name`)
 FROM Ordner o2 
 JOIN Ordner o1 ON o2.parent_id = o1.id AND o1.parent_id IS NULL
 UNION ALL
 SELECT o3.id, CONCAT('/', o1.`Name`, '/', o2.`Name`, '/', o3.`Name`) AS pfad
 FROM Ordner o3
 JOIN Ordner o2 ON o3.parent_id = o2.id
 JOIN Ordner o1 ON o2.parent_id = o1.id AND o1.parent_id IS NULL
 ORDER BY pfad

Mit rekursiver CTE

 WITH RECURSIVE tree AS (
   SELECT id,
          CONCAT('/', `Name`) AS pfad
   FROM Ordner WHERE parent_id IS NULL
 UNION ALL
   SELECT o.id,
          CONCAT(t.pfad, '/', o.`Name`) AS pfad
   FROM Ordner o
   JOIN tree t ON o.parent_id = t.id
 )
 SELECT id, pfad
 FROM tree
 ORDER BY pfad

Sequences

Generalisiert Auto-Increment

 CREATE SEQUENCE person_seq START WITH 1 INCREMENT BY 1

In phpmyadmin unter Views (Ansichten) zu finden. Abrufen mit NEXT VALUE FOR person_seq.

 SELECT NEXT VALUE FOR person_seq

Sequence kann als Spaltendefault mit NEXTVAL(person_seq) gesetzt werden

Cache_Size: Wieviele Nummern werden fortlaufend im Server vergeben, bevor auf den Datenspeicher hinter der Sequence zugegriffen werden muss. Dieser Zugriff muss exklusiv sein und kann einen Flaschenhals darstellen. Deshalb der Cache

Der Cache gilt für alle Connections zum Server.

 SELECT * FROM person_seq

Sequencenummern sind fortlaufend solange der Server nicht neu gestartet wird. Bei Neustart wird mit "NEXT_NOT_CACHED_VALUE" begonnen. Schwierigkeiten kann es nur bei Serverclustern geben. Die generierten Werte sind immer noch eindeutig, aber nicht mehr unbedingt monoton, je nach Cluster. Galera: nicht monoton. MaxScale: Monoton solange man auf dem gleichen Primary bleibt, aber ggf. nicht fortlaufend.

Letzten Wert abfragen mit LASTVAL(person_seq) - NUR in der gleichen Connection!

Round-Robin Sequenzen gibt es auch, diese sind bei Clustern riskant.

 CREATE SEQUENCE round_robin_10 START WITH 1 INCREMENT BY 1 MAXVALUE 10 CYCLE

JSON-Spalten

MySQL hat speziellen Columntyp JSON, MariaDB nicht. LONGTEXT verwenden.

Demo-Table mit einer Sequence zur ID-Vergabe (beachte: nextval statt NEXT VALUE OF)

 CREATE TABLE `json_demo` (
   `Id` int(11) NOT NULL DEFAULT nextval(`person_seq`),
   `Info` varchar(64) NOT NULL,
   `Json` longtext NOT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Daten schick ausgeben

 SELECT Info, JSON_PRETTY(json)
 FROM json_demo

Namen und Aktivitäten auflisten

 SELECT Info, JSON_EXTRACT(json, "$.name") as Name, JSON_EXTRACT(json, "$.aktivitäten") as Aktivität
 FROM json_demo

Einführung JSON_TABLE

 SELECT *
 FROM JSON_TABLE(
     '{"name":"Selfhtml","plz":"44147",
       "vorsitzender":"Matthias Scharwies","2. vorsitzender":"Rolf Borchmann","kassierer":"Julius Cordes",
       "aktivitäten":[ "Forum","Wiki","Testwiki","Mastodon","Meetings","Stammtisch"]}',
     "$.aktivitäten[*]" 
       COLUMNS (
         Nr FOR ORDINALITY,
         Aktivität VARCHAR(99) PATH "$"
       )
     ) jt;

Join mit JSON_TABLE

 SELECT Info
      , JSON_EXTRACT(json, "$.name") as Name
      , jt.aktivität
 FROM json_demo
   JOIN JSON_TABLE(json, "$.aktivitäten[*]" COLUMNS (aktivität varchar(99) path "$")) jt;