Benutzer mit den meisten Antworten
Problem beim Aggregieren/Gruppieren von Tabellen mit String-Werten

-
Hallo zusammen,
ich habe die folgende Tabelle:
Nun möchte ich die Tabelle in der Form aggregieren, dass die Pfade, die in anderen bereits vorhanden sind ausgeblendet werden, also
z.B. der Pfad von Zeile 2 ist bereits im Pfad von Zeile 1 enthalten und kann somit ausgeblendet werden, oder
der Pfad 'A-B' ist bereits in 'A-B-E' vorhanden und ist deshalb überflüssig. Dabei enthält Pfad_Nr alle zu einem Gesamtpfad gehörigen 'Teilpfade'.Weiß hier jemand Rat, wie ich das bewerkstelligen kann, ein GROUP BY Pfad_Nr liefert nicht das gewünschte Ergebnis.?
Gruß
Jürgen
Frage
Antworten
-
Hi Jürgen,
ich habe Dir mal ein einfaches Programm mit Deinen Ausgangsdaten geschrieben. Im Programm habe ich aber nicht die Weiterleitung, auch nicht Vertauschungen und nicht Überspringen einer Ebene berücksichtigt, also nur direkte Folgen. Die anderen Sonderfälle habe ich nicht so richtig verstanden. Da fehlt mir etwas Hintergrundwissen zur Aufgabenstellung. Hier mal der Code:using Microsoft.SqlServer.Server; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Xml.Linq; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void SqlStoredProcedure1(string Anrufer) { XElement xe0 = new XElement(Anrufer ); SqlDataRecord record = new SqlDataRecord(new SqlMetaData("Name", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene1", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene2", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene3", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene4", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene5", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene6", SqlDbType.NVarChar, 128)); using (SqlConnection connection = new SqlConnection("context connection=true;")) { connection.Open(); SqlCommand command = new SqlCommand("SELECT DISTINCT Anrufer, Angerufener FROM TestTable1 WHERE Anrufer=@Anr", connection); command.Parameters.Add("@Anr", SqlDbType.NVarChar).Value = Anrufer; foreach (XElement xe1 in GetData(1, command)) xe0.Add(xe1); } PipeResult(xe0, 0, record); } private static List<XElement> GetData(int level, SqlCommand command) { List<XElement> liste = new List<XElement>(); if (level > 6) return liste; using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read()) liste.Add(new XElement(reader["Angerufener"].ToString()));
} foreach (XElement xe1 in liste) { command.Parameters["@Anr"].Value = xe1.Name.LocalName; foreach (XElement xe2 in GetData(level + 1, command)) xe1.Add(xe2); } return liste; } private static void PipeResult(XElement xe, int level, SqlDataRecord record) { if (level > 6) { SqlContext.Pipe.Send(record); return; } bool sw = false; if (xe == null) record.SetSqlString(level, null); else { record.SetSqlString(level, xe.Name.LocalName); foreach (XElement xe1 in xe.Elements()) { sw = true; PipeResult(xe1, level + 1, record); } } if (!sw) PipeResult(null, level + 1, record); } }Und das Ergebnis:
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks- Bearbeitet Peter Fleischer Montag, 15. Oktober 2018 09:40 using reader
- Als Antwort vorgeschlagen Peter Fleischer Montag, 15. Oktober 2018 09:42
- Als Antwort markiert Jürgen Sch Montag, 15. Oktober 2018 10:14
Alle Antworten
-
Hi Jürgen, wenn das das Ergebnis der Frage aus dem anderen Thread ist, dann sollte dort angesetzt werden. Wie ich bereits geschrieben hatte, würde ich so etwas programmatisch lösen. Dafür bietet sich in diesem Fall Contains an. Wenn das Ergebnis von verschiedenen Anwendungen direkt aus dem SQL Server abgerufen werden soll, dann bietet sich ein SQL Server Projekt an (dll im SQL Server, mit der das Ergebnis wie bei einer SP geleifert werden kann).
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks -
Hallo Peter,
in dem anderen Thread kam keine Antwort mehr, deshalb dachte ich, ich müsste einen neuen Thread aufmachen, da die letzte Frage neue Problematiken aufwarf.
Das Problem mit der programmatischen Lösung ist, dass die Funktion von einem Client über eine WebAPI aufgerufen wird mit dem Parameter "Startnummer". Wenn dann aus dem Client in einer Schleife ständig neue Abfragen generiert werden, hat das sicherlich riesen Probleme bei der Performance weil die Suche u.U. auf eine Tabelle mit mehreren Millionen Datensätzen ausgeführt wird.
Es muss doch möglich sein, die Schleifen auf dem SQL-Server laufen zu lassen?
Ich hatte schon mehrere SQL-Spezies angesprochen, aber keiner konnte mir eine Lösung anbieten. Es ist auch nicht das Pfad-Problem von Christoph. Das passt nicht so ganz.
In VBA/c# habe ich diese Funktion bereits erfolgreich implementiert, aber das nutzt mir nichts bei unserem Projekt aufgrund der Struktur: Vom Client wird über eine WebAPI eine StoredProcedure auf dem SQL-DatenbankServer mit dem erforderlichen Parameter aufgerufen und der Client erhält dann das Ergebnis wieder über die WebAPI als Tabelle, denn ein Abrufen von einer Tabelle mit 3 Spalten und mehreren Millionen Werten über die WebAPI könnte sehr teuer werden.
Gruß
Jürgen
-
Hallo zusammen,
ich habe die folgende Tabelle:
Gruß
Jürgen
Welche Tabelle hast Du denn?
Bitte mal ein SQL-Script für Metadaten UND Daten hier einstellen; dann wäre auch eine Lösung möglich...
Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)
Hier das Tabellenscript:
USE [TestTabellenDB] GO /****** Object: Table [dbo].[TestTable1] Script Date: 09.10.2018 11:42:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TestTable1]( [Anrufer] [nvarchar](50) NULL, [Angerufener] [nvarchar](50) NULL, [Weitergeleitet] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'B', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'C', N'D'); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'B', N'E', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'F', N'G', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'X', N'C'); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'E', N'H', 'Z'); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'G', N'Y', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'K', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'K', N'L', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'L', N'M', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'M', N'N', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'N', N'O', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'O', N'P', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'B', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'E', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'O', N'P', NULL); INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'D', NULL);
Gruß Jürgen -
Hi Jürgen,
ich meinte auch ein SQL Server DataBase Projekt, welches eine dll erzeugt, die im SQL Server eine .NET-dll einfügt, mit der dann eigene SP's, Trigger oder UDF's bereitgestellt werden können. Solche SP's z.B. können genau so effektiv arbeiten wie eine komplexe SP, die nur SQL Anweisungen enthält. Von der Anwendung (z.B. WebAPI) wird dann diese .NET-SP wie eine ganz normale SP abgerufen. Da Du die Funktion bereits in C# implementiert hast, dürfte es einen sehr geringen Aufwand erfordern, diese Funktion in eine SP (auf Basis C#.NET) zu kapseln. In dieser SP kannst Du dann beliebige Schleifen laufen lassen und somit auch den Graphen auswerten und gleich die überflüssigen Zeilen übergehen (ausblenden).--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks -
Hi Jürgen,
dieses Tabellen Script passt aber überhaupt nicht zur Ausgangsdarstellung (Pfad bis Ebene 6).--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks -
Probier einfach mal:
select Pfad .... from Table A
where not exists (
select * from Table B
where A.Pfad <> B.Pfad
and left(B.Pfad, len(rtrim(A.Pfad) = B.Pfad
)A.Pfad <> B.Pfad => Schließt sich selber erst mal aus.
left(B.Pfad, len(rtrim(A.Pfad) => Der Pfad aus dem Subselect wird auf die Länge des Hauptselects gekürzt, rtrim() um ggf. Leerzeichen am Ende zu ignorieren. -
Hi Jürgen,
für mich gibt es noch ein paar Details, die ich noch nicht erkannt habe:1. z.B.: A-B-C-D und C-D. Wäre C-D auch zu löschen?
2. A-B-C und C-B. wird da C-B angehängt? Wie ist bei einer derartiger Zirkelbeziehung zu verfahren? A-B-C-B ?
3. Woher kommt A-C? Im 2. Insert ist A-C-D. Ein A-C kann ich nicht erkennen.
4. Im 5. Insert steh A-X-C. Im Ergebnis finde ich das nicht.
5. O-P gibt es 2-fach. Wie ist da zu verfahren?
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks -
Hallo Peter,
da ich das Ganze ebenenweise ausgeben muss, gelten folgende Konventionen:
1. C-D wird gelöscht, da bereits in A-B-C-D vorhanden
2. A-B-C => C wird gelöscht, da bereits in A-C vorhanden
3. Da habe ich - glaube ich, meine Tabelle noch einmal erweitert gehabt
4. Bei A-X-C muss C ebenfalls gelöscht werden, da bereits in A-C vorhanden
5. Bei mehrfachen Vorkommen wird nur eines benötigt (DISTINCT)
6. Falls B-A (kommt in Tabelle noch nicht vor) dann ist das zu entfernen, falls A-B bereits existiert. Falls nicht, muss daraus A-B werden, da A der Startname ist.
7. Gibt es A-B-C und C-B, dann entfällt C-B, da bereits in A-B-C vorhanden.
Insgesamt gilt: Die Beziehungen werden "nicht gerichtet" betrachtet, sondern gibt es irgendeine Verbindung zwischen 2 Namen, dabei können die beiden Parteien sowohl "Anrufer" als "Angerufener" sein. Bei Weitergeleitet wird der "Angerufene" zum "Anrufer" und der "Weitergeleitet" zum "Angerufenen".
Ein neuer Knoten wird also nur dann angehängt, wenn er noch nicht bereits in einem Pfad vorhanden ist. Jeder Knoten darf also nur einmal in der Ergebnistabelle vorkommen.
Gruß
Jürgen
-
Hallo zusammen,
ich habe meine Testtabelle noch um einige Datensätze erweitert, die entsprechende Zirkelbezüge enthalten:
/****** Object: Table [dbo].[TestTable1] Script Date: 14.10.2018 11:33:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TestTable1]( [Anrufer] [nvarchar](50) NULL, [Angerufener] [nvarchar](50) NULL, [Weitergeleitet] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'B', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'C', N'D') INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'B', N'E', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'F', N'G', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'X', N'C') INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'E', N'H', N'Z') INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'G', N'Y', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'K', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'K', N'L', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'L', N'M', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'M', N'N', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'N', N'O', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'O', N'P', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'B', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'E', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'O', N'P', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'B', N'A', NULL) INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'F', N'G', N'A') INSERT [dbo].[TestTable1] ([Anrufer], [Angerufener], [Weitergeleitet]) VALUES (N'A', N'D', NULL)
Meine SQL-Anweisung (Ansatz von Christoph):
With t as ( Select t1.Anrufer, t1.Angerufener, t1.Weitergeleitet, cast(t1.Anrufer + '-' + t1.Angerufener + coalesce('-' + t1.Weitergeleitet, '') as nvarchar(4000)) as Pfad, ROW_NUMBER() OVER(ORDER BY t1.Anrufer) as Pfad_Nr, case when t1.Weitergeleitet is not null then 2 else 1 end as Element, t1.Anrufer as Name, t1.Angerufener as Ebene1, case when t1.Weitergeleitet is not null then t1.Weitergeleitet else NULL end as Ebene2, cast(NULL as nvarchar(50)) as Ebene3, cast(NULL as nvarchar(50)) as Ebene4, cast(NULL as nvarchar(50)) as Ebene5, cast(NULL as nvarchar(50)) as Ebene6 from dbo.TestTable1 t1 where t1.Anrufer = 'A' UNION ALL Select t.Angerufener as Anrufer, t2.Angerufener, t2.Weitergeleitet, cast(t.Pfad + '-' + t2.Angerufener + coalesce('-' + t2.Weitergeleitet, '') as nvarchar(4000)) as Pfad, t.Pfad_Nr, case when t2.Weitergeleitet is not null then t.Element + 2 else t.Element + 1 end as Element, t.Name, t.Ebene1, case when t.Element = 1 then t2.Angerufener else t.Ebene2 end as Ebene2, case when t.Element = 1 and t2.Weitergeleitet is not null then t2.Weitergeleitet when t.Element = 2 then t2.Angerufener else t.Ebene3 end as Ebene3, case when t.Element = 2 and t2.Weitergeleitet is not null then t2.Weitergeleitet when t.Element = 3 then t2.Angerufener else t.Ebene4 end as Ebene4, case when t.Element = 3 and t2.Weitergeleitet is not null then t2.Weitergeleitet when t.Element = 4 then t2.Angerufener else t.Ebene5 end as Ebene5, case when t.Element = 4 and t2.Weitergeleitet is not null then t2.Weitergeleitet when t.Element = 5 then t2.Angerufener else t.Ebene6 end as Ebene6 from t Inner Join dbo.TestTable1 t2 on t.Angerufener = t2.Anrufer ), Aufbereitung as ( Select t.Pfad_Nr, t.Pfad, ROW_NUMBER() OVER(PARTITION BY Pfad_Nr ORDER BY LEN(Pfad) DESC) as Reihenfolge, Element, Name, Ebene1, Ebene2, Ebene3, Ebene4, Ebene5, Ebene6 from t ) Select a.Pfad, a.Reihenfolge, Element, Name, Ebene1, Ebene2, Ebene3, Ebene4, Ebene5, Ebene6 from Aufbereitung a where Reihenfolge = 1 GROUP BY a.Pfad, a.Reihenfolge, Element, Name, Ebene1, Ebene2, Ebene3, Ebene4, Ebene5, Ebene6 order by Element DESC go
läuft dabei in den Fehler:
Jetzt bin ich völlig durcheinander????
Gruß Jürgen
-
Hi Jochen,
Deine Erläuterungen hinterlassen bei mir immer noch Unklarheiten. Der Algorithmus muss ja irgendwie sequentiell die Daten verarbeiten. Das bedeutet, wenn in einem späteren Schritt ein Zustand entsteht, der auch anders verknüpft werden kann, dann kann das recht umfangreich werden., d.h. mehrfache auf- und absteigende Rekursion.1. A-G-C gefunden
2. D-E gefunden.
3. E-A gefunden, ergibt D-E-A und nach Umstellung A-D-E ?
4. C-D gefunden, ergibt A-G-C-D. Was wird dann mit A-D-E ? Löschen?
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks -
Dann musst du dir eine Prozedur schreiben, die den Begriff "Pfad" in Einzelwerte zerlegt und distinct sortiert.
Somit kannst du dann davon ausgehen, dass alle Kombinationen vereinheitlicht werden.In einer 2. Prozedur kannst du nun prüfen, ob alle Elemente aus der Zerlegung in Pfad1 in der Zerlegung Pfad 2 vorkommen.
Die Prozeduren werden wohl am effektivsten in CLR geschrieben (C#, VB), da du dann string.Split() und Array.IndexOf() effektiver anwenden kannst, da SQL native keine Arrays unterstützt.
Alternativ kann man auch das Feld Pfad in einer weiteren Tabelle mit dem originalen Pfad bzw. der Identity-Spalte verknüpfen und die Pfadauflösung dann als laufende Nummer in der neuen Tabelle ablegen.
Dann kann man wiederum per exists-Select die Subpfade des Hauptselects gegen die Subpfade des Subselects ausschließen.
Vielleicht reicht es ggf. in einer Schleife per Charindex des "-" und Substring den Teilpfad zu extrahieren und auf Existenz per charindex im 2. Pfad zu ermitteln.
-
Hallo an alle,
ich denke, dass ich das Ganze doch im Client mit c# mache, für die einzelnen Ebenen separate Abfragen an den Server sende und dann im Client die Ergebnisse aufbereite.
Der Ansatz mit den Pfaden führt zu keinen zufriedenstellenden Ergebnissen, da zu viele Dinge berücksichtigt werden müssen.
Meine neue Lösungsidee:
1. Client => WEB_API => DB-Server:
SELECT [Anrufer], [Angerufener], [Weitergeleitet] From Tabelle
WHERE Anrufer = @SearchNr OR Angerufener = @SearchNr OR Weitergeleitet = @SearchNr
GROUP BY Anrufer, Angerufener, Weitergeleitet
Resultset => Client:
Bereite das Resultset auf (Tauschungen, Vergleiche etc.) und ermittele alle Distinctwerte von Angerufener und Weitergeleitet
Schleife über alle Distinctwerte von Anrufer = Ebene1 mit 1. und @SearchNr die einzelnen Distinctwerte
solange bis entsprechende Abbruchbedingung "MaxEbene" oder leeres Resultset oder so.Schade, dass das mit SQL allein nicht möglich ist, denn der Aufruf dieser Funktion ist dann für unser gesamtes System (Netzwerk) wohl sehr "teuer".
Trotzdem danke an alle, die sich hier Gedanken gemacht haben.
Gruß Jürgen
-
Du kannst es dir da schon etwas einfacher machen.
Zu dem Zeitpunkt, an dem die Tabelle erstellt wird, erstellst du ein 2. Feld "PfadDistinctSort", dass genau eine eindeutige Hierarchie erstellt (After-Trigger, Prozedur, Instead-Trigger).
Also aus "B-A" wird dann "A-B" und aus "A-C-B" wird "A-B-C", "A-C-A" => "A-C" usw. usf.
Dieses Ergebnis wird zusätzlich in einer 2. Tabelle "Vorkommen" gepflegt.Dabei wird sich immer nur der gößte Pfad gemerkt:
Wenn ein Pfad bereits vorhanden ist, der den neuen Pfad enthält, wird kein Satz erstellt.
Ansonsten wird der neue Pfad erstellt und alle kürzeren Pfad, die im neuen Pfad enthalten sind, gelöscht.
Somit beliben in "Vorkommen" nur die eindeutigen Pfade zurück.Bei deiner Datenermittlung prüfst du nun per
select ....
where exists (select * from Vorkommen where PfadDistinctSort = Vorkommen.Pfad)
Ggf. musst du in PfadDistinctSort die "-" mit "%" ersetzen und per Like suchen, um ein "A-C" auch in der Kombination "A-B-C-D" zu finden.Fazit:
Wenn irgendwann ein Pfad auftritt, der alle möglichen Kombinationen enthält, hast du in der Tabelle Vorkommen genau noch nur 1 Satz. -
Hi Jürgen,
das ist im SQL Server möglich, wenn Du eine CLR-Procedure schreibst. Damit ist die Procedure aus dem gesamten Netzwerk wie eine übiche Stored Procedure (mit nur SQL Anweisungen) abrufbar. Der Vorteil einer solchen Lösung ist, dass nur die geringe Menge des Endergebnisses über das Netz aus dem SQL Server geschickt wird. Das ist um ein Vielfaches schneller, als jedes Mal ein selektiertes Recordset mit vielen Datensätzen für die Verarbeitung außerhalb des SQL Servers zu verschicken, vorausgesetzt, der SQL Server ist ausreichend ausgestattet.
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks- Bearbeitet Peter Fleischer Sonntag, 14. Oktober 2018 11:37
-
Hi,
das Problem wird sein, dass die Daten aus einem Fremdsystem einlaufen und dieser dynamische Prozess bei einer weiteren aggregierten Tabelle recht komplex werden kann. Auch könnte es vorkommen, dass fehlerhafte Einträge bearbeitet werden und dann die aggregierte Tabelle auch aktualisiert werden muss. Außerdem steht vermutlich der Buchstabe nur für ein etwas komplexeres Objekt bzw. für Telefonnummern mit unterschiedlicher Struktur.Man müsste einfach mal die CLR-Lösung für den anvisierten Datenumfang testen und messen.
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks -
Die Ursprungsaufgabe stammt ja aus einem anderen Thread, so dass der Pfad-Begriff ebenso mittels verbundener normierter Tabelle und Unique-Key "[Anrufer], [Angerufener]" den sortieren Pfad aller Angerufener zum Anrufer enthält, also "A->B->X..." werden normiert in A->B, A->X, ...
Somit lässt sich jede Kombination daraus ableiten, da B->X ja enthalten ist.
Und irgendwann kommen die Daten ja in die Tabelle und das ist der Zeitüunkt der benötigten Aggregattabelle die eben mittels Trigger ständig mitgepflegt werden kann. -
Hi,
das Problem wird sein, dass die Daten aus einem Fremdsystem einlaufen und dieser dynamische Prozess bei einer weiteren aggregierten Tabelle recht komplex werden kann. Auch könnte es vorkommen, dass fehlerhafte Einträge bearbeitet werden und dann die aggregierte Tabelle auch aktualisiert werden muss. Außerdem steht vermutlich der Buchstabe nur für ein etwas komplexeres Objekt bzw. für Telefonnummern mit unterschiedlicher Struktur.Man müsste einfach mal die CLR-Lösung für den anvisierten Datenumfang testen und messen.
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und TricksJa, das ist korrekt. Bei den Objekten im Echtsystem handelt sich um Telefonnummern und der auszuwertende Datenumfang kann bis zu 15 Millionen Datensätzen in einer Tabelle mit 154 Spalten sein, wobei aber nur die 3 Spalten - wie im Beispiel - analysiert werden.
Gruß Jürgen
-
Du kannst es dir da schon etwas einfacher machen.
Zu dem Zeitpunkt, an dem die Tabelle erstellt wird, erstellst du ein 2. Feld "PfadDistinctSort", dass genau eine eindeutige Hierarchie erstellt (After-Trigger, Prozedur, Instead-Trigger).
Also aus "B-A" wird dann "A-B" und aus "A-C-B" wird "A-B-C", "A-C-A" => "A-C" usw. usf.
Dieses Ergebnis wird zusätzlich in einer 2. Tabelle "Vorkommen" gepflegt.Dabei wird sich immer nur der gößte Pfad gemerkt:
Wenn ein Pfad bereits vorhanden ist, der den neuen Pfad enthält, wird kein Satz erstellt.
Ansonsten wird der neue Pfad erstellt und alle kürzeren Pfad, die im neuen Pfad enthalten sind, gelöscht.
Somit beliben in "Vorkommen" nur die eindeutigen Pfade zurück.Bei deiner Datenermittlung prüfst du nun per
select ....
where exists (select * from Vorkommen where PfadDistinctSort = Vorkommen.Pfad)
Ggf. musst du in PfadDistinctSort die "-" mit "%" ersetzen und per Like suchen, um ein "A-C" auch in der Kombination "A-B-C-D" zu finden.Fazit:
Wenn irgendwann ein Pfad auftritt, der alle möglichen Kombinationen enthält, hast du in der Tabelle Vorkommen genau noch nur 1 Satz.Hast du da bitte einmal ein Beispiel für deine Idee???
Gruß Jürgen
-
Hi Jürgen,
das ist im SQL Server möglich, wenn Du eine CLR-Procedure schreibst. Damit ist die Procedure aus dem gesamten Netzwerk wie eine übiche Stored Procedure (mit nur SQL Anweisungen) abrufbar. Der Vorteil einer solchen Lösung ist, dass nur die geringe Menge des Endergebnisses über das Netz aus dem SQL Server geschickt wird. Das ist um ein Vielfaches schneller, als jedes Mal ein selektiertes Recordset mit vielen Datensätzen für die Verarbeitung außerhalb des SQL Servers zu verschicken, vorausgesetzt, der SQL Server ist ausreichend ausgestattet.
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks
So etwas habe ich noch nicht gemacht. Wie geht das???
Gruß Jürgen
-
Nun ja, kein konkretes Beispiel sondern nur eine Verfahrensanweisung:
Du erstellt eine kleine neue Tabelle "PfadSorted" mit dem Feld "PfadSort" (groß genug).
Wenn du das Feld "Pfad" an irgendeiner Stelle erstellst, hast du ja die Einzel-Begriffe im Zugriff.
Diese Einzelbegriffe schiebst du in eine "Arbeitstabelle" (Global temporary) und liest diese sortiert und distinct wieder aus. Damit bildest du den PfadSort mit dem Muster (wie oben) "%A%B%C%....%Z%".
Um nicht zufällige Überschneidungen zu finden, kannst du ja jeden Begriff in Sonderzeichen einbetten.
Z.B. "[[" + Name + "]]", da die Wahrscheinlichkeit dagegen spricht, dass jemand solchen Blödsinn erfasst.
Dies ergibt dann '%[[A]]%[[B]]...%[[Z]]%'
http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/01/07/sql-server-global-temporary-tables/
Den PfadSort suchst du nun in der Tabelle PfadSortet like PfadSort.
Wenn du einen Satz gefunden hast, brauchst du nichts weiter zu tun.Ist der Satz neu, fügst du diesen in die Tabelle PfadSorted ein und löscht alle Sätze, deren Kombination nun im neuen Begriff vorhanden sind.
Das ist wiederum in einer Schleife erforderlich, in dem du aus der obigen Temp-Tabelle nun alle Begriffe von Top 1 bis Top Count bildest:
delete from PfadSorted
where PfadSorted <> <GeradeEingefügt> and PfadSorted like <TeilPfad>
Beispiel:
Der Begriff A-B wird eingefügt.
Der Begriff A-C wird eingefügt.
Der Begriff A-B-C wird eingefügt und A-B sowie A-C werden gelöscht.usw. usf.
Zusätzlich fügst du PfadSorted ebenso in deine Hauptdatei ein um per Exists auf PfadSorted mit Like zu prüfen
Das Bilden dieser Zusatztabelle dürfte je Insert/Update relativ zügig gehen und passiert halt nur beim Erstellen/Ändern. Die Abfragen wiederum dürfte da eher häufiger auftreten.
Beim Löschen von Informationen muss jedoch geprüft werden, ob genau dieser PfadSorted ebenso gelöscht werden sollte. In diesem Fall muss nun der größte gemeinsame Nenner zu diesem Pfad aus den Restdaten ermittelt werden.- Bearbeitet bfuerchau Montag, 15. Oktober 2018 08:13
-
Hi Jürgen,
ich habe Dir mal ein einfaches Programm mit Deinen Ausgangsdaten geschrieben. Im Programm habe ich aber nicht die Weiterleitung, auch nicht Vertauschungen und nicht Überspringen einer Ebene berücksichtigt, also nur direkte Folgen. Die anderen Sonderfälle habe ich nicht so richtig verstanden. Da fehlt mir etwas Hintergrundwissen zur Aufgabenstellung. Hier mal der Code:using Microsoft.SqlServer.Server; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Xml.Linq; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void SqlStoredProcedure1(string Anrufer) { XElement xe0 = new XElement(Anrufer ); SqlDataRecord record = new SqlDataRecord(new SqlMetaData("Name", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene1", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene2", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene3", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene4", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene5", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene6", SqlDbType.NVarChar, 128)); using (SqlConnection connection = new SqlConnection("context connection=true;")) { connection.Open(); SqlCommand command = new SqlCommand("SELECT DISTINCT Anrufer, Angerufener FROM TestTable1 WHERE Anrufer=@Anr", connection); command.Parameters.Add("@Anr", SqlDbType.NVarChar).Value = Anrufer; foreach (XElement xe1 in GetData(1, command)) xe0.Add(xe1); } PipeResult(xe0, 0, record); } private static List<XElement> GetData(int level, SqlCommand command) { List<XElement> liste = new List<XElement>(); if (level > 6) return liste; using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read()) liste.Add(new XElement(reader["Angerufener"].ToString()));
} foreach (XElement xe1 in liste) { command.Parameters["@Anr"].Value = xe1.Name.LocalName; foreach (XElement xe2 in GetData(level + 1, command)) xe1.Add(xe2); } return liste; } private static void PipeResult(XElement xe, int level, SqlDataRecord record) { if (level > 6) { SqlContext.Pipe.Send(record); return; } bool sw = false; if (xe == null) record.SetSqlString(level, null); else { record.SetSqlString(level, xe.Name.LocalName); foreach (XElement xe1 in xe.Elements()) { sw = true; PipeResult(xe1, level + 1, record); } } if (!sw) PipeResult(null, level + 1, record); } }Und das Ergebnis:
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks- Bearbeitet Peter Fleischer Montag, 15. Oktober 2018 09:40 using reader
- Als Antwort vorgeschlagen Peter Fleischer Montag, 15. Oktober 2018 09:42
- Als Antwort markiert Jürgen Sch Montag, 15. Oktober 2018 10:14
-
Hi Jürgen,
wenn Du das Ergebnis als Set von Rows haben willst, dann nutze folgenden Code:using Microsoft.SqlServer.Server; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Xml.Linq; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void SqlStoredProcedure1(string Anrufer) { XElement xe0 = new XElement(Anrufer ); SqlDataRecord record = new SqlDataRecord(new SqlMetaData("Name", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene1", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene2", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene3", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene4", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene5", SqlDbType.NVarChar, 128), new SqlMetaData("Ebene6", SqlDbType.NVarChar, 128)); using (SqlConnection connection = new SqlConnection("context connection=true;")) { connection.Open(); SqlCommand command = new SqlCommand("SELECT DISTINCT Anrufer, Angerufener FROM TestTable1 WHERE Anrufer=@Anr", connection); command.Parameters.Add("@Anr", SqlDbType.NVarChar).Value = Anrufer; foreach (XElement xe1 in GetData(1, command)) xe0.Add(xe1); } SqlContext.Pipe.SendResultsStart(record); PipeResult(xe0, 0, record); SqlContext.Pipe.SendResultsEnd(); } private static List<XElement> GetData(int level, SqlCommand command) { List<XElement> liste = new List<XElement>(); if (level > 6) return liste; using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) liste.Add(new XElement(reader["Angerufener"].ToString())); } foreach (XElement xe1 in liste) { command.Parameters["@Anr"].Value = xe1.Name.LocalName; foreach (XElement xe2 in GetData(level + 1, command)) xe1.Add(xe2); } return liste; } private static void PipeResult(XElement xe, int level, SqlDataRecord record) { if (level > 6) { SqlContext.Pipe.SendResultsRow(record); return; } bool sw = false; if (xe == null) record.SetSqlString(level, null); else { record.SetSqlString(level, xe.Name.LocalName); foreach (XElement xe1 in xe.Elements()) { sw = true; PipeResult(xe1, level + 1, record); } } if (!sw) PipeResult(null, level + 1, record); } }
Das Ergebnis sieht dann so aus (in einem WPF-DataGrid):
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks -
Hallo Peter,
danke für deinen CLR-Code. Den werde ich noch anpassen müssen, da in der ersten Zeile E in Ebene 2 erscheint, obwohl schon in Ebene 1 vorhanden. Das gleiche gilt analog für H.
Damit dürfte in der 1. Zeile nur A B stehen.
Aber da werde ich mich die Tage mal drum kümmern.
Gruß Jürgen
-
Hi Jürgen,
wie ich bereits geschrieben hatte, habe ich den Algorithmus nur so auf gebaut, dass die Weiterleitung, Vertauschungen und Überspringen einer Ebene nicht berücksichtigt sind. Du hattest Dich bisher nicht geäußert, was konkret alles nicht zu berücksichtigen ist. Im Beispiel werden nur die unikaten Ketten der Folgen Anrufer-Angerufener ohne Sonderbedingungen berücksichtigt.Ich würde das erst einmal mit dem großen Datenumfang testen und das Zeitverhalten messen. Dann kannst Du ja noch die zusätzlichen Bedingungen einbauen.
--
Viele Grüsse
Peter Fleischer (ehem. MVP für Developer Technologies)
Meine Homepage mit Tipps und Tricks