Book Image

Data Science with SQL Server Quick Start Guide

By : Dejan Sarka
Book Image

Data Science with SQL Server Quick Start Guide

By: Dejan Sarka

Overview of this book

SQL Server only started to fully support data science with its two most recent editions. If you are a professional from both worlds, SQL Server and data science, and interested in using SQL Server and Machine Learning (ML) Services for your projects, then this is the ideal book for you. This book is the ideal introduction to data science with Microsoft SQL Server and In-Database ML Services. It covers all stages of a data science project, from businessand data understanding,through data overview, data preparation, modeling and using algorithms, model evaluation, and deployment. You will learn to use the engines and languages that come with SQL Server, including ML Services with R and Python languages and Transact-SQL. You will also learn how to choose which algorithm to use for which task, and learn the working of each algorithm.

Related Content you might be interested in

Current Title:

Small book image
Data Science with SQL Server Quick Start Guide
Dejan Sarka
Aug, 2018 | 206 pages
Small book image
SQL Server 2017 Machine Learning Services with R
Julie Koesmarno | Tomaž Kaštrun
Feb, 2018 | 338 pages
Small book image
Hands-On Data Science with SQL Server 2017
Vladimir Muzny | Marek Chmel
Nov, 2018 | 506 pages
Small book image
SQL Server 2017 Integration Services Cookbook
Dejan Sarka | Christo Olivier | Samuel Lester | David Peter Hansen | Christian Cote | Matija Lah
Jun, 2017 | 558 pages
Table of Contents (15 chapters)
Title Page
Title Page
Copyright and Credits
Copyright and Credits
Packt Upsell
Packt Upsell
Contributors
Contributors
Preface
Preface
Free Chapter
1
Writing Queries with T-SQL
Writing Queries with T-SQL
Before starting – installing SQL Server
Core T-SQL SELECT statement elements
Advanced SELECT techniques
Summary
2
Introducing R
Introducing R
Obtaining R
Your first line R of code in R
Learning the basics of the R language
Using R data structures
Summary
3
Getting Familiar with Python
Getting Familiar with Python
Selecting the Python environment
Writing your first python code
Using functions, branches, and loops
Organizing the data
Integrating SQL Server and ML
Summary
4
Data Overview
Data Overview
Getting familiar with a data science project life cycle
Ways to measure data values
Introducing descriptive statistics for continuous variables
Using frequency tables to understand discrete variables
Showing associations graphically
Summary
5
Data Preparation
Data Preparation
Handling missing values
Creating dummies
Discretizing continuous variables
The entropy of a discrete variable
Advanced data preparation topics
Summary
6
Intermediate Statistics and Graphs
Intermediate Statistics and Graphs
Exploring associations between continuous variables
Measuring dependencies between discrete variables
Discovering associations between continuous and discrete variables
Expressing dependencies with a linear regression formula
Summary
7
Unsupervised Machine Learning
Unsupervised Machine Learning
Installing ML services (In-Database) packages 
Performing market-basket analysis
Finding clusters of similar cases
Principal components and factor analyses
Summary
8
Supervised Machine Learning
Supervised Machine Learning
Evaluating predictive models
Using the Naive Bayes algorithm
Predicting with logistic regression
Trees, forests, and more trees
Predicting with T-SQL
Summary
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think
Index
Index

Using frequency tables to understand discrete variables

For an overview of discrete variables, you use frequency tables and charts. A frequency table can show the following:

  • The values
  • The count of the values, or the absolute frequency
  • The proportion of the value, or the absolute percentage
  • The cumulative frequency
  • The cumulative percent
  • Plus, you can create a bar chart or a histogram of the values' absolute percentage

Minimally, you need to calculate the counts of the distinct values of the variable.

Let me start with calculating the frequencies with T-SQL. Window aggregate functions are very handy here. The following query is a very efficient one. If you look at the first part of the query, you'll notice that there is a common table expression (CTE) query that calculates the absolute numbers, or the counts. The cumulative values—the running totals—are calculated with the help of window aggregate functions.

The query calculates the frequencies of the CommuteDistance variable from the dbo.vTargetMail...

Previous Section
End of Section 5
Next Section