Assimil O Novo Italiano sem Esforço

Take your first steps to becoming a fully qualified data analyst by learning how to explore complex datasets Key Features Master each concept through practical exercises and activities Discover various statistical techniques to analyze your data Implement everything you've learned on a real-world case study to uncover valuable insights Book Description Every day, businesses operate around the clock, and a huge amount of data is generated at a rapid pace. This book helps you analyze this data and identify key patterns and behaviors that can help you and your business understand your customers at a deep, fundamental level. SQL for Data Analytics, Third Edition is a great way to get started with data analysis, showing how to effectively sort and process information from raw data, even without any prior experience. You will begin by learning how to form hypotheses and generate descriptive statistics that can provide key insights into your existing data. As you progress, you will learn how to write SQL queries to aggregate, calculate, and combine SQL data from sources outside of your current dataset. You will also discover how to work with advanced data types, like JSON. By exploring advanced techniques, such as geospatial analysis and text analysis, you will be able to understand your business at a deeper level. Finally, the book lets you in on the secret to getting information faster and more effectively by using advanced techniques like profiling and automation. By the end of this book, you will be proficient in the efficient application of SQL techniques in everyday business scenarios and looking at data with the critical eye of�analytics professional. What you will learn Use SQL to clean, prepare, and combine different datasets Aggregate basic statistics using GROUP BY clauses Perform advanced statistical calculations using a WINDOW function Import data into a database to combine with other tables Export SQL query results into various sources Analyze special data types in SQL, including geospatial, date/time, and JSON data Optimize queries and automate tasks Think about data problems and find answers using SQL Who this book is for If you're a database engineer looking to transition into analytics or a backend engineer who wants to develop a deeper understanding of production data and gain practical SQL knowledge, you will find this book useful. This book is also ideal for data scientists or business analysts who want to improve their data analytics skills using SQL. Basic familiarity with SQL (such as basic SELECT, WHERE, and GROUP BY clauses) as well as a good understanding of linear algebra, statistics, and PostgreSQL 14 are necessary to make the most of this SQL data analytics book. Table of Contents Understanding and Describing Data The Basics of SQL for Analytics SQL for Data Preparation Aggregate Functions for Data Analysis Window Functions for Data Analysis Importing and Exporting Data Analytics Using Complex Data Types Performant SQL Using SQL to Uncover the Truth – a Case Study Cover 1 FM 2 Copyright 3 Table of Contents 4 Preface 16 Chapter 1: Understanding and Describing Data 48 Introduction 49 Data Analytics and Statistics 49 Activity 1.01: Classifying a New Dataset 51 Types of Statistics 52 Methods of Descriptive Statistics 53 Univariate Analysis 53 Data Frequency Distribution 53 Exercise 1.01: Creating a Histogram 54 Quantiles 63 Exercise 1.02: Calculating the Quartiles for Add-On Sales 65 Central Tendency 67 Exercise 1.03: Calculating the Central Tendency of Add-On Sales 69 Dispersion 71 Exercise 1.04: Dispersion of Add-On Sales 73 Bivariate Analysis 74 Scatterplots 74 Linear Trend Analysis and Pearson Correlation Coefficient 80 Exercise 1.05: Calculating the Pearson Correlation Coefficient for Two Variables 82 Interpreting and Analyzing the Correlation Coefficient 84 Time Series Data 87 Activity 1.02: Exploring Dealership Sales Data 89 Working with Missing Data 89 Statistical Significance Testing 90 Common Statistical Significance Tests 92 SQL and Analytics 92 Summary 93 Chapter 2: The Basics of SQL for Analytics 96 Introduction 97 The World of Data 98 Types of Data 99 Relational Databases and SQL 99 Advantages and Disadvantages of SQL Databases 101 PostgreSQL Relational Database Management System (RDBMS) 102 Exercise 2.01: Running Your First SELECT Query 105 SELECT Statement 109 The WHERE Clause 113 The AND/OR Clause 114 The IN/NOT IN Clause 116 ORDER BY Clause 118 The LIMIT Clause 122 IS NULL/IS NOT NULL Clause 123 Exercise 2.02: Querying the salespeople Table Using Basic Keywords in a SELECT Query 125 Activity 2.01: Querying the customers Table Using Basic Keywords in a SELECT Query 128 Creating Tables 129 Creating Blank Tables 129 Basic Data Types of SQL 130 Numeric 131 Character 131 Boolean 132 Datetime 132 Data Structures: JSON and Arrays 133 Column Constraints 133 Simple CREATE Statement 134 Exercise 2.03: Creating a Table in SQL 135 Creating Tables with SELECT 136 Updating Tables 138 Adding and Removing Columns 139 Adding New Data 140 Updating Existing Rows 142 Exercise 2.04: Updating the Table to Increase the Price of a Vehicle 143 Deleting Data and Tables 144 Deleting Values from a Row 144 Deleting Rows from a Table 145 Deleting Tables 146 Exercise 2.05: Deleting an Unnecessary Reference Table 148 Activity 2.02: Creating and Modifying Tables for Marketing Operations 149 SQL and Analytics 150 Summary 151 Chapter 3: SQL for Data Preparation 154 Introduction 155 Assembling Data 155 Connecting Tables Using JOIN 156 Types of Joins 160 Inner Joins 160 Outer Joins 165 Cross Joins 171 Exercise 3.01: Using Joins to Analyze a Sales Dealership 173 Subqueries 175 Unions 176 Exercise 3.02: Generating an Elite Customer Party Guest List Using UNION 178 Common Table Expressions 180 Cleaning Data 182 The CASE WHEN Function 182 Exercise 3.03: Using the CASE WHEN Function to Get Regional Lists 183 The COALESCE Function 186 The NULLIF Function 187 The LEAST/GREATEST Functions 189 The Casting Function 190 Transforming Data 191 The DISTINCT and DISTINCT ON Functions 191 Activity 3.01: Building a Sales Model Using SQL Techniques 194 Summary 196 Chapter 4: Aggregate Functions for Data Analysis 198 Introduction 199 Aggregate Functions 199 Exercise 4.01: Using Aggregate Functions to Analyze Data 206 Aggregate Functions with the GROUP BY Clause 208 The GROUP BY Clause 209 Multiple-Column GROUP BY 217 Exercise 4.02: Calculating the Cost by Product Type Using GROUP BY 218 Grouping Sets 219 Ordered Set Aggregates 221 Aggregate Functions with the HAVING Clause 223 Exercise 4.03: Calculating and Displaying Data Using the HAVING Clause 225 Using Aggregates to Clean Data and Examine Data Quality 226 Finding Missing Values with GROUP BY 226 Measuring Data Uniqueness with Aggregates 229 Activity 4.01: Analyzing Sales Data Using Aggregate Functions 230 Summary 231 Chapter 5: Window Functions for Data Analysis 234 Introduction 235 Window Functions 236 The Basics of Window Functions 238 Exercise 5.01: Analyzing Customer Data Fill Rates over Time 245 The WINDOW Keyword 248 Statistics with Window Functions 250 Exercise 5.02: Rank Order of Hiring 251 Window Frame 253 Exercise 5.03: Team Lunch Motivation 256 Activity 5.01: Analyzing Sales Using Window Frames and Window Functions 259 Summary 261 Chapter 6: Importing and Exporting Data 264 Introduction 265 The COPY Command 266 Running the psql Command 266 The COPY Statement 268 \COPY with psql 271 Creating Temporary Views 273 Configuring COPY and \COPY 275 Using COPY and \COPY to Bulk Upload Data to Your Database 276 Exercise 6.01: Exporting Data to a File for Further Processing in Excel 278 Using Python with your Database 284 Getting Started with Python 284 Improving PostgreSQL Access in Python with SQLAlchemy and pandas 289 What is SQLAlchemy? 290 Using Python with SQLAlchemy and pandas 291 Reading and Writing to a Database with pandas 294 Writing Data to the Database Using Python 296 Exercise 6.02: Reading, Visualizing, and Saving Data in Python 296 Improving Python Write Speed with COPY 303 Reading and Writing CSV Files with Python 305 Best Practices for Importing and Exporting Data 307 Going Passwordless 308 Activity 6.01: Using an External Dataset to Discover Sales Trends 309 Summary 311 Chapter 7: Analytics Using Complex Data Types 314 Introduction 315 Date and Time Data types for Analysis 315 The DATE Data type 316 Transforming Date Data types 319 Intervals 322 Exercise 7.01: Analytics with Time Series Data 324 Performing Geospatial Analysis in PostgreSQL 327 Latitude and Longitude 327 Representing Latitude and Longitude in PostgreSQL 328 Exercise 7.02: Geospatial Analysis 331 Using Array Data types in PostgreSQL 335 Starting with Arrays 335 Exercise 7.03: Analyzing Sequences Using Arrays 339 Using JSON Data types in PostgreSQL 342 JSONB: Pre-Parsed JSON 345 Accessing Data from a JSON or JSONB Field 345 Leveraging the JSON Path Language for JSONB Fields 348 Creating and Modifying Data in a JSONB Field 351 Exercise 7.04: Searching through JSONB 352 Text Analytics Using PostgreSQL 356 Tokenizing Text 356 Exercise 7.05: Performing Text Analytics 358 Performing Text Search 364 Optimizing Text Search on PostgreSQL 367 Activity 7.01: Sales Search and Analysis 370 Summary 372 Chapter 8: Performant SQL 374 Introduction 375 The Importance of Highly Efficient SQL 375 Database Scanning Methods 377 Query Planning 378 Exercise 8.01: Interpreting the Query Planner 379 Activity 8.01: Query Planning 384 Index Scanning 385 The B-Tree Index 386 Exercise 8.02: Creating an Index Scan 388 Activity 8.02: Implementing Index Scans 394 The Hash Index 395 Exercise 8.03: Generating Several Hash Indexes to Investigate Performance 396 Activity 8.03: Implementing Hash Indexes 400 Effective Index Use 401 Killing Queries 403 Exercise 8.04: Canceling a Long-Running Query 404 Functions and Triggers 405 Function Definitions 406 Exercise 8.05: Creating Functions without Arguments 408 Activity 8.04: Defining a Largest Sale Value Function 411 Exercise 8.06: Creating Functions with Arguments 412 The \df and \sf commands 414 Activity 8.05: Creating Functions with Arguments 415 Triggers 416 Exercise 8.07: Creating Triggers to Update Fields 419 Activity 8.06: Creating a Trigger to Track Average Purchases 426 Summary 427 Chapter 9: Using SQL to Uncover the Truth – a Case Study 430 Introduction 431 Case Study 431 The Scientific Method 431 Exercise 9.01: Preliminary Data Collection Using SQL Techniques 432 Exercise 9.02: Extracting the Sales Information 436 Activity 9.01: Quantifying the Sales Drop 443 Exercise 9.03: Launch Timing Analysis 445 Activity 9.02: Analyzing the Difference in the Sales Price Hypothesis 455 Exercise 9.04: Analyzing Sales Growth by Email Opening Rate 458 Exercise 9.05: Analyzing the Performance of the Email Marketing Campaign 469 Conclusions 474 In-Field Testing 475 Summary 476 Appendix 478 Author Page 534 Index 536 Take your first steps to becoming a fully qualified data analyst by learning how to explore complex datasetsKey FeaturesMaster each concept through practical exercises and activitiesDiscover various statistical techniques to analyze your dataImplement everything you've learned on a real-world case study to uncover valuable insightsBook DescriptionEvery day, businesses operate around the clock, and a huge amount of data is generated at a rapid pace. This book helps you analyze this data and identify key patterns and behaviors that can help you and your business understand your customers at a deep, fundamental level. SQL for Data Analytics, Third Edition is a great way to get started with data analysis, showing how to effectively sort and process information from raw data, even without any prior experience. You will begin by learning how to form hypotheses and generate descriptive statistics that can provide key insights into your existing data. As you progress, you will learn how to write SQL queries to aggregate, calculate, and combine SQL data from sources outside of your current dataset. You will also discover how to work with advanced data types, like JSON. By exploring advanced techniques, such as geospatial analysis and text analysis, you will be able to understand your business at a deeper level. Finally, the book lets you in on the secret to getting information faster and more effectively by using advanced techniques like profiling and automation. By the end of this book, you will be proficient in the efficient application of SQL techniques in everyday business scenarios and looking at data with the critical eye of analytics professional.What you will learnUse SQL to clean, prepare, and combine different datasetsAggregate basic statistics using GROUP BY clausesPerform advanced statistical calculations using a WINDOW functionImport data into a database to combine with other tablesExport SQL query results into various sourcesAnalyze special data types in SQL, including geospatial, date/time, and JSON dataOptimize queries and automate tasksThink about data problems and find answers using SQLWho this book is forIf you're a database engineer looking to transition into analytics or a backend engineer who wants to develop a deeper understanding of production data and gain practical SQL knowledge, you will find this book useful. This book is also ideal for data scientists or business analysts who want to improve their data analytics skills using SQL.Basic familiarity with SQL (such as basic SELECT, WHERE, and GROUP BY clauses) as well as a good understanding of linear algebra, statistics, and PostgreSQL 14 are necessary to make the most of this SQL data analytics book.