Анализ данных SQL с помощью запросов Практические семинары

SQL. Analiza danych za pomocą zapytań. Warsztaty praktyczne

Matt Goldwasser, Upom Malik, Benjamin Johnston

Obecnie mamy dostęp do terabajtów danych. To nieprzebrane źródło cennych informacji, które mogą decydować o upadku albo o rozkwicie firmy. Aby jednak wydobyć z danych potrzebną wiedzę, trzeba się wykazać kompetencjami. Są to cenne umiejętności - profesjonalny analityk danych może przebierać w atrakcyjnych ofertach pracy. Spośród różnych technik analizy danych warto się przyjrzeć zastosowaniu zapytań SQL. SQL to język tworzony i rozwijany dla potrzeb pracy z bazami danych, jest więc szczególnie przydatnym narzędziem w przyborniku analityka danych.

Ta książka jest znakomitym przewodnikiem dla początkującego analityka danych. Dzięki niej dowiesz się, jak skutecznie przesiewać i uzyskiwać informacje z surowych danych. Nauczysz się formułować hipotezy i generować opisowe statystyki, a także pisać złożone zapytania SQL, które pozwalają na zagregowanie danych z bazy SQL z danymi pochodzącymi z innych źródeł. Zobaczysz, jak pracować z danymi w różnych formatach, i nauczysz się analizy geoprzestrzennej i analizy tekstu. Poznasz też tajniki pozyskiwania informacji z wykorzystaniem takich metod jak profilowanie i automatyzacja.

W książce:

• przygotowanie danych za pomocą zapytań SQL
• funkcje agregujące i funkcje okna w SQL
• bazy danych i Excel oraz kod w R i w Pythonie
• praca ze złożonymi typami danych
• optymalizacja zapytań SQL
• metodyczne rozwiązywanie problemów

SQL: znakomite narzędzie w profesjonalnej analizie danych!

O autorach

Matt Goldwasser od lat pracuje jako analityk danych w prestiżowych firmach. Lubi rozwiązywać problemy z uczeniem maszynowym i poznawać nowe technologie.

Upom Malik jest naukowcem, zajmuje się analizą danych i stosowaniem SQL do rozwiązywania problemów z branży finansów i energetyki.

Benjamin Johnston zajmuje się zaawansowaną analizą danych w branży medycznej. Interesuje się uczeniem maszynowym, przetwarzaniem obrazów i sieciami neuronowymi.

Spis treści:

• Wprowadzenie

  O książce

  Odbiorcy

  O rozdziałach

  Konwencje stosowane w książce

  Przygotowywanie środowiska

  Instalowanie systemu PostgreSQL 12

  Pobieranie i instalowanie systemu PostgreSQL dla systemu Windows

  Konfigurowanie zmiennej Path

  Instalowanie systemu PostgreSQL w systemie Linux

  Instalowanie systemu PostgreSQL w systemie macOS

  Instalowanie Pythona

  Instalowanie Pythona w systemie Windows

  Instalowanie Pythona w systemie Linux

  Instalowanie Pythona w systemie macOS

  Instalowanie systemu Git

  Instalowanie systemu Git w systemach Windows i macOS X

  Instalowanie systemu Git w systemie Linux

  Pobieranie przykładowych zbiorów danych dla systemu Windows

  Pobieranie przykładowych zbiorów danych dla systemu Linux

  Pobieranie przykładowych zbiorów danych dla systemu macOS

  Uruchamianie plików SQL

  Instalowanie bibliotek

  Pliki z kodem

