Machine Learning with SQL : A Complete Guide to Regression

In this video, we’ll explore how to perform Machine Learning with SQL. To be more precise, we’ll explore how to apply regression modeling to the well-known housing prices dataset.

DATASET: https://www.betterdatascience.com/wp-content/uploads/2020/11/HousingPrices.csv

CODE SNIPPETS:


It seems like everybody knows how to train predictive models in languages like Python, R, and Julia. But what about SQL? How can you leverage the power of a well-known database language for machine learning?

SQL has been around for decades, but still isn’t recognized as a language for machine learning. Sure, I’d pick Python any day of the week, but sometimes in-database machine learning is the only option.

We’ll use Oracle Cloud for this article. It’s free, so please register and create an instance of the OLTP database (Version 19c, has 0.2TB of storage). Once done, establish a connection through the SQL Developer Web — or any other tool.

We’ll use a slightly modified version of a well-known Housing prices dataset — you can download it here. I’ve chosen this dataset because it doesn’t require any extensive preparation, so the focus can immediately be shifted to predictive modeling.

The article is structured as follows:

  • Dataset loading and preparation
  • Model settings
  • Model training and evaluation
  • Conclusion

Dataset loading and preparation

If you are following along, I hope you’ve downloaded the dataset already. You need to load it into the database with a tool of your choice — I’m using SQL Developer Web, a free tool provided by Oracle Cloud.

The loading process is straightforward — click on the upload button, choose the CSV file, and click Next a couple of time:

Image for post

Image 1 — Dataset loading with Oracle SQL Developer Web (image by author)

Mine is now stored in the housingprices table. Here’s how the first couple of rows look like:

Image for post

Image 2 — First 10 rows of the loaded dataset (image by author)

Oracle Machine Learning (OML) is a bit strange when it comes to creating models. Your data table must contain a column with an ID — numbers generated from a sequence. Our dataset doesn’t have that by default, so let’s add it manually:

CREATE SEQUENCE seq_housingprices;
ALTER TABLE housingprices ADD (
    row_id INTEGER DEFAULT seq_housingprices.NEXTVAL
);

Image for post

Image 3 — Added a column for row ID (image by author)

There’s only one thing left to do in this section: the train/test split. In SQL, that’s done by creating two views. The first view has 75% of the data randomly sampled, and the second one has the remaining 25%. The percentages were chosen arbitrarily:

BEGIN
    EXECUTE IMMEDIATE 
        'CREATE OR REPLACE VIEW 
        	housing_train_data AS 
        	SELECT * FROM housingprices 
        	SAMPLE (75) SEED (42)';
    EXECUTE IMMEDIATE 
        'CREATE OR REPLACE VIEW 
        	housing_test_data AS 
        	SELECT * FROM housingprices 
        	MINUS 
        	SELECT * FROM housing_train_data';
END;
/

We now have two views created — housing_train_data for training and housing_test_data for testing. There’s still one thing left to do before model training: to specify settings for the model. Let’s do that in the next section.

Model settings

Oracle uses this settings table style for training predictive models. The settings table is made of two columns — one for the name and the other for the value.

Here’s how to create the table:

CREATE TABLE housing_model_settings(
	setting_name VARCHAR2(30),
  	setting_value VARCHAR2(4000)
);

The following information will be stored in this table:

  • Type of model — let’s use a Generalized Linear Model (GLM)
  • Diagnostics table name — for model statistics
  • Data preparation technique — let’s go with the automatic
  • Feature selection — disabled or enabled, let’s go with enabled
  • Feature generation — same as with feature selection

Yes, you are reading this right — all of that is done automatically without the need for your assistance. Let’s fill the settings table next:

BEGIN
    INSERT INTO housing_model_settings VALUES(DBMS_DATA_MINING.ALGO_NAME, DBMS_DATA_MINING.ALGO_GENERALIZED_LINEAR_MODEL);

    -- Row diagnostic statistics
    INSERT INTO housing_model_settings VALUES(DBMS_DATA_MINING.GLMS_DIAGNOSTICS_TABLE_NAME, 'GLMS_HOUSING_DIAG');

    -- Auto data preparation
    INSERT INTO housing_model_settings VALUES(DBMS_DATA_MINING.PREP_AUTO, DBMS_DATA_MINING.PREP_AUTO_ON);

    -- Feature selection
    INSERT INTO housing_model_settings VALUES(DBMS_DATA_MINING.GLMS_FTR_SELECTION, DBMS_DATA_MINING.GLMS_FTR_SELECTION_ENABLE);

    -- Feature generation
    INSERT INTO housing_model_settings VALUES(DBMS_DATA_MINING.GLMS_FTR_GENERATION, DBMS_DATA_MINING.GLMS_FTR_GENERATION_ENABLE);
END;
/

Full Article: https://towardsdatascience.com/machine-learning-with-sql-a-complete-guide-to-regression-b0e5e2db22da

#sql #data-science #machine-learning #developer

What is GEEK

Buddha Community

Machine Learning with SQL : A Complete Guide to Regression
Cayla  Erdman

Cayla Erdman

1594369800

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

Ray  Patel

Ray Patel

1625843760

Python Packages in SQL Server – Get Started with SQL Server Machine Learning Services

Introduction

When installing Machine Learning Services in SQL Server by default few Python Packages are installed. In this article, we will have a look on how to get those installed python package information.

Python Packages

When we choose Python as Machine Learning Service during installation, the following packages are installed in SQL Server,

  • revoscalepy – This Microsoft Python package is used for remote compute contexts, streaming, parallel execution of rx functions for data import and transformation, modeling, visualization, and analysis.
  • microsoftml – This is another Microsoft Python package which adds machine learning algorithms in Python.
  • Anaconda 4.2 – Anaconda is an opensource Python package

#machine learning #sql server #executing python in sql server #machine learning using python #machine learning with sql server #ml in sql server using python #python in sql server ml #python packages #python packages for machine learning services #sql server machine learning services

sophia tondon

sophia tondon

1620898103

5 Latest Technology Trends of Machine Learning for 2021

Check out the 5 latest technologies of machine learning trends to boost business growth in 2021 by considering the best version of digital development tools. It is the right time to accelerate user experience by bringing advancement in their lifestyle.

#machinelearningapps #machinelearningdevelopers #machinelearningexpert #machinelearningexperts #expertmachinelearningservices #topmachinelearningcompanies #machinelearningdevelopmentcompany

Visit Blog- https://www.xplace.com/article/8743

#machine learning companies #top machine learning companies #machine learning development company #expert machine learning services #machine learning experts #machine learning expert

Michael  Hamill

Michael Hamill

1618233360

Get Started with SQL Server Machine Learning Services

We know Humans learn from their past experiences. Mean while Machines follow Instructions given by Humans. But what if Human can train Machines to learn from the past data?. In simple, this is what Machine learning is !!!. SQL Server has capabilities of Machine Learning. In this article, we will discuss about the capabilities of Machine Learning in SQL Server.

#machine learning #sql server #machine learning with sql server #ml in sql server using python #python in sql server ml #sql server machine learning services

Nora Joy

1604154094

Hire Machine Learning Developers in India

Hire machine learning developers in India ,DxMinds Technologies is the best product engineering company in India making innovative solutions using Machine learning and deep learning. We are among the best to hire machine learning experts in India work in different industry domains like Healthcare retail, banking and finance ,oil and gas, ecommerce, telecommunication ,FMCG, fashion etc.
**
Services**
Product Engineering & Development
Re-engineering
Maintenance / Support / Sustenance
Integration / Data Management
QA & Automation
Reach us 917483546629

Hire machine learning developers in India ,DxMinds Technologies is the best product engineering company in India making innovative solutions using Machine learning and deep learning. We are among the best to hire machine learning experts in India work in different industry domains like Healthcare retail, banking and finance ,oil and gas, ecommerce, telecommunication ,FMCG, fashion etc.

Services

Product Engineering & Development

Re-engineering

Maintenance / Support / Sustenance

Integration / Data Management

QA & Automation

Reach us 917483546629

#hire machine learning developers in india #hire dedicated machine learning developers in india #hire machine learning programmers in india #hire machine learning programmers #hire dedicated machine learning developers #hire machine learning developers