DB2 - eine Spalte in einer Tabelle auf unterschiedliche Werte prüfen

[mirscho]

Halli Hallo!

Ich habe folgende Tabelle fiktiv ausgedacht ( es sind viel mehr als 250000 Datensätze )

ID+++++ART++++++GUELTIG_AB+++++++DATUM_TS+++++++ERGEBNIS
100 ++++ IQ TEST+++++01.01.1901++++++1101201017562445++++++10
100 ++++ SEHTEST+++++25.12.2009++++++2512200916362255++++++2-
100 ++++ IQ TEST+++++11.02.2010++++++1102201016443317++++++13

Ein kurze Erläuterung zu der Tabelle:

Ich kann an der ganzen Datenbank NICHTS verändern! Nur abfragen!
Es sind noch andere Felder vorhanden, welche hierfür aber keine Rolle spielen.
Die Spalte DATUM_TS ist im TIMESTAMP Format. Die Spalte ID ebenso.
Die Spalte ID dient als Verknüpfung zu einer Personentabelle.
Die Spalte ERGEBNIS ist CHAR(4).
In der Spalte GUELTIG_AB steht das Datum ab wann der Wert der Spalte ERGEBNIS gültig ist. Die Datumswerte von abgelaufenen Tests werden dann einfach mit "01.01.1901" aufgefüllt.

Nun mein Problem:

Ich möchte alle Personen abfragen welche beim letzten Sehtest einen Wert zwischen 10 und 15 hatten UND sich dieser Wert verschlechtert hat.

Beispiel: Eine Person hatte erst 10 Punkte und jetzt 13. Dann soll diese Person in der Liste erscheinen.
Eine Person hatte erst 11 Punkte und 16. Dann soll diese Person ebenfalls erscheinen.

Welchen Daten sollen ausgegeben werden?

• Daten aus der Personentabelle
• Datum des letzten Tests
• Testart
• Ergebnis des Testes

Ich habe schon einiges probiert (Nested Table, Subselect) doch komme ich nicht hin. Leider kann ich hier auch nichts an SQL einbinden, da der PC nicht am Internet ist.

Ich danke schon mal im Voraus!

 

[Vereth]

Ich habe hier eine Lösungsmöglichkeit für dich ausgeknobelt; ich hoffe, die Parameter in der WHERE-Klausel entsprechen deinen Wünschen. Notfalls kannst du sie aber leicht anpassen, denn eigentlich ist die Abfrage nicht schwer zu verstehen. Grundlage ist ein Equi-Join der Tabelle test mit sich selbst, um so die Werte der Spalte ergebnis zweimal zur Verfügung zu haben.
Die Abfrage setzt voraus, dass für eine Person nur maximal zwei Datensätze mit Gültigkeitsdatum > 01.01.1901 vorhanden sind, sonst sind die Resultate nicht eindeutig.

SELECT p1.*, t1.datum_ts, t1.art, t1.ergebnis, t2.ergebnis
FROM test t1, test  t2, person p1
WHERE p1.id = t1.id
  AND t1.id = t2.id
  AND t1.art = t2.art
  AND t1.art = "Sehtest"
  AND t1.gueltig_ab > 01.01.1901
  AND t2.gueltig_ab > 01.01.1901
  AND t1.datum_ts > t2.datum_ts
  AND t2.ergebnis >= 10
  AND t2.ergebnis <= 16
  AND t1.ergebnis > t2.ergebnis

Obwohl die Vorgehensweise im Prinzip funktionieren dürfte, rate ich aus Performancegründen davon ab. Ich persönlich würde lieber einen passenden Datenbankauszug in eine Textdatei erstellen und diese mit einem Programm oder awk-Skript auswerten; das dürfte wahrscheinlich schneller und sicherer sein als ein Equi-Join über 250.000 Datensätze.

ACHTUNG: Anwendung auf eigene Gefahr. Der Autor übernimmt keine Haftung für z.B. Rechnerabstürze wegen Speichermangels oder Prozessorüberlastung.

 

[Thomas Darimont]

Hallo,

interessante Fragestellung Habs gerade mal nachgespielt.
Wenn db2 die von Oracle bekannten analytischen Funktionen lead / lag kennen würde
könnte man das Problem damit recht einfach lösen.

