Mining über RDBMSe - web

Mining über RDBMSe
von
Christian Widmer
 Wie gut lässt sich Mining mit SQL
realisieren?
 Müssen neue Konstrukte zur Verfügung gestellt werden, wenn ja
welche?
Vortragsüberblick
 Association Rules
 Apriori Algorithmus
 Implementationsalternativen
 Alternativen in SQL-92
 Alternativen in SQL-OR
 Schlussfolgerungen
Association Rules
Syntax: X  Y
z.B. {Bier}  {Chips}
Salopp: Wer Bier kauft, kauft auch Chips.
(X, Y sind Mengen von Items, und X  Y = )
Anforderung an eine Regel

Minimum Support (Relevanz)

Minimum Confidence (Vertrauen)
 2% der Kunden kaufen Bier und Chips.
 40% der Kunden, die Bier kaufen,
kaufen auch Chips.
Apriori Algorithmus
Begriffe, die für den Algorithmus wichtig sind


Frequent Itemset
Itemmengen, deren Relevanz grösser ist als Minimum Support.
Candidate Itemset
Ist eine potentielle Kandidatenmenge für ein Frequent Itemset.
 Candidate Itemset  Frequent Itemset
Vorgehen des Algorithmus
1) Finde alle Frequent Itemsets durch Iteration von a) und b)
a) Generiere Candidate Itemsets
b) Bestimme die Relevanz (Support Counting)
2) Finde alle gültigen Regeln
Bemerkungen
1) Jede Teilmenge eines Frequent Itemset ist auch ein Frequent Itemset.
2) Itemsets müssen geordnet sein (Implementationsanforderung)
Apriori Algorithmus
Frequent Itemsets mit Support = 50 %
Frequent Itemset F1
{Käse}
{Milch}
{Butter}
{Brot}
TID
Alder
Durrer
Moser
Specht
Transaktionen
Items
{Käse, Butter, Honig}
{Milch, Butter, Brot}
{Käse, Milch, Butter, Brot}
{Milch, Brot}
Candidate Itemset C2 Support
{Käse, Mich}
1
{Käse, Butter}
2
{Käse, Brot}
1
{Milch, Butter}
2
{Mich, Brot}
3
{Butter, Brot}
2
Frequent Itemset F2
{Käse, Butter}
{Milch, Butter}
{Milch, Brot}
{Butter, Brot}
Candidate Itemset C3 Support
{Milch, Butter, Brot}
2
Frequent Itemset F3
{Milch, Butter, Brot}
Apriori Algorithmus
Generieren der Kandidatenmenge: Beispiel
Gegeben
F3 =
{{1,2,3},{1,2,4},{1,3,4},{1,3,5},{2,3,4}}
Gesucht Eine möglichst kleines Candidate Itemset
C4
 C*4 = {{1,2,3,4},{1,3,4,5}}
Kandidatenmenge)
 C4 = {{1,2,3,4}}
