The Applied SQL Data Analytics Workshop - Second Edition

Kup polskie wydanie:

Spis treści książki

• The Applied SQL Data Analytics Workshop
• Second Edition
• Preface
  • About the Book
    • Audience
    • About the Chapters
    • Conventions
    • Setting up Your Environment
    • Installing PostgreSQL 12
    • Downloading and Installing PostgreSQL on Windows
      • Setting the PATH Variable
    • Installation on Linux
    • Installation on MacOS
    • Installing Python
    • Installing Git
    • Loading the Sample Datasets Linux
    • Loading the Sample Datasets MacOS
    • Running SQL Files
    • Installing Libraries
    • Accessing the Code Files
• 1. Introduction to SQL for Analytics
  • Introduction
  • The World of Data
    • Types of Data
    • Data Analytics and Statistics
    • Types of Statistics
    • Activity 1.01: Classifying a New Dataset
  • Methods of Descriptive Statistics
    • Univariate Analysis
    • Data Frequency Distribution
    • Exercise 1.01: Creating a Histogram
      • Quantiles
    • Exercise 1.02: Calculating the Quartiles for Add-on Sales
    • Central Tendency
    • Exercise 1.03: Calculating the Central Tendency of Add-on Sales
    • Dispersion
    • Exercise 1.04: Dispersion of Add-on Sales
    • Bivariate Analysis
    • Scatterplots
      • Pearson Correlation Coefficient
    • Exercise 1.05: Calculating the Pearson Correlation Coefficient for Two Variables
      • Interpreting and Analyzing the Correlation Coefficient
    • Activity 1.02: Exploring Dealership Sales Data
    • Working with Missing Data
  • Statistical Significance Testing
    • Common Statistical Significance Tests
  • Relational Databases and SQL
    • Advantages and Disadvantages of SQL Databases
  • Basic Data Types of SQL
    • Numeric
    • Character
    • Boolean
    • Datetime
    • Data Structures: JSON and Arrays
  • Reading Tables: The SELECT Query
    • Basic Anatomy and Working of a SELECT Query
    • Basic Keywords in a SELECT Query
      • The SELECT and FROM Statements
      • The WHERE Clause
      • The AND/OR Clause
      • The IN/NOT IN Clause
      • The ORDER BY Clause
      • The LIMIT Clause
      • The IS NULL/IS NOT NULL Clause
    • Exercise 1.06: Querying the salespeople Table Using Basic Keywords in a SELECT Query
    • Activity 1.03: Querying the customers Table Using Basic Keywords in a SELECT Query
  • Creating Tables
    • Creating Blank Tables
    • Exercise 1.07: Creating a Table in SQL
    • Creating Tables with SELECT
  • Updating Tables
    • Adding and Removing Columns
    • Adding New Data
    • Updating Existing Rows
    • Exercise 1.08: Updating the Table to Increase the Price of a Vehicle
  • Deleting Data and Tables
    • Deleting Values from a Row
    • Deleting Rows from a Table
    • Deleting Tables
    • Exercise 1.09: Deleting an Unnecessary Reference Table
    • Activity 1.04: Creating and Modifying Tables for Marketing Operations
  • SQL and Analytics
  • Summary
• 2. SQL for Data Preparation
  • Introduction
  • Assembling Data
    • Connecting Tables Using JOIN
    • Types of Joins
      • Inner Joins
      • Outer Joins
      • Cross Joins
    • Exercise 2.01: Using Joins to Analyze a Sales Dealership
    • Subqueries
    • Unions
    • Exercise 2.02: Generating an Elite Customer Party Guest List Using UNION
    • Common Table Expressions
  • Transforming Data
    • The CASE WHEN Function
    • Exercise 2.03: Using the CASE WHEN Function to Get Regional Lists
    • The COALESCE Function
    • The NULLIF Function
    • The LEAST/GREATEST Function
    • The Casting Function
    • The DISTINCT and DISTINCT ON Functions
    • Activity 2.01: Building a Sales Model Using SQL Techniques
  • Summary
• 3. Aggregate and Window Functions
  • Introduction
  • Aggregate Functions
    • Exercise 3.01: Using Aggregate Functions to Analyze Data
  • Aggregate Functions with GROUP BY
    • The GROUP BY Clause
    • Multiple Column GROUP BY
    • Exercise 3.02: Calculating the Cost by Product Type Using GROUP BY
    • Grouping Sets
    • Ordered Set Aggregates
  • The HAVING Clause
    • Exercise 3.03: Calculating and Displaying Data Using the HAVING Clause
  • Using Aggregates to Clean Data and Examine Data Quality
    • Finding Missing Values with GROUP BY
    • Measuring Data Quality with Aggregates
    • Activity 3.01: Analyzing Sales Data Using Aggregate Functions
  • Window Functions
    • The Basics of Window Functions
    • Exercise 3.04: Analyzing Customer Data Fill Rates over Time
    • The WINDOW Keyword
  • Statistics with Window Functions
    • Exercise 3.05: Rank Order of Hiring
    • Window Frame
    • Exercise 3.06: Team Lunch Motivation
    • Activity 3.02: Analyzing Sales Using Window Frames and Window Functions
  • Summary
