Bestimmte Datensätze ausschließen

Hallo,
ich versende SMSe an Kunden und möchte feststellen, wieviele Kunden auf
die erste oder zweite SMS reagiert haben.

Fall 1: Kunde erhält SMS 1 und ruft die in der SMS genannte Hotline an.
Fall 2: Kunde erhält SMS 1, ruft nicht an, ruft aber nach SMS 2 an.
Kunde 1 erhielt keine 2. SMS, da bereits angerufen

Ob ein Kunde nach einer bestimmten SMS angerufen hat, bewerte ich
dadurch, dass das Anrufdatum aktueller als das Datum der versendeten SMS
ist.

Das Problem ist nun, dass wenn ich die Kunden mit Rückruf ermitteln
möchte, die SMS 1 erhalten haben, auch die Kunden mit Rückruf in der
Ergebnismenge habe, die eine zweite SMS erhielten, da ja durch deren
Anruf nach SMS 2 das Anrufdatum aktueller ist, als das Versanddatum der
ersten SMS.

Wie könnte ich diese Kunden voneinander trennen?

Ich verwendete zwei Tabellen, einmal eine Tabelle mit den
Kundenstammdaten, in der u.a. das Rückrufdatum gespeichert ist, dann die
zweite Tabelle, in der die versendeten Kurznachrichten mit Datum
abgelegt sind. In etwa so:

customer
+-----------------+
| date_time |
| phone |
| transfer_status |
| calls_in_inc |
+-----------------+

sms_out
+-----------------+
| phone |
| sms_date |
| transfer_status |
| sms_out_inc |
+-----------------+

SELECT COUNT(a.phone) AS cnt
FROM sms_out AS a,
customer AS b
WHERE a.phone=b.phone
AND a.transfer_status = 1
AND a.sms_date < b.date_time

transfer_status 1 in der Tabelle sms_out (a) bedeutet, dass SMS 1
versendet wurde.

Im Moment rufe ich diesen SELECT zweimal auf für transfer_status 1 und 2
und substrahiere Ergebnis 2 von 1, da das erste Ergebnis ja beide
Kundengruppen enthält.

Hat jemand eine Idee, wie man das besser lösen könnte?

Gruß,
Thomas

Re: Bestimmte Datensätze ausschließen

Thomas Barth schrieb:
>
> customer
> +-----------------+
> | date_time |
> | phone |
> | transfer_status |
> | calls_in_inc |
> +-----------------+
>
> sms_out
> +-----------------+
> | phone |
> | sms_date |
> | transfer_status |
> | sms_out_inc |
> +-----------------+
>
> SELECT COUNT(a.phone) AS cnt
> FROM sms_out AS a,
> customer AS b
> WHERE a.phone=b.phone
> AND a.transfer_status = 1
> AND a.sms_date < b.date_time
>
> transfer_status 1 in der Tabelle sms_out (a) bedeutet, dass SMS 1
> versendet wurde.

Ohne groß drüber nachgedacht zu haben: ein GROUP BY a.transfer_status
sollte hier doch helfen. Oder hab ich mich jetzt verguckt? In etwa so:
SELECT COUNT(a.phone) AS cnt, a.transfer_status
FROM sms_out AS a,
customer AS b
WHERE a.phone=b.phone
AND a.sms_date < b.date_time
GROUP BY a.transfer_status

> Im Moment rufe ich diesen SELECT zweimal auf für transfer_status 1 und 2
> und substrahiere Ergebnis 2 von 1, da das erste Ergebnis ja beide
> Kundengruppen enthält.

Das habe ich jetzt nicht verstanden. Entweder ist transfer_status = 1,
dann ist nur die erste SMS rausgegangen, oder der transfer_status = 2,
dann sind beide SMS raus gegangen. Korrekterweise müßtest du also
Ergebnis 1 von Ergebnis 2 abziehen.

Grüße,
Dominik

Re: Bestimmte Datensätze ausschließen

