Konsolidieren mit SQL

Einlesen der konsolidierten Daten

Aus dem XML-Export wurden eine Reihe von SQL-Insert-Anweisungen generiert, die nun eine verbesserte Struktur (im Vergleich zu den Rohdaten) aufweist. Damit soll nun weiter gearbeitet werden.

Aufgabe

Bitte die folgenden Realationen anlegen und danach die Daten importieren.

-- -----------------------------------------------------
-- Schema public
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table `leika`
-- -----------------------------------------------------
DROP TABLE IF EXISTS leika;

CREATE TABLE leika (
  schluessel bigint NOT NULL PRIMARY KEY,
  gruppierung VARCHAR(161) NULL,
  kennung VARCHAR(162) NULL,
  verrichtung VARCHAR(163) NULL,
  verrichtungsdetail VARCHAR(164) NULL,
  bezeichnung VARCHAR(250) NULL,
  bezeichnung2 VARCHAR(250) NULL,
  typ VARCHAR(10) NULL,
  datum VARCHAR(167) NULL,
  merkmale VARCHAR(168) NULL,
  synonyme VARCHAR(169) NULL);

-- -----------------------------------------------------
-- Table leikatyp
-- -----------------------------------------------------
DROP TABLE IF EXISTS leikatyp;

CREATE TABLE leikatyp (
  id bigint NOT NULL,
  typ VARCHAR(165) NULL);


-- -----------------------------------------------------
-- Table leikasynonyme
-- -----------------------------------------------------
DROP TABLE leikasynonyme ;

CREATE TABLE IF NOT EXISTS leikasynonyme (
  id bigint NOT NULL,
  synonym VARCHAR(165) NOT NULL);

Tabellenstruktur

Aktuell existieren folgende Tabellen:

\dt
       List of relations
 Schema |      Name      | Type  |  Owner
-------------------------------+----------
 public | leika          | table | postgres
 public | leikasynonyme  | table | postgres
 public | leikatyp       | table | postgres
 public | leikarow       | table | postgres
 (4 rows)

Abfragen (Fragenkatalog)

Bemerkung

Beantworten Sie die folgenden Fragen.

Hinweis: Nur klicken hat keinen Lerneffekt, diese Fähigkeit ist »Lieutenant Commander Data« aus dem Film »Star Trek« vorbehalten!

Also lesen Sie die Frage und versuchen Sie selbst eine Lösung bzw. einen Lösungsansatz zu finden.

Sie haben immer die Option einen Hinweis einzublenden und die angebotene Lösungsvariante mit der Eigenen zu vergleichen.

Viel Spaß beim Knobeln…

Anzahl Datensätze?

Frage: Wieviele Datensätze befinden sich in den einzelnen Tabellen?

Hinweis:
Verwende die Funktion »count«...
Lösung:

Tabelle

Anzahl Datensätze

leika

6167

leikasynonyme

18629

leikatyp

14409

leikarow

6176

Doppelte Datensätze filtern?

In den Rohdaten sind viele Datensätze doppelt vorhanden, weil sie bei der Konvertierung nicht gefiltert worden sind.

Frage: Wie bekomme ich zu jedem Synonym bzw. Typ nur einen Datensatz angezeigt?

Hinweis:
Verwende das Schlüsselwort: DISTINCT
Lösung: 
select distinct id, typ from leikatyp;
select distinct id, synonym from leikasynonym;

Doppelte Einträge

Frage: Wie kann ich doppelte Einträge löschen?

Hinweis zeigen:
Nicht jede Lösung im Netz ist korrekt und muss genau bewertet und angepasst werden! Der folgende Vorschlag ist ein gutes Beispiel dafür.
Lösungsansatz: siehe Vorschlag auf Website Riptutorial
HINWEIS: Da steckt noch ein Fehler im Lösungsansatz! Finde den Fehler!
Lösung zeigen: 
DELETE FROM
    leikatyp
WHERE
    ctid NOT IN
        (
        SELECT
            MAX(ctid)
        FROM
            leikatyp
        GROUP BY
            typ
        )
;


DELETE FROM
    leikasynonyme
WHERE
    ctid NOT IN
        (
        SELECT
            MAX(ctid)
        FROM
            leikasynonyme
        GROUP BY
            synonym
        )
;

DELETE FROM
    leika
WHERE
    ctid NOT IN
        (
        SELECT
            MAX(ctid)
        FROM
            leika
        GROUP BY
            schluessel
        )
;