SQL w praktyce. Jak dzięki danym uzyskiwać cenne informacje - ebook

Język SQL służy do definiowania, porządkowania i eksplorowania danych w relacyjnych bazach danych. Nieco bardziej złożone zapytania SQL pozwalają na efektywne wydobywanie wiedzy z danych. SQL jest dojrzałym językiem, używanym w wielu systemach bazodanowych. Jednym z nich jest PostgreSQL, darmowy i powszechnie znany, dostępny również dla środowisk chmurowych.

Książka jest przystępnym przewodnikiem po zastosowaniu języka SQL w procesie uzyskiwania informacji zawartych w danych. Zaczniesz od przyswojenia podstawowej wiedzy o bazach danych i SQL, a następnie przystąpisz do analizy prawdziwych zbiorów danych, takich jak demografia spisu ludności w Stanach Zjednoczonych, przejazdy taksówek w Nowym Jorku i szczegóły z krajowego katalogu targów rolniczych. Dzięki ćwiczeniom i przykładom zamieszczonym w każdym rozdziale szybko zaznajomisz się ze wszystkimi, również najnowszymi, narzędziami niezbędnymi do budowania zaawansowanych baz danych PostgreSQL. Zrozumiesz również, jak w szybki i efektywny sposób dane pozwalają zdobyć potrzebne informacje.

Dowiedz się, jak:

• tworzyć bazy PostgreSQL z użyciem własnych danych
• agregować, sortować i filtrować dane w celu zidentyfikowania wzorców
• tworzyć zapytania dla systemów GIS
• używać funkcji do wykonywania działań matematycznych i operacji statystycznych
• tworzyć złożone zapytania i automatyzować zadania

Dobrze napisana i pełna przydatnej wiedzy książka. Dla każdego, kto chce poznać PostgreSQL i uzyskiwać informacje na podstawie danych!

Joshua Allen Holm, OpenSource.com

Przedmowa do wydania drugiego

Wprowadzenie

1. Konfigurowanie środowiska programistycznego

• Instalowanie edytora tekstu
• Pobieranie kodu i danych przykładów z serwisu GitHub
• Instalowanie systemu bazy danych PostgreSQL i narzędzia pgAdmin
  • Instalacja w systemie Windows
  • Instalacja w systemie macOS
  • Instalacja w systemie Linux
• Użycie narzędzia pgAdmin
  • Uruchamianie narzędzia pgAdmin i ustawianie hasła głównego
  • Łączenie się z domyślną bazą danych postgres
  • Zaznajomienie się z narzędziem Query Tool
  • Dostosowywanie narzędzia pgAdmin
• Alternatywy dla narzędzia pgAdmin
• Podsumowanie

2. Tworzenie pierwszej bazy danych i tabeli

• Tabele
• Tworzenie bazy danych
  • Uruchamianie kodu SQL w narzędziu pgAdmin
  • Łączenie się z bazą danych analysis
• Tworzenie tabeli
  • Zastosowanie instrukcji CREATE TABLE
  • Tworzenie tabeli teachers
• Wstawianie wierszy do tabeli
  • Użycie instrukcji INSERT
  • Wyświetlanie danych
• Uzyskiwanie pomocy, gdy kod nie działa poprawnie
• Formatowanie kodu SQL pod kątem czytelności
• Podsumowanie

3. Rozpoczęcie eksplorowaniadanych za pomocą instrukcji SELECT

• Podstawowa składnia instrukcji SELECT
  • Tworzenie zapytania dotyczącego podzbioru kolumn
• Sortowanie danych za pomocą klauzuli ORDER BY
• Zastosowanie słowa kluczowego DISTINCT do znajdowania unikalnych wartości
• Filtrowanie wierszy za pomocą klauzuli WHERE
  • Zastosowanie operatorów LIKE i ILIKE z klauzulą WHERE
  • Łączenie operatorów za pomocą operatorów AND i OR
• Połączenie wszystkiego ze sobą
• Podsumowanie

4. Typy danych

• Typy znakowe
• Typy liczbowe
  • Użycie liczb całkowitych
  • Liczby całkowite automatycznie zwiększane
  • Zastosowanie liczb dziesiętnych
  • Wybieranie swojego liczbowego typu danych