• 1. Wprowadzenie do SQL-a dla analityków

  Wprowadzenie

  Świat danych

  Rodzaje danych

  Analityka danych i statystyka

  Rodzaje statystyki

  Zadanie 1.01 klasyfikowanie nowego zbioru danych

  Metody z obszaru statystyki opisowej

  Analiza jednoczynnikowa

  Rozkład danych

  Ćwiczenie 1.01 tworzenie histogramu

  Kwantyle

  Ćwiczenie 1.02 obliczanie kwartyli dla sprzedaży dodatków

  Tendencja centralna

  Ćwiczenie 1.03 obliczanie miar tendencji centralnej dla sprzedaży dodatków

  Dyspersja

  Ćwiczenie 1.04 obliczanie dyspersji dla sprzedaży dodatków

  Analiza dwuczynnikowa

  Wykresy punktowe

  Współczynnik korelacji Pearsona

  Ćwiczenie 1.05 obliczanie współczynnika korelacji Pearsona dla dwóch zmiennych

  Interpretowanie i analizowanie współczynnika korelacji

  Dane w postaci szeregów czasowych

  Zadanie 1.02 eksplorowanie danych sprzedażowych z salonu samochodowego

  Praca z niepełnymi danymi

  Testy istotności statystycznej

  Często używane testy istotności statystycznej

  Relacyjne bazy danych i SQL

  Wady i zalety baz SQL-owych

  Podstawowe typy danych w SQL-u

  Typy liczbowe

  Typy znakowe

  Typ logiczny

  Daty i godziny

  Struktury danych format JSON i tablice

  Wczytywanie tabel kwerenda SELECT

  Podstawowa budowa i działanie kwerendy SELECT

  Podstawowe słowa kluczowe w kwerendach SELECT

  Instrukcje SELECT i FROM

  Klauzula WHERE

  Klauzule AND i OR

  Klauzule IN i NOT IN

  Klauzula ORDER BY

  Klauzula LIMIT

  Klauzule IS NULL i IS NOT NULL

  Ćwiczenie 1.06 kwerenda SELECT z podstawowymi słowami kluczowymi dotycząca tabeli salespeople

  Zadanie 1.03 kwerenda SELECT z podstawowymi słowami kluczowymi dotycząca tabeli customers

  Tworzenie tabel

  Tworzenie pustych tabel

  Ograniczenia kolumn

  Ćwiczenie 1.07 tworzenie tabeli w SQL-u

  Tworzenie tabel za pomocą kwerendy SELECT

  Aktualizowanie tabel

  Dodawanie i usuwanie kolumn

  Dodawanie nowych danych

  Aktualizowanie istniejących wierszy

  Ćwiczenie 1.08 aktualizowanie tabeli w celu podniesienia ceny pojazdu

  Usuwanie danych i tabel

  Usuwanie wartości z wiersza

  Usuwanie wierszy z tabeli

  Usuwanie tabel

  Ćwiczenie 1.09 usuwanie niepotrzebnej tabeli

  Zadanie 1.04 tworzenie i modyfikowanie tabel na potrzeby działań marketingowych

  SQL i analityka

  Podsumowanie

• 2. Przygotowywanie danych za pomocą SQL-a

  Wprowadzenie

  Łączenie danych

  Łączenie tabel za pomocą słowa kluczowego JOIN

  Rodzaje złączeń

  Złączenia wewnętrzne

  Złączenia zewnętrzne

  Złączenia krzyżowe

  Ćwiczenie 2.01 używanie złączeń do analizy sprzedaży w salonach

  Podkwerendy

  Sumy

  Ćwiczenie 2.02 generowanie listy gości na przyjęcie dla klientów VIP za pomocą klauzuli UNION

  Wyrażenia WITH

  Przekształcanie danych

  Funkcja CASE WHEN

  Ćwiczenie 2.03 używanie funkcji CASE WHEN do pobierania list klientów z danego regionu

  Funkcja COALESCE

  Funkcja NULLIF

  Funkcje LEAST i GREATEST

  Funkcja CASTING

  Funkcje DISTINCT i DISTINCT ON

  Zadanie 2.01 używanie SQL-a do tworzenia modelu wspomagającego sprzedaż

  Podsumowanie

• 3. Agregacja i funkcje okna

  Wprowadzenie

  Funkcje agregujące

  Ćwiczenie 3.01 używanie funkcji agregujących do analizowania danych

  Funkcje agregujące z klauzulą GROUP BY

  Klauzula GROUP BY

  Klauzula GROUP BY dla kilku kolumn

  Ćwiczenie 3.02 obliczanie cen dla typów produktów za pomocą klauzuli GROUP BY

  Klauzula GROUPING SETS

  Funkcje agregujące dla zbiorów uporządkowanych

  Klauzula HAVING

  Ćwiczenie 3.03 obliczanie wyników i wyświetlanie danych z użyciem klauzuli HAVING

  Stosowanie funkcji agregujących do oczyszczania danych i sprawdzania ich jakości

  Znajdowanie brakujących wartości za pomocą klauzuli GROUP BY

  Pomiar jakości danych za pomocą funkcji agregujących

  Zadanie 3.01 analizowanie danych sprzedażowych z użyciem funkcji agregujących

  Funkcje okna

  Podstawy funkcji okna

  Ćwiczenie 3.04 analizowanie zmian współczynnika podawania danych przez klientów w czasie

  Słowo kluczowe WINDOW

  Obliczanie statystyk z użyciem funkcji okna

  Ćwiczenie 3.05 określanie pozycji na podstawie daty zatrudnienia

  Ramka okna

  Ćwiczenie 3.06 motywowanie pracowników lunchem

  Zadanie 3.02 analizowanie sprzedaży z wykorzystaniem ramek okna i funkcji okna

  Podsumowanie

