Auswählen SUMME der TOP 2 Werte in einer Tabelle mit mehreren GRUPPE in SQL

Spiele ich schon mit sets in SQL Server 2000 und habe folgende Tabellenstruktur für eine meiner temporären Tabellen (#Perioden):

 RestCTR HoursCTR Dauer Rest 
---------------------------------------- 
1 337 2 0 
2 337 46 1 
3 337 2 0 
4 337 46 1 
5 338 1 0 
6 338 46 1 
7 338 2 0 
8 338 46 1 
9 338 1 0 
10 339 46 1 
...

Was ich gerne machen würde, ist die Berechnung der Summe der 2 längsten Ruhezeiten für jeden HoursCTR, vorzugsweise mit sets, und temporäre Tabellen (eher als Cursor, oder verschachtelte Unterabfragen).

Hier ist das Traum-Abfrage, die einfach nicht funktioniert in SQL (egal wie oft ich es laufen):

Select HoursCTR, SUM ( TOP 2 Duration ) as LongestBreaks
FROM #Periods
WHERE Rest = 1
Group By HoursCTR

Den HoursCTR kann eine beliebige Anzahl von Ruhezeiten (einschließlich keine).

Meine aktuelle Lösung ist nicht sehr elegant und im Grunde umfasst die folgenden Schritte:

  1. Man die max Dauer der rest, Gruppe, durch HoursCTR
  2. Wählen Sie den ersten (min) RestCTR Zeile zurückgibt, die diese max Dauer für jede HoursCTR
  3. Wiederholen Sie Schritt 1 (ohne die Zeilen, die bereits gesammelt in Schritt 2)
  4. Wiederholen Sie Schritt 2, (wieder, ohne Zeilen gesammelt in Schritt 2)
  5. Kombinieren die RestCTR Zeilen (Schritt 2 und 4) in eine einzige Tabelle
  6. Bekommen SUMME der Dauer wies auf die durch die Zeilen in Schritt 5, gruppiert nach HoursCTR

Wenn es irgendwelche set-Funktionen, schneiden Sie diesen Prozess verlangsamen, dann wären Sie sehr willkommen.

InformationsquelleAutor alex.zambila | 2010-09-29
  1. 7

    Der beste Weg, dies zu tun in der SQL-Server ist mit einem common table expression, die Nummerierung der Zeilen in jeder Gruppe mit der windowing-Funktion ROW_NUMBER():

    WITH NumberedPeriods AS (
  SELECT HoursCTR, Duration, ROW_NUMBER() 
    OVER (PARTITION BY HoursCTR ORDER BY Duration DESC) AS RN
  FROM #Periods
  WHERE Rest = 1
)
SELECT HoursCTR, SUM(Duration) AS LongestBreaks
FROM NumberedPeriods
WHERE RN <= 2
GROUP BY HoursCTR

    edit: ich habe eine ORDER BY-Klausel in der Partitionierung, um die zwei längsten Pausen.

    Mea culpa, ich habe nicht bemerkt, dass Sie dies brauchen, um Ihre Arbeit in Microsoft SQL Server 2000. Diese version keine Unterstützung für CTE-oder Window-Funktionen. Ich überlasse die Antwort oben, falls es jemandem hilft.

    In SQL Server 2000, der gemeinsame Rat ist, verwenden Sie eine korrelierte Unterabfrage:

    SELECT p1.HoursCTR, (SELECT SUM(t.Duration) FROM 
    (SELECT TOP 2 p2.Duration FROM #Periods AS p2
     WHERE p2.HoursCTR = p1.HoursCTR 
     ORDER BY p2.Duration DESC) AS t) AS LongestBreaks
FROM #Periods AS p1
    • wie funktioniert es wählen Sie zwei längste bricht?Was bin ich?
    • OP angegeben SQL-Server 2000. ROW_NUMBER() und CTE sind nicht verfügbar.
    • Vielen Dank, ich verpasst, dass. Ich habe eine andere Lösung.
    • Bill, danke, da dies einer geschachtelten (korrelierte) Unterabfrage, ich habe versucht, es zu vermeiden, aber wenn es nicht zu viel von einem hit, ich würde es prüfen. Habend sagte das, ich kann nicht erhalten Ihr Vorschlag, alle am arbeiten - können Sie, bitte haben Sie einen Blick auf die syntax und sehen, was Beschwörungen müssen gemacht werden, die für SQL Server 2000 zu akzeptieren Sie dieses Angebot?
    • Wahrscheinlich, weil ich vergaß zu geben, die innerste Unterabfrage eine alias-Tabelle. Werde ich Bearbeiten. Ich don ' T haben eine Kopie von MS-SQL-Server 2000 zum testen mit, sorry.
    InformationsquelleAutor Bill Karwin
  2. 2

    SQL 2000 nicht haben CTE ist, noch ROW_NUMBER().

    Korrelierte Unterabfragen können, brauchen Sie einen extra Schritt bei der Verwendung group by.

    Sollte diese Arbeit für Sie:

    SELECT 
    F.HoursCTR,
    MAX (F.LongestBreaks) AS LongestBreaks -- Dummy max() so that groupby can be used.
FROM
    (
        SELECT 
            Pm.HoursCTR, 
            (
                SELECT 
                    COALESCE (SUM (S.Duration), 0)    
                FROM 
                    (
                        SELECT TOP 2    T.Duration
                        FROM            #Periods    AS T
                        WHERE           T.HoursCTR  = Pm.HoursCTR 
                        AND             T.Rest      = 1
                        ORDER BY        T.Duration  DESC
                    ) AS S
             ) AS LongestBreaks
        FROM
            #Periods AS Pm
    ) AS F
GROUP BY
    F.HoursCTR
    InformationsquelleAutor Brock Adams
  3. 1

    Leider für dich, Alex, du hast die richtige Lösung: korrelierte Unterabfragen, je nachdem, wie Sie strukturiert sind, wird am Ende feuern mehrere Male, potenziell geben Sie Hunderten von einzelnen query-Ausführungen.

    Setzen Sie Ihre aktuelle Lösung in den Query Analyzer, aktivieren Sie "Show Execution Plan" (Strg+K), und führen Sie es. Sie haben eine zusätzliche Registerkarte auf der Unterseite, die Ihnen zeigen wird, wie der Motor ging über den Prozess des Sammelns Ihre Ergebnisse. Wenn Sie das gleiche tun mit der korrelierten Unterabfrage, werden Sie sehen, was diese option tut.

    Ich glaube, dass es wahrscheinlich der hammer #Perioden-Tabelle, etwa so viele Male, wie Sie einzelne Zeilen in dieser Tabelle.

    Auch etwas aus über die korrelierte Unterabfrage, scheint mir. Da ich vermeiden wie die Pest, zu wissen, dass Sie sind böse, ich bin mir nicht sicher, wie man über die Festsetzung es.

    • Danke David, ich hatte gehofft, dass jemand vielleicht wissen, der clevere gesetzt tricks, die ich noch nicht entdeckt, aber es sieht aus wie dieser 6-Schritt-Prozess könnte das Ende der Linie.
    • Naja ... das sagst du der Datenbank-engine, was zu tun ist und wie es zu tun, das ist nicht so eine schlechte Sache, wirklich. Der Motor ist nicht alles, was hell, wenn es darauf ankommt. Wie soll es wissen, dass Sie nicht wollen, dass es zu iterativen Müll, wenn Sie sagen, es zu tun?
    InformationsquelleAutor David T. Macknet
Schreibe einen Kommentar