• Daty i czas
• Zastosowanie typu danych interval w obliczeniach
• Formaty JSON i JSONB
• Użycie różnych typów
• Przekształcanie typu wartości na inny typ za pomocą funkcji CAST
• Zastosowanie skróconej notacji funkcji CAST
• Podsumowanie

5. Importowanie i eksportowanie danych

• Praca z plikami tekstowymi z separatorami
  • Obsługa wierszy nagłówka
  • Ujęcie w cudzysłów wartości kolumny zawierających znaki separatora
• Użycie instrukcji COPY do importowania danych
• Importowanie danych spisu ludności opisujących hrabstwa
  • Tworzenie tabeli us_counties_pop_est_2019
  • Kolumny i typy danych zbioru spisu ludności
  • Przeprowadzanie importu zbioru danych spisu ludności za pomocą polecenia COPY
  • Sprawdzanie zaimportowanych danych
• Importowanie podzbioru kolumn za pomocą polecenia COPY
• Importowanie podzbioru wierszy za pomocą instrukcji COPY
• Dodawanie wartości do kolumny w trakcie importowania
• Zastosowanie instrukcji COPY do eksportowania danych
  • Eksportowanie wszystkich danych
  • Eksportowanie określonych kolumn
  • Eksportowanie wyników zapytania
• Importowanie i eksportowanie z użyciem narzędzia pgAdmin
• Podsumowanie

6. Podstawowe operacje matematyczne i statystyczne w języku SQL

• Operatory i funkcje matematyczne
  • Operacje matematyczne i typy danych
  • Dodawanie, odejmowanie i mnożenie
  • Użycie operatorów dzielenia i modulo
  • Zastosowanie wykładników potęgi, pierwiastków kwadratowych i silni
  • Zwracanie uwagi na kolejność operacji
• Wykonywanie operacji matematycznych względem kolumn tabeli spisu ludności
  • Dodawanie i odejmowanie wartości kolumn
  • Określanie wartości procentowych całości
  • Monitorowanie zmiany procentowej
• Użycie funkcji agregujących do obliczania średnich i sum
• Określanie mediany
  • Wyznaczanie mediany za pomocą funkcji percentyla
  • Wyznaczanie mediany i percentyli dla danych spisu ludności
  • Wyznaczanie innych kwantyli przy użyciu funkcji percentyla
• Znajdowanie mody
• Podsumowanie

7. Łączenie tabel w relacyjnej bazie danych

• Łączenie tabel za pomocą klauzuli JOIN
• Powiązanie tabel z kolumnami kluczy
• Uzyskiwanie danych z wielu tabel z użyciem klauzuli JOIN
• Typy złączeń klauzuli JOIN
  • Złączenie JOIN
  • Złączenia LEFT JOIN i RIGHT JOIN
  • Złączenie FULL OUTER JOIN
  • Złączenie CROSS JOIN
• Użycie wartości NULL do znajdowania wierszy z brakującymi wartościami
• Trzy typy relacji między tabelami
  • Relacja jeden do jednego
  • Relacja jeden do wielu
  • Relacja wiele do wielu
• Wybieranie w złączeniu konkretnych kolumn
• Upraszczanie składni złączenia JOIN za pomocą aliasów tabel
• Łączenie wielu tabel
• Łączenie wyników zapytań za pomocą operatorów zbiorów
  • Operatory UNION i UNION ALL
  • Operatory INTERSECT i EXCEPT
• Wykonywanie operacji matematycznych względem kolumn złączonych tabel
• Podsumowanie

8. Optymalny projekt tabel

• Przestrzeganie konwencji nazewniczych
  • Ujęcie identyfikatorów w cudzysłów umożliwia użycie różnych wielkości liter
  • Pułapki związane z ujmowaniem identyfikatorów w cudzysłów
  • Wytyczne dotyczące nadawania nazw identyfikatorom
• Kontrolowanie wartości kolumn za pomocą ograniczeń
  • Klucze główne - naturalne albo zastępcze
  • Klucze obce
  • Metoda automatycznego usuwania powiązanych rekordów z użyciem opcji CASCADE
  • Ograniczenie CHECK
  • Ograniczenie UNIQUE
  • Ograniczenie NOT NULL
  • Metoda usuwania ograniczeń lub późniejszego ich dodawania
