| Home | Entdecken | Lernen | Testen | Service & News | Download | |||
Inhalt
1 SQL/NCL Tutorial am Beispiel des WP-Datenbestands
1.3 Der aktuelle Datenbestand (statistic)
1.4 Group by / Formatierung Ausgabe
2 Als Nachwort, gutes SQL - schlechtes SQL
1 SQL/NCL Tutorial am Beispiel des WP-Datenbestands
Die Urversion von nadas wurde in einem Wertpapierstammdaten-Projekt entwickelt. Dieser Datenbestand ist für die Einschätzung der Leistungsfähigkeit von nadas insofern interessant, weil hier sowohl eine große Datenbreite wie Datentiefe geben ist.
Der Bestand umfasst ca. 170.000 Emittenten-Stammsätze und ca. 300.000 Wertpapiere (lebende Gattungen).
Insgesamt gibt es ca. 1.800 Datenspalten (Columns) verteilt auf 30 Tabellen (Tables).
Das ist die Datenkomplexität für die nadas konzipiert wurde.
Zielsetzung: In diesem Tutorial soll eine Liste der größten Wertpapier-Emittenten im Deutschen Markt erstellt werden.
Die SQL-Statements und das Ausgabelayout werden hierzu schrittweise verfeinert.
Das Dokument gibt so einen guten Überblich zur nadas SQL-Abfragesprache und zu den Formatierungsmöglichkeiten der NCL ( nadas command language). NCL_manual
SQL- Voraussetzungen
Solange Ihnen die Grundzüge von SQL bekannt sind und
die Bedeutung bzw. Unterschied zwischen den folgenden SELECTS
Dieses Tutorial setzt elementare SQL-Kenntnisse voraus, mehr nicht.
SELECT *
SELECT count(*)
SELECT WKN
SELECT distinct WKN
SELECT count (distinct WKN)
ansatzweise klar ist, sollte alles Folgende verständlich sein. Die Spalte WKN steht dabei für Wertpapier-Kenn-Nummer
Hinweise:
1. Die verwendeten Daten sind nicht aktuell, sondern geben die Marktsituation im Jahr 2004 wieder.
2. Diese WP-Daten sind leider nicht "public domain" und können von uns deshalb auch nicht weitergegeben werden. Wir müssen uns daher in diesem Fall darauf beschränken, die Ergebnisse aufzulisten und die Messergebnisse auf verschiedenen Systemen zu veröffentlichen. Für eine aktive Arbeit mit den nadas Tools bietet sich als Alternative das ndwriter-Tutorial bzw. die Arbeit mit den Geo-Daten an, beide Beispiele haben aber nicht die Komplexität der hier verwendeten WP-Daten.
3. Alle SQL-Statements wurden sorgfältig erstellt und im ndreader ausgeführt. Die Gesamtausführungszeit auf dem Referenzsystem liegt bei unter 20 Sekunden, für alle Statements (insgesamt).
1.1 Starten des ndreader.
Für die folgenden Auswertungen benötigen wir den Gattungs-Stamm (WP_G.ndf) und den Emittenten-Stamm (WP_M.ndf).
Der ndreader wird mit folgendem Kommando:
ndreader -sWP WP_G.ndf WP_M.ndf
Mit der Option -sWP wird das Default Schema auf WP gesetzt. Dadurch müssen Tabellennamen in diesem Schema nicht qualifiziert werden.
Wir dürfen dann in Anweisungen statt z.B. wp.wp_m_base auch kurz wp_m_base schreiben.
Unsere erste SQL-Abfrage, wie viele Emittenten-Stammsätze sind im Bestand?
SQL:
select count(*) from wp_m_base;
Antwort:
174646
Für diese Abfrage muss man zumindest wissen, dass es in der Datenbank eine Tabelle WP_M_BASE gibt.
1.2 Der Datenkatalog
Im ndreader ist das einfach festzustellen, mit unserer zweiten Abfrage listen wir alle Tabellen aus dem Datenkatalog auf.
SQL:
describe schema
Antwort:
SCHEMA
--------
WP
SCHEMA TABLE
------------------
WP WP_G_BASE
WP WP_G_CON
WP WP_G_TXT
WP WP_M_BASE
WP WP_M_CON
WP WP_M_TXT
Es gibt ein Schema "WP" mit insgesamt 6 Tabellen.
Diese Abfrage ist kein SQL, sondern gehört zur NCL- Spracherweiterung (nadas-command-language). Neben den (vorgefertigten) Abfragen zum Data-Catalog , ermöglicht die NCL das Customizing der Ausgabe, z.B. durch drucken von Überschriften, Ausrichtung der Spalten, etc..
Als nächstes möchten wir wissen, wie die Tabelle WP_G_TXT aussieht
SQL:
describe table 'WP_G_TXT'
Antwort:
QUALIFIED_TABNAME COL_NAME PRIMARY_KEY DATA_TYPE LENGTH PRECISION SCALE
-------------------------------------------------------------------------------
WP.WP_G_TXT ISIN -- 5 12 -- --
WP.WP_G_TXT WKN 1 5 6 -- --
WP.WP_G_TXT DATUM 1 5 8 -- --
WP.WP_G_TXT TERMIN 1 5 1 -- --
WP.WP_G_TXT TIMES -- 5 30 -- 1
WP.WP_G_TXT FID 1 5 7 -- --
WP.WP_G_TXT SNR 1 5 3 -- --
WP.WP_G_TXT TEXT -- 5 2000 -- 1
Bitte beachten Sie in der Abfrage die Hochkomma um den Tabellennamen!
Wir verzichten hier auf die Auflistung der WP_G_BASE, die hat über 600 Spalten.
1.3 Der aktuelle Datenbestand (statistic)
Neben dem Datenmodell interessieren uns die Daten selbst.
Zum Beispiel: wie viele Datensätze aus WP_G_TXT haben eine SNR (Satznummer) und wie viele unterschiedliche Satznummern gibt es?
SQL:
select count(snr), count(distinct snr)
from wp_g_txt;
Antwort:
457224!12
Soll heißen es gibt 457.224 Datensätze mit Satznummer, in der Satznummer gibt es (nur) 12 unterschiedliche Werte.
Auch für diese Information gibt es ein NCL-Kommando, das wir jetzt für eine andere Spalte, die "WKN" (Wertpapier-Kenn-Nummer) ausführen.
SQL:
describe stat 'wkn'
Antwort:
QUALIFIED_COLUMNNAME REC_COUNT NULL_COUNT DUPL_COUNT
---------------------------------------------------------
WP.WP_G_BASE.WKN : 306516 0 0
WP.WP_G_CON.WKN : 2066155 0 1767864
WP.WP_G_TXT.WKN : 457224 0 240483
WP.WP_M_BASE.WKN : 174646 0 35279
WP.WP_M_CON.WKN : 0 0 0
WP.WP_M_TXT.WKN : 174639 0 35272
Das Kommando listet zu allen Spalten mit Namen WKN, die Anzahl der Einträge (REC_COUNT), die Anzahl der NULL Werte (NULL_COUNT) und die Anzahl der Duplikate (DUPL_COUNT).
Es gilt für alle Tabellenspalten: REC_COUNT+NULL_COUNT = ANZAHL_ZEILEN (also gleich count(*)).
In der WP_G_BASE ist die WKN allein Primärschlüssel, folglich gibt es weder NULL Werte noch Duplikate.
Wir untersuchen als nächstes die WP_M_BASE. Das ist die 4. Tabelle der obigen Liste. Dort bilden die Spalten WKN und GENER zusammen den Primärschlüssel.
SQL:
select count (distinct(wkn)), count(wkn)
from wp_m_base;
Antwort
139367!174646
Was wir schon aus dem "describe stat" von oben wissen
(könnten)
174646 REC_COUNT
- 35279 DUP_COUNT
= 139367 unterschiedliche Werte.
1.4 Group by / Formatierung Ausgabe
Um der Sache näher zu kommen, versuchen wir die Häufigkeit der Werte zur Generationsnummer heraus zu finden.
Das geht z.B. mit der SQL Anweisung GROUP BY. Wir gruppieren das Ergebnis nach GENER und zählen die Einträge in jeder Gruppe.
SQL:
select gener, count(*)
from wp_m_base
group by gener;
Antwort
91!2
92!10
93!35
94!96
95!366
96!1459
97!6110
98!27203
99!139365
Um das etwas verständlicher zu machen, definieren wir uns zunächst ein besser lesbares Ausgabeformat, durch 4 NCL Kommandos:
set heading on
set colsep ' '
set feedback on
set format auto
Die gleiche Abfrage nochmals ausgeführt.
SQL:
select gener, count(*)
from wp_m_base
group by gener;
Antwort
GENER column[2]
-----------------
91 2
92 10
93 35
94 96
95 366
96 1459
97 6110
98 27203
99 139365
number of record(s) selected: 9
Der Reihe nach: Die Abschlusszeile für die Anzahl der Records wurde durch 'set feedback on' eingeschaltet.
'Group by' fasst, ähnlich wie 'distinct', gemeinsame Werte zusammen. Auf Gruppen können aber zusätzlich Aggregatsfunktionen angewendet werden. Im obigen Beispiel count(*).
Der Theorie nach, sollte es für jeden Emittenten einen Satz mit Generationsnummer 99 geben, bei Änderung des Stammsatzes, speziell Namenswechseln, wird dann Nummer 98,97, etc. angelegt.
Die obige Liste spricht dafür. Wir wollen das trotzdem überprüfen und sehen nach ob es vielleicht Werte ohne 99er Generationsnummer gibt.
1.5 Subselects
Für die folgende Suche verwenden wir ein Subselect.
Das untere Subselect liefert uns alle WKN aus dem Emittentenstamm, zu denen es eine Generation '99' gibt. Das müssten der Theorie nach alle sein.
Wir suchen nun zur Probe einfach nach allen WKN die nicht im Subselect sind. Den Fall sollte es nicht geben.
SQL:
select wkn
from wp_m_base
where wkn not in
( select wkn
from wp_m_base
where gener = '99');
Antwort
WKN
-------
921384
974992
number of record(s) selected: 2
Hinweis: Die obige SQL-Anweisung ist in machen Datenbanksystemen, vorsichtig gesagt, nicht ratsam. In nadas ist [NOT] IN SUBSELECT ein sehr performantes Konstrukt . Bei den knapp 170.000 Datensätzen liegt die Ausführungszeit unter einer Sekunde.
Wir zeigen jetzt 3Spalten zu allenDatensätzen mit diesen beiden WKN an. Neu ist die Spalte MD031, die den Namen enthält. (Die WP_M_BASE Tabelle enthält insgesamt 59 Spalten).
SQL:
select wkn,gener,md031
from wp_m_base
where wkn ='921384' or wkn='974992';
Antwort
WKN GENER MD031
------------------------------------------------------------
921384 96 American Express Funds SICAV - Euro Liquidity
974992 97 American Express Funds SICAV - US$ Liquidity
number of record(s) selected: 2
Beachten Sie die automatische Ausrichtung von Überschrift und Datenfeldern. Die hatten wir weiter oben mit 'set format auto' eingeschaltet.
Die Ergebnisse sind schlüssig, wie wir vorher bereits per 'describe stat' festgestellt hatten, gibt es 139367 unterschiedliche WKN, aber laut GROUP BY nur 139365 mal die Generationsnummer (GENER) 99!
Die Daten in WP_G_BASE (Wertpapiere) und WP_M_BASE (Emittenten), stehen in einem fachlichen Zusammenhang. Zu jedem Wertpapier muss es einen Emittenten geben.
Damit kommen wir zurück zum eingangs formulierten Ziel: Wir möchten eine Liste der Emittenten erstellen, die die Anzahl der Wertpapiere zum Emittenten enthält. Da in dem Datenbestand alle Emittenten und alle aktuellen Wertpapiere (Stand 2004) gespeichert sind, würde das etwas umfangreich. Wir beschränken uns deshalb auf die Emittenten die 500 oder mehr Wertpapiere begeben haben.
Das SQL Statement hierzu wird schrittweise entwickelt.
Die Emittenten-Nummer steht bei den Gattungen im Feld GD240.
Wir prüfen vorsichtshalber, gibt es einen Gattungsstammsatz, zu dem es keinen Emittenten gibt? Hierfür verwenden wir wieder ein Subselect.
SQL:
select wkn,gd260
from wp_g_base
where gd240 not in (select wkn from wp_m_base);
Antwort
WKN GD260
------------------
number of record(s) selected: 0
Der Fall tritt also nicht auf.
Wir suchen jetzt nach den Emittenten mit mehr als 500 Wertpapieren.
Das ist ein einfaches GROUP BY über GD240, mit dem bekannten count(*).
Das Ergebnis wird mit der HAVING Anweisung gefiltert.
SQL:
select gd240, count(*) num
from wp_g_base
group by gd240
having num >499;
Antwort
GD240 NUM
------------------
110000 1186
175289 9080
184245 1798
212000 1887
238000 3265
268008 505
276000 1135
283000 602
301000 739
337000 646
346012 2119
390121 5675
405322 1666
405670 772
500610 1133
588820 3941
659999 666
724230 2009
765420 3726
802200 761
802500 798
803200 7376
804010 6845
804610 5456
807600 539
811510 5148
812500 706
856099 5135
876872 1982
878481 1282
895719 817
915776 6461
968746 2086
968753 960
number of record(s) selected: 34
Die Anweisung 'Having > 499' eliminiert dabei alle Gruppen < 500.
1.6 Join Syntax
Wir müssen jetzt nur noch die Namen der Emittenten anzeigen. Dafür ist ein JOIN mit dem Emittenten-Stamm erforderlich.
Das können wir in SQL-99 oder SQL-92 Syntax schreiben.
select count(*) cnt, gd240, md031
from wp_g_base g
join wp_m_base m
on gd240=m.wkn
group by gd240,md031
having cnt >= 500
order by md031;
oder
select count(*) cnt, gd240, md031
from wp_g_base g,wp_m_base m
where gd240=m.wkn
group by gd240,md031
having cnt >= 500
order by md031;
Das Ergebnis ist das Gleiche. Beide Lösungen sind aber leider nicht nur "straight forward" sondern (fachlich) falsch! Wir erhalten.
Antwort
CNT GD240 MD031
-------------------------------------------------------------------------
1666 405322 ABN AMRO Bank N.V.
798 802500 BHF-BANK AG
1133 500610 BNP Emissions- und Handelsgesellschaft mbH
666 659999 BNP PARIBAS ARBITRAGE ISSUANCE B.V.
1133 500610 BNP Paribas Emissions- und Handelsgesellschaft mbH
706 812500 Baden-Württembergische Bank AG
1920 968753 Basis Trades [Eurex Bonds]
761 802200 Bayerische Hypo- und Vereinsbank AG
1887 212000 Bayerische Landesbank
1887 212000 Bayerische Landesbank Girozentrale
761 802200 Bayerische Vereinsbank AG
798 802500 Berliner Handels- und Frankfurter Bank
798 802500 Berliner Handels-Gesellschaft
798 802500 Berliner Handels-Gesellschaft - Frankfurter Bank
1186 110000 Bundesrepublik Deutschland
817 895719 Centro Internationale Handelsbank AG
3726 765420 Citibank AG
3726 765420 Citibank AG & Co. KGaA
3726 765420 Citigroup Global Markets Deutschland AG & Co. KGaA
14752 803200 Commerzbank AG
CNT GD240 MD031
----------------------------------------------------------------------------------------
3265 238000 DG BANK Deutsche Genossenschaftsbank AG
3265 238000 DZ BANK AG Deutsche Zentral-Genossenschaftsbank, Frankfurt am Main
6845 804010 Deutsche Bank AG
772 405670 Deutsche Bank AG London
1798 184245 Deutsche Bank AG, London Branch
3265 238000 Deutsche Genossenschaftsbank
3265 238000 Deutsche Genossenschaftskasse
539 807600 Deutsche Hyp Deutsche Hypothekenbank Frankfurt-Hamburg AG
5456 804610 Dresdner Bank AG
539 807600 Eurohypo AG
1982 876872 Federal Home Loan Mortgage Corp.
5135 856099 Federal National Mortgage Association
7882 588820 Goldman, Sachs & Co. Wertpapier GmbH
9080 175289 Government National Mortgage Association
5148 811510 HSBC Trinkaus & Burkhardt KGaA
505 268008 Hessische Landesbank - Girozentrale -
674 805890 Hypothekenbank in Berlin AG
534 805910 Hypothekenbank in Essen AG
798 802500 ING BHF-BANK AG
1135 276000 Kreditanstalt für Wiederaufbau
CNT GD240 MD031
------------------------------------------------------------------------------------------------------
2119 346012 Landesbank Baden-Württemberg
505 268008 Landesbank Hessen-Thüringen Girozentrale
602 283000 Landesbank Rheinland-Pfalz - Girozentrale -
602 283000 Landesbank und Girozentrale Kaiserslautern
602 283000 Landesbank und Girozentrale Rheinland-Pfalz
539 807600 Mecklenburgische Hypotheken- und Wechselbank
539 807600 Norddeutsche Hypotheken- und Wechselbank AG
539 807600 Norddeutsche Hypotheken- und Wechselbank [vormals Mecklenburgische Hypotheken- u
739 301000 Norddeutsche Landesbank Girozentrale
817 895719 Raiffeisen Centrobank AG
1282 878481 SGA Société Générale Acceptance N.V.
5675 390121 Sal. Oppenheim jr. & Cie.
5675 390121 Sal. Oppenheim jr. & Cie. KGaA
2009 724230 Société Générale Effekten GmbH
2086 968746 Strom, Futures, Indizes, Optionen [EEX]
2119 346012 Südwestdeutsche Landesbank Girozentrale
5148 811510 Trinkaus & Burkhardt KGaA
6461 915776 UBS AG [London Branch]
3726 765420 VERMÖGENSVERWALTUNG AG DER CITIBANK
646 337000 WestLB AG
CNT GD240 MD031
----------------------------------------------------------
646 337000 Westdeutsche Landesbank Girozentrale
706 812500 Württembergische Bank
number of record(s) selected: 62
Das Ergebnis ist nicht korrekt, obwohl es auf den ersten Blick ganz glaubhaft aussieht und vermutlich auch die allgemein erwarteten Namen enthält.
Hier wurden aus 34 Emittenten (siehe obere Abfrage) jetzt 62(!) Ergebniszeilen, kann irgendwie nicht stimmen.
Der Grund ist der Aufbau des Emittenten-Stamms, mit mehreren Eintragungen unterschieden nach Generationsnummer!
Richtig wäre es zu jedem Emittenten den aktuellsten Satz, also nur den mit der niedrigsten Generationsnummer auszuwählen.
Hierfür verwenden wir eine weitere Aggregatfunktion: MIN
Mit MIN wird das Minimum bestimmt, also MIN(GENER) pro Emittent, was wir wieder einfach per GROUP BY ermitteln können.
das Subselect
select wkn,min(gener)
from wp_m_base
group by wkn;
bzw. das ganze Statement mit dem Subselect
SQL:
select count(*) cnt, gd240, md031
from wp_g_base g
join wp_m_base m
on gd240=m.wkn
where (m.wkn,gener)
in (select wkn,min(gener)
from wp_m_base
group by wkn)
group by gd240,md031
having cnt >= 500
order by md031;
Antwort
CNT GD240 MD031
----------------------------------------------------------------------------------------
1666 405322 ABN AMRO Bank N.V.
666 659999 BNP PARIBAS ARBITRAGE ISSUANCE B.V.
1133 500610 BNP Paribas Emissions- und Handelsgesellschaft mbH
706 812500 Baden-Württembergische Bank AG
960 968753 Basis Trades [Eurex Bonds]
761 802200 Bayerische Hypo- und Vereinsbank AG
1887 212000 Bayerische Landesbank
1186 110000 Bundesrepublik Deutschland
3726 765420 Citigroup Global Markets Deutschland AG & Co. KGaA
7376 803200 Commerzbank AG
3265 238000 DZ BANK AG Deutsche Zentral-Genossenschaftsbank, Frankfurt am Main
6845 804010 Deutsche Bank AG
772 405670 Deutsche Bank AG London
1798 184245 Deutsche Bank AG, London Branch
5456 804610 Dresdner Bank AG
539 807600 Eurohypo AG
1982 876872 Federal Home Loan Mortgage Corp.
5135 856099 Federal National Mortgage Association
3941 588820 Goldman, Sachs & Co. Wertpapier GmbH
9080 175289 Government National Mortgage Association
CNT GD240 MD031
-----------------------------------------------------------------
5148 811510 HSBC Trinkaus & Burkhardt KGaA
798 802500 ING BHF-BANK AG
1135 276000 Kreditanstalt für Wiederaufbau
2119 346012 Landesbank Baden-Württemberg
505 268008 Landesbank Hessen-Thüringen Girozentrale
602 283000 Landesbank Rheinland-Pfalz - Girozentrale -
739 301000 Norddeutsche Landesbank Girozentrale
817 895719 Raiffeisen Centrobank AG
1282 878481 SGA Société Générale Acceptance N.V.
5675 390121 Sal. Oppenheim jr. & Cie. KGaA
2009 724230 Société Générale Effekten GmbH
2086 968746 Strom, Futures, Indizes, Optionen [EEX]
6461 915776 UBS AG [London Branch]
646 337000 WestLB AG
number of record(s) selected: 34
Die Anzahl stimmt jetzt wieder überein, wir haben 34 Ergebniszeilen.
Tipp: Statt das jedesmal das SQL-Statement direkt einzugeben, hätten wir es auch per Editor in eine Datei schreiben können. Zum Beispiel in die Datei t.sql im aktuellen Verzeichnis.
Der Aufruf lautet dann
SQL:
@t.sql
Gerade wenn ein Statement Schritt für Schritt entwickelt wird, empfiehlt sich die Arbeit mit einem Editor.
Entsprechend würden wir uns eine Datei mit den 'set' Anweisungen für unsere Formate erstellen, z.B setformats.sql, und dann ausführen mit:
@setformats.sql
Vielleicht hat der eine oder andere bemerkt, dass in der obigen Liste einige ganz prominente Namen am deutschen Wertpapiermarkt fehlen. Der Grund liegt darin, dass nicht alle Emittenten über Feld GD240 geschlüsselt werden. Speziell die Fondsgesellschaften finden sich in Feld GD245, das aber auch noch für andere Zwecke verwendet wird.
Die Situation ist typisch für Datenmodelle aus der Praxis, die über Jahrzehnte angepasst und überarbeitet werden, "dritte Normalform" finden sich eher in Beispieldatenbanken.
Wir erstellen zum Abschluss dieses Abschnitts jetzt ein SQL-Statement, das nur diejenigen Emittenten selektiert, die in GD240 überhaupt nicht vorkommen!
Das Subselect hierfür lautet:
gd245 not in (select distinct gd240
from wp_g_base)
Das vollständige Select, GD245 hat hier gegenüber der vorigen Abfrage GD240 ersetzt!
SQL:
select count(*) cnt, md031 NAME,md051 SITZ, gd245 ID
from wp_g_base g
join wp_m_base m
on (m.wkn =gd245)
where
(m.wkn,gener)
in (select wkn,min(gener)
from wp_m_base
group by wkn)
and
gd245 not in (select distinct gd240
from wp_g_base)
group by gd245, md031, md051
having count(*) >= 500
order by 1;
Antwort
CNT NAME SITZ ID
--------------------------------------------------------------------------------------------
512 Union Investment Institutional GmbH Frankfurt a.M. 150149
535 Deutsche Asset Management Investmentgesellschaft mbH Frankfurt a.M. 848772
542 SGAM Fund Sicav Luxemburg 919694
747 Deka Investment GmbH Frankfurt a.M. 848771
1201 STOXX Ltd. Zürich 966604
number of record(s) selected: 5
Das obige Statement verwendet noch weitere SQL-Features.
Frage: Wie wurden die Überschriften erzeugt, was bedeutet in ANSI-SQL die Anweisung ORDER BY 1 (??).
1.7 Rechnen in nadas
Zum Schluss noch etwas Rechnen in SQL. Dzu nehmen wir zwei Felder aus dem Gattungsstamm WP_G_BASE her, die den Mindestpreis beim IPO (Initial Public Offering) eines WP angeben und zwar in der Form von/bis.
Die Felder heißen GD488A und GD488B.
Wir suchen hier der Einfachheit halber einfach nach der größten Differenz, ohne weitere Angaben wie Währung etc. zu verwenden.
SQL:
select count(*) cnt,gd488b-gd488a diff
from wp_g_base
where gd488a is not null
group by diff
order by cnt;
Antwort
CNT DIFF
---------------
1 13
1 1,2
1 25
1 50
1 0,5
1 NULL
2 16
2 20
2 4,5
3 12
4 1,3
4 11
5 15
5 9
6 10
7 1
8 8
10 3,5
16 7
16 1,5
16 2,5
21 6
38 2
39 5
48 4
87 3
number of record(s) selected: 26
Beachten Sie bitte den NULL Wert in der Liste. Wie kann der zu Stande kommen, was steht in diesem Fall in GD488B?
Wie man sieht, ist das Datenfeld nur bei wenigen der 300.000 Gattungen belegt.
Zu den Feldern berechnen wir nun noch Summen (SUM) und Durchschnittswerte (AVG), speziell diese Rechen-Ergebnisse sollen auf verschiedenen Rechnersysteme verifiziert werden.
Wie ist der Durchschnittswert für die beiden Preise?
SQL:
select
avg(gd488b) , avg(gd488a)
from wp_g_base
where gd488a is not null;
Antwort
column[1] column[2]
--------------------------------------------
30,2729651162790697 25,6782608695652173
number of record(s) selected: 1
Da die Werte als DECIMALS angelegt sind, werden sie in nadas direkt im Dezimalsystem berechnet, das Ergebnis ist (vgl. Testergebnisse) überall gleich.
Warnung: Dezimalbrüche umgerechnet in Fließpunktzahlen (Float/Double) sind nie präzise Werte (in SQL deshalb auch als APROXNUM bezeichnet). Verwenden Sie immer DECIMAL wenn Sie wie z.B. bei Währungsbeträgen, einen Wert mit definierter Genauigkeit benötigen.
Wir suchen jetzt nach dem Wert mit der größten Preisspanne.
Die obige Liste weißt einen Wert mit einer Differenz von 50 aus.
Wir versuchen das zu plausibilisieren, indem wir wieder per Subselect nach den Werten mit der größten Differenz suchen.
SQL:
select wkn,gd260, gd488a, gd488b
from wp_g_base
where (gd488b - gd488a ) in
(select
max(gd488b-gd488a) diff
from wp_g_base
where gd488a is not null);
Antwort
WKN GD260 GD488A GD488B
-----------------------------------------------------------------------
581005 DEUTSCHE BOERSE NA O.N. 285,0000000 335,0000000
number of record(s) selected: 1
2 Als Nachwort, gutes SQL - schlechtes SQL
Beantwortet ndreader jede SQL-Abfrage in Sekunden?
Im Folgenden zwei äquivalente Statements. Das eine ist mengenorientiert lösbar, das andere erzwingt eine Iteration.
Schlechtes SQL
select emi.wkn,emi.gener,md031, count(*) num
from wp_g_base gat
join wp_m_base emi
on gd245=emi.wkn
where
emi.gener <= ALL
(select gener
from wp_m_base
where wkn=emi.wkn )
group by emi.wkn,emi.gener,md031
having num >= 500
order by num;
Beachten Sie die rote Markierung. Im Subselect ist eine where-Bedingung mit Bezug zum äußeren Block definiert (correlated subquery).
Bedeutet: Das innere Subselect muss für jede WKN im äußeren Block einmal ausgeführt werden. In diesem Fall 165.000 mal.
Laufzeit ca. 50 Minuten.
Der ndreader löst hierbei ca. 55 Subselects pro Sekunde oder 3.300 pro Minute.
Gutes SQL
select emi.wkn,emi.gener,md031, count(*) num
from wp_g_base g
join wp_m_base emi
on (emi.wkn =gd245)
where
(emi.wkn,gener)
in (select wkn,min(gener)
from wp_m_base
group by wkn)
group by emi.wkn,emi.gener,md031
having num >= 500
order by num;
Das Ergebnis kann mit einer einmal auszuführenden Subquery ermittelt werden. Laufzeit < 2 Sekunden.
Der Unterschied ergibt sich aufgrund unserer Datenmenge. In einer 'Spieldatenbank' mit 1.000 oder noch weniger Eintragungen, fällt der Unterschiede zwischen beiden Statements vielleicht auf, vielleicht auch nicht.
In SQL ist es aufgrund der Sprachfeatures möglich, einfache Abfrage kompliziert zu gestalten.
Wir wollen kein "Rumpf"-SQL in nadas, das alle unperformanten Features ausschließt. Folglich können wir aber auch nicht verhindern, dass ungeeignete Features verwendet werden.
Auch in SQL kann man schlechte Software schreiben und gegen die lässt sich mit keiner noch so performanten Hardware etwas ausrichten.