Vergleichen von 2 Spalten

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
BoOnOdY
User
Beiträge: 112
Registriert: Samstag 22. Juli 2006, 13:38

Vergleichen von 2 Spalten

Beitragvon BoOnOdY » Donnerstag 19. Oktober 2006, 14:17

Hy,
ich möchte 2 Zeilen aus 2 verschiedenen PostgreSQL-Tabellen vergleichen, das heißt :

Telefonnummer von Winfried: 004908001101

Nummer von Deutschland: 0049


Diese beiden Informationen stehen in verschiedenen Tabellen.

Ich will lesen:

Winfried Deutschland

Wie kann ich mit einer SQL-Methode oder einer anderen Methode in Zope das erreichen?

Oder sollte ich vieleicht eine neue Tabelle anlegen, dass würde einiges erleichtern.

Wie schreibe ich einen Pythonscript, dass die beiden daten aus der Anrufe tabelle mit der Ländertabelle vergleicht, und mir dann in eine neue Tabelle schreib, wer aus welchem Land mit welcher Nummer angerufen hat.

also die Informationen :
Name, Rufnummer, Land aus den Tabellen zusammen in eine neue Tabelle kommen?.

Oder kann ich einfach eine Procedure verwenden?


Jedoch gestaltet sich noch zusätzlich schwierig, dass ich zum beispielt auch die nummer:004916 für T mobile Deutschland vermerkt habe, und dann auch stehen haben möchte Winfried Tmobile Deutschland 4 mal
aber Winfried Deutschland 0 mal
weil er ja immer vom Handy angerufen hat.
Also müsstte man, immer alle Übereinstimmungen die länger sind, von den niedrigeren abziehen:
Anrufe aus Deutschland - Anrufe von T-Mobile, Anrufe von D2 usw.


Hab leider keinen richtigen Plan wie das gehen soll :(


Gruß Tim

ps: Bin leider noch nicht so geübt im Umgang mit Python und SQL.



Vielen Dank =)
Benutzeravatar
tabellar
User
Beiträge: 186
Registriert: Mittwoch 4. September 2002, 15:28

Beitragvon tabellar » Donnerstag 19. Oktober 2006, 20:12

Hi BoOnOdY,

es gibt viele Möglichkeiten Dein Problem zu lösen und vor allem kommt es
darauf an, wie Du den Datenvergleich weiterverarbeiten möchtest. Ob als
Daten in der DB oder als Pythonstruktur zum direkten (HTML) ausgeben...

Ich hab das ganze mal als relativ komplexe SQL Query abgebildet. Ich ging
mal davon aus, dass Du noch eine dritte Tabelle hast, in der die calls abgelegt
sind, sonst wüsstest Du ja auch nicht, ob Winfried 3x oder nur 1x angerufen
hat.

Da ich mich nicht mit Zope auskenne, habe ich hier den direkten Weg für
PostgreSQL beschrieben.

Hier die einfachen Tabellen:

Code: Alles auswählen

CREATE TABLE phones (
id   SERIAL PRIMARY KEY,
name VARCHAR(32),
nr   VARCHAR(32)
)

CREATE TABLE countries (
id   SERIAL PRIMARY KEY,
name VARCHAR(32),
nr   VARCHAR(32)
)

CREATE TABLE calls (
id   SERIAL PRIMARY KEY,
name VARCHAR(32),
nr   VARCHAR(32)
)


Damit man die 0049, 004916, etc. Nummern auseinander halten kann,
musste ich einen Trick anwenden. Durch regular expressions kann man
hier die Nummern genau voneinander trennen.

Code: Alles auswählen

Deutschland,              ^0049[2-8]
TMobile Deutschland,  ^004916


Durch entsprechende subselects, regex und count geschichten ergibt
sich dann das gewünschte Ergebnis (ja, PostgreSQL kann das alles )...

Code: Alles auswählen

SELECT p.nr,p.name,
(
SELECT c.name
FROM countries c
WHERE p.nr ~ c.nr
) AS trunk,
(
SELECT count(calls.nr)
FROM calls
WHERE calls.nr = p.nr
) AS calls

