Fuzzy Data Matching with SQL - Helion

ebook

Autor: Jim Lehmer
ISBN: 9781098152239
stron: 284, Format: ebook
Data wydania: 2023-10-03
Księgarnia: Helion

Cena książki: 203,15 zł (poprzednio: 236,22 zł)
Oszczędzasz: 14% (-33,07 zł)

Osoby, które kupiły tę książkę, wybierały także »

If you were handed two different but related sets of data, what tools would you use to find the matches? What if all you had was SQL SELECT access to a database? In this practical book, author Jim Lehmer provides best practices, techniques, and tricks to help you import, clean, match, score, and think about heterogeneous data using SQL.

DBAs, programmers, business analysts, and data scientists will learn how to identify and remove duplicates, parse strings, extract data from XML and JSON, generate SQL using SQL, regularize data and prepare datasets, and apply data quality and ETL approaches for finding the similarities and differences between various expressions of the same data.

Full of real-world techniques, the examples in the book contain working code. You'll learn how to:

Identity and remove duplicates in two different datasets using SQL
Regularize data and achieve data quality using SQL
Extract data from XML and JSON
Generate SQL using SQL to increase your productivity
Prepare datasets for import, merging, and better analysis using SQL
Report results using SQL
Apply data quality and ETL approaches to finding similarities and differences between various expressions of the same data

Osoby które kupowały "Fuzzy Data Matching with SQL", wybierały także:

Windows Media Center. Domowe centrum rozrywki 66,67 zł, (8,00 zł -88%)
Ruby on Rails. Ćwiczenia 18,75 zł, (3,00 zł -84%)
DevOps w praktyce. Kurs video. Jenkins, Ansible, Terraform i Docker 190,00 zł, (39,90 zł -79%)
Przywództwo w świecie VUCA. Jak być skutecznym liderem w niepewnym środowisku 58,64 zł, (12,90 zł -78%)
Scrum. O zwinnym zarządzaniu projektami. Wydanie II rozszerzone 58,64 zł, (12,90 zł -78%)

Spis treści

Fuzzy Data Matching with SQL eBook -- spis treści

Preface
- What Problems Are We Trying to Solve?
- What Will We Cover?
  - Part I: Review
  - Part II: Various Data Problems
  - Part III: Bringing It Together
  - Appendix
- Who Is This Book For?
- Why SQL?
- Warning! Opinions Ahead!
- Typographical Conventions Used in This Book
- Additional Information on the Books Conventions
- The Data Model
  - Environment Layout
  - Customer Table
  - Normalized View
  - Meet the Snedleys
- Using Code Examples
- OReilly Online Learning
- How to Contact Us
- Acknowledgments
I. Review
1. A SELECT Review
- Simple SELECT Statements
- Common Table Expressions
- In CASE of Emergency
- Joins
  - A Diversion into NULL Values
  - OUTER JOINs
  - Finding the Most Current Value
- Final Thoughts on SELECT
2. Function Junction
- Aggregate Functions
  - MAX
  - MIN
  - COUNT
  - SUM
  - AVG
- Conversion Functions
  - CAST and CONVERT
  - COALESCE
  - TRY_CONVERT
- Cryptographic Functions: HASHBYTES
- Date and Time Functions
  - GETDATE
  - DATEADD
  - DATEDIFF
  - DATEPART
  - ISDATE
- Logical Functions: IIF
- String Functions
  - CHARINDEX and PATINDEX
  - LEN
  - LEFT, RIGHT, and SUBSTRING
  - LTRIM, RTRIM, and TRIM
  - LOWER and UPPER
  - REPLACE and TRANSLATE
  - REVERSE
  - STRING_AGG
- System Functions
  - ISNULL
  - ISNUMERIC
- Final Thoughts on Functions
II. Various Data Problems
3. Names, Names, Names
- Whats in a Name?
- Last Names
  - Punctuation
  - Suffixes
- First Names
- Middle Name
- Nicknames
- Company Name
- Full Name
- Person-Like Entities
- Final Thoughts on Names
4. Location, Location, Location
- What Makes an Address?
- Street Address
  - Box, Suite, Lot, or Apartment Number
  - Dont Overdo It!
- City
- County
- State or State Abbreviation
- ZIP or Postal Code
- Country
- Final Thoughts on Locations
5. Dates, Dates, Dates
- Time Is Relative
- Final Thoughts on Dates
6. Email
- What Makes a Valid Email Address?
- Final Thoughts on Email
7. Phone Numbers
- What Makes a Phone Number?
- One Final Note on Tax IDs
- Final Thoughts on Phone Numbers (and Tax IDs)
8. Bad Characters
- Data Representations
- Invisible Whitespace
- COLLATE
- Cleaning Up the Input Data
- Final Thoughts on Bad Characters
9. Orthogonal Data
- A Common Problem, A Common Solution, A New Common Problem
- Lather, Rinse, Repeat
- Final Thoughts on Orthogonal Data
III. Bringing It Together
10. The Big Score
- What Will We Want?
- Tuning Scores
- Eliminating Duplicates
  - Duplicate Data
  - Duplicated Data
- Final Thoughts on Scoring
11. Data Quality, or GIGO
- Sneaking Data Quality In
- Impossible Data
  - Simply Wrong
  - Semantically Wrong
- ETL Your Way to Success
- Final Thoughts on Data Quality
12. Tying It All Together
- Approach
- Whats the Score?
- First Pass: Naive Matching
- Second Pass: Normalizing Relations
  - Impossible Data
  - Now Lets Normalize
- Third Pass: Score!
- What About Tuning?
- Final Thoughts on Practical Matters
13. Code Is Data, Too!
- Working with XML Data
- Working with JSON Data
- Extracting Data from HTML
- Code-Generating Code
- Impact Analysis: The Second Case Study
  - Gather Together Every Code Artifact You Can
  - Import Artifacts into SQL
  - And Now, for My Next Trick
- Final Thoughts on Code As Data
- Final Thoughts on All of It
A. The Data Model
- Customer Table
- NormalizedCustomer View
- PotentialMatches Table
- CustomerCountByState View
- PostalAbbreviations Table
Glossary
Index