Dominik Echterbruch wrote:
> Thomas Barth schrieb:
>>
>> customer
>> +-----------------+
>> | date_time |
>> | phone |
>> | transfer_status |
>> | calls_in_inc |
>> +-----------------+
>>
>> sms_out
>> +-----------------+
>> | phone |
>> | sms_date |
>> | transfer_status |
>> | sms_out_inc |
>> +-----------------+
>>
>> SELECT COUNT(a.phone) AS cnt
>> FROM sms_out AS a,
>> customer AS b
>> WHERE a.phone=b.phone
>> AND a.transfer_status = 1
>> AND a.sms_date < b.date_time
>>
>> transfer_status 1 in der Tabelle sms_out (a) bedeutet, dass SMS 1
>> versendet wurde.
>
> Ohne groß drüber nachgedacht zu haben: ein GROUP BY a.transfer_status
> sollte hier doch helfen. Oder hab ich mich jetzt verguckt? In etwa so:
> SELECT COUNT(a.phone) AS cnt, a.transfer_status
> FROM sms_out AS a,
> customer AS b
> WHERE a.phone=b.phone
> AND a.sms_date < b.date_time
> GROUP BY a.transfer_status

Geht ja nicht, weil der Kunde, der SMS 1 und 2 erhalten hat, auch in der
Ergebnismenge 1 ist, da das Datum seines Rückrufs ebenfalls aktueller
als das Versanddatum von SMS 1 ist. Er soll möglichst aber nur in der
Ergebnismenge 2 erscheinen.

>
>> Im Moment rufe ich diesen SELECT zweimal auf für transfer_status 1 und 2
>> und substrahiere Ergebnis 2 von 1, da das erste Ergebnis ja beide
>> Kundengruppen enthält.
>
> Das habe ich jetzt nicht verstanden. Entweder ist transfer_status = 1,
> dann ist nur die erste SMS rausgegangen, oder der transfer_status = 2,
> dann sind beide SMS raus gegangen. Korrekterweise müßtest du also
> Ergebnis 1 von Ergebnis 2 abziehen.
>

Da Ergebnismenge 1 größer als Ergebnismenge 2 ist, ziehe ich doch 2 von
1 ab?

Im Grunde muss ich die Kunden mit SMS 1 und 2 ausschließen können. Suche
alle Rückrufer mit SMS 1, aber nicht die Rückrufer, die nach SMS 2
zurückgerufen haben. Vielleicht muss ich auch das DB-Design noch einmal
überdenken.

Gruß,
Thomas

Re: Bestimmte Datensätze ausschließen

On Wed, 18 Apr 2007 21:12:49 +0200, Thomas Barth
wrote:

>Dominik Echterbruch wrote:
>> Thomas Barth schrieb:
>>>
>>> customer
>>> +-----------------+
>>> | date_time |
>>> | phone |
>>> | transfer_status |
>>> | calls_in_inc |
>>> +-----------------+
>>>
>>> sms_out
>>> +-----------------+
>>> | phone |
>>> | sms_date |
>>> | transfer_status |
>>> | sms_out_inc |
>>> +-----------------+
>>>
>>> SELECT COUNT(a.phone) AS cnt
>>> FROM sms_out AS a,
>>> customer AS b
>>> WHERE a.phone=b.phone
>>> AND a.transfer_status = 1
>>> AND a.sms_date < b.date_time
>>>
>>> transfer_status 1 in der Tabelle sms_out (a) bedeutet, dass SMS 1
>>> versendet wurde.
>>
>> Ohne groß drüber nachgedacht zu haben: ein GROUP BY a.transfer_status
>> sollte hier doch helfen. Oder hab ich mich jetzt verguckt? In etwa so:
>> SELECT COUNT(a.phone) AS cnt, a.transfer_status
>> FROM sms_out AS a,
>> customer AS b
>> WHERE a.phone=b.phone
>> AND a.sms_date < b.date_time
>> GROUP BY a.transfer_status
>
>Geht ja nicht, weil der Kunde, der SMS 1 und 2 erhalten hat, auch in der
>Ergebnismenge 1 ist, da das Datum seines Rückrufs ebenfalls aktueller
>als das Versanddatum von SMS 1 ist. Er soll möglichst aber nur in der
>Ergebnismenge 2 erscheinen.
>
>>
>>> Im Moment rufe ich diesen SELECT zweimal auf für transfer_status 1 und 2
>>> und substrahiere Ergebnis 2 von 1, da das erste Ergebnis ja beide
>>> Kundengruppen enthält.
>>
>> Das habe ich jetzt nicht verstanden. Entweder ist transfer_status = 1,
>> dann ist nur die erste SMS rausgegangen, oder der transfer_status = 2,
>> dann sind beide SMS raus gegangen. Korrekterweise müßtest du also
>> Ergebnis 1 von Ergebnis 2 abziehen.
>>
>
>Da Ergebnismenge 1 größer als Ergebnismenge 2 ist, ziehe ich doch 2 von
>1 ab?
>
>Im Grunde muss ich die Kunden mit SMS 1 und 2 ausschließen können. Suche
>alle Rückrufer mit SMS 1, aber nicht die Rückrufer, die nach SMS 2
>zurückgerufen haben. Vielleicht muss ich auch das DB-Design noch einmal
>überdenken.
>
>Gruß,
>Thomas

so etwas?

SELECT COUNT(a.phone) AS cnt
FROM sms_out AS a,
customer AS b
WHERE a.phone=b.phone
AND a.transfer_status = 1
AND a.sms_date < b.date_time
AND a.phone NOT IN
(SELECT phone FROM sms_out WHERE transfer_status = 2)

gibt aber bestimmt noch eine bessere lösung!

cu stephan

cu Stephan

Re: Bestimmte Datensätze ausschließen

Thomas Barth schrieb:
>
> Geht ja nicht, weil der Kunde, der SMS 1 und 2 erhalten hat, auch in der
> Ergebnismenge 1 ist

Wieso? Die Spalte tansfer_status kann doch nur entweder 1 ODER 2 sein,
aber nie beides gleichzeitig. Wie sollen da die Kunden, die nach der
ersten SMS reagiert haben in der Menge derer vor kommen, die nach der
zweiten SMS erst reagiert haben? Bis auf eine kleine Menge Kunden, die
aufgrund sich überschneidender Aktionen anrufen, während die zweite SMS
raus geht.

Vielleicht helfen mir ein paar BEispieldaten auf die Sprünge. Ich habe
scheinbar noch nicht verstanden, wann welche Daten in die Tabellen
geschrieben werden udn welche Werte welche Spalte nach welcher SMS annimmt.

Grüße,
Dominik

Re: Bestimmte Datensätze ausschließen

On Wed, 18 Apr 2007 22:05:24 +0200, Dominik Echterbruch
wrote:

>Thomas Barth schrieb:
>>
>> Geht ja nicht, weil der Kunde, der SMS 1 und 2 erhalten hat, auch in der
>> Ergebnismenge 1 ist
>
>Wieso? Die Spalte tansfer_status kann doch nur entweder 1 ODER 2 sein,
>aber nie beides gleichzeitig. Wie sollen da die Kunden, die nach der
>ersten SMS reagiert haben in der Menge derer vor kommen, die nach der
>zweiten SMS erst reagiert haben? Bis auf eine kleine Menge Kunden, die
>aufgrund sich überschneidender Aktionen anrufen, während die zweite SMS
>raus geht.

Ich denke da tritt das Problem auf, das ja bevor sms 2 versendet wird,
grundsätzlich sms 1 schon versand ist, die dann wohl auch nicht
entfernt wird, wenn ich die Struktur so richtig interpretiere!?
Womit dann bei a.transfer_status = 1 grundsätzilch die Menge derer
die sms 2 erhalten haben immer mit enthalten ist, da
a.transfer_status = 1 Vorraussetzung für a.transfer_status = 2 ist.

