SQL 저장 프로시저를 사용한 데이터 분석 자동화 스크립트

편집자 이미지

# 소개

현재 디지털 시대에 데이터는 저장하기 더 쉬운 상품이 되었습니다. 비즈니스를 위한 풍부한 데이터가 있다는 장점으로 인해 기업이 통찰력을 얻을 수 있도록 데이터를 분석하는 것이 그 어느 때보다 중요해졌습니다.

대부분의 기업에서 데이터는 구조화된 데이터베이스 내에 저장되며, 이를 획득하는 데 SQL이 사용됩니다. SQL을 사용하면 스크립트가 유효한 한 원하는 형식으로 데이터를 쿼리할 수 있습니다.

문제는 때때로 우리가 원하는 데이터를 얻기 위한 쿼리가 복잡하고 동적이지 않다는 것입니다. 이 경우 SQL 저장 프로시저를 사용하여 지루한 스크립트를 간단한 호출 가능 항목으로 간소화할 수 있습니다.

이 문서에서는 SQL 저장 프로시저를 사용하여 데이터 분석 자동화 스크립트를 만드는 방법을 설명합니다.

궁금한? 방법은 다음과 같습니다.

# SQL 저장 프로시저

SQL 저장 프로시저는 데이터베이스 내에 직접 저장된 SQL 쿼리 모음입니다. Python에 능숙하다면 이를 함수로 생각할 수 있습니다. 즉, 일련의 작업을 언제든지 호출할 수 있는 단일 실행 가능 단위로 캡슐화합니다. 이를 동적으로 만들 수 있다는 점에서 유익합니다.

그렇기 때문에 코드를 단순화하고 반복 작업을 자동화할 수 있는 SQL 저장 프로시저를 이해하는 것이 도움이 됩니다.

예제를 통해 시도해 보겠습니다. 이 튜토리얼에서는 MySQL 데이터베이스와 주식 데이터 테이블 예제는 Kaggle에서 가져왔습니다. 로컬 머신에 MySQL Workbench를 설정하고 테이블을 저장할 수 있는 스키마를 생성하세요. 내 예에서는 다음과 같은 데이터베이스를 만들었습니다. finance_db 라는 테이블이 있는 stock_data.

다음과 같은 것을 사용하여 데이터를 쿼리할 수 있습니다.

USE finance_db;

SELECT * FROM stock_data;

일반적으로 저장 프로시저의 구조는 다음과 같습니다.

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

보시다시피 저장 프로시저는 쿼리에 전달되는 매개변수를 수신할 수 있습니다.

실제 구현을 살펴보겠습니다. 예를 들어 특정 날짜 범위의 주식 지표를 집계하는 저장 프로시저를 만들 수 있습니다.

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

위 쿼리에서는 다음과 같은 저장 프로시저를 만들었습니다. AggregateStockMetrics. 이 프로시저는 시작 날짜와 종료 날짜를 매개변수로 받아들입니다. 그런 다음 매개변수는 데이터를 필터링하기 위한 조건으로 사용됩니다.

다음과 같이 저장 프로시저를 호출할 수 있습니다.

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

프로시저는 우리가 전달한 매개변수로 실행됩니다. 저장 프로시저는 데이터베이스에 저장되므로 프로시저가 포함된 데이터베이스에 연결하는 모든 스크립트에서 이를 사용할 수 있습니다.

저장 프로시저를 사용하면 다른 환경에서 로직을 쉽게 재사용할 수 있습니다. 예를 들어 MySQL 커넥터를 사용하여 Python에서 프로시저를 호출하겠습니다.

그렇게 하려면 먼저 라이브러리를 설치하십시오.

pip install mysql-connector-python

그런 다음 데이터베이스에 연결하고, 저장 프로시저를 호출하고, 결과를 검색하고, 연결을 닫는 함수를 만듭니다.

import mysql.connector

def call_aggregate_stock_metrics(start_date, end_date):
    cnx = mysql.connector.connect(
        user="your_username",
        password='your_password',
        host="localhost",
        database="finance_db"
    )
    cursor = cnx.cursor()
    try:
        cursor.callproc('AggregateStockMetrics', [start_date, end_date])
        results = []
        for result in cursor.stored_results():
            results.extend(result.fetchall())
        return results
    finally:
        cursor.close()
        cnx.close()

결과는 아래 출력과 유사합니다.

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

이것이 SQL 저장 프로시저에 대해 알아야 할 전부입니다. 파이프라인에서 스케줄러를 사용하여 자동화를 위해 이를 더욱 확장할 수 있습니다.

# 마무리

SQL 저장 프로시저는 복잡한 쿼리를 반복적인 데이터 분석 작업에 재사용할 수 있는 동적 단일 단위 함수로 캡슐화하는 방법을 제공합니다. 절차는 데이터베이스 내에 저장되며 Python과 같은 다른 스크립트나 응용 프로그램에서 쉽게 사용할 수 있습니다.

이것이 도움이 되었기를 바랍니다.

코넬리우스 유다 위자야 데이터 과학 보조 관리자이자 데이터 작성자입니다. Allianz Indonesia에서 풀타임으로 일하는 동안 그는 소셜 미디어와 글쓰기 미디어를 통해 Python과 데이터 팁을 공유하는 것을 좋아합니다. Cornellius는 다양한 AI 및 기계 학습 주제에 대해 글을 씁니다.