Daten per SQL gruppieren

[jobo]

Zitat:

Zitat von p80286 (Beitrag 1366965)

Ich würde eine Lösung über den Delphi Client bevorzugen, da SQL von Haus aus für Mengen und nicht für (sortierte) Folgen gemacht ist. Im Client kann ich die Abfolge der Daten problemlos berücksichtigen. das Problem jetzt mit SQL zu lösen mag zwar reizvoll sein, aber meiner Meinung nach ist das ineffizient.

Im Prinzip sehe ich das auch so, ich würde aber eine SQL Lösung für solche Probleme nicht pauschal als ineffizient bezeichnen. "Window Functions" sind genau für das "Sortierproblem" gemacht, sie bieten dabei nicht nur eine simple Syntax, als die vielen Workarounds, die es so gibt, sie sind idR auch effizient(er als die Workarounds implementiert).

Wenn ich in Folge sowohl im SQL, einer SP/UDF oder im Client auf die Cursor / Loop Geschichte verzichten kann, ist es auch ein Pluspunkt (effizientere Entwicklung, weniger Resourcenverbrauch)
Wenn leider die Findung eines effizienten Statements an gewisser Funktionalität (oder Kenntnismangel) scheitert, ist man am Ende leider eben doch bei der Standardlösung.

Nach meinen lauen Erfahrungen ist MS mit den Window Functions leider etwas hinterher. Der 2008er Server hat etwas aufgeholt, der 2012 kann noch deutlich mehr, wird aber hier nicht eingesetzt.
Die Sequenz Thematik (die m.E. in diesem und anderen Bereichen eine weitere Steigerung böte/ Notwendigkeit ist), ist dann hier leider noch ein weiterer Sargnagel.

[himitsu]

Zitat:

Zitat von jobo (Beitrag 1366952)

Ein Select würde ich immer einer SP vorziehen, wenn es geht und performant genug ist. Daher war das Select Statement mein Ansatz.

Wenn nur als SELECT, dann eben das Problem auftrennen:

1)

SELECT auf alle Anfangs-Datensätze
* über einen JOIN auf IS NULL oder als Subselect mit NOT EXISTS
* alles auflisten, wo es keinen "zugehörigen" Datensatz mit AbSerNr-1 gibt

2)

und dann über ein rekursives SELECT alle nachfolgenden Datensätze suchen und dranjoinen
(frag mich bitte nicht wie, denn mit dem rekursiven steh ich auf Kriegsfuß und bei deinem DBMS ist das bestimmt eh anders, da ich es bissher nur im PostgreSql gemacht hatte)

oder jeweils den "nächsen" Anfangsdatensatz suchen (alle Anfangs-Datensätze die eine größere AbSerNr haben, das dann ORDER BY und LIMIT 1)
und über ein BETWEEN dann alle dazwischen liegenden AbSerNr dranjoinen

hier bekommen alle zusammengehörigen Datensasätze als GroupID z.B. die AbSerNr des jeweiligen Anfangs-Datensatzes

3)

Und zum Schluss dann GROUP BY und Count() drüber

[jobo]

Zitat:

Zitat von himitsu (Beitrag 1366976)

Zitat:

Zitat von jobo (Beitrag 1366952)

Ein Select würde ich immer einer SP vorziehen, wenn es geht und performant genug ist. Daher war das Select Statement mein Ansatz.

Wenn nur als SELECT, dann eben das Problem auftrennen:

Klar, dann ist man aber -aus meiner Sicht- eher auf der Ebene "Workaround", jenseits von schnell und effizient. Subselects in der Select Clause wären m.E. genau solche Kandidaten, die zwar alles möglich machen, aber zu fürchterlichen Ausführungsplänen führen. Oft nicht schlimm für den Anfang (neue Implementierung, wenig Daten), dann aber mit mit wachsender Betriebsdauer und Datenvolumen kann man irgendwann zusehen, wie sortiert und aggregiert wird.
Der Vorschlag von jasucol mit einer alternativen Datenquelle für "generische" Sequenzen wäre da noch naheliegender, das ist in meinem Statement eingebaut übersichtlich und meinetwegen auch zukunftssicher, weil diese olle Systemtabelle so tief "mit drin" hängt, dass sie nie abgelöst wird. Aber dann ist da noch das 2041 Limit, was es hier konkret grenzwertig macht. (Dann noch eher eine eigene Tabelle die Zahlen von 1 bis 10000 enthält - eben genug jedenfalls, und man wäre auf der sicheren Seite. Die kann man auch an anderer Stelle immer gebrauchen, wenn man keine vernünftigen Sequenzen hat, dann würde es noch mehr Sinn machen)

Recursive Aufrufe in 2008 weiß ich grad auch nicht auswendig, aber an der Stelle fliegt man bei MS SQL 2008 oder niedriger auch gern auf die Nase.
Da ist die gezeigte UDF dann schon ziemlich straight forward und sicher auch relativ flott. Oder man schreibt sie nochmal clientsetig um in Delphi, wie p80286 es machen würde.

Das sind alles so Themen, die im Grunde eigentlich interessant sind, wenn Leute hier aufschlagen und wissen wollen, welchen Server soll ich nehmen (siehe neulich). Bei MS stelle ich im DB Bereich leider oft fest, dass Standards oder Quasi Standards nicht vorhanden / umsetzbar / adaptierbar sind.

Bei den Sequenzen bspw fürchte ich fast, die Einschränkungen, die es dort gibt, sind nicht technischer Natur, sondern sollen den Anwender vor irgendwas schützen. Ich weiß nur nicht wovor.

[nahpets]

Irgendwie hat es mich gereizt.

Keine Ahnung ob brauchbar, getestet mit FireBird, dürfte aber auf andere Datenbanken übersetzbar sein.