nach Prune Step)
(provisorische
(Kandidatenmenge
 Generiere aus jeweils 2 Elementen (Generatoren) aus F3
einen Kandidaten.
INSERT INTO C*4
I1.item1, I1.item2, I1.item3, I2.item3
FROM
F3 I1, F3 I2,
WHERE
I1.item1 = I2.item1
AND
I1.item2 = I2.item2
AND
I1.item3 < I2.item3
Implementationsarten
 Cursor Interface
 viel Kontextwechsel zwischen DBMS und
Applikation
 Stored Procedure
 keine Kontextwechsel zwischen DBMS und
Applikation
 Cache Mine
 Daten werden einmal von der Datenbank
gelesen und in einem Binärfile gespeichert
(sehr effizient).
 User Defined Function (UDF)
 keine Kontextwechsel zwischen DBMS und
Applikation
 aufwendig und gefährlich (programmiert in
K Way Join (SQL 92)
Support Counting
Gegeben
 Transaktionstabelle T mit Tupeln (tid, item)
 Candidate Itemset Ck
Gesucht
 Frequent Itemset Fk
Vorgehen
1. k-facher JOIN von T mit sich selbst (Input: T / Output: T*)
(unnesting)
2. JOIN von T mit Ck und Aggregation (Input: T* / Output: Fk)
Beispiel für k = 3
INSERT INTO F3 SELECT item1, item2 COUNT(*)
FROM
C3, T t1, T t2
K Way Join: Support Counting
TID
100
Itemset
1, 1
100
100
100
100
100
100
100
100
200
200
200
200
200
200
200
200
200
1, 2
1, 3
2, 1
2, 2
2, 3
3, 1
3, 2
3, 3
1, 1
1, 2
1, 4
2, 1
2, 2
2, 4
4, 1
4, 2
4, 4
HAVING
COUNT(*) > :minsup
GROUP BY
item1, item 2
Itemset Support
100
1, 2
200
1, 2
C3.item 1 = t 1.item
C3.item 2 = t 2.item
C2
t1.tid = t 2.tid
T t1
C2 = {1, 2}
T t2
TID
Itemset
100
100
100
200
200
200
1
2
3
1
2
4
Subquery Based (SQL-92)
Support Counting
Idee

Ausnutzen der Eigenschaft, dass Items in einem
Candidate Itemset gemeinsame Präfixe haben.
(Items sind in kanonischer Reihenfolge sortiert)
Vorgehensweise


Verwenden einer rekursive Subquery
Aggregation der finalen Subquery k findet das Frequent Itemset
Eigenschaft der Subquery

Subquery der Rekursionstiefe v findet alle TIDs, welche die ersten v
Items vom Candidate Itemset enthält.
Beispiel: Subquery Q3
SELECT
FROM
WHERE
AND
item1, item2, item3, tid
T t3, (Subquery Q2) AS r2
(SELECT DISTINCT item1, item2, item3 FROM Ck) AS d3
r2.item1 = d3.item1 AND r2.item2 = d3.item2
r2.tid = t3.tid AND t3.item = d3.item3
Subquery Base: Q3 (j =3)
100
200
100
300
200
400
Q3
{1, 2, 3}
{1, 2, 3}
{1, 2, 5}
{1, 2, 5}
{1, 3, 6}
{1, 3, 6}
item1, item 2, item 3 , tid
t3.item 1 = d 1.item 3
t3.tid = r 1.tid
T t3
r1.item 1 = d 1.item 1
r2.item 2 = d 1.item 2
d2
r2
Subquery Q 2
Q2
item-set
{1, 2}
{1, 2}
{1, 2}
{1, 3}
{1, 3}
{1, 3}
tid
100
200
300
100
200
400
SELECT DISTINCT
item1, item 2
C3
{1, 2, 3}
{1, 2, 5}
{1, 3, 6}
Transactions
TID
item
100
1
100
2
100
3
100
5
200
1
200
2
200
3
200
6
300
1
300
2
300
4
300
5
400
1
400
3
400
4
400
6
Support Counting
Resultate SQL-92
Subquery
 beste SQL-92 Implementation
K-Way Join
 in vielen Fällen vergleichbar mit Subquery
 viel schlechter wenn SQL-92 gesamthaft schlecht
abschneidet
Bemerkung
Gesamthaft gesehen ist leider keine SQL-92
Implementation brauchbar.
Einige SQL-OR Konzepte aus
IBM DB2, die verwendet werden
Table Function
 Virtuelle Tabelle, welche zu einer UDF gehört.
Diese UDF generiert die Tabelle “on the fly“.
(verwandt mit Views aus SQL-92)
BLOB’s
 Zur Verwaltung grosser Objekte im DBMS
 Wir werden sie zur Parameterübergabe zwischen
Table Functions benützen
Gather Join (SQL-OR)
Support Counting
Dieses Verfahren entspricht dem k-Way Join in SQL-92.
Algorithmus
 Bringe die Transaktionstabelle T mit der Table Function
Gather in die flache Form T*(TID, item-list)
 Bilde mit der Table Function Comb-k für jedes Tupel von T*
alle möglichen Teilmengen von T* der Grösse k.
 JOIN von Comb-k mit Ck und Aggregation liefern Fk
Bemerkungen
 Gather und Comb-k werden bei der Implementierung in
einer Table Function zusammengefasst.
Gather Join (SQL-OR)
Support Counting
Beispiel für k = 3 und Support = 50%
T
TID item
100 1
100 2
100 3
100 4
200 1
200 2
200 3
300 1
300 4
300 6
400 1
400 4
400 5
Gather
TID item-list
100 {1, 2, 3, 4}
200 {1, 2, 3}
300 {1, 4, 6}
400 {1, 4, 5}
Comb-k item-set JOIN
{1, 2, 3}
{1, 2, 4}
{1, 3, 4}
{2, 3, 4}
{1, 2, 3}
{1, 4, 6}
{1, 4, 5}
C*3
{1, 2, 3}
{1, 2, 3}
{1, 2, 4}
GROUP BY COUNT
C3 = {{1,2,3}, {1,2,4}}
F3
{1, 2, 3}
Gather Count (SQL-OR)
Support Counting
Variation von Gather Join
(Spezielle Optimierung für 2. Durchgang)
Idee
 GROUP BY wird in die Table Function GatherComb
eingebettet, und es werden direkt die Frequent Itemsets
zurückgegeben.
Effiziente Implementation
 2-dim Array (für jedes Item von C2 eine Dimension)
 Candidate Itemsets sind in kanonischer Reihenfolge
geordnet
 Ein Candidate Itemset kann in linearer Zeit einem Punkt im
2-dim-Array zugeordnet werden.
Problematik
 Funktioniert nur effizient für 2. Durchgang, für alle anderen
Gather Count (SQL-OR)
Support Counting
Item Set: {2, 3}
1 2 3
1
2
3
Counter wird inkrementiert
item-set
{1, 2}
{2, 3}
{1, 3}
...
Vertical (SQL-OR)
Support Counting
Algorithmus





Bringe die Transaktionstabelle T mit der Table Function Gather in
vertikale Form T*(item, tid-list).
Sortiere jene (item, tid-list) Tupel aus, welche die Minimum Support
Bedingung erfüllen  T* (TidTable).
Für jedes Item aus Ck suche die entsprechenden tid-lists aus T*.
Bestimme die Schnittmenge aller gesammelten tid-lists.
Ist die Grösse der Schnittmenge grösser als Minimum Support, so
haben wir ein Frequent Itemset gefunden.
Subquery Optimierung
Ausnutzen der Eigenschaft, dass tid-lists gemeinsame Präfixe haben.
 Rekursive Subquery
(äquivalent zum Subquery Verfahren mit SQL-92)
Vertical (SQL-OR)
Support Counting
TID
1
2
4
1
4
2
3
3
item
a
a
a
b
b
c
c
d
Gather
Count
item
a
b
c
d
tid-list Support
{1, 2, 4}
3
{1, 4}
2
{2, 3}
2
{3}
1
item-set tid-list support
{a, b} {1, 4}
2
{a, c}
{2}
1
{b, c}
{}
0
intersect, count
C2 = {{a, b}, {a, c}, {b, c}}
item-set
tid-list
{a, b} {{1, 2, 4}, {1, 4}}
{a, c} {{1, 2, 4}, {2, 3}}
{b, c}
{{1, 4}, {2, 3}}
Performanzvergleich der
SQL-OR Varianten
Gesamtperformanz
 Vertical
 Mit Abstand am schnellsten
 Transformation vertikale Form ist am
zeitaufwendigsten (Mammutanteil)
 Gather Join / Gather Count
 Sind langsamer als Vertical
 Untereinander liegen sie etwa gleich auf.
Performanzvergleich der
SQL-OR Varianten
 Durchgang 2
 Vertical
Das Transformieren der Transaktionstabelle kann
hier schlechte Resultate produzieren.
 Gather Join
Ist für kleine Support Werte meistens besser als
Vertical.
 Gather Count
Für grosse, durchschnittliche Anzahl Items pro
Transaktion ist es die einzig gute Lösung.
 Durchgänge > 3
Schlussfolgerungen
Durch etwas mehr Unterstützung vom DBMS
könnte Mining
effizienter
und einfacher implementiert
 Mengenoperationen
werden.
 Bilden aller möglichen Teilmengen

Schnittmengen berechnen
Probleme entstehen auch dadurch, dass in Subqueries kein GROUP BY
gemacht werden kann und keine Aggregation in rekursiven Queries
erlaubt ist.
 User Defined Aggregates (UDA)



Damit lassen sich die Subquery Probleme lösen
Bestandteil von SQL-3
Wird noch selten unterstützt
User Defined Aggregate
UDA, die sogar Zwischenresultate abliefern
können, sind noch einiges mächtiger, was in
anderen Mining-Varianten von grossem
 AGGREGATE FUNCTION
Nutzen
sein kann.
mvavg(value INTEGER)

RETURNS
(rvalue INTEGER)

INITIALIZE
(VALUES(value, 1))

ITERATE
(SELECT sum + value, count+1 FROM state)

PRODUCE
(SELECT sum / count FROM state WHERE count MOD 100 = 0)

TERMINATE
(SELECT sum / count FROM state)
Literatur
 Integrating Association Rule Mining with
Relational Database Systems: Alternatives
and Implications
Sunita Sarawagi, Shiby Thomas, Rakesh Agrawal
 User Defined Aggregates in Object-Relational
Systems
Haixun Wang, Carlo Zaniolo
 Objektverwaltung höherer Ordnung (OHO) SS
99
Prof. Dr. Ing. H.-J. Schek, Dr. Klemens Böhm und
das OHO-Team
 Datenbanksysteme II
Universität Leipzig, Prof. Dr. E. Rahm
Wie gut skalieren die Verfahren
Parameter der Testdaten
Datensätze aus real
existierenden
Datenbanken
Anzahl Records in
Millionen
Anzahl
Transaktionen in
Millionen
Anzahl Items in
Tausenden
Durchschnittliche
Anzahl Items pro
Transaktion
Datensatz A
2.5
6.6
0.57
0.21
85
15.8
4.4
31
Datensatz C