• Przyspieszanie zapytań za pomocą indeksów
  • B-drzewo - domyślny indeks systemu PostgreSQL
  • Kwestie dotyczące stosowania indeksów
• Podsumowanie

9. Wyodrębnianie informacji przez grupowanie i podsumowywanie

• Tworzenie tabel badań dotyczących bibliotek
  • Tworzenie tabeli danych bibliotecznych z roku 2018
  • Tworzenie tabel z danymi dotyczącymi bibliotek z roku 2016 i 2017
• Eksplorowanie danych dotyczących bibliotek za pomocą funkcji agregujących
  • Ustalanie liczby wierszy i wartości za pomocą funkcji count()
  • Znajdowanie maksymalnych i minimalnych wartości za pomocą funkcji max() i min()
  • Agregowanie danych za pomocą klauzuli GROUP BY
• Podsumowanie

10. Sprawdzanie i modyfikowanie danych

• Importowanie danych dotyczących producentów mięsa, drobiu i jaj
• Sprawdzanie zbioru danych
  • Sprawdzanie pod kątem brakujących wartości
  • Sprawdzanie pod kątem niespójnych wartości danych
  • Sprawdzanie pod kątem niepoprawnie sformatowanych wartości za pomocą funkcji length()
• Modyfikowanie tabel, kolumn i danych
  • Modyfikowanie tabel za pomocą instrukcji ALTER TABLE
  • Modyfikowanie wartości przy użyciu instrukcji UPDATE
  • Wyświetlanie zmodyfikowanych danych za pomocą klauzuli RETURNING
  • Tworzenie tabel zapasowych
  • Przywracanie brakujących wartości kolumn
  • Aktualizowanie wartości w celu zapewnienia spójności
  • Poprawianie kodów pocztowych za pomocą operacji złączania (konkatenacji)
  • Aktualizowanie wartości między tabelami
• Usuwanie niepotrzebnych danych
  • Usuwanie wierszy z tabeli
  • Usuwanie kolumny z tabeli
  • Usuwanie tabeli z bazy danych
• Użycie transakcji do zapisywania lub cofania zmian
• Zwiększanie wydajności podczas aktualizowania dużych tabel
• Podsumowanie

11. Funkcje statystyczne języka SQL

• Tworzenie tabeli danych statystycznych spisu ludności
  • Pomiar korelacji za pomocą funkcji corr(Y, X)
  • Sprawdzanie dodatkowych korelacji
  • Predykcja wartości z wykorzystaniem analizy regresji
  • Identyfikowanie efektu zmiennej niezależnej za pomocą współczynnika determinacji (r-kwadrat)
  • Określanie zmienności i odchylenia standardowego
• Tworzenie rankingów za pomocą języka SQL
  • Tworzenie rankingów przy użyciu funkcji rank() i dense_rank()
  • Tworzenie rankingu w obrębie podgrup za pomocą klauzuli PARTITION BY
• Obliczanie wartości współczynników do dokonywania sensownych porównań
  • Ustalanie współczynników dla firm z branży turystycznej
• Wygładzanie niejednolitych danych
• Podsumowanie

12. Przetwarzanie dat i czasu

• Typy danych i funkcje obsługujące daty i godziny
• Przetwarzanie dat i godzin
  • Wyodrębnianie składników wartości znacznika czasu
  • Tworzenie wartości dat i godzin za pomocą składników znacznika czasu
  • Uzyskiwanie aktualnej daty i godziny
• Użycie stref czasowych
  • Znajdowanie ustawienia swojej strefy czasowej
  • Ustawianie strefy czasowej
• Wykonywanie obliczeń względem dat i godzin
  • Znajdowanie wzorców w danych dotyczących taksówek w Nowym Jorku
  • Znajdowanie wzorców w danych operatora Amtrak
• Podsumowanie

13. Zaawansowane techniki tworzenia zapytań