>
>Vielleicht helfen mir ein paar BEispieldaten auf die Sprünge. Ich habe
>scheinbar noch nicht verstanden, wann welche Daten in die Tabellen
>geschrieben werden udn welche Werte welche Spalte nach welcher SMS annimmt.
>
>Grüße,
>Dominik

cu Stephan

Re: Bestimmte Datensätze ausschließen

Dominik Echterbruch wrote:
> Thomas Barth schrieb:
>>
>> Geht ja nicht, weil der Kunde, der SMS 1 und 2 erhalten hat, auch in der
>> Ergebnismenge 1 ist
>
> Wieso? Die Spalte tansfer_status kann doch nur entweder 1 ODER 2 sein,
> aber nie beides gleichzeitig. Wie sollen da die Kunden, die nach der
> ersten SMS reagiert haben in der Menge derer vor kommen, die nach der
> zweiten SMS erst reagiert haben? Bis auf eine kleine Menge Kunden, die
> aufgrund sich überschneidender Aktionen anrufen, während die zweite SMS
> raus geht.
>

Ja, aber es existieren beim Kunden mit SMS 1 und 2 zwei Datensätze, bei
denen dann das Versanddatum älter als das Datum seines Rückrufes nach
SMS 2 ist. Die Zeit seines Anrufes wird in der Stammdatentabelle geführt.


> Vielleicht helfen mir ein paar BEispieldaten auf die Sprünge. Ich habe
> scheinbar noch nicht verstanden, wann welche Daten in die Tabellen
> geschrieben werden udn welche Werte welche Spalte nach welcher SMS annimmt.
>
> Grüße,
> Dominik

Hier ein Auszug aus meinen beiden Tabellen:

Fall 1

Stammdaten
+---------------------+--------------+-----------------+
| date_time | caller_id | transfer_status |
+---------------------+--------------+-----------------+
| 2007-03-07 20:29:14 | 0160XXXXXXXX | 1 |


Erstes Feld ist das Datum des letzten Anrufes, zweites Feld
Telefonnummer, drittes Feld sagt aus, dass Kunde bisher eine SMS
erhalten hat. Der Status kann aber hier jeder Zeit auch -1 werden, wenn
Kunde keine SMS mehr möchte. Außerdem stelle ich damit sicher, dass
Kunde keine SMS zweimal erhält. Wenn 1 dann nehme 2.

Versendete SMS
+---------------------+--------------+-----------------+
| sms_date | caller_id | transfer_status |
+---------------------+--------------+-----------------+
| 2007-03-06 14:52:48 | 0160XXXXXXXX | 1 |
+---------------------+--------------+-----------------+

SMS 1 wurde am an versendet und Kunde rief am 2007-03-07 20:29:14 zurück.


Fall 2

Stammdaten (customer)
+---------------------+--------------+-----------------+
| date_time | caller_id | transfer_status |
+---------------------+--------------+-----------------+
| 2007-04-18 20:29:14 | 0160XXXXXXXX | -1 |

Versendete SMS (sms_out)
+---------------------+--------------+-----------------+
| sms_date | caller_id | transfer_status |
+---------------------+--------------+-----------------+
| 2007-03-06 14:53:13 | 0160XXXXXXXX | 1 |
+---------------------+--------------+-----------------+
| 2007-04-18 09:18:43 | 0160XXXXXXXX | 2 |
+---------------------+--------------+-----------------+

Hier hat Kunde zwei SMS erhalten und nach SMS angerufen, zudem wird
keine weitere SMS gewünscht.

SELECT COUNT(a.caller_id) AS cnt
FROM sms_out AS a,
customer AS b
WHERE a.caller_id=b.caller_id
AND a.transfer_status = 1
AND a.sms_date < b.date_time

Obwohl ich versucht habe mit AND a.transfer_status = 1 die Menge und mit
dem Datumsvergleich einzugrenzen, war Datensatz mit transfer_status 1
ebenfalls in der Menge, da date_time aktueller als sms_date in beiden
Datensätzen. Das war mein Problem.

