BoOnOdY hat geschrieben:habe richtig verstanden, dass man eigentlich gar keine 3te Tabelle braucht um diesen Vergleich auszuführen?
Hi Tim!
Wenn bei jedem Anruf der Name mitgespeichert wird, dann braucht es die dritte Tabelle nicht. Wie Tabellar bereits aufgezeigt hat, gibt es dann aber die Information, wer nie angerufen hat (0 mal), auch nicht. Ich kenne deine Anwendung nicht, deshalb gehe ich vorerst mal davon aus, dass diese Information auch nicht wichtig ist.
Aber, um auch diese Information zurück zu geben, habe ich hier noch ein Beispiel. Dieses Beispiel geht **NICHT** davon aus, dass jeder Anruf von einem registrierten Anrufer kommt. Dadurch wird zwar auch diese Information aufgezeigt, aber die Datenbank muss dadurch auch mehr arbeiten um diese Information herauszubekommen.
Bei mehreren Tausend Anrufen pro Tag, sollte man sich eine bessere Struktur oder einen Archivierungsplan überlegen. PostgreSQL bietet zum Glück alles was man dazu braucht. (Auch wenn es ein wenig komplizierter wird.)
Code: Alles auswählen
#!/usr/bin/env python -O
# -*- coding: iso-8859-1 -*-
import psycopg2
# Verbinden
conn = psycopg2.connect(
user = "testuser", password = "testuser", database = "testdb"
)
cur = conn.cursor()
# locations-Tabelle (neu) erstellen
sql = """
SELECT
EXISTS(SELECT tablename FROM pg_tables WHERE tablename = 'locations')
"""
cur.execute(sql)
if cur.fetchone()[0]:
sql = "DROP TABLE locations CASCADE"
cur.execute(sql)
conn.commit()
sql = """
CREATE TABLE locations (
searchstring TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL
)
"""
cur.execute(sql)
conn.commit()
# locations-Tabelle mit Demodaten füllen
sql = """
INSERT INTO locations (searchstring, name) VALUES (%s, %s)
"""
cur.executemany(
sql,
(
("", u"<UNKNOWN LOCATION>"),
("0049", u"Deutschland"),
("0043", u"Österreich"),
("00435262", u"Telfs, Pfaffenhofen, Oberhofen im Inntal; Tirol; Österreich"),
("0043512", u"Innsbruck; Tirol; Österreich"),
("0043699", u"ONE-Mobil; Österreich"),
)
)
conn.commit()
# calls-Tabelle (neu) erstellen
sql = """
SELECT
EXISTS(SELECT tablename FROM pg_tables WHERE tablename = 'calls')
"""
cur.execute(sql)
if cur.fetchone()[0]:
sql = "DROP TABLE calls CASCADE"
cur.execute(sql)
conn.commit()
sql = """
CREATE TABLE calls (
id SERIAL PRIMARY KEY NOT NULL,
number TEXT NOT NULL
);
CREATE INDEX ix_calls_number ON calls(number);
"""
cur.execute(sql)
conn.commit()
# calls-Tabelle mit Demodaten füllen
sql = """
INSERT INTO calls (number) VALUES (%s)
"""
cur.executemany(
sql,
(
("0043512552233",), # Innsbruck
("0043699123456",), # ONE-Mobil
("0049888888",), # Deutschland
("00435262665544",), # Telfs
("00435262445566",), # Oberhofen
("00445262445566",), # keine Ahnung
("0043512552233",), # Innsbruck
("0043699556688",), # ONE-Mobil
("0049888888",), # Deutschland
("0049888888",), # Deutschland
("004977668855",), # Deutschland
("004977668855",), # Deutschland
("004977668855",), # Deutschland
("004977668855",), # Deutschland
("004978688858",), # Deutschland
("004978688858",), # Deutschland
("004978688858",), # Deutschland
)
)
conn.commit()
# contacts-Tabelle (neu) erstellen
sql = """
SELECT
EXISTS(SELECT tablename FROM pg_tables WHERE tablename = 'contacts')
"""
cur.execute(sql)
if cur.fetchone()[0]:
sql = "DROP TABLE contacts CASCADE"
cur.execute(sql)
conn.commit()
sql = """
CREATE TABLE contacts (
number TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL
)
"""
cur.execute(sql)
conn.commit()
# contacts-Tabelle mit Demodaten füllen
sql = """
INSERT INTO contacts (number, name) VALUES (%s, %s)
"""
cur.executemany(
sql,
(
("0043512552233", u"Peter Müller"), # Innsbruck
("0043699123456", u"Walter Müller"), # ONE-Mobil
("0049888888", u"Gertraud Holly"), # Deutschland
("00435262665544", u"Gertraud Wolli"), # Telfs
("00435262445566", u"Thomas Wolli"), # Oberhofen
("00445262445566", u"Thomas Hauser"), # keine Ahnung
("0043699556688", u"Peter Müller"), # ONE-Mobil
("0043699556644", u"Walter Nase"), # ONE-Mobil
("0043699556633", u"Sabine Muster"), # ONE-Mobil
)
)
conn.commit()
# VIEW "calls_view" (neu) erstellen
sql = """
SELECT
EXISTS(SELECT viewname FROM pg_views WHERE viewname = 'calls_view')
"""
cur.execute(sql)
if cur.fetchone()[0]:
sql = "DROP VIEW calls_view"
cur.execute(sql)
conn.commit()
sql = """
CREATE VIEW calls_view AS
SELECT
grouped_contacts.name,
grouped_contacts.number,
count(calls.*) as calls_count,
(
SELECT
name
FROM
locations
WHERE
grouped_contacts.number like(locations.searchstring || '%')
ORDER BY
char_length(locations.searchstring) DESC
LIMIT 1
) as location
FROM
(
SELECT DISTINCT
name,
number
FROM
(
SELECT
name,
number
FROM
contacts
UNION ALL
SELECT
'<UNKNOWN CONTACT>' as name,
calls.number
FROM
calls
WHERE
(calls.number NOT IN (SELECT contacts.number FROM contacts))
) united_contacts
) grouped_contacts
LEFT OUTER JOIN
calls
ON
grouped_contacts.number = calls.number
GROUP BY
grouped_contacts.name,
grouped_contacts.number
"""
cur.execute(sql)
conn.commit()
# Anzeigen
sql = """
SELECT
calls_count, name, number, location
FROM
calls_view
ORDER BY
calls_count DESC, name, number
"""
cur.execute(sql)
for row in cur.fetchall():
print row[0], repr(row[1]), row[2], repr(row[3]) # repr() wegen den Umlauten
cur.close()
conn.close()
mfg
Gerold
und ich bekomm anrufe von überall, geht da was mit variablen? weil ich muss ja d1 0049160 auch von d2 0049172 auseinander halten und so... 
