Automatisierungsskripte für die Datenanalyse mit gespeicherten SQL-Prozeduren

Bild vom Herausgeber

# Einführung

Daten sind im aktuellen digitalen Zeitalter zu einem einfacher zu speichernden Intestine geworden. Angesichts des Vorteils, über reichlich Daten für Unternehmen zu verfügen, ist die Analyse von Daten, um Unternehmen dabei zu helfen, Erkenntnisse zu gewinnen, wichtiger denn je.

In den meisten Unternehmen werden Daten in einer strukturierten Datenbank gespeichert und mit SQL erfasst. Mit SQL können wir Daten in der gewünschten Kind abfragen, solange das Skript gültig ist.

Das Downside besteht darin, dass die Abfrage zur Erfassung der gewünschten Daten manchmal komplex und nicht dynamisch ist. In diesem Fall können wir gespeicherte SQL-Prozeduren verwenden, um langwierige Skripte in einfache aufrufbare Skripte zu verwandeln.

In diesem Artikel wird das Erstellen von Datenanalyse-Automatisierungsskripts mit gespeicherten SQL-Prozeduren erläutert.

Neugierig? Hier erfahren Sie, wie.

# Gespeicherte SQL-Prozeduren

Gespeicherte SQL-Prozeduren sind eine Sammlung von SQL-Abfragen, die direkt in der Datenbank gespeichert werden. Wenn Sie sich mit Python auskennen, können Sie sie sich als Funktionen vorstellen: Sie kapseln eine Reihe von Operationen in einer einzigen ausführbaren Einheit, die wir jederzeit aufrufen können. Es ist von Vorteil, weil wir es dynamisch gestalten können.

Aus diesem Grund ist es hilfreich, gespeicherte SQL-Prozeduren zu verstehen, mit denen wir Code vereinfachen und sich wiederholende Aufgaben automatisieren können.

Probieren wir es mal anhand eines Beispiels aus. In diesem Tutorial werde ich verwenden MySQL für die Datenbank und Bestandsdaten von Kaggle für das Tabellenbeispiel. Richten Sie MySQL Workbench auf Ihrem lokalen Laptop ein und erstellen Sie ein Schema, in dem wir die Tabelle speichern können. In meinem Beispiel habe ich eine Datenbank namens erstellt finance_db mit einer Tabelle namens stock_data.

Wir können die Daten wie folgt abfragen.

USE finance_db;

SELECT * FROM stock_data;

Im Allgemeinen hat eine gespeicherte Prozedur die folgende Struktur.

DELIMITER $$
CREATE PROCEDURE procedure_name(param_1, param_2, . . ., param_n)
BEGIN
    instruct_1;
    instruct_2;
    . . .
    instruct_n;
END $$
DELIMITER ;

Wie Sie sehen, kann die gespeicherte Prozedur Parameter empfangen, die an unsere Abfrage übergeben werden.

Sehen wir uns eine tatsächliche Implementierung an. Beispielsweise können wir eine gespeicherte Prozedur erstellen, um Bestandsmetriken für einen bestimmten Datumsbereich zu aggregieren.

USE finance_db;
DELIMITER $$
CREATE PROCEDURE AggregateStockMetrics(
    IN p_StartDate DATE,
    IN p_EndDate DATE
)
BEGIN
    SELECT
        COUNT(*) AS TradingDays,
        AVG(Shut) AS AvgClose,
        MIN(Low) AS MinLow,
        MAX(Excessive) AS MaxHigh,
        SUM(Quantity) AS TotalVolume
    FROM stock_data
    WHERE 
        (p_StartDate IS NULL OR Date >= p_StartDate)
      AND (p_EndDate IS NULL OR Date <= p_EndDate);
END $$
DELIMITER ;

In der obigen Abfrage haben wir die gespeicherte Prozedur mit dem Namen erstellt AggregateStockMetrics. Dieses Verfahren akzeptiert ein Startdatum und ein Enddatum als Parameter. Die Parameter werden dann als Bedingungen zum Filtern der Daten verwendet.

Sie können die gespeicherte Prozedur wie folgt aufrufen:

CALL AggregateStockMetrics('2015-01-01', '2015-12-31');

Die Prozedur wird mit den von uns übergebenen Parametern ausgeführt. Da die gespeicherte Prozedur in der Datenbank gespeichert wird, können Sie sie von jedem Skript aus verwenden, das eine Verbindung zu der Datenbank herstellt, die die Prozedur enthält.

Mit gespeicherten Prozeduren können wir Logik problemlos in anderen Umgebungen wiederverwenden. Ich werde die Prozedur beispielsweise von Python aus über den MySQL-Connector aufrufen.

Installieren Sie dazu zunächst die Bibliothek:

pip set up mysql-connector-python

Erstellen Sie dann eine Funktion, die eine Verbindung zur Datenbank herstellt, die gespeicherte Prozedur aufruft, das Ergebnis abruft und die Verbindung schließt.

import mysql.connector

def call_aggregate_stock_metrics(start_date, end_date):
    cnx = mysql.connector.join(
        consumer="your_username",
        password='your_password',
        host="localhost",
        database="finance_db"
    )
    cursor = cnx.cursor()
    strive:
        cursor.callproc('AggregateStockMetrics', (start_date, end_date))
        outcomes = ()
        for end in cursor.stored_results():
            outcomes.lengthen(consequence.fetchall())
        return outcomes
    lastly:
        cursor.shut()
        cnx.shut()

Das Ergebnis ähnelt der folgenden Ausgabe.

((39, 2058.875660431691, 1993.260009765625, 2104.27001953125, 140137260000.0))

Das ist alles, was Sie über gespeicherte SQL-Prozeduren wissen müssen. Sie können dies zur Automatisierung weiter ausbauen, indem Sie einen Planer in Ihrer Pipeline verwenden.

# Zusammenfassung

Gespeicherte SQL-Prozeduren bieten eine Methode zum Kapseln komplexer Abfragen in dynamische Einzeleinheitenfunktionen, die für sich wiederholende Datenanalyseaufgaben wiederverwendet werden können. Die Prozeduren werden in der Datenbank gespeichert und können problemlos aus verschiedenen Skripten oder Anwendungen wie Python verwendet werden.

Ich hoffe, das hat geholfen!

Cornellius Yudha Wijaya ist stellvertretender Supervisor und Datenautor im Bereich Information Science. Während er Vollzeit bei Allianz Indonesia arbeitet, teilt er gerne Python- und Datentipps über soziale Medien und Schreibmedien. Cornellius schreibt über eine Vielzahl von Themen zu KI und maschinellem Lernen.