FROM phones p
ORDER BY calls DESC


Ergebnis:

Code: Alles auswählen

"0049160012345"; "Winfried";  "TMobile Deutschland"; 3
"00498001101";    "Winfried";  "Deutschland";             1
"004312345678";  "BoOnOdY"; "";                              0


Tabellar
Zuletzt geändert von tabellar am Donnerstag 19. Oktober 2006, 20:15, insgesamt 1-mal geändert.
BoOnOdY
User
Beiträge: 112
Registriert: Samstag 22. Juli 2006, 13:38

Beitragvon BoOnOdY » Donnerstag 19. Oktober 2006, 20:15

*g*
das probier ich gleihc morgen mal aus, sieht ganz plausiebel aus :) danke schon mal.

Will das alles mit Zope ausgeben, daher kommt das in ne normale SQL methode mit nem searchinterface.

Aber es gibt leider 3040 Vorwahlen auf dieser Welt ;) 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...

Hab 2 Tabellen,
in einer steht, Hans mit der nummer 0049800342 hat angerufen und in der anderen
0049= Deutschland
Benutzeravatar
tabellar
User
Beiträge: 186
Registriert: Mittwoch 4. September 2002, 15:28

Beitragvon tabellar » Donnerstag 19. Oktober 2006, 22:17

BoOnOdY hat geschrieben:*g*
... Aber es gibt leider 3040 Vorwahlen auf dieser Welt ;) 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...

Hab 2 Tabellen,
in einer steht, Hans mit der nummer 0049800342 hat angerufen und in der anderen
0049= Deutschland


Du scheinst ja ne nette PBX zu haben, die Dir Deine DB mit calls zumüllt ;-).
Wie schon oben beschrieben, habe ich in der Tabelle countries die Provider
(trunks) drinnen:

Du musst die Tabelle eben noch ergänzen:

Code: Alles auswählen

------------------------------------------------
NAME                          ,   NR
------------------------------------------------
Deutschland,               ^0049[2-8]
d1,                        ^0049160
d2,                        ^0049172
------------------------------------------------


Wo willst Du hier Variablen verwenden?
Edit:
Das ^-Zeichen kann man ev. auch weglassen und oben im select
mit dem || Operator "verketten". Aber die 0049[2-8] - das bedeutet
00492..., 00493..., - 00498... - das muss sein, denn sonst hast Du keinen
eindeutigen Treffer bei der Suche! Oder Du baust die Nummern eben
manuell mehrfach ein. Bei 3040 Vorwahlen kommt es dann auch nicht
mehr drauf an ... :D

Tabellar
BoOnOdY
User
Beiträge: 112
Registriert: Samstag 22. Juli 2006, 13:38

Beitragvon BoOnOdY » Freitag 20. Oktober 2006, 06:46

Hy, also such PostgreSQL immer nach den größten übereinstimmungen? weil ´d1 und d2 haben ja 00491 gemeinsam.
Ich geh erst ma ne Runde Joggen und dann meld ich mich noch ma hier ;) befass mich noch ma intensiv damit,
bis dahin, vielen dank :)

bis nachher :)
BoOnOdY
User
Beiträge: 112
Registriert: Samstag 22. Juli 2006, 13:38

Beitragvon BoOnOdY » Freitag 20. Oktober 2006, 09:44

Also ich glaube, das wird etwas schwieriger, denn das hat keine feste logig dahinter, man müsste also sagen:

Vorwahldatensatz mit Telefonnummerdatensatz vergleichen, der mit den meisten Übereinstimmungen wird benutzt

Denke mal am einfachsten klappt das mit ner neuen Tabelle, speicher is egal, hab ich genug

Tabelle 1.

primarykey;10.05.2006;16:00:44:485;5;1;441582526619;2306745425;;CAU_NUNSP

an der roten Zahl sieht man von wo mein anrufer mich anruft.
Tabelle2.
Vorwahl;Land

und die neue Tabelle soll dann einfach
primarykey;10.05.2006;16:00:44:485;5;1;441582526619;2306745425;10;CAU_NUNSP;Land