SQL-Code:

select SerNr, Typ, Status, Anzahl from
(
  select SerNr, BisNr, Typ, Status,
    Cast(SubString(BisNr from 2 for 10) as Integer) - Cast(SubString(SerNr from 2 for 10) as Integer) As Anzahl
  from
  (
    select Min(SerNr) As SerNr, BisNr, Typ, Status from
    (
      select SerNr, Typ, Status,
        /* Unter Oracle wäre das nvl, bei MSSQL dürfte es IsNull sein. */
        /* Beim letzten Satz fehlt uns die BisNr für die Anzahlberechnung. */
        /* Daher wird hier die folgende "Krücke" benötigt. */
        /* Schön und / oder gar elegant ist was vollkommen anderes, */
        /* und durch das SubString-Gewusel wird das nícht unbedingt pflegeleicht */
        /* oder gar (welch Graus) auf andere SerNr-Arten anpassbar. */
        Coalesce((select min(b.SerNr) from norwegen60 b
                  where a.SerNr < b.SerNr
                  and  (   a.Status <> b.Status
                        or a.Typ <> b.Typ
                       )
                  ),
                  /* LPAD kennt MSSQL nicht. */
                  /* Da müsste man wohl mit sowas in der Art "rumkaspern".
                  /* right(replicate('0',8) + cast(cast(SubString(max(SerNr) from 2 for 10) as Integer) + 1 as varchar(15)),8) */
                  (select 'F'||lpad(cast(SubString(max(SerNr) from 2 for 10) as Integer) + 1,8,'0') from norwegen60)
                 ) as BisNr
      from norwegen60 a
      where SerNr Between 'F05323690' and 'F05323695'
    )
    group by BisNr, Typ, Status
  )
)
order by SerNr, Typ;

Ausgehend von meinen Testversuchen, sollte das Ganze nicht nur bei 'nem Statuswechsel funktionieren, sondern auch bei gleichem Status aber wechselndem Typ.

Sollte die SerNr auch mit anderen Buchstaben beginnen oder gar am Anfang mehr oder weniger viele Buchstaben enthalten, dann wird das Ganze nicht mehr wirklich witzig.

Bei ca. 10000 Sätzen in der Ausgangstabelle und ca. 3200 Sätzen im Ergebnis, benötigt das SQL ca. 4 Sekunden.
Hardware: Ein sehr in die Jahre gekommener Laptop (D810 von Dell)

Viel Vergnügen beim Testen und Auseinanderpflücken ;-)

[norwegen60]

Hallo zusammen,

Danke für die vielen Inputs.
Wenn ich Zeit habe, werde ich mir den Ansatz von Naphet mal anschauen. Es hätte mich gereizt, aber da ich im Moment den neuen Treiber zum Laufen bringen muss werde ich die Gruppierung in Delphi belassen. Sie funktioniert ja bereits.
Es kommen nämlich noch genau die Probleme dazu die auch erwähnt wurden.

• Es ist nicht nur der Status der die Gruppierung bestimmt sondern noch ein paar andere Felder.
• Die DB beinhaltet ca 1.5 Mio Datensätze
• Wenns dumm läuft, ändert sich auch noch der Aufbau der Serien-Nr.

Danke trotzdem
Gerd

[nahpets]

Um das SQL auch auf andere Seriennummerntypen anwendbar zu machen, schlage ich vor, die Seriennummer in den konstanten Teil (in den Beispieldaten das F) und den nummerischen Teil, also die aufsteigende Nummer, in der Tabelle / Datenbank zu unterteilen. Das macht die weitere Verarbeitung deutlich einfacher, erfordert aber eine Datenmigration mit allen erforderlichen Nacharbeiten in vorhandenen Routinen.

Das von oben übernommene SQL könnte dann in etwa so aussehen (ungetestet):

SQL-Code:

select
  Concat(SerConst,Replicate('0', 8 - Len(SerNr)) + SerNr) As SerNr,
  Typ, Status,
  WeiteresFeld1, /* ... */ WeiteredFeldX,
  Anzahl from
(
  select
    SerConst,
    SerNr,
    BisNr,
    Typ,
    Status,
    WeiteresFeld1, /* ... */ WeiteresFeldX,
    (BisNr - SerNr) As Anzahl
  from
  (
    select
      SerConst,
      Min(SerNr) As SerNr,
      BisNr,
      Typ,
      Status,
      WeiteresFeld1, /* ... */ WeiteresFeldX
    from
    (
      select
        SerConst,
        SerNr,
        Typ,
        Status,
        WeiteresFeld1, /* ... */ WeiteresFeldX,
        IsNull((select
                  min(b.SerNr)
                from norwegen60 b
                where a.SerConst = b.SerConst
                and   a.SerNr < b.SerNr
                and ( a.Status <> b.Status
                      or a.Typ <> b.Typ
                      or a.WeiteresFeld1 <> or b.WeiteresFeld1
                      /* ... */
                      or a.WeiteresFeldX <> or b.WeiteresFeldX
                     )
                ),
                (select Concat(SerConst,Replicate('0', 8 - Len(Max(SerNr))) + Max(SerNr)) As SerNr
                 from norwegen60)
              ) as BisNr
      from norwegen60 a
      where SerConst = 'F' and SerNr Between 5323690 and 5323695
      /* Parametrisiert: */
      /* where SerConst = :ParamSerConst and SerNr Between :ParamStartSerNr and :ParamEndeSerNr */
    )
    group by SerConst, BisNr, Typ, Status, WeiteresFeld1, /* ... */ WeiteresFeldX
  )
)
order by 1, 2, 3, 4, 5 /* , ... NrLetztesFeldImSelect */;