• Zastosowanie podzapytań
  • Filtrowanie za pomocą podzapytań użytych w klauzuli WHERE
  • Tworzenie tabel pochodnych przy użyciu podzapytań
  • Złączanie tabel pochodnych
  • Generowanie kolumn za pomocą podzapytań
  • Wyrażenia podzapytań
  • Zastosowanie podzapytań ze słowem kluczowym LATERAL
• Zastosowanie wyrażeń CTE
• Zastosowanie tabel krzyżowych
  • Instalowanie funkcji crosstab()
  • Umieszczanie w tabeli wyników ankiety
  • Zastosowanie tabeli krzyżowej dla odczytów temperatury w mieście
• Ponowne klasyfikowanie wartości za pomocą instrukcji CASE
• Zastosowanie instrukcji CASE w wyrażeniu CTE
• Podsumowanie

14. Przeszukiwanie tekstu w celu znalezienia wartościowych danych

• Formatowanie tekstu za pomocą funkcji łańcuchowych
  • Formatowanie dotyczące wielkości znaków
  • Informacje o znakach
  • Usuwanie znaków
  • Wyodrębnianie i zastępowanie znaków
• Dopasowywanie wzorców tekstowych za pomocą wyrażeń regularnych
  • Notacja wyrażeń regularnych
  • Użycie wyrażeń regularnych z klauzulą WHERE
  • Funkcje z wyrażeniami regularnymi zastępujące lub dzielące tekst
  • Przekształcanie tekstu w dane za pomocą funkcji wyrażeń regularnych
• Wyszukiwanie pełnotekstowe systemu PostgreSQL
  • Typy danych wyszukiwania tekstowego
  • Tworzenie tabeli do wyszukiwania pełnotekstowego
  • Przeszukiwanie tekstu przemówień
  • Tworzenie rankingu dopasowań zapytania według stopnia powiązania
• Podsumowanie

15. Analizowanie danych przestrzennych za pomocą rozszerzenia PostGIS

• Aktywowanie rozszerzenia PostGIS i tworzenie bazy danych przestrzennych
• Bloki konstrukcyjne danych przestrzennych
• Dwuwymiarowe elementy geometryczne
  • Dane formatu WKT
  • Projekcje i układy współrzędnych
  • Identyfikator SRID
• Typy danych rozszerzenia PostGIS
• Tworzenie obiektów przestrzennych za pomocą funkcji rozszerzenia PostGIS
  • Tworzenie typu geometry za pomocą formatu WKT
  • Tworzenie typu geography za pomocą formatu WKT
  • Zastosowanie funkcji punktów
  • Użycie funkcji obiektu LineString
  • Zastosowanie funkcji wielokątów
• Analizowanie danych dotyczących targów rolniczych
  • Tworzenie kolumny typu danych geography i wypełnianie jej
  • Dodawanie indeksu danych przestrzennych
  • Znajdowanie lokalizacji geograficznych w danej odległości
  • Określanie odległości między lokalizacjami geograficznymi
  • Znajdowanie najbliższych lokalizacji geograficznych
• Praca z plikami shapefile spisu ludności
  • Zawartość plików formatu shapefile
  • Ładowanie plików shapefile
  • Eksplorowanie pliku shapefile z danymi spisu ludności hrabstw z roku 2019
  • Sprawdzanie danych demograficznych dla konkretnego dystansu
• Tworzenie złączeń danych przestrzennych
  • Eksplorowanie danych dotyczących dróg gruntowych i wodnych
  • Złączanie tabel danych spisu ludności dotyczących dróg gruntowych i wodnych
  • Znajdowanie miejsca przecięcia obiektów
• Podsumowanie

16. Użycie danych formatu JSON

• Struktura formatu JSON
• Kiedy warto zastosować format JSON razem z kodem języka SQL?
• Zastosowanie typów danych json i jsonb
• Importowanie i indeksowanie danych JSON
• Użycie operatorów wyodrębniania typów danych json i jsonb
  • Wyodrębnianie wartości klucza
  • Wyodrębnianie elementów tablicy
  • Wyodrębnianie ze ścieżki
  • Ograniczanie i obecność