Gibts da auch nen Weg ? von wegen meiste übereinstimmung und so?


Gruß Tim :)
BoOnOdY
User
Beiträge: 112
Registriert: Samstag 22. Juli 2006, 13:38

Beitragvon BoOnOdY » Freitag 20. Oktober 2006, 20:41

Ich hätte ne idee, müsst ich aber was zu wissen.

Einfach ein Tool schreiben, dass aus der Tabelle mit den Ländercodes, eine abfrage genneriert.

Die dann so aussehen muss, dass er zuerst sucht, mit welchen nummern er übereinstimmt, also

Suchen nach Deutschland, und wenn call aus deutschland, gucken, ob anderer eintrag auch mit dem übereinstimmt, und wenn dem nicht der fall in tabelle 3 eintragen, er kommt aus Deutschland, ansonsten eintragen wo er sonst hekommt.
also so ne abfrage in 2 ebenen.

Wäre das irgendwie ermöglichbar?
Weiß ja net ob das jetzt ne total hirnrissige idee is sich einfach einen so langen quelltext generiern zu lassen, aber vieleicht gehts ja irgendwie :)

Würd mich tierisch freun.

hab mir überlegt das müsste ja dann so aussehen

Select* from call, destination
where call.anrufernummer=destination.vowahl(aktuelle in der gerade durchlaufenen spalte)%
select* from destination
where destination.vorwahl=destination.vowahl(aktuelle in der gerade durchlaufenen spalte)
[außer die schon gefundene]

(vergleichen von allen gefunden vorwahlen mit der Telefonummer und dann nimmt er die, bei der er, wenn er die Suche macht, nur noch eine Möglichkeit findet)

Also müsste da ne schleife drumm "mach es so lange, bis du deine Spalte aus der Anruferliste einer Spalte aus der destinantionsliste zuordnen kannst"

und dann schreibe alle daten aus der ersten tabelle, zusammen mit dem Land in eine zweite Tabelle.

weui0ßt net ob der denkansatz was bringt, hoffe schon :)


Gruß Tim
Benutzeravatar
gerold
Python-Forum Veteran
Beiträge: 5554
Registriert: Samstag 28. Februar 2004, 22:04
Wohnort: Telfs (Tirol)
Kontaktdaten:

Beitragvon gerold » Freitag 20. Oktober 2006, 23:28

Hallo Tim!

Die Anzahl der Stellen grenzt normalerweise eine Telefonnummer ein. "0043" bedeutet immer "Österreich". Es gibt keine Telefonnummer, die so beginnt und nicht für Österreich steht. So ist es auch mit den Vorwahlen. Diese sind wahrscheinlich so gewählt, dass sie sich nicht überschneiden können. Also ist es so, dass wenn ich mehrere Übereinstimmungen mit dem Suchstring habe, dann ist die Übereinstimmung mit dem **längsten** Suchstring auch die **genaueste**. Anders kann ich mir das bei Telefonnummern im Moment nicht vorstellen.

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()

# 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,
  name TEXT NOT NULL,
  number TEXT NOT NULL
);

CREATE INDEX ix_calls_number ON calls(number);
"""
cur.execute(sql)
conn.commit()

# locations-Tabelle mit Demodaten füllen
sql = """
INSERT INTO locations (searchstring, name) VALUES (%s, %s)
"""
cur.executemany(
    sql,
    (
        ("", u"Unbekannt"),
        ("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 mit Demodaten füllen
sql = """
INSERT INTO calls (name, number) VALUES (%s, %s)
"""
cur.executemany(
    sql,
    (
        (u"Peter Müller", "0043512552233"), # Innsbruck
        (u"Walter Müller", "0043699123456"), # ONE-Mobil
        (u"Gertraud Holly", "0049888888"), # Deutschland
        (u"Gertraud Wolli", "00435262665544"), # Telfs
        (u"Thomas Wolli", "00435262445566"), # Oberhofen
        (u"Thomas Hauser", "00445262445566"), # keine Ahnung
        (u"Peter Müller", "0043512552233"), # Innsbruck
        (u"Peter Müller", "0043699556688"), # ONE-Mobil
        (u"Gertraud Holly", "0049888888"), # Deutschland
        (u"Gertraud Holly", "0049888888"), # Deutschland
    )
)
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
    name,
    number,
    (
      SELECT
        name
      FROM
        locations
      WHERE
        grouped_calls.number like(locations.searchstring || '%')
      ORDER BY
        char_length(locations.searchstring) DESC
      LIMIT 1
    ) as location,
    calls_count
  FROM
    (
      SELECT
        name,
        number,
        count(*) as calls_count
      FROM
        calls
      GROUP BY
        name,
        number
    ) AS grouped_calls
"""
cur.execute(sql)
conn.commit()

