SQL bietet drei leistungsstarke Funktionen: Fensterfunktionen, gemeinsame Tabellenausdrücke (CTEs) und gespeicherte Verfahren. 1. Fensterfunktionen erm?glichen die Gruppierung und Sortiervorg?nge, ohne den Datensatz zu ?ndern. 2.CTes bieten tempor?re Ergebniss?tze zur Vereinfachung komplexer Abfragen. 3. Die gespeicherten Verfahren sind vorkompilierte SQL -Codebl?cke, die wiederholt ausgeführt werden k?nnen, um die Effizienz und Konsistenz zu verbessern.

Einführung

In einer datengetriebenen Welt ist SQL nicht nur eine Abfragesprache, sondern auch eine Kunst. Heute werden wir in drei leistungsstarke Merkmale in SQL: Fensterfunktionen, gemeinsame Tabellenausdrücke (CTEs) und gespeicherte Verfahren eintauchen. In diesem Artikel lernen Sie, wie Sie diese Tools verwenden, um mit komplexen Datenproblemen umzugehen, Ihre SQL -F?higkeiten zu verbessern und sich bei der Datenanalyse und dem Management wohl zu fühlen.

überprüfung des Grundwissens

Der Charme von SQL liegt in seiner Einfachheit und leistungsstarken Funktionen. Mit Fensterfunktionen k?nnen Sie Daten gruppieren und sortieren, ohne den Datensatz zu ?ndern. CTEs bieten einen vorübergehenden Ergebnis, um komplexe Abfragen lesbarer und verwalteter zu gestalten. Speichernde Verfahren sind vorkompilierte SQL -Codebl?cke, die wiederholt ausgeführt werden k?nnen, um die Effizienz und Konsistenz zu verbessern.

Kernkonzept oder Funktionsanalyse

Fensterfunktionen

Fensterfunktionen sind ein magisches Tool in SQL, mit dem Sie Daten gruppieren und sortieren k?nnen, ohne den Datensatz zu ?ndern. Sie sind sehr nützlich in der Datenanalyse, da sie Ihnen helfen k?nnen, bewegende Durchschnittswerte, Rankings, kumulative Summen und mehr zu berechnen.

 W?HLEN 
    Mitarbeiter_id,
    Gehalt,
    AVG (Gehalt) über (Partition durch Abteilung) als avg_department_salary,
    Rank () over (Order by Gehalt Desc) als Gehaltsrank
AUS 
    Mitarbeiter;

In diesem Beispiel berechnen wir das durchschnittliche Gehalt für die Abteilung jedes Mitarbeiters und bewerten die Mitarbeiter anhand ihres Gehalts. Die Kraft der Fensterfunktionen besteht darin, dass Sie mehrere Berechnungen in derselben Abfrage durchführen k?nnen, ohne Unterabfragen oder Selbstjoins zu verwenden.

Gemeinsame Tabellenausdrücke (CTEs)

