Weihnachtsgeschäft

Es ist November, das Weihnachtsgeschäft soll’s ‘rausreissen. Also schicken wir denjenigen Kunden eine Ansichtskarte an die letzte Lieferadresse, die seit zwei Jahren nichts gekauft haben, aber davor wenigstens dreimal etwas geordert haben. Jetzt Du, Programmierer …

Hier ist ein Ausschnitt der (My)Sql-Tabellen-Struktur, gefüllt mit Fantasie-Daten. Ich habe es für MySQL entwickelt, es sollte mit keinen oder wenigen Anpassungen auf andere RDBMS übertragbar sein.

CREATE TABLE adresse
(
   id int PRIMARY KEY NOT NULL,
   vorname varchar(45),
   name varchar(45),
   strasse varchar(80),
   plz varchar(10),
   ort varchar(20)
);

insert into adresse (id,vorname,name,strasse,plz,ort) values (1,'Peter','Mustermann','Musterstr.42','12345','Musterhausen');
insert into adresse (id,vorname,name,strasse,plz,ort) values (2,'Paul','Mustermann','Musterstr.43','12345','Musterhausen');
insert into adresse (id,vorname,name,strasse,plz,ort) values (3,'Erika','Musterfrau','Musterstr.44','12345','Musterhausen');
insert into adresse (id,vorname,name,strasse,plz,ort) values (4,'Erna','Normalverbraucher','Musterstr.45','12345','Musterhausen');
insert into adresse (id,vorname,name,strasse,plz,ort) values (5,'Erna','Normalverbraucher','Ottostr.45','12345','Musterhausen');

