none
Problem beim Aggregieren/Gruppieren von Tabellen mit String-Werten

    Frage

  • 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

    Freitag, 12. Oktober 2018 10:48

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
    Montag, 15. Oktober 2018 09:24

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

    Freitag, 12. Oktober 2018 11:41
  • 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

    Freitag, 12. Oktober 2018 12:03
  • 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)

    Hallo Uwe,
    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
    Freitag, 12. Oktober 2018 12:10
  • 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

    Freitag, 12. Oktober 2018 12:32
  • 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

    Samstag, 13. Oktober 2018 05:15
  • 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.

    Samstag, 13. Oktober 2018 08:41
  • 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

    Samstag, 13. Oktober 2018 18:01
  • 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

    Sonntag, 14. Oktober 2018 09:19
  • 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

    Sonntag, 14. Oktober 2018 09:40
  • 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

    Sonntag, 14. Oktober 2018 09:53
  • 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.

    Sonntag, 14. Oktober 2018 10:21
  • 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

    Sonntag, 14. Oktober 2018 11:08
  • 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.

    Sonntag, 14. Oktober 2018 11:33
  • 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


    Sonntag, 14. Oktober 2018 11:35
  • Trotzdem wird die Lösung da nicht so performant werden, da mehr abgefragt werden muss.
    Wenn man die Daten bereits bei der Entstehung für kümftige Abfragen optimiert, erübrigt sich das dann.
    Sonntag, 14. Oktober 2018 11:38
  • 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

    Sonntag, 14. Oktober 2018 11:59
  • 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.
    Sonntag, 14. Oktober 2018 14:50
  • 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

    Ja, 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

    Montag, 15. Oktober 2018 04:47
  • 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

    Montag, 15. Oktober 2018 04:49
  • 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

    Montag, 15. Oktober 2018 04:50
  • Schau mal hier:

    https://msdn.microsoft.com/en-us/library/ms131094.aspx?f=255&MSPPError=-2147217396

    VisualStudio-Express oder -Community reicht da ggf. aus.

    Montag, 15. Oktober 2018 07:45
  • 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
    Montag, 15. Oktober 2018 08:08
  • 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
    Montag, 15. Oktober 2018 09:24
  • Hallo Peter,

    danke für deinen Lösungsansatz. Ich werde das mal in unser Produktivsystem übertragen und ggf. noch anpassen.

    Gruß Jürgen

    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

    vor 22 Stunden 39 Minuten
  • 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

    vor 13 Stunden 49 Minuten
  • 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

    vor 13 Stunden 10 Minuten