• Analizowanie danych o trzęsieniach ziemi
  • Eksplorowanie i ładowanie danych o trzęsieniach ziemi
  • Przetwarzanie czasu wystąpienia trzęsień ziemi
  • Znajdowanie największych i najczęściej zgłaszanych trzęsień ziemi
  • Przekształcanie w dane przestrzenne danych JSON dotyczących trzęsień ziemi
• Generowanie i przetwarzanie danych formatu JSON
  • Przekształcanie wyników zapytania w dane formatu JSON
  • Dodawanie, aktualizowanie oraz usuwanie kluczy i wartości
• Zastosowanie funkcji przetwarzających dane JSON
  • Ustalanie długości tablicy
  • Zwracanie elementów tablicy jako wierszy
• Podsumowanie

17. Oszczędzanie czasu dzięki widokom, funkcjom i wyzwalaczom

• Zastosowanie widoków do uproszczenia zapytań
  • Tworzenie widoków i wykonywanie dotyczących ich zapytań
  • Tworzenie i odświeżanie widoku zmaterializowanego
  • Wstawianie, aktualizowanie i usuwanie danych za pomocą widoku
• Tworzenie własnych funkcji i procedur
  • Tworzenie funkcji percent_change()
  • Zastosowanie funkcji percent_change()
  • Aktualizowanie danych za pomocą procedury
  • Zastosowanie w funkcji kodu języka Python
• Automatyzowanie operacji w bazie danych za pomocą wyzwalaczy
  • Rejestrowanie w tabeli aktualizacji ocen
  • Automatyczne klasyfikowanie temperatur
• Podsumowanie

18. Praca z systemem PostgreSQL w trybie wiersza poleceń

• Konfigurowanie trybu wiersza poleceń dla narzędzia psql
  • Konfiguracja narzędzia psql w systemie Windows
  • Konfiguracja narzędzia psql w systemie macOS
  • Konfiguracja narzędzia psql w systemie Linux
• Korzystanie z narzędzia psql
  • Uruchamianie narzędzia psql i łączenie się z bazą danych
  • Wykonywanie zapytań języka SQL w oknie narzędzia psql
  • Nawigacja i formatowanie wyników
  • Metapolecenia uzyskujące informacje z bazy danych
  • Importowanie, eksportowanie i użycie plików
• Dodatkowe narzędzia trybu wiersza poleceń usprawniające realizowanie zadań
  • Dodawanie bazy danych za pomocą narzędzia createdb
  • Ładowanie plików z kształtami za pomocą narzędzia shp2pgsql
• Podsumowanie

19. Utrzymanie bazy danych

• Odzyskiwanie niewykorzystywanej przestrzeni za pomocą polecenia VACUUM
  • Monitorowanie rozmiaru tabeli
  • Monitorowanie procesu automatycznego opróżniania
  • Ręczne wykonywanie polecenia VACUUM
  • Ograniczanie rozmiaru tabeli za pomocą polecenia VACUUM FULL
• Modyfikowanie ustawień serwera
  • Lokalizowanie i edytowanie pliku postgresql.conf
  • Ponowne wczytywanie ustawień przy użyciu polecenia pg_ctl
• Tworzenie kopii zapasowych i przywracanie bazy danych
  • Użycie narzędzia pg_dump do eksportowania bazy danych lub tabeli
  • Przywracanie danych wyeksportowanej bazy za pomocą narzędzia pg_restore
  • Sprawdzenie dodatkowych opcji tworzenia kopii zapasowych i przywracania
• Podsumowanie

20. Uzyskiwanie informacji na podstawie danych

• Zacznij od pytania
• Dokumentuj realizowany proces
• Zgromadź swoje dane
• Brak danych? Zbuduj własną bazę danych
• Oceń pochodzenie danych
• Sprawdź dane za pomocą zapytań
• Skontaktuj się z właścicielem danych
• Zidentyfikuj kluczowe wskaźniki i trendy pojawiające się z upływem czasu
• Poproś o wyjaśnienie
• Poinformuj o swoich obserwacjach
• Podsumowanie

Dodatek. Dodatkowe zasoby związane z systemem PostgreSQL

• Środowiska projektowe systemu PostgreSQL
• Narzędzia i rozszerzenia systemu PostgreSQL
• Społeczność i wiadomości związane z systemem PostgreSQL
• Dokumentacja