• 4. Importowanie i eksportowanie danych

  Wprowadzenie

  Polecenie COPY

  Kopiowanie danych za pomocą narzędzia psql

  Konfigurowanie poleceń COPY i \copy

  Użycie poleceń COPY i \copy do masowego wczytywania danych do bazy

  Ćwiczenie 4.01 eksportowanie danych do pliku w celu dalszego przetwarzania ich w Excelu

  Zastosowanie języka R do bazy danych

  Po co korzystać z języka R?

  Wprowadzenie do języka R

  Zastosowanie języka Python do bazy danych

  Po co korzystać z języka Python?

  Wprowadzenie do języka Python

  Ułatwianie dostępu do baz PostgreSQL w Pythonie za pomocą narzędzi SQLAlchemy i pandas

  Czym jest SQLAlchemy?

  Używanie Pythona w narzędziu Jupyter Notebook

  Pobieranie danych z bazy i ich zapisywanie w bazie za pomocą pakietu pandas

  Ćwiczenie 4.02 wczytywanie i wizualizowanie danych w Pythonie

  Zapisywanie danych w bazie za pomocą Pythona

  Zwiększanie szybkości zapisu w Pythonie za pomocą polecenia COPY

  Odczyt i zapis plików CSV w Pythonie

  Najlepsze praktyki z obszaru importowania i eksportowania danych

  Pomijanie podawania hasła

  Zadanie 4.01 używanie zewnętrznego zbioru danych do wykrywania trendów sprzedażowych

  Podsumowanie

• 5. Analityka z wykorzystaniem złożonych typów danych

  Wprowadzenie

  Wykorzystywanie typów danych z datami i czasem do analiz

  Wprowadzenie do typu date

  Przekształcanie typów danych

  Przedziały

  Ćwiczenie 5.01 analiza danych z szeregów czasowych

  Przeprowadzanie analiz geoprzestrzennych w PostgreSQL

  Długość i szerokość geograficzna

  Reprezentowanie długości i szerokości geograficznej w PostgreSQL

  Ćwiczenie 5.02 analizy geoprzestrzenne

  Stosowanie tablicowych typów danych w PostgreSQL

  Wprowadzenie do tablic

  Ćwiczenie 5.03 analizowanie sekwencji z użyciem tablic

  Stosowanie formatu JSON w PostgreSQL

  JSONB wstępnie przetworzone dane w formacie JSON

  Dostęp do danych z pól w formacie JSON lub JSONB

  Stosowanie języka JSONPath do pól w formacie JSONB

  Tworzenie i modyfikowanie danych w polu w formacie JSONB

  Ćwiczenie 5.04 przeszukiwanie obiektów JSONB

  Analiza tekstu za pomocą PostgreSQL

  Tokenizacja tekstu

  Ćwiczenie 5.05 analizowanie tekstu

  Wyszukiwanie tekstu

  Optymalizowanie wyszukiwania tekstu w PostgreSQL

  Zadanie 5.01 wyszukiwanie i analiza transakcji sprzedaży

  Podsumowanie

• 6. Wydajny SQL

  Wprowadzenie

  Metody skanowania baz danych

  Plany wykonywania kwerend

  Skanowanie sekwencyjne i inne metody skanowania

  Ćwiczenie 6.01 interpretowanie działania planera kwerend

  Zadanie 6.01 plany wykonywania kwerendy

  Skanowanie indeksu

  Indeks w postaci B-drzewa

  Ćwiczenie 6.02 kwerenda ze skanowaniem indeksu

  Zadanie 6.02 skanowanie indeksu

  Indeks z haszowaniem

  Ćwiczenie 6.03 tworzenie kilku indeksów z haszowaniem, aby zbadać ich wydajność

  Zadanie 6.03 stosowanie indeksów z haszowaniem

  Skuteczne korzystanie z indeksów

  Wydajne złączenia

  Ćwiczenie 6.04 ocenianie zastosowania złączeń wewnętrznych

  Zadanie 6.04 stosowanie wydajnych złączeń

  Funkcje i wyzwalacze

  Definicje funkcji

  Ćwiczenie 6.05 tworzenie funkcji, które nie przyjmują argumentów

  Zadanie 6.05 definiowanie funkcji zwracającej maksymalną wartość sprzedaży

  Ćwiczenie 6.06 tworzenie funkcji przyjmujących argumenty

  Polecenia \df i \sf

  Zadanie 6.06 tworzenie funkcji przyjmujących argumenty

  Wyzwalacze

  Ćwiczenie 6.07 tworzenie wyzwalaczy do aktualizowania pól

  Zadanie 6.07 tworzenie wyzwalacza do śledzenia średniej liczby kupionych sztuk

  Kończenie pracy kwerend

  Ćwiczenie 6.08 anulowanie długo działającej kwerendy

  Zadanie 6.08 kończenie długo działającej kwerendy

  Podsumowanie