• 4. Importing and Exporting Data
  • Introduction
  • The COPY Command
    • Copying Data with psql
    • Configuring COPY and \copy
    • Using COPY and \copy to Bulk Upload Data to Your Database
    • Exercise 4.01: Exporting Data to a File for Further Processing in Excel
  • Using R with Our Database
    • Why Use R?
    • Getting Started with R
  • Using Python with Our Database
    • Why Use Python?
    • Getting Started with Python
    • Improving Postgres Access in Python with SQLAlchemy and pandas
    • What is SQLAlchemy?
    • Using Python with Jupyter Notebook
    • Reading and Writing to our Database with pandas
    • Exercise 4.02: Reading Data and Visualizing Data in Python
    • Writing Data to the Database Using Python
    • Improving Python Write Speed with COPY
    • Reading and Writing CSV File with Python
  • Best Practices for Importing and Exporting Data
    • Going Passwordless
    • Activity 4.01: Using an External Dataset to Discover Sales Trends
  • Summary
• 5. Analytics Using Complex Data Types
  • Introduction
  • Date and Time Data Types for Analysis
    • Starting with the date Type
    • Transforming Date Types
    • Intervals
    • Exercise 5.01: Analytics with Time Series Data
  • Performing Geospatial Analysis in PostgreSQL
    • Latitude and Longitude
    • Representing Latitude and Longitude in PostgreSQL
    • Exercise 5.02: Geospatial Analysis
  • Using Array Data Types in PostgreSQL
    • Starting with Arrays
    • Exercise 5.03: Analyzing Sequences Using Arrays
  • Using JSON Data Types in PostgreSQL
    • JSONB: Pre-Parsed JSON
    • Accessing Data from a JSON or JSONB Field
    • Leveraging the JSON Path Language for JSONB Fields
    • Creating and Modifying Data in a JSONB Field
    • Exercise 5.04: Searching through JSONB
  • Text Analytics Using PostgreSQL
    • Tokenizing Text
    • Exercise 5.05: Performing Text Analytics
    • Performing Text Search
    • Optimizing Text Search on PostgreSQL
    • Activity 5.01: Sales Search and Analysis
  • Summary
• 6. Performant SQL
  • Introduction
  • Database Scanning Methods
    • Query Planning
    • Scanning and Sequential Scans
    • Exercise 6.01: Interpreting the Query Planner
    • Activity 6.01: Query Planning
    • Index Scanning
    • B The B-tree Index
    • Exercise 6.02: Creating an Index Scan
    • Activity 6.02: Implementing Index Scans
    • The Hash Index
    • Exercise 6.03: Generating Several Hash Indexes to Investigate Performance
    • Activity 6.03: Implementing Hash Indexes
    • Effective Index Use
  • Performant Joins
    • Exercise 6.04: Determining the Use of Inner Joins
    • Activity 6.04: Implementing Performant Joins
  • Functions and Triggers
    • Function Definitions
    • Exercise 6.05: Creating Functions without Arguments
    • Activity 6.05: Defining a Maximum Sale Function
    • Exercise 6.06: Creating Functions with Arguments
      • The \df and \sf commands
    • Activity 6.06: Creating Functions with Arguments
    • Triggers
    • Exercise 6.07: Creating Triggers to Update Fields
    • Activity 6.07: Creating a Trigger to Track Average Purchases
    • Killing Queries
    • Exercise 6.08: Canceling a Long-Running Query
    • Activity 6.08: Terminating a Long-Running Query
  • Summary
• 7. The Scientific Method and Applied Problem Solving
  • Introduction
  • Case Study
    • The Scientific Method
    • Exercise 7.01: Preliminary Data Collection Using SQL Techniques
    • Exercise 7.02: Extracting the Sales Information
    • Activity 7.01: Quantifying the Sales Drop
    • Exercise 7.03: Launch Timing Analysis
    • Activity 7.02: Analyzing the Difference in the Sales Price Hypothesis
    • Exercise 7.04: Analyzing Sales Growth by Email Opening Rate
    • Exercise 7.05: Analyzing the Performance of the Email Marketing Campaign
    • Conclusions
    • In-Field Testing
  • Summary
• Appendix
  • 1. Introduction to SQL for Analytics
    • Activity 1.01: Classifying a New Dataset
    • Activity 1.02: Exploring Dealership Sales Data
    • Activity 1.03: Querying the customers Table Using Basic Keywords in a SELECT Query
    • Activity 1.04: Creating and Modifying Tables for Marketing Operations
  • 2. SQL for Data Preparation
    • Activity 2.01: Building a Sales Model Using SQL Techniques
  • 3. Aggregate and Window Functions
    • Activity 3.01: Analyzing Sales Data Using Aggregate Functions
    • Activity 3.02: Analyzing Sales Using Window Frames and Window Functions
  • 4. Importing and Exporting Data
    • Activity 4.01: Using an External Dataset to Discover Sales Trends
  • 5. Analytics Using Complex Data Types
    • Activity 5.01: Sales Search and Analysis
  • 6. Performant SQL
    • Activity 6.01: Query Planning
    • Activity 6.02: Implementing Index Scans
    • Activity 6.03: Implementing Hash Indexes
    • Activity 6.04: Implementing Performant Joins
    • Activity 6.05: Defining a Maximum Sale Function
    • Activity 6.06: Creating Functions with Arguments
    • Activity 6.07: Creating a Trigger to Track Average Purchases
    • Activity 6.08: Terminating a Long-Running Query
  • 7. The Scientific Method and Applied Problem Solving
    • Activity 7.01: Quantifying the Sales Drop
    • Activity 7.02: Analyzing the Difference in the Sales Price Hypothesis

pokaż cały spis treści

ukryj recenzje