CREATE TABLE bestellung
(
   id int PRIMARY KEY NOT NULL,
   rechnungsadresse int DEFAULT 0 NOT NULL,
   lieferadresse int DEFAULT 0 NOT NULL,
   id_person int DEFAULT 0 NOT NULL,
   datum timestamp NOT NULL
);

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (1,1,1,1,'2010.12.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (2,1,1,1,'2011.01.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (3,1,1,1,'2011.02.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (4,1,1,1,'2011.12.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (5,1,1,1,'2012.01.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (6,1,1,1,'2012.12.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (7,2,2,2,'2010.12.02');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (8,2,2,2,'2011.02.01');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (9,3,3,3,'2011.02.03');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (10,3,3,3,'2011.12.03');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (11,3,3,3,'2012.03.03');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (12,3,3,3,'2012.12.03');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (13,4,4,4,'2011.05.04');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (14,4,4,4,'2011.07.04');
insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (15,5,4,4,'2011.09.04');

CREATE TABLE adresse

(

id int PRIMARY KEY NOT NULL,

vorname varchar(45),

name varchar(45),

strasse varchar(80),

plz varchar(10),

ort varchar(20)

);

insert into adresse (id,vorname,name,strasse,plz,ort) values (1,'Peter','Mustermann','Musterstr.42','12345','Musterhausen');

insert into adresse (id,vorname,name,strasse,plz,ort) values (2,'Paul','Mustermann','Musterstr.43','12345','Musterhausen');

insert into adresse (id,vorname,name,strasse,plz,ort) values (3,'Erika','Musterfrau','Musterstr.44','12345','Musterhausen');

insert into adresse (id,vorname,name,strasse,plz,ort) values (4,'Erna','Normalverbraucher','Musterstr.45','12345','Musterhausen');

insert into adresse (id,vorname,name,strasse,plz,ort) values (5,'Erna','Normalverbraucher','Ottostr.45','12345','Musterhausen');

CREATE TABLE bestellung

(

id int PRIMARY KEY NOT NULL,

rechnungsadresse int DEFAULT 0 NOT NULL,

lieferadresse int DEFAULT 0 NOT NULL,

id_person int DEFAULT 0 NOT NULL,

datum timestamp NOT NULL

);

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (1,1,1,1,'2010.12.01');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (2,1,1,1,'2011.01.01');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (3,1,1,1,'2011.02.01');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (4,1,1,1,'2011.12.01');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (5,1,1,1,'2012.01.01');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (6,1,1,1,'2012.12.01');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (7,2,2,2,'2010.12.02');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (8,2,2,2,'2011.02.01');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (9,3,3,3,'2011.02.03');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (10,3,3,3,'2011.12.03');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (11,3,3,3,'2012.03.03');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (12,3,3,3,'2012.12.03');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (13,4,4,4,'2011.05.04');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (14,4,4,4,'2011.07.04');

insert into bestellung (id,rechnungsadresse,lieferadresse,id_person,datum) values (15,5,4,4,'2011.09.04');

Zuerst finde heraus, wer wieoft bestellt hat und wann die letzte Bestellung stattfand – unter Bachtung der oben gegebenen Einschränkungen:

-- INTERVAL 22 MONTH
-- weil es erst November ist und wir den Januar auch "mitnehmen" möchten.

SELECT id_person, max(datum) as letzteBestellung, count(*) as anzahlBestellungen
FROM (
	SELECT id_person, datum
	FROM bestellung
) t1
group by id_person
having count(*) &gt;= 3 -- wenigstens 3 Bestellungen 
and max(datum) &lt; DATE_SUB(now(), INTERVAL 22 MONTH) -- und seit 22 Monaten ohne Umsatz

-- INTERVAL 22 MONTH

-- weil es erst November ist und wir den Januar auch "mitnehmen" möchten.

SELECT id_person, max(datum) as letzteBestellung, count(*) as anzahlBestellungen

FROM (

SELECT id_person, datum

FROM bestellung

) t1

group by id_person

having count(*) >= 3 -- wenigstens 3 Bestellungen

and max(datum) < DATE_SUB(now(), INTERVAL 22 MONTH) -- und seit 22 Monaten ohne Umsatz

Aufgrund dieser Daten suche ich die Rechnungsadresse – mit einem self-join:

SELECT distinct taba.id_person, tabb.letzteBestellung, tabb.anzahlBestellungen, taba.rechnungsadresse
FROM bestellung taba inner join
(
	SELECT id_person, max(datum) as letzteBestellung, count(*) as anzahlBestellungen
	FROM (
		SELECT id_person, datum
		FROM bestellung
	) t1
	group by id_person
	having count(*) &gt;= 3
	and max(datum) &lt; DATE_SUB(now(), INTERVAL 22 MONTH)
) tabb
on (taba.id_person = tabb.id_person
and tabb.letzteBestellung = taba.datum)

SELECT distinct taba.id_person, tabb.letzteBestellung, tabb.anzahlBestellungen, taba.rechnungsadresse

FROM bestellung taba inner join

(

SELECT id_person, max(datum) as letzteBestellung, count(*) as anzahlBestellungen

FROM (

SELECT id_person, datum

FROM bestellung

) t1

group by id_person

having count(*) >= 3

and max(datum) < DATE_SUB(now(), INTERVAL 22 MONTH)

) tabb

on (taba.id_person = tabb.id_person

and tabb.letzteBestellung = taba.datum)

Und dann brauche ich das nur noch mit den Adressen zu verbinden und habe das Ergebnis:

SELECT *
FROM (
	SELECT distinct taba.id_person, tabb.letzteBestellung, tabb.anzahlBestellungen, taba.rechnungsadresse
	FROM bestellung taba inner join
	(
		SELECT id_person, max(datum) as letzteBestellung, count(*) as anzahlBestellungen
		FROM (
			SELECT id_person, datum
			FROM bestellung
		) t1
		group by id_person
		having count(*) &gt;= 3
		and max(datum) &lt; DATE_SUB(now(), INTERVAL 22 MONTH)
	) tabb
	on (taba.id_person = tabb.id_person
	and tabb.letzteBestellung = taba.datum)
) ago inner join adresse
on ago.rechnungsadresse = adresse.id

SELECT *

FROM (

SELECT distinct taba.id_person, tabb.letzteBestellung, tabb.anzahlBestellungen, taba.rechnungsadresse

FROM bestellung taba inner join

(

SELECT id_person, max(datum) as letzteBestellung, count(*) as anzahlBestellungen

FROM (

SELECT id_person, datum

FROM bestellung

) t1

group by id_person

having count(*) >= 3

and max(datum) < DATE_SUB(now(), INTERVAL 22 MONTH)

) tabb

on (taba.id_person = tabb.id_person

and tabb.letzteBestellung = taba.datum)

) ago inner join adresse

on ago.rechnungsadresse = adresse.id

Die Bestelldaten sind auch für Menschen überschaubar. Peter bekommt keine Karte, weil er ein treuer Kunde ist und wir hoffen, dass er in diesem Jahr auch so wieder etwas bestellt. Paul bekommt keine Karte, weil er – nach unseren Masstäben – kein Interesse hat. Erika ergeht es wie Peter. Aber eine Karte in Erna zu investieren lohnt bestimmt!

Aufräumen nicht vergessen, weil das nur ein Bespiel ist.

drop table bestellung;
drop table adresse;

1 2	drop table bestellung; drop table adresse;

Und das SQL ist so, dass wir es im kommenden Jahr wieder verwenden können. Der Serienbrief-Druck kann beginnen.

Über den Sinn und Unsinn einer solchen Weihnachtskarten-Aktion kann man trefflich streiten. Erfolgskontrolle wird die Marketing-Abteilung freuen: Wer von den angeschriebenen hat wieder etwas bei uns gekauft? Und warum? War die Karte der Auslöser oder die Trennung vom Partner oder … was?

Neueste Beiträge

Archive

Kategorien

Meta