Leider gibt es diese Funktionen so nicht direkt in DB2 aber man kann sich die
Funktionalität auch selber bauen, in dem man eine Aggregatsfunktion mit entsprechender
over / partition-Klause verwendet.

Zur Lösung habe ich mit max(xxx) over (partition by ...) gearbeitet und somit die Oracle-Funktion lag
emuliert.

Unsere Tabelle test_records:

CREATE TABLE "DB2ADMIN"."TEST_RECORDS"  (
		  "ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (  
		    START WITH +0  
		    INCREMENT BY +1  
		    MINVALUE +0  
		    MAXVALUE +9223372036854775807  
		    NO CYCLE  
		    NO CACHE  
		    NO ORDER ) , 
		  "USERID" BIGINT NOT NULL , 
		  "KIND" VARCHAR(32) NOT NULL , 
		  "VALID_FROM" BIGINT NOT NULL , 
		  "SCORE" BIGINT NOT NULL )   
		 IN "USERSPACE1" ;

Unsere Inserts:

insert into DB2ADMIN."TEST_RECORDS" (userid,kind,valid_from,score) values (1,'sehtest',20010101,12);
insert into DB2ADMIN."TEST_RECORDS" (userid,kind,valid_from,score) values (1,'sehtest',20010103,15);
-- Verschlechterung
insert into DB2ADMIN."TEST_RECORDS" (userid,kind,valid_from,score) values (1,'sehtest',20010110,13); 

-- Nur 1 Satz -> Keine Verschlechterung
insert into DB2ADMIN."TEST_RECORDS" (userid,kind,valid_from,score) values (2,'sehtest',20010101,6);

insert into DB2ADMIN."TEST_RECORDS" (userid,kind,valid_from,score) values (3,'sehtest',20010101,7);
insert into DB2ADMIN."TEST_RECORDS" (userid,kind,valid_from,score) values (3,'sehtest',20010102,10);
-- Verschlechterung
insert into DB2ADMIN."TEST_RECORDS" (userid,kind,valid_from,score) values (3,'sehtest',20010103,9);

insert into DB2ADMIN."TEST_RECORDS" (userid,kind,valid_from,score) values (4,'sehtest',20010101,3);
insert into DB2ADMIN."TEST_RECORDS" (userid,kind,valid_from,score) values (4,'sehtest',20010102,5);
insert into DB2ADMIN."TEST_RECORDS" (userid,kind,valid_from,score) values (4,'sehtest',20010103,2);
-- Verbesserung
insert into DB2ADMIN."TEST_RECORDS" (userid,kind,valid_from,score) values (4,'sehtest',20010104,11);

Daten:

ID;USERID;KIND;VALID_FROM;SCORE
26;1;"sehtest";20010101;12
27;1;"sehtest";20010103;15
28;1;"sehtest";20010110;13
29;2;"sehtest";20010101;6
30;3;"sehtest";20010101;7
31;3;"sehtest";20010102;10
32;3;"sehtest";20010103;9
33;4;"sehtest";20010101;3
34;4;"sehtest";20010102;5
35;4;"sehtest";20010103;2
36;4;"sehtest";20010104;11

select 
	t.id
	,t.userid
	,t.kind
	,t.valid_from
	,t.current_score
	,t.previous_score
from 
(
	select 
		  id
		, userid
		, kind
		, valid_from
		, score current_score
		, max(score) over (partition by userid order by valid_from asc rows between 1 preceding and 1 preceding) previous_score 
		, max(valid_from) over (partition by userid) latest_valid_from
	FROM 
		DB2ADMIN."TEST_RECORDS"
) t
where 
	t.current_score < t.previous_score 
	and t.valid_from = t.latest_valid_from
order by 
	  t.userid
	, t.valid_from

Ausgabe:

ID;USERID;KIND;VALID_FROM;CURRENT_SCORE;PREVIOUS_SCORE
28;1;"sehtest";20010110;13;15
32;3;"sehtest";20010103;9;10

User 2 ist nicht dabei da es dafür nur einen Eintrag gibt -> keine Verschlechterung
User 4 hat beim letzten Test eine Verbesserung geschafft.

Gruß Tom