Sicher Normalisierung der Daten über SQL-Abfrage
Angenommen ich habe eine Tabelle Kunden:
CREATE TABLE customers (
customer_number INTEGER,
customer_name VARCHAR(...),
customer_address VARCHAR(...)
)
In dieser Tabelle keine nicht einen Primärschlüssel haben. Allerdings customer_name
und customer_address
sollte einzigartig für jede gegebene customer_number
.
Es ist nicht ungewöhnlich für diese Tabelle enthalten viele doppelte Kunden. Um dies zu umgehen Vervielfältigung, die folgende Abfrage verwendet wird, zu isolieren, nur die eindeutige Kunden:
SELECT
DISTINCT customer_number, customer_name, customer_address
FROM customers
Zum Glück, der Tisch hat traditionell enthalten genaue Daten. Das heißt, es hat nie eine entgegenstehende customer_name
oder customer_address
für alle customer_number
. Jedoch, angenommen, widersprüchliche Daten habe es in die Tabelle ein. Ich möchte eine Abfrage schreiben, die scheitern wird, eher als die Rückkehr mehrere Zeilen für die customer_number
in Frage stellen.
Beispielsweise, ich habe diese Abfrage mit Nein Erfolg:
SELECT
customer_number, DISTINCT(customer_name, customer_address)
FROM customers
GROUP BY customer_number
Gibt es eine Möglichkeit zu schreiben, wie eine Abfrage mit SQL-standard? Wenn nicht, gibt es eine Lösung, die in Oracle-spezifische SQL?
EDIT: Die Gründe hinter dem bizarre-Abfrage:
Wahrheit gesagt werden, diese Kunden-Tabelle tatsächlich nicht vorhanden ist (Gott sei Dank). Ich habe es gehofft, es wäre klar genug, um zu zeigen, die Bedürfnisse der Abfrage. Jedoch, Menschen sind (zum Glück) fangen auf, dass die Notwendigkeit für eine solche Abfrage ist meine kleinste Sorge, basiert auf diesem Beispiel. Daher muss ich nun abziehen einige der Abstraktion und hoffentlich wieder meinen Ruf für die Annahme, eine solche Abscheulichkeit von einer Tabelle...
Erhalte ich eine flat-Datei mit Rechnungen (eine pro Zeile) aus einem externen system. Ich habe diese Datei Lesen, Zeile für Zeile, einfügen und die Felder in dieser Tabelle:
CREATE TABLE unprocessed_invoices (
invoice_number INTEGER,
invoice_date DATE,
...
// other invoice columns
...
customer_number INTEGER,
customer_name VARCHAR(...),
customer_address VARCHAR(...)
)
Wie Sie sehen können, werden die Daten für die Anreise aus dem externen system ist denormalisierten. Das heißt, das externe system umfasst sowohl die Rechnungs-Daten und Ihre zugeordneten Kunden-Daten auf der gleichen Linie. Es ist möglich, dass mehrere Rechnungen über die gleichen Kunden, daher ist es möglich, doppelte Kundendaten.
Kann das system nicht mit der Verarbeitung beginnen die Rechnungen, bis alle Kunden garantiert werden, die im system registriert. Deshalb muss das system identifizieren Sie die einzigartige Kunden und registrieren Sie Sie als notwendig. Dies ist der Grund, warum ich wollte die Abfrage: , weil ich arbeiten war mit denormalisierten Daten, die ich hatte keine Kontrolle über.
SELECT
customer_number, DISTINCT(customer_name, customer_address)
FROM unprocessed_invoices
GROUP BY customer_number
Ich hoffe, dies hilft bei der Klärung der ursprünglichen Absicht der Frage.
EDIT: Beispiele für gute/schlechte Daten
Klären: customer_name
und customer_address
nur eindeutig sein für einen bestimmten customer_number
.
customer_number | customer_name | customer_address
----------------------------------------------------
1 | 'Bob' | '123 Street'
1 | 'Bob' | '123 Street'
2 | 'Bob' | '123 Street'
2 | 'Bob' | '123 Street'
3 | 'Fred' | '456 Avenue'
3 | 'Fred' | '789 Crescent'
Die ersten beiden Zeilen sind in Ordnung, da es das gleiche customer_name
und customer_address
für customer_number
1.
Den mittleren beiden Zeilen sind in Ordnung, da es das gleiche customer_name
und customer_address
für customer_number
2 (obwohl ein anderer customer_number
hat die gleiche customer_name
und customer_address
).
Den letzten beiden Zeilen sind nicht okay, weil es zwei verschiedene customer_address
es für customer_number
3.
Die Abfrage, die ich bin auf der Suche nach scheitern würden, wenn gegen alle diese sechs Zeilen. Jedoch, wenn nur die ersten vier Zeilen, die eigentlich vorhanden sind, sollte die Ansicht zurück:
customer_number | customer_name | customer_address
----------------------------------------------------
1 | 'Bob' | '123 Street'
2 | 'Bob' | '123 Street'
Ich hoffe das verdeutlicht, was ich mit "Konflikt customer_name
und customer_address
". Sie haben eindeutig pro customer_number
.
Ich schätzen diejenigen, die erklären, wie man richtig importieren von Daten aus externen Systemen. In der Tat, ich bin schon dabei die meisten bereits. Ich absichtlich hid all die details von dem, was ich bin zu tun, so dass es einfacher wäre, den Fokus auf die Frage auf der hand. Diese Abfrage soll nicht die einzige form der überprüfung. Ich dachte nur es wäre ein nettes letzten Schliff (Letzte Verteidigung, so zu sprechen). Diese Frage war einfach konzipiert, um zu untersuchen, was nur möglich war, mit SQL. 🙂
- Was meinst du mit "fail, anstelle der Rückgabe von mehreren Zeilen"? In der Regel, wenn ich denke, der eine sql-Abfrage fehlschlägt, bedeutet es, dass ich habe keine Zeilen, oder auch ein Produkt der Tabellen, ich bin bei. Ich dachte, Sie waren auf der Suche für ein select * from (select count(*) as cnt, kunden_nr, customer_name, customer_address From Kunden group by kunden_nr, customer_name, customer_address), wo die cnt - > 1 Art der Abfrage.
- Von "fail", ich meinte das DBMS sollte einen Fehler zurückgeben, anstatt die Ergebnisse (wie bei der Abfrage einer nicht existierenden Tabelle). Ich verstehe, dass ich kann verwenden Sie die "SELECT COUNT(*) ... GROUP BY ... HAVING ..." - Abfrage zu ermitteln, die Anwesenheit von widersprüchlichen Daten, aber ich war einfach neugierig zu sehen, wenn Sie eine Abfrage ähnlich der, die ich beschrieben tatsächlich existierte. Danke für den Kommentar, aber! 🙂
Du musst angemeldet sein, um einen Kommentar abzugeben.
Eine Skalare Unterabfrage darf nur eine Zeile zurückgeben (pro ResultSet Zeile...), so dass Sie könnte so etwas wie:
Dein Ansatz ist fehlerhaft. Sie wollen nicht, dass Daten erfolgreich gespeichert werden, um dann einen Fehler auslösen, die auf einer select - das ist eine land mine warten, um zu geschehen und bedeutet, dass Sie nie wissen, wenn eine select-scheitern könnte.
Was ich empfehle ist das hinzufügen einer eindeutigen Schlüssel auf den Tisch, und langsam beginnen, ändern Sie Ihre Anwendung verwenden Sie diese Taste, anstatt sich auf eine beliebige Kombination von aussagekräftigen Daten.
Können Sie dann stoppen Sie die Sorge um doppelte Daten, die nicht wirklich duplizieren in den ersten Platz. Es ist durchaus möglich, für zwei Personen mit dem gleichen Namen an die gleiche Adresse.
Bekommen Sie die performance-Verbesserungen von diesem Ansatz.
Als ein beiseite, ich sehr empfehlen Sie, normalisieren Sie Ihre Daten, das ist Pause, bis der name in Vorname und Nachname (Optional MiddleName auch), und brechen die Adresse Feld in separate Felder für jede Komponente (Address1, Address2, City, State, Country, Zip, oder was auch immer)
Update: Wenn ich verstehe deine situation richtig (was ich nicht bin sicher, dass ich tun), Sie wollen verhindern, dass doppelte Kombinationen von Namen und Adresse immer die in der Tabelle (obwohl das ist ein mögliches vorkommen in real life). Dies wird am besten durch eine unique-Einschränkung oder einen index über diese beiden Felder zu verhindern, dass die Daten eingefügt werden. Das heißt, fangen die Fehler vor Sie es einsetzen. Das wird Ihnen sagen, die import-Datei oder Ihr resultierende app-Logik ist schlecht, und Sie können wählen, um die geeigneten Maßnahmen dann.
Ich behaupte immer noch, dass das auslösen der Fehler bei der Abfrage ist zu spät, etwas dagegen zu tun.
Machen die Abfrage fehl, kann schwierig sein...
Dies wird Ihnen zeigen, wenn es keine doppelten Datensätze in der Tabelle:
Wenn Sie fügen Sie einfach einen eindeutigen index für alle drei Felder, kann niemand erstellen Sie einen doppelten Datensatz in der Tabelle.
Den defacto-Taste Name+Adresse, also das, was Sie brauchen, um Gruppe durch.
Wenn Sie wollen, um es aus der Sicht eines Kunden_nr, dann ist das auch gut.
Wenn Sie dreckig Daten, ich würde es sauber erste.
Verwenden Sie diese, um zu finden, die doppelte Kunden-Datensätze...
Wenn Sie es wollen, um zu scheitern, Sie gehen zu müssen, um über einen index. Wenn Sie nicht wollen, um einen index verfügen, dann können Sie einfach erstellen Sie eine temporäre Tabelle zu diesem all-in.
)
Dies wird fehlschlagen, wenn es Probleme sind, aber halten Sie Ihre doppelte Datensätze verursachen Probleme.
Stellen wir die Daten in eine temporäre Tabelle oder eine Tabellenvariable mit Ihrem distinct-Abfrage
Ich persönlich würde eine indetity zu unporcessed Rechnungen, wenn möglich. Ich mache nie ein import ohne das erstellen einer staging-Tabelle, die eine identity-Spalte, nur weil es einfacher ist, löschen Sie doppelte Datensätze.
Lassen Sie uns nun die Abfrage der Tabelle zu finden, der Ihre problem-Datensätze. Ich nehme an, Sie möchten, um zu sehen, was das problem verursacht nicht nur scheitern.
Können Sie eine variation auf diese Abfragen zum löschen der problem-records aus #temp (hängt davon ab, ob Sie wählen, um zu halten ein oder löschen Sie alle mögliche Probleme) und dann einfügen von #temp um Ihre Produktion Tabelle. Sie können auch porvide das problem Datensätze zurück, wer ist die Bereitstellung der Daten fest auf Ihre Ende.