Book Image

Data Analysis Using SQL and Excel - Second Edition

By : Gordon S. S. Linoff
Book Image

Data Analysis Using SQL and Excel - Second Edition

By: Gordon S. S. Linoff

Overview of this book

Data Analysis Using SQL and Excel, 2nd Edition shows you how to leverage the two most popular tools for data query and analysis—SQL and Excel—to perform sophisticated data analysis without the need for complex and expensive data mining tools. Written by a leading expert on business data mining, this book shows you how to extract useful business information from relational databases. You'll learn the fundamental techniques before moving into the "where" and "why" of each analysis, and then learn how to design and perform these analyses using SQL and Excel. Examples include SQL and Excel code, and the appendix shows how non-standard constructs are implemented in other major databases, including Oracle and IBM DB2/UDB. The companion website includes datasets and Excel spreadsheets, and the book provides hints, warnings, and technical asides to help you every step of the way. Data Analysis Using SQL and Excel, 2nd Edition shows you how to perform a wide range of sophisticated analyses using these simple tools, sparing you the significant expense of proprietary data mining tools like SAS.

Related Content you might be interested in

Current Title:

Small book image
Data Analysis Using SQL and Excel - Second Edition
Gordon S. S. Linoff
Dec, 2015 | 800 pages
No titles found
Table of Contents (18 chapters)
Free Chapter
1
Foreword
Foreword
2
Introduction
Introduction
Overview of the Book and Technology
How This Book Is Organized
Who Should Read This Book
Tools You Will Need
What’s on the Website
Summary
3
Chapter 1: A Data Miner Looks at SQL
Chapter 1: A Data Miner Looks at SQL
Databases, SQL, and Big Data
Picturing the Structure of the Data
Picturing Data Analysis Using Dataflows
SQL Queries
Subqueries and Common Table Expressions Are Our Friends
Lessons Learned
4
Chapter 2: What’s in a Table? Getting Started with Data Exploration
Chapter 2: What’s in a Table? Getting Started with Data Exploration
What Is Data Exploration?
Excel for Charting
Sparklines
What Values Are in the Columns?
More Values to Explore—Min, Max, and Mode
Exploring String Values
Exploring Values in Two Columns
From Summarizing One Column to Summarizing All Columns
Lessons Learned
5
Chapter 3: How Different Is Different?
Chapter 3: How Different Is Different?
Basic Statistical Concepts
How Different Are the Averages?
Sampling from a Table
Counting Possibilities
Ratios and Their Statistics
Chi-Square
What Months and Payment Types Have Unusual Affinities for Which Types of Products?
Lessons Learned
6
Chapter 4: Where Is It All Happening? Location, Location, Location
Chapter 4: Where Is It All Happening? Location, Location, Location
Latitude and Longitude
Census Demographics
Geographic Hierarchies
Mapping in Excel
Lessons Learned
7
Chapter 5: It’s a Matter of Time
Chapter 5: It’s a Matter of Time
Dates and Times in Databases
Starting to Investigate Dates
How Long Between Two Dates?
Year-over-Year Comparisons
Counting Active Customers by Day
Simple Chart Animation in Excel
Lessons Learned
8
Chapter 6: How Long Will Customers Last? Survival Analysis to Understand Customers and Their Value
Chapter 6: How Long Will Customers Last? Survival Analysis to Understand Customers and Their Value
Background on Survival Analysis
The Hazard Calculation
Survival and Retention
Comparing Different Groups of Customers
Comparing Survival over Time
Important Measures Derived from Survival
Using Survival for Customer Value Calculations
Forecasting
Lessons Learned
9
Chapter 7: Factors Affecting Survival: The What and Why of Customer Tenure
Chapter 7: Factors Affecting Survival: The What and Why of Customer Tenure
Which Factors Are Important and When
Left Truncation
Time Windowing
Competing Risks
Before and After
Lessons Learned
10
Chapter 8: Customer Purchases and Other Repeated Events
Chapter 8: Customer Purchases and Other Repeated Events
Identifying Customers
RFM Analysis
Which Households Are Increasing Purchase Amounts Over Time?
Time to Next Event
Lessons Learned
11
Chapter 9: What’s in a Shopping Cart? Market Basket Analysis
Chapter 9: What’s in a Shopping Cart? Market Basket Analysis
Exploring the Products
Products and Customer Worth
Product Geographic Distribution
Which Customers Have Particular Products?
Lessons Learned
12
Chapter 10: Association Rules and Beyond
Chapter 10: Association Rules and Beyond
Item Sets
The Simplest Association Rules
One-Way Association Rules
Two-Way Associations
Extending Association Rules
Lessons Learned
13
Chapter 11: Data Mining Models in SQL
Chapter 11: Data Mining Models in SQL
Introduction to Directed Data Mining
Look-Alike Models
Lookup Model for Most Popular Product
Lookup Model for Order Size
Lookup Model for Probability of Response
Naïve Bayesian Models (Evidence Models)
Lessons Learned
14
Chapter 12: The Best-Fit Line: Linear Regression Models
Chapter 12: The Best-Fit Line: Linear Regression Models
The Best-Fit Line
Measuring Goodness of Fit Using R2
Direct Calculation of Best-Fit Line Coefficients
Weighted Linear Regression
More Than One Input Variable
Lessons Learned
15
Chapter 13: Building Customer Signatures for Further Analysis
Chapter 13: Building Customer Signatures for Further Analysis
What Is a Customer Signature?
Designing Customer Signatures
Operations to Build Customer Signatures
Extracting Features
Summarizing Customer Behaviors
Lessons Learned
16
Chapter 14: Performance Is the Issue: Using SQL EffectivelyChapter 14
Chapter 14: Performance Is the Issue: Using SQL EffectivelyChapter 14
Query Engines and Performance
Using Indexes Effectively
When OR Is a Bad Thing
Pros and Cons: Different Ways of Expressing the Same Thing
Window Functions
Lessons Learned
17
EULA
EULA
Appendix Equivalent Constructs Among Databases
Appendix Equivalent Constructs Among Databases
String Functions
Date/Time Functions
Mathematical Functions
Other Functions and Features

Window Functions

Window functions are a very powerful part of the SQL language. They are often the most efficient way to solve problems—this is especially true when the window function is the “natural” method for doing a calculation. They can also be used in clever ways.

Where Window Functions Are Appropriate

What proportion of a state's population lives in each zip code? This query can be answered using “traditional” SQL using a join and an aggregation:

SELECT zc.zcta5, zc.TotPop / s.StatePop
FROM ZipCensus zc JOIN
     (SELECT zc.Stab, SUM(1.0 * zc.TotPop) as StatePop
      FROM ZipCensus zc
      GROUP BY zc.Stab
     ) s
     ON zc.Stab = s.Stab

Although readily expressed in traditional SQL, this is not the best way to answer this question.

One problem with this approach is evident if filtering is needed. Say we were to add a WHERE clause to choose a single state: WHERE stab = 'MA'. The subquery would still process the data for all...

Previous Section
End of Section 6
Next Section