Data default Data Enalytics with SQL prescriptions

Image by seated

Obvious Introduction

The data has become a simple asset to keep current digital season. By the benefit of having a lot of business data, data analyzing to help companies to gain understanding is more important than ever.

For many businesses, information is kept within a formal data, and the SQL is used to find it. With SQL, we can ask the data on the desired form, as long as the text is valid.

The problem is, sometimes, the question of getting the information we want is complex and powerless. In this case, we can use SQL stored procedures to move soft texts to the mountains.

This document also discusses creating data analytics of data with SQL prescriptions.

Want to know? Here it's.

Obvious SQL stored procedures

SQL preservices are a set of SQL questionnaire in the database. If Adept Epython, you can think of it as works: put a series of app on one used unit to call at any time. It helps because we can make it stronger.

That is why it helps to understand the saved SQL processes, which allow to facilitate the code and change repetitive tasks.

Let us try by example. In this lesson, I will use MySQL For information and stock data from kaggle with a table example. Set MySQL Workbench in your local machine and create a schema where we can be able to. In my example, I formed a database called finance_db and a table called stock_data.

We can ask information that uses something similar to the following.

USE finance_db;

SELECT * FROM stock_data;

Usually, the maintenance process has the next structure.

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

As you can see, the stored procedure can find parameters referred to our question.

Let us examine real implementation. For example, we can create a stored procedure in the metrics of stock-covered stock markets.

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 <= p_EndDate);
END $$
DELIMITER ;

In the above question, we create a database called a word AggregateStockMetrics. This procedure welcomes the first day and the last day as parameters. Parameters and then used as conditions filtering data.

You can call a saved procedure as follows:

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

The process will do the parameters more than. As the archived process is stored in the database, you can use from any text that connects to the database contains process.

By gestures, we can also use it easily elsewhere. For example, I will call the process from Python using MySQL connector.

To do that, start installing the library:

pip install mysql-connector-python

After that, create a database connecting work, calls the stored procedure, returning the result, and closes the connection.

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()

The result will be like getting out below.

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

That's all you need to know about SQL prescriptions. You can add this one one more automatically using the schedule to your pipe.

Obvious Rolling up

SQL records provide a way to include complex questions into dynamic energy, the functions of a single unit that can be reused by the repetitive functions of the data. Processes are kept inside the database and easy to use from different text or apps such as Python.

I hope this has helped!

Cornellius Yudha Wijaya It is a scientific science manager and the database author. While working full-time in Allianz Indonesia, she likes to share the python and data advice with social media and media writing. Cornellius writes to a variety of AI and a study machine.