• 7. Metoda naukowa i rozwiązywanie problemów w praktyce

  Wprowadzenie

  Studium przypadku

  Metoda naukowa

  Ćwiczenie 7.01 wstępne zbieranie danych za pomocą technik SQL-a

  Ćwiczenie 7.02 pobieranie informacji sprzedażowych

  Zadanie 7.01 ilościowa ocena spadku sprzedaży

  Ćwiczenie 7.03 analiza czasu rozpoczęcia sprzedaży

  Zadanie 7.02 analiza hipotezy dotyczącej różnicy w cenie sprzedaży

  Ćwiczenie 7.04 analiza zależności wzrostu sprzedaży od współczynnika otwarć e-maili

  Ćwiczenie 7.05 analiza skuteczności e-mailowej kampanii marketingowej

  Wnioski

  Badania terenowe

  Podsumowanie

• Dodatek

  Rozdział 1. Wprowadzenie do SQL-a dla analityków

  Zadanie 1.01 klasyfikowanie nowego zbioru danych

  Rozwiązanie

  Zadanie 1.02 eksplorowanie danych sprzedażowych z salonu samochodowego

  Rozwiązanie

  Zadanie 1.03 kwerenda SELECT z podstawowymi słowami kluczowymi dotycząca tabeli customers

  Rozwiązanie

  Zadanie 1.04 tworzenie i modyfikowanie tabel na potrzeby działań marketingowych

  Rozwiązanie

  Rozdział 2. Przygotowywanie danych za pomocą SQL-a

  Zadanie 2.01 używanie SQL-a do tworzenia modelu wspomagającego sprzedaż

  Rozwiązanie

  Rozdział 3. Agregacja i funkcje okna

  Zadanie 3.01 analizowanie danych sprzedażowych z użyciem funkcji agregujących

  Rozwiązanie

  Zadanie 3.02 analizowanie sprzedaży z wykorzystaniem ramek okna i funkcji okna

  Rozwiązanie

  Rozdział 4. Importowanie i eksportowanie danych

  Zadanie 4.01 używanie zewnętrznego zbioru danych do wykrywania trendów sprzedażowych

  Rozwiązanie

  Rozdział 5. Analizy z wykorzystaniem złożonych typów danych

  Zadanie 5.01 wyszukiwanie i analiza transakcji sprzedaży

  Rozwiązanie

  Rozdział 6. Wydajny SQL

  Zadanie 6.01 plany wykonywania kwerendy

  Rozwiązanie

  Zadanie 6.02 skanowanie indeksu

  Rozwiązanie

  Zadanie 6.03 stosowanie indeksów z haszowaniem

  Rozwiązanie

  Zadanie 6.04 stosowanie wydajnych złączeń

  Rozwiązanie

  Zadanie 6.05 definiowanie funkcji zwracającej maksymalną wartość sprzedaży

  Rozwiązanie

  Zadanie 6.06 tworzenie funkcji przyjmujących argumenty

  Rozwiązanie

  Zadanie 6.07 tworzenie wyzwalacza do śledzenia średniej liczby kupionych sztuk

  Rozwiązanie

  Zadanie 6.08 kończenie długo działającej kwerendy

  Rozwiązanie

  Rozdział 7. Metoda naukowa i rozwiązywanie problemów w praktyce

  Zadanie 7.01 ilościowa ocena spadku sprzedaży

  Rozwiązanie

  Zadanie 7.02 analiza hipotezy dotyczącej różnicy w cenie sprzedaży

  Rozwiązanie