# Anzeigen
sql = """
SELECT
  calls_count, name, number, location
FROM
  calls_view
ORDER BY
  calls_count, 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
:-)
Zuletzt geändert von gerold am Samstag 21. Oktober 2006, 08:51, insgesamt 2-mal geändert.
http://halvar.at | Kleiner Bascom AVR Kurs
Wissen hat eine wunderbare Eigenschaft: Es verdoppelt sich, wenn man es teilt.
BoOnOdY
User
Beiträge: 112
Registriert: Samstag 22. Juli 2006, 13:38

Beitragvon BoOnOdY » Freitag 20. Oktober 2006, 23:42

Hy, ich probier das gleich morgen mal aus. Meld mich dann noch mal,

Ja es gibt immer nur eine Vorwahl die Stimmt

es soll halt bei 004916 anzeigen T-Mobile
und nicht Deutschland, das war mir nur wichtig.


Weil ich halt den eintrag
0049; Deutschland
und
004916 T-Mobile habe
und da soll dann in der neunen tabelle nur stehen "T-Mobile" und nicht Deutschland, weil ja in Deutschland auch 0049 drinne is, aber bei T-Mobile ist halt Deutschland und T-mobile drinne .. und ich will nur das eindeutige Ergebnis wissen, also wo genau der anruf her kommt, also wo die meisten übereinstimmungen sind, der rest ist mir egal
sol nur T-Mobile stehen und nichts anderes.

Aber schon ma vielen dank :) ich meld mich dan noch ma deswegen, guck mir das ma an, wenn meine beschreibung jetzt was gändert hat, verstehste vieleicht jetzt meine "hersauforderung" ;)

Wenn das so in einen SQL-Befehl passt, dann is das ja perfekt, dann kann nich das ja auch in Zope benutzen :)

Werd mich ma mit dem "view" beschäftigen.
Aber ich habe richtig verstanden, dass man eigentlich gar keine 3te Tabelle braucht um diesen Vergleich auszuführen `? oder habe ich was falsch verstanden?


Viele Grüße Tim
Benutzeravatar
tabellar
User
Beiträge: 186
Registriert: Mittwoch 4. September 2002, 15:28

Re: Vergleichen von 2 Spalten

Beitragvon tabellar » Samstag 21. Oktober 2006, 19:04

BoOnOdY hat geschrieben:...Jedoch gestaltet sich noch zusätzlich schwierig, dass ich zum beispielt auch die nummer:004916 für T mobile Deutschland vermerkt habe, und dann auch stehen haben möchte Winfried Tmobile Deutschland 4 mal
aber Winfried Deutschland 0 mal
weil er ja immer vom Handy angerufen hat.


Wenn Du die Information "Winfried Deutschland - 0 mal" haben möchtest,
dann brauchst Du eine Telefonnummerntabelle, die Du mit der "calls" Tabelle vergleichen kannst. Das GROUP BY statement in der "calls"
Tabelle von Gerold kann nur Zahlen >= 1 ergeben! Ausserdem wird die
Ausgabe Winfried - Deutschland und Winfried - D1 mit einem GROUP
calls.name auch schwierig ... :)

BoOnOdY hat geschrieben:...Also ich glaube, das wird etwas schwieriger, denn das hat keine feste logig dahinter, man müsste also sagen:
Vorwahldatensatz mit Telefonnummerdatensatz vergleichen, der mit den meisten Übereinstimmungen wird benutzt


Nochmals, befass Dich mal bitte mit dem Thema "Reguläre Ausdrücke" im
Allgemeinen und in Postgres. Die Logik ist doch in den Telefonnummern
an sich gegeben. Deutsche Festnetznummern sind 00492xxx, 00493xxx, etc.
Anbieter im deutschen Mobilnetzen sind 0049160xxx D1, 0049172xxx - D2, etc.
In Österreich gilt ein ähnliche Logik. Durch entsprechende "Text-
muster" kann man so den Provider herausfiltern (s.Beitrag oben). Es gibt
entsprechende PBXe, z.B. Asterisk, da läuft das ganze Routing so ...
:wink:

Tabellar
Benutzeravatar
gerold
Python-Forum Veteran
Beiträge: 5554
Registriert: Samstag 28. Februar 2004, 22:04
Wohnort: Telfs (Tirol)
Kontaktdaten:

Beitragvon gerold » Samstag 21. Oktober 2006, 21:30

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
:-)
http://halvar.at | Kleiner Bascom AVR Kurs

Wissen hat eine wunderbare Eigenschaft: Es verdoppelt sich, wenn man es teilt.
Benutzeravatar
gerold
Python-Forum Veteran
Beiträge: 5554
Registriert: Samstag 28. Februar 2004, 22:04
Wohnort: Telfs (Tirol)
Kontaktdaten:

Beitragvon gerold » Samstag 21. Oktober 2006, 21:54

Wenn man davon ausgehen kann, dass jeder Anrufer in die Tabelle ``contacts`` eingetragen wird, dann kann man sich einen Teil der Abfrage sparen und die Anwendung wird schneller.

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()

# 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()

# 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 REFERENCES contacts (number) /* Beziehung zur Tabelle 'contacts' */
);

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
    )
)
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
    contacts.name,
    contacts.number,
    count(calls.*) as calls_count,
    (
      SELECT
        name
      FROM
        locations
      WHERE
        contacts.number like(locations.searchstring || '%')
      ORDER BY
        char_length(locations.searchstring) DESC
      LIMIT 1
    ) as location
  FROM
    contacts
  LEFT OUTER JOIN
    calls
  ON
    contacts.number = calls.number
  GROUP BY
    contacts.name,
    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()

Man achte auf die Beziehung zwischen der ``contacts``-Tabelle und der ``calls``-Tabelle (REFERENCES). Diese stellt sicher, dass kein Anruf eingetragen werden kann, der nicht vorher als Kontakt eingetragen wurde. So spart man sich beim Abfragen aber auch die nicht als Kontakt eingetragenen Anrufe extra zu behandeln. --> schneller!

Wobei ich aber auch bemerken muss, dass man große Geschwindigkeitsunterschiede wahrscheinlich erst ab ein paar Millionen Datensätzen bemerken wird.

mfg
Gerold
:-)
http://halvar.at | Kleiner Bascom AVR Kurs

Wissen hat eine wunderbare Eigenschaft: Es verdoppelt sich, wenn man es teilt.
BoOnOdY
User
Beiträge: 112
Registriert: Samstag 22. Juli 2006, 13:38

Beitragvon BoOnOdY » Samstag 21. Oktober 2006, 23:19

Hy,

also ich werd das morgen früh alles genaustens durchugcken, damit ichs kapiere, aber wie müsste der View aussehen, wenn ich die beidne tabellen schon habe, und die felder wie folgt lauten:

calls:
identify;datum;uhrzeit;eingangsport;ausgangsport;eingangsnummer;ausgangsnummer;dauer;fehlercode
destination:
dialcode;name




wie schreib ich dann die funktion die mir sagt, zu welchem Land die Ausgangsnummer gegangen sind?

Ich setzt mich morgen ma drann, und frag dann noch ma,
=)

Und davon, diesen View dann in ne neune Tabelle zu schreiben, davon hältst du nichts? Weil dadurch könnte man Zeit sparen, weil dass dann nur einma durchgerechnet werden muss, der view aber durch ein einfachen group by befehl immer wieder schnell gemacht werden könnte.

Ich rechne jede Nacht meine Anrufe durch, da wärs doch lohnenswert die einfach in ne dritte tabelle einzufügen, oder einfach nur den "identify" also den primary key+das Land in eine neue Tabelle zu schreiben, dann könnte man doch auch mit inner joy Zeit sparen und so?
Oder was meint ihr??


aber shcon ma taudend dank für die schnellen antworten ich guck mir das alels ma an :) is echt super :)

Gruß Tim
Benutzeravatar
gerold
Python-Forum Veteran
Beiträge: 5554
Registriert: Samstag 28. Februar 2004, 22:04
Wohnort: Telfs (Tirol)
Kontaktdaten:

Beitragvon gerold » Sonntag 22. Oktober 2006, 00:09

BoOnOdY hat geschrieben:Und davon, diesen View dann in ne neune Tabelle zu schreiben, davon hältst du nichts? Weil dadurch könnte man Zeit sparen, weil dass dann nur einma durchgerechnet werden muss, der view aber durch ein einfachen group by befehl immer wieder schnell gemacht werden könnte.

Hi Tim!

Kommt darauf an, mit wieviel GB Daten du rechnest.

Es gibt viele Methoden eine Datenbank schnell zu machen. Eine davon ist, alle Daten in einer Datenbank zu sammeln und in einer anderen Datenbank die "Echtdaten" aufbereitet abzuspeichern.
(=Data Warehouse) Das hat den Vorteil, dass die Aufbereitung der Daten auch auf einen anderen Computer ausgelagert werden kann. So wird die Echtdaten-Datenbank nicht damit belastet.

Natürlich kannst du das so machen. Allerdings rentiert sich das erst wenn die Datenbank wirklich groß (=mehrere GB) wird.

Wenn du nie mit so viel Daten rechnest, dann kann ich mir nicht denken, dass PostgreSQL dir die Daten nicht schnell genug zur Verfügung stellen kann.

Wenn du Views und Prozeduren verwendest, oder wenn du einfach nur nomale SELECT-Abfragen verwendest, dann kannst du dir sicher sein, immer mit den aktuellen Daten zu arbeiten.
Ist das nicht wichtig und wenn du mit vielen Daten rechnest, dann spricht nichts dagegen, dir die aufbereiteten Daten auch irgendwo zwischenzuspeichern. Der Vorteil muss aber gegenüber der Verwendung von Echtzeitdaten wirklich **überwiegen**, sonst bringt es nichts.

mfg
Gerold
:-)
http://halvar.at | Kleiner Bascom AVR Kurs

Wissen hat eine wunderbare Eigenschaft: Es verdoppelt sich, wenn man es teilt.
BoOnOdY
User
Beiträge: 112
Registriert: Samstag 22. Juli 2006, 13:38

Beitragvon BoOnOdY » Sonntag 22. Oktober 2006, 07:10

Hy,
gut also ich lese eh nur einmal am Tag meine Daten ein, von daher wäre diese Data Warehouse methode für mich optimal. Denn ein "group by" befehl funktionert um einiges schneller als so ein ganz langer vergleichsoperator :)

Wie würde denn die Data Warehouse Version aussehen?

3 Datenbanken:
Calls
Destinationen
Calls+Länderzeile


Dadurch, dass ich diese neue Tabelle immer gleich mit Daten füttere, wenn ich die anderen Daten einfüge, geht das ja dann bei ner Abfrage viel schneller ;)

Ich importiere die Daten jeden Tag aus ner .CSV in die Tabelle. Kann man da vieleicht gleich beim Importieren den Vergleich machen?, So dass man nur 2 Tabellen braucht weil die tabelle mit Calls gleich beim Importieren mit der Tabelle Destinationen ergänzt wird? Weil für das Importieren hab ich Zeit, das mach ich automatisch Nachts.



Vielen Dank :)
Tim

PS: Wo find ich denn gute Tutorials, von mir aus auch ein Buch, in dem ich so was alles lerne? Weil ich habe im moment nur das Einstieg in SQL von Galileo Computing. Möchte das aber mir gerne selbst beibringen.

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder