SQL Deep Dive: Mastering -Fensterfunktionen, gemeinsame Tabellenausdrücke (CTEs) und gespeicherte Verfahren

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!