CTEs sind tempor?re Ergebniss?tze in SQL, die die Struktur komplexer Abfragen vereinfachen und den Code lesbarer und gepflegt machen. CTEs sind besonders nützlich bei rekursiven Abfragen, da sie sich auf sich selbst beziehen k?nnen.

 Mit rekursivem Mitarbeiter_Hierarchie als ((
    W?hlen Sie Mitarbeiter_ID, Manager_ID, 0 als Ebene
    Von Mitarbeitern
    Wobei Manager_id null ist
    Gewerkschaft alle
    W?hlen Sie e.aMployee_id, e.Manager_id, Eh.level 1
    Von Mitarbeitern e
    Schlie?en Sie die Mitarbeiter_Hierarchie EH auf e.Manager_id = Eh.Amployee_id bei
)
W?hlen Sie * von Employee_hierarchy;

In diesem Beispiel verwenden wir CTE, um die Hierarchie der Mitarbeiter aufzubauen. Die rekursive Natur von CTE erm?glicht es uns, den gesamten Mitarbeiterbaum leicht zu durchqueren, ohne komplexe Selbstverbindungsanfragen zu schreiben.

Gespeicherte Verfahren

Speichernde Prozeduren sind vorkompilierte Bl?cke des SQL -Code, die wiederholt ausgeführt werden k?nnen. Sie sind nützlich, wenn Sie eine komplexe Logik ausführen oder die Leistung verbessern müssen, da sie den Netzwerkverkehr reduzieren und die Zeit kompilieren.

 Erstellen Sie die Prozedur Get_EMPOTEE_DETAILS (in EMP_ID INT)
BEGINNEN
    W?HLEN 
        e.aMlyee_id,
        E.First_Name,
        E. last_name,
        D. Department_Name
    AUS 
        Mitarbeiter e
    VERBINDEN 
        Abteilungen d auf E. Department_id = D. Department_ID
    WO 
        e.Anlaee_id = emp_id;
ENDE;

In diesem Beispiel erstellen wir ein gespeichertes Verfahren, um Mitarbeiterdetails zu erhalten. Der Vorteil gespeicherter Verfahren besteht darin, dass sie eine komplexe Logik zusammenfassen und mehrmals aufgerufen werden k?nnen, wodurch die Wiederverwendbarkeit und Konsistenz von Code verbessert werden kann.

Beispiel für die Nutzung

Grundnutzung

Die grundlegende Verwendung von Fensterfunktionen ist sehr einfach. Sie k?nnen die OVER verwenden, um das Fenster zu definieren und verschiedene Aggregatfunktionen zu verwenden, um das Ergebnis zu berechnen.

 W?HLEN 
    product_id,
    sale_date,
    sale_amount,
    Sum
AUS 
    Verk?ufe;

In diesem Beispiel berechnen wir die akkumulierten Verk?ufe für jedes Produkt. PARTITION BY Klausel gruppiert Daten und ORDER BY Klausel definiert die Reihenfolge der Fenster.

Die grundlegende Verwendung von CTES ist ebenfalls sehr einfach. Sie k?nnen das WITH Schlüsselwort verwenden, um einen CTE zu definieren und es dann in nachfolgenden Abfragen zu verweisen.

 Mit top_sellern als (
    W?hlen Sie Product_id, sum (sale_amount) als Total_Sales aus
    Aus dem Verkauf
    Gruppe von product_id
    Bestellung von Total_Sales Desc
    Grenze 10
)
W?hlen Sie * von top_sellers aus;

In diesem Beispiel verwenden wir CTE, um die 10 Produkte mit dem h?chsten Umsatz zu finden. CTE macht die Abfragestruktur klarer und leichter zu verwalten.

Die grundlegende Verwendung gespeicherter Verfahren ist ebenfalls sehr einfach. Sie k?nnen mit der Anweisung CREATE PROCEDURE eine gespeicherte Prozedur definieren und dann die CALL -Anweisung verwenden, um sie aufzurufen.

 Rufen Sie Get_EMPOTEE_DETAILS (1) an;

In diesem Beispiel nennen wir das zuvor definierte gespeicherte Verfahren, um Mitarbeiterdetails mit der Mitarbeiter -ID 1 zu erhalten.

Erweiterte Verwendung

Die erweiterte Verwendung von Fensterfunktionen umfasst die Verwendung von ROWS oder RANGE , um den Umfang eines Fensters zu definieren, und die Verwendung von LAG und LEAD -Funktionen, um auf die Daten der Vorder- und Rückzeilen zuzugreifen.

 W?HLEN 
    product_id,
    sale_date,
    sale_amount,
    Lag (sale_amount) over (partition by product_id order von sale_date) als vorheriger_sale,
    Lead (sale_amount) over (partition von product_id order von sale_date) als next_sale
AUS 
    Verk?ufe;

In diesem Beispiel verwenden wir LAG und LEAD -Funktionen, um den vorherigen und n?chsten Verkauf jedes Produkts zu erhalten. Eine solche erweiterte Verwendung kann Ihnen helfen, komplexere Datenanalysen durchzuführen.

Die erweiterte Verwendung von CTEs umfasst die Verwendung rekursiver CTEs zur Verarbeitung hierarchischer Daten und die Verwendung mehrerer CTEs zur Vereinfachung komplexer Abfragen.

 Mit rekursiver Kategorie_Hierarchie als ((
    W?hlen Sie category_id, Eltern_Category_id, 0 als Ebene
    Aus Kategorien
    Wobei Eltern_Category_id null ist
    Gewerkschaft alle
    W?hlen Sie C.Category_id, C.Parent_Category_ID, Ch.Level 1
    Aus Kategorien c
    Join Category_hierarchy CH auf C.Parent_Category_id = ch.category_id
),
product_categories als (
    W?hlen Sie P.Product_id, Ch.Category_ID, Ch.Level
    Aus Produkten p
    Join Category_hierarchy CH auf p.category_id = ch.category_id
)
W?hlen Sie * aus product_categories;

In diesem Beispiel verwenden wir rekursives CTE, um die Hierarchie der Produktkategorie zu erstellen und dann ein anderes CTE zu dem Produkt mit seiner Kategorie zu verbinden. Eine solche erweiterte Verwendung kann Ihnen helfen, mit komplexen hierarchischen Daten umzugehen.

Die fortgeschrittene Verwendung gespeicherter Verfahren umfasst die Verwendung von Cursors, Ausnahmebehandlung und Transaktionsmanagement zur Implementierung komplexer Gesch?ftslogik.

 Create Procedure update_aMployee_salary (in emp_id int, in New_Salary Decimal (10, 2))
BEGINNEN
    Deklary Exit -Handler für SQLEXception
    BEGINNEN
        Rollback;
        Resignal;
    ENDE;

    Transaktion starten;
    Mitarbeiter aktualisieren
    Setzen Sie Gehalt = new_salary
    Wo Mitarbeiter_id = emp_id;
    BEGEHEN;
ENDE;

In diesem Beispiel erstellen wir eine gespeicherte Prozedur, um die Geh?lter der Mitarbeiter zu aktualisieren. Speichernde Verfahren verwenden das Transaktionsmanagement und die Ausnahmebehandlung, um die Datenkonsistenz und Integrit?t zu gew?hrleisten.

H?ufige Fehler und Debugging -Tipps

Ein h?ufiger Fehler bei der Verwendung von Fensterfunktionen besteht darin, die OVER zu vergessen. Dies führt dazu, dass der SQL -Engine die Fensterfunktionen nicht richtig analysiert.

 - Fehler bei Beispiels ausw?hlen 
    Mitarbeiter_id,
    Gehalt,
    AVG (Gehalt) - fehlte über Klausel von 
    Mitarbeiter;

Um diesen Fehler zu vermeiden, stellen Sie sicher, dass die OVER bei der Verwendung der Fensterfunktion immer enthalten ist.

Bei der Verwendung von CTEs besteht ein h?ufiger Fehler darin, alle erforderlichen Spalten im CTE zu vergessen. Dies führt dazu, dass nachfolgende Abfragen die Daten im CTE nicht korrekt referenzieren.

 - Fehlerbeispiel mit top_sellern als (
    W?hlen Sie Product_id - Total_Sales -Spalte, die im Verkauf fehlen
    Gruppe von product_id
    Bestellung von Total_Sales Desc
    Grenze 10
)
W?hlen Sie * von top_sellers aus;

Um diesen Fehler zu vermeiden, stellen Sie sicher, dass alle erforderlichen Spalten bei der Definition des CTE einbezogen werden.

Ein h?ufiger Fehler bei der Verwendung gespeicherter Verfahren ist, dass es vergisst, Ausnahmen zu behandeln. Dies kann dazu führen, dass gespeicherte Verfahren die Transaktion nicht korrekt zurückrollen, wenn sie auf einen Fehler sto?en.

 - Fehler beispielsweise Erstellen Sie Prozedur update_eployee_salary (in emp_id int, in New_Salary Decimal (10, 2))
BEGINNEN
    Mitarbeiter aktualisieren
    Setzen Sie Gehalt = new_salary
    Wo Mitarbeiter_id = emp_id;
ENDE;

Um diesen Fehler zu vermeiden, nehmen Sie sicher, dass Sie die Ausnahmeregelung und das Transaktionsmanagement in das gespeicherte Verfahren einbeziehen.

Leistungsoptimierung und Best Practices

Bei der Verwendung von Fensterfunktionen besteht ein wichtiger Punkt bei der Leistungsoptimierung darin, den entsprechenden Fensterrahmen auszuw?hlen. Die Verwendung ROWS oder RANGE kann die Abfrageleistung erheblich verbessern, da sie das Rechenvolumen der Fensterfunktionen verringern k?nnen.

 - Optimierungsbeispiel ausw?hlen 
    product_id,
    sale_date,
    sale_amount,
    Sum (sale_amount) over (partition by product_id order by sale_date Zeilen zwischen unbegrenzten vorhergehenden und aktuellen Zeile) als Running_total
AUS 
    Verk?ufe;

In diesem Beispiel verwenden wir die ROWS , um das Fenster -Framework zu definieren, wodurch die Abfrageleistung verbessert wird.

Bei der Verwendung von CTEs besteht ein wichtiger Punkt bei der Leistungsoptimierung darin, die Verwendung komplexer Berechnungen in CTEs zu vermeiden. CTEs sind tempor?re Ergebniss?tze, die die Abfrageleistung beeinflussen k?nnen, wenn sie komplexe Berechnungen enthalten.

 - Optimierungsbeispiel mit Sales_Summary AS (
    W?hlen Sie Product_id, sum (sale_amount) als Total_Sales aus
    Aus dem Verkauf
    Gruppe von product_id
)
W?hlen Sie * aus Sales_Summary;

In diesem Beispiel stellen wir komplexe Berechnungen au?erhalb von CTE ein, um die Abfrageleistung zu verbessern.

Bei der Verwendung gespeicherter Verfahren besteht ein wichtiger Punkt bei der Leistungsoptimierung darin, die Verwendung von Cursors in gespeicherten Verfahren zu vermeiden. Cursor k?nnen Leistungsverschlechterungen verursachen, da sie die Datenlinie nach Linie verarbeiten müssen.

 - Optimierungsbeispiel erstellen Prozedur update_eplede_salaries ()
BEGINNEN
    Mitarbeiter aktualisieren
    Setzen Sie Gehalt = Gehalt * 1.1;
ENDE;

In diesem Beispiel vermeiden wir die Verwendung von Cursors und verwenden Batch -Update -Vorg?nge, um die Leistung zu verbessern.

Beim Schreiben von SQL -Code umfassen Best Practices die Verwendung von aussagekr?ftigem Alias, Annotierende Code und das Leswert und die Wartung des Codes.

 - Best Practice Beispiel ausw?hlen 
    e.Anlaee_id als emp_id, - verwenden aussagekr?ftige alias e.first_name, - Kommentarcode E. last_name,
    D. Department_Name - Halten Sie die Lesbarkeit und Wartbarkeit der Code von 
    Mitarbeiter e
VERBINDEN 
    Abteilungen d auf E. Department_id = D. Department_id;

Durch die Befolgung dieser Best Practices k?nnen Sie effizientere und wartbarere SQL -Code schreiben.

W?hrend der Erforschung von SQL haben wir nicht nur die grundlegende und erweiterte Verwendung von Fensterfunktionen, CTEs und gespeicherten Verfahren gemeistert, sondern auch gelernt, wie man h?ufige Fehler vermeidet und die Leistung optimiert. Ich hoffe, dieser Artikel kann Ihnen helfen, diese leistungsstarken SQL -Funktionen besser zu verstehen und anzuwenden und einen gr??eren Erfolg bei der Datenanalyse und bei der Verwaltung zu erzielen.

Das obige ist der detaillierte Inhalt vonSQL Deep Dive: Mastering -Fensterfunktionen, gemeinsame Tabellenausdrücke (CTEs) und gespeicherte Verfahren. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!