Gruß,
Thomas

Re: Bestimmte Datensätze ausschließen

Stephan Menzel wrote:

>
> so etwas?
>
> SELECT COUNT(a.phone) AS cnt
> FROM sms_out AS a,
> customer AS b
> WHERE a.phone=b.phone
> AND a.transfer_status = 1
> AND a.sms_date < b.date_time
> AND a.phone NOT IN
> (SELECT phone FROM sms_out WHERE transfer_status = 2)
>
> gibt aber bestimmt noch eine bessere lösung!
>

Danke, es funktioniert wunderbar! Es ärgert mich nun ein wenig, dass ich
nicht selbst darauf gekommen bin, da ich NOT IN und Subselects kenne :-)
Irgendwann wird aber noch ein weiteres Problem hinzukommen. Dann bekommt
auch der Kunde eine zweite SMS, der nach der ersten SMS zurückgerufen
hat. Aber das hat noch Zeit :)

Gruß,
Thomas

Re: Bestimmte Datensätze ausschließen

Stephan Menzel schrieb:
>
>>> Geht ja nicht, weil der Kunde, der SMS 1 und 2 erhalten hat, auch in der
>>> Ergebnismenge 1 ist
>> Wieso? Die Spalte tansfer_status kann doch nur entweder 1 ODER 2 sein,
>> aber nie beides gleichzeitig. Wie sollen da die Kunden, die nach der
>> ersten SMS reagiert haben in der Menge derer vor kommen, die nach der
>> zweiten SMS erst reagiert haben? Bis auf eine kleine Menge Kunden, die
>> aufgrund sich überschneidender Aktionen anrufen, während die zweite SMS
>> raus geht.
>
> Ich denke da tritt das Problem auf, das ja bevor sms 2 versendet wird,
> grundsätzlich sms 1 schon versand ist, die dann wohl auch nicht
> entfernt wird, wenn ich die Struktur so richtig interpretiere!?
> Womit dann bei a.transfer_status = 1 grundsätzilch die Menge derer
> die sms 2 erhalten haben immer mit enthalten ist, da
> a.transfer_status = 1 Vorraussetzung für a.transfer_status = 2 ist.

Ach so, du meinst, daß kein UPDATE stattfindet, wenn die zweite SMS raus
geht, sondern immer nur per INSERT ein weiterer Datensatz hinzugefügt
wird? Das würde natürlich dann erklären, warum bei transfer_status = 1
immer zu viele Datensätze kommen.

Aber dieses Problem kann man ja per Selfjoin leicht umschiffen:
SELECT COUNT(*) AS cnt, IF(c.phone IS NULL, 1, 2) AS anz_sms
FROM sms_out AS a
INNER JOIN customer AS b ON b.phone = a.phone AND a.transfer_status = 1
LEFT JOIN sms_out AS c ON c.phone = a.phone AND c.transfer_status = 2
WHERE a.sms_date < c.date_time
GROUP BY 2;

Ich bin mir nicht sicher, ob die Bedingung a.sms_date < c.date_time
jetzt noch wichtig ist. Das möge der OP bitte selber testen. Ebenso, ob
die o.g. Variante wirklich schneller ist, als zwei Selects abzufeuern
und zu subtrahieren.

Grüße,
Dominik

Re: Bestimmte Datensätze ausschließen

Thomas Barth schrieb:
>
>> SELECT COUNT(a.phone) AS cnt
>> FROM sms_out AS a,
>> customer AS b
>> WHERE a.phone=b.phone
>> AND a.transfer_status = 1
>> AND a.sms_date < b.date_time
>> AND a.phone NOT IN
>> (SELECT phone FROM sms_out WHERE transfer_status = 2)
>
> Danke, es funktioniert wunderbar!

Schön :) Vielleicht interessiert dich die Lösung aus meinem Post von
23:09 Uhr trotzdem.

Grüße,
Dominik