SQL Bez Tajemnic - Pytania i Odpowiedzi

Krzysztof Bury
Twój Przewodnik
Cześć!
Przygotowujesz się do rozmowy rekrutacyjnej lub chcesz sprawdzić swoją wiedzę z SQL-a?
Jesteś w dobrym miejscu, przygotowaliśmy dla Ciebie zbiór pytań rekrutacyjnych, dzięki którym język zapytań do baz danych nie będzie miał przed Tobą tajemnic.
Dla Twojej wygody pytania oznaczone są kategoriami, co pozwoli Ci na łatwiejsze wyszukiwanie.
Kategorie: #podstawy, #skladnia #join #wydajnosc, #antywzorce, #teoria, #praktyka
Miłej Nauki!
Schemat tabel użytych w pytaniach:
CREATE TABLE IF NOT EXISTS products ( product_id SERIAL, product_name varchar(500), product_category varchar(250), random_desc text );
CREATE TABLE IF NOT EXISTS sales ( id SERIAL, sales_date TIMESTAMP, sales_amount NUMERIC(38,2), sales_qty INTEGER, discount NUMERIC(38,2), product_id INTEGER );
CREATE TABLE IF NOT EXISTS contacts( imie_nazwisko TEXT, gender TEXT, city TEXT, country TEXT, id_company INTEGER, company TEXT, added_by TEXT, verified_by TEXT );//UWAGA: ta tabele świadomie zawiera błędy strukturalne (nie trzyma się zasad normalizacji itp.) związane są z tym ćwiczenia poniżej
Przykładowe zasilenie danymi (na podstawie PostgreSQL):
INSERT INTO products (product_name, product_category, random_desc) SELECT 'Product '||floor(random() * 10 + 1)::int, 'Category '||floor(random() * 10 + 1)::int, left(md5(i::text), 15) FROM generate_series(1, 1000) s(i);
INSERT INTO sales (sales_date, sales_amount, sales_qty, product_id) SELECT NOW() + (random() * (interval '90 days')) + '30 days', random() * 10 + 1, floor(random() * 10 + 1)::int, floor(random() * 100)::int FROM generate_series(1, 10000) s(i);
INSERT INTO contacts VALUES ('Krzysztof, Bury', 'M', 'Kraków', 'Polska', 1, 'ACME', 'Osoba A', 'Osoba B'), ('Magda, Kowalska', 'F', 'New York', 'USA', 2, 'CDE', 'Osoba C', 'Osoba A');
Schemat tabel dla zadań praktycznych i czystą kartę do pracy znajdziesz również na SQL Fiddle, tutaj: http://sqlfiddle.com/#!17/d89b5 (kliknij Build Schema, żeby móc pracować z panelem wykonywania zapytań).
Odpowiedzi znajdziesz po kliknięciu pytania.
Przykładowe rozwiązania zostały przygotowane na podstawie bazy danych PostgreSQL.
SQL (Structure Query Language) to język zapytań do baz danych.
SQL możemy podzielić na kilka części:
- DDL - Data Definition Language - cześć języka SQL, odpowiadające za tworzenie, modyfikacje czy usuwanie obiektów bazy danych, operacje takie jak CREATE, ALTER, DROP.
- DML - Data Manipluation Langugage - zbiór instrukcji języka SQL, pozwalający na wykonanie operacji INSERT, UPDATE, DELETE (dodawanie, aktualizacja, usuwanie wierszy).
- DQL - Data Query Language - podstawa pobierania danych, czyli SELECT.
- DCL - Data Control Language - element pozwalające na dodawanie lub odbieranie uprawnień, GRANT / REVOKE.
- DTL / TCL - Data Transaction Language / Transaction Control Language - cześć odpowiedzialna za obsługę transakcji: COMMIT, ROLLBACK , SAVEPOINT.
DBMS - Database Management System - jest zbiorem reguł (system) opisanych przez programy komputerowe, które pozwalają na manipulację (manage) wartości atrybutów kilku różnych typów jednostek uporządkowanych w sensowny sposób (database).
DDL - Data Definition Language, jest elementem języka zapytań do baz danych, który ma na celu, tworzenie, modyfikację oraz usuwanie elementów struktury bazy danych - tabele, schematy, indeksy etc.
Do kluczowych elementów składni należą operacje: CREATE, ALTER oraz DELETE.
Pomimo, że powyższe operacje są elementem standardu, to ich dodatkowe elementy są różnie implementowane przez różnych dostawców rozwiązań baz danych. Poniżej przykładowe definicje instrukcji CREATE TABLE u różnych dostawców
CREATE TABLE:
CREATE:
CREATE TABLE sales ( id SERIAL );
ALTER:
ALTER TABLE sales ADD COLUMN sales_amount NUMERIC(10,2);
DROP:
DROP TABLE sales;
DCL (Data Control Language) jest elementem języka zapytań do baz danych, który ma na celu, nadawanie i odbieranie dostępu do obiektów bazy danych.
Do kluczowych elementów składni należą operacje: GRANT / REVOKE (DENY)
- GRANT - nadaję dostępy do obiektów bazodanowych
- REVOKE (DENY) - odbiera dostęp do obiektów bazodanowych
TCL (Transaction Control Language) - jest elementem języka zapytań do baz danych, który ma na celu zarządzanie transakcjami w bazie danych.
Do kluczowych elementów składni należą operacje: COMMIT, ROLLBACK, SAVEPOINT.
- COMMIT - potwierdza transakcje i wszystkie akcje w niej wykonane.
- ROLLBACK - wycofuje transakcje i wszystkie akcje w niej wykonane mające wpływ na struktury bazy danych i samych danych.
- SAVEPOINT - zapisuje stan, do którego można się odwołać wewnątrz transakcji.
Typy danych możemy podzielić na:
- alfanumeryczne
- numeryczne
- data i czas
oraz inne typy, które można w większości przypadków zaliczyć jako podgrupę jednego z powyższych elementów, są to:
- boolean
- tablice (arrays)
- binary
- json / bson
- xml
Typy danych są ścisle związane z rozwiązaniem bazodanowym, które wybierzesz i tak np.
Co do przykładowych typów danych w grupach, na przykładzie PostgreSQL:
- PRIMARY KEY - klucz główny tabeli (relacji) - jeżeli, jest prosty tzn. jednoelementowy, jest ograniczeniem atrybutu. W przypadku złożonego klucza głównego będzie on ograniczeniem relacji.
- NOT NULL / NULL - wartość nie może / może być nieokreślona
- UNIQUE - wartość musi być unikalna w całej relacji
- SERIAL / AUTO_INCREMENT - atrybut jest typu numerycznego z automatycznym wzrostem wartości w polu w momencie wykonywania operacji INSERT
- DEFAULT wartosc - wartość domyślna dla atrybutu
- CHECK warunek - ograniczenie dziedziny atrybutu np. kolumna WIEK z ograniczeniem CHECK > 14, tj. wartość atrybutu WIEK musi być wyższy od 14 w momencie wykonywania operacji INSERT
To nic innego jak metoda wykorzystywana przez aplikację do grupowania, operacji odczytu i zapisu w jedną logiczną jednostkę.
Wynikiem transakcji jest jeden z dwóch stanów: Sukces (transakcja jest zatwierdzana) lub Porażka (transakcja jest anulowana lub wycofywana).
Zerknijmy na klasyczny przykład transakcji: transakcja bankowa wymiany środków pomiędzy dwoma kontami, Kontem A i Kontem B.
Właściciel Konta A chce przelać 100 PLN na Konto B Generalnie mamy w tym przykładzie dwa podejścia.
Podejście 1:
- Operacja 1: Stan Konta A = Stan Konta A - 100 PLN;
- Operacja 2: Stan Konta B = Stan Konta B + 100 PLN;
Podejście 2:
- Operacja 1: Stan Konta B = Stan Konta B + 100 PLN;
- Operacja 2: Stan Konta A = Stan Konta A - 100 PLN;
W rzeczywistości cała operacja jest troszkę bardziej skomplikowana (dochodzą sprawdzenia, reguły biznesowe i prawne banku itp.), jednak idea jest ta sama.
Jeżeli dojdzie do awarii w trakcie wykonywania operacji, mamy problem. Albo 100 PLN wyparuje z Konta A i nie pojawi się na Koncie B, lub pojawi się na Koncie B, ale stan Konta A nie zostanie zmieniony. Z pomocą przychodzi transakcja, która obejmując wykonanie operacji w jednym z Podejść, gwarantuje: wykonanie obu operacji z sukcesem (COMMIT), lub brak zmian w stanach obu Kont (cofnięcie zmian / ROLLBACK)
BEGIN [TRANSACTION]
StanKontaA = StanKontaA - 100;
StanKontaB = StanKontaB + 100;
END [TRANSACTION] (COMMIT)
ACID gwarantem bezpieczeństwa w relacyjnych bazach danych.
Jest to zestaw mechanizmów, który ma zapewnić pewną odporność na błędy i bolączki relacyjnych baz danych.
ACID to akronim od 4 takich gwarantów:
- A (Atomicity) - atomowość
- C (Consistency) - spójność
- I (Isolation) - izolacja
- D (Durability) - trwałość
Kontrola Współbieżności (Concurrency Control) - zbiór funkcji dostarczanych przez bazę danych w celu zezwolenia wielu użytkownikom jednoczesnego dostępu do danych i operacji na nic.
Kontrola Współbieżności Problemy:
- Dirty Read - odczytanie "aktualizowanych" (UPDATE) danych przed ich zaakceptowaniem (COMMIT) przez inną transakcję.
- Phantom Read - pojawienie się nowych rekordów / lub zniknięcie istniejących podczas gdy takie samo zapytanie zostanie uruchomione po wykonaniu 1 zapytania równoważnego.
- Non-Repeatable Read - powoduje pojawienie się rekordów z poprzedniego zapytania w ich zmienionej formie w momencie ponownego wykonania zapytania.
- Lost Update - powoduje nadpisanie oczekującej transakcji inną transakcją zapoczątkowaną wcześniej przez inną transakcję.
LOCK ma miejsce gdy dwie transakcje czekają wzajemnie na zwolnienie zasobów i przynajmniej jedna z nich nie może kontynuować swojego działania.
Transakcja 1: UPDATE products SET product_name = 'Product 999' where product_name = 'Product 1'; UPDATE products SET product_name = 'Product 991' where product_name = 'Product 2'; | Transakcja 2: UPDATE products SET product_name = 'Product 998' where product_name = 'Product 2'; UPDATE products SET product_name = 'Product 001' where product_name = 'Product 1'; |
Izolacja oznacza, że jednocześnie wykonywane transakcje są od siebie odizolowane - nie mogą mieć na siebie wpływu. Jest to szczególnie ważne w sytuacji dostępu do tego samego zasobu (tej samej tabeli), w przypadku dostępu do różnych obiektów konflikt nie będzie występował.
Inna definicja to taka, w której izolacja jest gwarantem, w którym równoległy sposób wykonania transakcji pozostawi bazę danych w takim stanie, jakby operacje zostały wykonane w sposób sekwencyjny.
Warto pamiętać, że jest izolacja to wymiana integralności bazy kosztem wydajności.
Przykład:

Poziomy izolacji transakcji (isolation levels) są standardami / podejściami radzenia sobie z problemami współbieżności transakcji.
- Read Uncommited - najmniej restrykcyjny, dopuszcza 3 typy odczytów (Dirty, Phantom i Non-Repeatable Reads). Raczej nie stosowany w komercyjnych bazach danych.
- Read Commited - pozwala zapytaniu na dostęp do danych, które zostały zaakceptowane (COMMIT). Brak Dirty Reads, zezwolenie na Phantom i Non-Repeatable Reads. Najczęściej stosowany poziom izolacji.
- Repeatable Read - gwarantuje spójność odczytów. Zezwala jedynie na Phantom Read.
- Serializable - najbardziej restrykcyjny poziom izolacji (nie dopuszcza Dirty, Non-Repeatable i Phantom Reads). Każda transakcja jest traktowana, jakby była jedyną transakcją wykonywaną w bazie danych w danym momencie.
Read Commited - dobry podział pomiędzy spójnością a współbieżnością. Dobra dla standardowych aplikacji OLTP (OnLine Transaction Processing) w których istnieje sporo współbieżnych, krótko trwałych transakcji z których rzadko które powodują konflikty. Lepsza wydajność.
Serializable - dobre do baz danych z wieloma spójnymi transakcjami. Mniejsza wydajność z uwagi na większe blokowanie. Używana głównie w bazach danych z transakcjami typu read-only, które pracują przez dłuższy czas (zapytania analityczne, patrz OLAP - OnLine Analytical Processing).
Normalizacja bazy danych to technika organizacji danych w bazie, jest ona systematycznym metodycznym podejście "rozbijania" tabel w celu eliminowania powtórzeń i niechcianych cech operacji dodawania, usuwania i aktualizacji wierszy.
Dwa kluczowe cele normalizacji:
- Reprezentacja faktów o rzeczywistym świecie w zrozumiały sposób;
- Ograniczenie nadmiarowego przechowywania faktów i zapobiegania występowaniu nieprawidłowych lub niespójnych danych;
INSERT - problemy przy braku normalizacji
Kolumna miejscowość (CITY) w tabeli CONTACTS, jest dobrym przykładem, żeby rozpatrzeć problemy braku normalizacji w kontekście operacji INSERT.
W przypadku dodawania 100 kontaktów z Poznania wszystkim rekordom przypisujemy taką samą informację tekstową. Z drugiej strony, gdy jakiś kontakt nie podał informacji o miejscowości (nie jest ona wymagana), będziemy musieli wstawić w to pole wartość nieokreśloną NULL.
UPDATE - problemy przy braku normalizacji
Kolumna firma (COMPANY) w tabeli CONTACTS, jest dobrym przykładem, żeby rozpatrzeć problemy braku normalizacji w kontekście operacji UPDATE.
Jeżeli wiele kontaktów, pracuje dla tej samej firmy - ACME - i firma ta zmieni nazwę, lub zniknie z rynku. Wszystkie rekordy będą musiały mieć zaktualizowaną wartość, pominięcie jakiegoś kontaktu i pozostawienie mu wartości ACME, będzie skutkować niespójnością w danych.
DELETE - problemy przy braku normalizacji
Załóżmy, że tabela CONTACTS, to jedyne miejsce, w którym przechowujemy informacje o miejscowości (CITY). Jeżeli z jakiegoś powodu usuniemy wszystkie kontakty z Poznania, nie będziemy w stanie odnieść się do tej wartości w przypadku innych zapytań.
1 Postać Normalna (1NF) - Tabela musi być relacją i nie może zawierać żadnych powtarzających się grup.
2 Postać Normalna (2NF) - tabela jest w 1 postaci normalnej, i posiada dokładnie jeden klucz kandydat (candidate key, który jest tym samym kluczem głównym - primary key) i nie jest on kluczem złożonym (składa się z 1 kolumny).
3 Postać Normalna (3NF) - w 3 postaci normalnej wszystkie atrybuty spoza klucza muszą zależeć od klucza danej tabeli.
3.5 / BCNF Postać Normalna Boyce'a - Codda (BCNF) - w postaci normalnej Boyce'a - Codda wszystkie atrybuty również te należące do klucza muszą zależeć od klucza danej tabeli.
4 Postać Normalna (4NF) - każda tabela, która w założeniu ma reprezentować wiele relacji wiele-do-wielu, narusza reguły czwartej postaci normalnej.
5 Postać Normalna (5NF) - każda tabela, która spełnia kryteria postaci normalnej Boyce'a - Codda i nie zawiera złożonego klucza głównego, znajduje się w piątej postaci normalnej.
5.5 Postać Normalna DKNF (Domain-Key Normal Form) (DKNF) - każde ograniczenie w tabeli musi być logiczną konsekwencją ograniczeń dziedziny tabeli i ograniczeń kluczy.
6 Postać Normalna (6NF) - tabela jest w 6 postaci normalnej jeżeli oprócz atrybutu, który jest jednocześnie kluczem głównym, posiada maksymalnie 1 dodatkowy atrybut.
1NF - rozdzielamy pole NAME_SURNAME.| NAME| SURNAME | GENDER | CITY | COUNTRY | ID_COMPANY | COMPANY | ADDED_BY | VERIFIED_BY | |---|---|---|---|---|---|---|---|---| | Krzysztof | Bury | M | Kraków | Polska | 1 | ACME | OsobaA | OsobaB | | Magda| Kowalski | F | New York | USA | 2 | CDE | OsobaC | OsobaA |2NF - wydzielamy tabelę GENDER na podstawie kolumny GENDER z CONTACTS.CONTACTS | NAME| SURNAME | CITY | COUNTRY | ID_COMPANY | COMPANY | COMPANY | ADDED_BY | VERIFIED_BY | |---|---|---|---|---|---|---|---| | Krzysztof | Bury | Kraków | Polska | 1 | ACME | OsobaA | OsobaB | | Magda| Kowalski | New York | USA | 2 | CDE | OsobaC | OsobaA |GENDER | NAME| GENDER| |---|---| | Krzysztof | M | | Magda | F |3NF - wydzielamy tabelę COMPANY na podstawie kolumn ID_COMPANY i COMPANY z CONTACTS.CONTACTS | NAME| SURNAME | CITY | COUNTRY | ID_COMPANY | ADDED_BY | VERIFIED_BY | |---|---|---|---|---|---|---| | Krzysztof | Bury | Kraków | Polska | 1 | OsobaA | OsobaB | | Magda| Kowalski | New York | USA | 2 | OsobaC | OsobaA |GENDER | NAME| GENDER| |---|---| | Krzysztof | M | | Magda | F |COMPANY | ID_COMPANY | COMPANY| |---|---| | 1 | ACME | | 2 | CDE |BCNF - tworzymy nową tabelę CITY_COUNTRY na podstawie kolumn CITY i COUNTRY z CONTACTS.CONTACTS | NAME| SURNAME | CITY | ID_COMPANY | ADDED_BY | VERIFIED_BY | |---|---|---|---|---|---|---| | Krzysztof | Bury | Kraków | 1 | OsobaA | OsobaB | | Magda| Kowalski | New York | 2 | OsobaC | OsobaA |GENDER | NAME| GENDER| |---|---| | Krzysztof | M | | Magda | F |COMPANY | ID_COMPANY | COMPANY| |---|---| | 1 | ACME | | 2 | CDE |CITY_COUNTRY | CITY | COUNTRY | |---|---| | Kraków | Polska | | New York | USA |4NF - tworzymy nową tabelę CONTACTS_VERIFIED_BY i CONTACTS_ADDED_BY na podstawie kolumn VERIFIED_BY i ADDED_BY z tabeli CONTACTS.CONTACTS | NAME| SURNAME | CITY | ID_COMPANY | |---|---|---|---| | Krzysztof | Bury | Kraków | 1 | | Magda | Kowalski | New York | 2 |GENDER | NAME| GENDER| |---|---| | Krzysztof | M | | Magda | F |COMPANY | ID_COMPANY | COMPANY| |---|---| | 1 | ACME | | 2 | CDE |CITY_COUNTRY | CITY | COUNTRY | |---|---| | Kraków | Polska | | New York | USA |CONTACTS_VERIFIED_BY | CONTACT_NAME | VERIFIED_BY| |---|---| | Krzysztof | OsobaB | | Magda | OsobaA |CONTACTS_ADDED_BY | CONTACT_NAME | ADDED_BY| |---|---| | Krzysztof | OsobaA | | Magda | OsobaC |
Widok jest trwałą definicją tabeli pochodnej (wynikiem zapytania SELECT), która jest przechowywana w bazie danych.
W SQL tabela, którą utworzono za pomocą zapytania CREATE TABLE, nazywana jest tabelą bazową (base table). Tabelą pochodną (derived table) formalnie nazywamy wynik każdego zapytania SELECT. Tabele, których użyto do zdefiniowania widoku, są nazywane underlying tables.
Składnia:
CREATE OR REPLACE VIEW view_name AS SELECT ...
DROP VIEW view_name;
Właściwości widoku:
- nie przechowuje danych - definicja widoku jest zapisana w bazie danych. Sam widok jest jedynie interfejsem odczytu danych;
- zawsze pokazuje aktualne dane - baza danych odtwarza dane za każdym razem aktualne z tabel źródłowych użytych do zbudowania widoku;
Widoki są używane do:
- utworzenia dodatkowego poziomu zabezpieczenia tabeli poprzez ograniczenie dostępu do określonych kolumn lub wierszy tabeli bazowej;
- ukrycia złożoności danych - gdy definicja widoku jest złożonym zapytaniem SELECT, samo odpytanie widoku jest widoczne jako proste zapytanie;
- pokazywania danych z innej perspektywy - dla przykładu widok może zostać użyty do zmiany nazwy kolumny bez zmiany rzeczywistych danych zapisanych w tabeli;
Widok zmaterializowany w odróżnieniu od widoku jest definicją zapytania SELECT, która w momencie stworzenia (i z określoną częstotliwością) zapisuje wyniki zapytania do obiektu bazodanowego, tabeli.
Dane z defnicji widoku zmaterializowanego odświeżają się według ustalonego harmonogramu (ustawianego w defnicji widoku zmaterializowanego).
Co do zasady w przypadku skomplikowanych zapytań w momencie odpytania zwykłego widoku, całe skomplikowane zapytanie musi zostać wykonane. W przypadku widoku zmaterializowanego, dane są od razu dostępne z aktualnością równą ostatniemu odświeżeniu widoku według harmonogramu.
OLTP - OnLine Transaction Processing - to system zorientowany na krótkotrwałe, szybie transakcje.
- źródło danych: dane operacyjne; systemy OLTP są oryginalnie źródłem danych;
- cel danych: obsługa podstawowych procesów biznesowych i ich kontrola;
- co jest w danych: stan bieżący procesów biznesowych;
- operacje Insert / Update: krótkie i szybkie operacje Insert / Update inicjowane przez użytkowników końcowych;
- zapytania: relatywnie proste i standardowe zapytania zwykle zwracające kilka rekordów;
- czas procesowania: zwykle bardzo szybki;
- potrzebne miejsce: dane mogą zajmować relatywnie mało miejsca jeżeli dane historyczne są archiwizowane;
- struktura bazy danych: wysoce znormalizowana z dużą ilością tabel;
- kopia i odzyskiwanie danych: obowiązkowe robienie kopii danych; dane operacyjne są krytyczne do prowadzenia biznesu, utrata danych zwykle pociąga za sobą duże straty finansowe i odpowiedzialność prawną;
Głównym celem jest przetwarzanie danych.
Typowe przykłady to w zasadzie większość produkcyjnych systemów wspomagania pracę przedsiębiorstwa - systemy ERP / CRM (z wyjątkiem modułów analitycznych).
Inne Przykłady: system bankowy obsługujący odczytywanie i modyfikację salda rachunków klientów; systemy finansowo-księgowe.
OLAP - OnLine Analytical Processing - to systemy zorientowane na dostarczenie modelu analitycznego, tj. takie przygotowanie danych i optymalizacja modelu, żeby procesy analityczne był priorytetowe.
- źródło danych: dane skonsolidowane; dane źródłowe systemów OLAP pochodzą z różnych baz / źródeł danych;
- cel danych: pomoc w planowaniu, rozwiązywaniu problemów i procesie podejmowania strategicznych decyzji;
- co jest w danych: wielowymiarowe spojrzenie na różnego rodzaju aktywności biznesowe, stan obecny i historia;
- operacje Insert / Update: cykliczne, długo trwające odświeżanie danych zwykle przy pomocy plików wsadowych;
- zapytania: zapytania często bardzo złożone wymagające agregacji;
- czas procesowania: zależy od ilości procesowanych danych;
- potrzebne miejsce: potrzebna duża ilość miejsca ze względu na istnienie danych zagregowanych i historycznych;
- struktura bazy danych: zwykle zdenormalizowana z niewielką ilością tabel; używane schematy gwiazdy i / lub płatka śniegu;
- kopia i odzyskiwanie danych: dodatkowo do kopii zapasowych można rozważyć ponowne załadowanie danych z systemów źródłowych jako metodę odzyskiwania danych;
Głównym celem jest analiza danych.
Typowym przykładem są hurtownie danych, lub ogólnie systemy stworzone i zoptymalizowane pod kątem procesów analitycznych.
OLTP:
- duża liczba prostych zapytań (transakcji)
- systemy zoptymalizowane pod kątem szybkiego dodawania, usuwania i modyfikowania pojedynczych rekordów
- zwykle utożsamiane z tradycyjnymi bazami relacyjnymi
OLAP:
- nieduża liczba zapytań, dotycząca często wielu elementów biznesu
- systemy zoptymalizowane pod kątem szybkiego odczytu informacji analitycznych (z agregowanych danych)
- zwykle utożsamiane z hurtowniami danych, wspomaganiem decyzji biznesowych firmy (systemy Business Intelligence) i "bardzo dużą" ilością danych
- przetwarzanie danych (do najbardziej aktualnych informacji) może zająć minuty lub godziny - nie zakładamy aktualności danych
Funkcje agregujące w SQL, to grupa funkcji, które jak wskazuje nazwa, służą do otrzymania wyniku operacji dla grupy rekordów, według wybranego klucza (kluczy) grupy.
Przykładem, może być wartość sprzedaży w podziale na kategorie produktów.
Najczęściej wykorzystywane funkcje agregujące:
- SUM() - sumowanie
- AVG() - średnia
- COUNT() - zliczanie wystąpień
- MIN() / MAX() - minimum / maksimum
- STDEV() - odchylenie standardowe
Funkcje analityczne są specyficznym rodzaje funkcji SQL-owe, które dają wynik dla danej wiersza w kontekście wierszy sąsiednich (poprzedzających / następujących) po wierszu. Ten rodzaj funkcji wykorzystujemy np. do "rankingu" danych czy wyliczania stanów (balance).
Najczęściej wykorzystywane funkcje analityczne:
- ROW_NUMBER() OVER()
- DENSE_RANK() / RANK () OVER()
- SUM() OVER()
- LEAD() / LAG() OVER()
W momencie gdy nie są one użyte w kontekście grupowania (GROUP BY), są one w zasadzie równoważne.
Jednak prawdziwe zastosowanie mają właśnie w momencie grupowania danych.
- Warunek WHERE odfiltrowuje rekordy z wyniku przed wykonaniem grupowania.
- Warunek HAVING odfiltrowuje rekordy z wyniku po wykonaniu grupowania
Obie komendy służą do usuwania wierszy z tabeli, ale są między nimi pewne subtelne różnice.
DELETE jest operacją DML-ową (Data Manipulation Language) działa na poziomie wiersza tabeli. Z jej pomocą możemy usunąć wszystkie wierszy lub jeden konkretny (dokładając WHERE).
DELETE FROM <TABLE NAME> WHERE …
TRUNCATE jest operacją DDL-ową (Data Definition Language) działa na poziomie całej tabeli. Jeżeli chcesz usunąć wszystkie wiersze z tabeli (a dodatkowo Twoja tabela nie ma kluczy obcych), to prawdopodobnie to będzie komenda, której szukasz.
TRUNCATE TABLE <TABLE NAME> …
Trochę o detalach obu komend.
Transakcyjność
- DELETE podobnie jak INSERT czy UPDATE może zostać zaakceptowany (COMMIT) lub cofnięty (ROLLBACK).
- TRUNCATE w części systemów baz danych ma w sobie tzw. „niejawny COMMIT”. To znaczy, że operacja COMMIT jest wykonywana przed i po wykonaniu operacji TRUNCATE bez konieczności dodatkowego wykonywania COMMIT-a.
Uwaga: Powyższe jest prawdą w przypadku np. Oracle-a, w przypadku SQL Server-a lub PostgreSQL-a operacja TRUNCATE może być wycofana (ROLLBACK).
Odzyskiwanie Miejsca
- DELETE nie odzyskuje miejsca po usunięciu wierszy. Potrzebna jest dodatkowa operacja VACUUM.
- TRUNCATE odzyskuje miejsce po usunięciu wierszy.
Lockowanie
- DELETE blokuje konkretne wiersze tabeli, które są usuwane.
- TRUNCATE blokuje całą tabelę.
Wyzwalacze / Triggers
- DELETE wykonuje operacje wyzwalaczy AFTER / BEFORE DELETE.
- TRUNCATE nie wykonuje wyzwalaczy.
Pomiędzy TRUNCATE-em a DELETE-em jest jeszcze kilka różnic, które są związane z ich obsługą pomiędzy bazami danych np. kwestie indeksów, kluczy obcych, kolumn typu IDENTITY.
Jednak są to już dość specyficzne kwestie, jak chcesz zgłębić temat, odwołałbym się do konkretnych dokumentacji systemów, na których pracujesz.
Gdy pomiędzy tabelami istnieje zdefiniowana relacja klucza obcego, oraz istnieją powiązane z tą relacją rekordy, wykonanie operacji DELETE na danych tabeli, do której jest stworzone powiązanie, nie będzie możliwe.
Baza danych poinformuje nas o tym, że w relacji pomiędzy tabelą A i B, istnieją rekordy, które chcemy usunąć, żeby wykonać tę operację, najpierw musimy usunąć rekordy powiązane z tabeli B lub skorzystać z opcji CASCADE.
Opcja CASCADE usuwa wszystkie wiersze z tabeli A oraz wszystkie powiązane wierszy z tabeli B, które łączy relacja klucza obcego pomiędzy tabelami.
Gdy pomiędzy obiektami bazy danych istnieje relacja, tzn. obiekt B zależy od obiektu A, np. widok B od tabeli A, wykonanie operacji DROP na obiekcie A, nie będzie możliwe.
Baza danych poinformuje nas o tym, że w relacji pomiędzy obiektem A i B, istnieje powiązanie. Jeżeli chcemy usunąć obiekt A, należy wcześniej usunąć obiekt B lub skorzystać z opcji CASCADE.
Opcja CASCADE usuwa obiekt zdefiniowany do usunięcia oraz wszystkie obiekty, którego korzystają / są stworzone na podstawie obiektu A.
IN jest operatorem logicznym używanym w części WHERE, zapytania. Pozwala na sprawdzenie, czy wartość z danej kolumny / funkcji istnieje / równa się dowolnej wartości z listy.
Porównanie wartości względem listy jest bardzo wygodne, przykładem może być wykorzystanie takiej listy na warstwie prezentacji (strona internetowa) i późniejsze wysłanie, żądania do bazy danych o wyświetlenie tylko tych wyników, dla których użytkownik dokonał zaznaczenia.
Operator IN jest w zasadzie równoznaczny z pisaniem wielu warunków OR dotyczących tej samej kolumny a różnych wartości. Dlatego warto go zastosować w takim przypadku choćby ze względu na czytelność.
Jednym z pytań, które powinno Ci się nasunąć, to: "ile wartości mogę w IN użyć”.
Odpowiedź, nie jest jednoznaczna i będzie zależeć od bazy danych.
W SQL Server „bardzo dużo” (https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-2017).
W przypadku bazy danych Oracle będzie to „bardzo mało” (1000 według tego artykułu http://www.dba-oracle.com/t_maximum_number_of_sql_in_list_values.htm).
Pamiętaj, że fakt, że możemy użyć wielu wartości w operatorze IN, nie znaczy, że powinniśmy. Wykonujemy tutaj po prostu wiele operacji LUB (OR), a nie jest to najbardziej wydajne podejście do tworzenia zapytań.
Funkcje CEIL oraz FLOOR pozwalają na zaokrąglenie danej liczby do części całkowitej.
Pamiętasz pewnie z matematyki kwestie zaokrąglania. Mając liczbę 254 gdy chcemy zaokrąglić ją do pełnych dziesiątek, dostaniemy w wyniku liczbę 250. Gdy mamy np. 255 to dostaniemy 260.
CEIL (lub CEILING) oraz FLOOR. W “wolnym” tłumaczeniu sufit i podłoga i już się domyślasz co do czego.
Funkcja CEIL (CEILING) zwraca najmniejszą liczbę całkowitą większą lub równą, względem podanej na wejściu wartości.
SELECT 8.28 as wartosc_wyjsciowa,
CEILING(8.28) as wartosc_pozytywna,
CEILING(-8.28) as wartosc_negatywna;
Wynik:
wartosc_wyjsciowa: 8.28
wartosc_pozytywna: 9
wartosc_negatywna: -8
Funkcja FLOOR zwraca największą liczbę całkowitą mniejszą lub równą, względem podanej na wejściu wartości.
SELECT 8.28 as wartosc_wyjsciowa,
FLOOR(8.28) as wartosc_pozytywna,
FLOOR(-8.28) as wartosc_negatywna;
Wynik:
wartosc_wyjsciowa: 8.28
wartosc_pozytywna: 8
wartosc_negatywna: -9
Funkcja ROUND podobnie jak CEIL i FLOOR pozwala na zaokrąglenie wartości. Jednak zaokrąglenie bardziej "po matematycznemu” (czyli jak 5 to w górę a jak mniej niż 5 to w dół).
Dodatkowo przy tej funkcji możesz doprecyzować, do ilu miejsc po przecinku, wartość wyjściowa ma zostać zaokrąglona.
SELECT 8.49 as wartosc_wyjsciowa,
FLOOR(8.28) as wartosc_floor,
CEIL(8.28) as wartosc_ceil,
ROUND(8.49) as wartosc_zaokraglona
wartosc_wyjsciowa: 8.49
wartosc_floor: 8
wartosc_ceil: 9
wartosc_zaokraglona: 8 (niepodane zostało miejsce zaokrągleń po przecinku, domyślnie 0 miejsc po przecinku)
SELECT 8.51 as wartosc_wyjsciowa,
FLOOR(8.51) as wartosc_floor,
CEIL(8.51) as wartosc_ceil,
ROUND(8.51) as wartosc_zaokraglona
wartosc_wyjsciowa: 8.49
wartosc_floor: 8
wartosc_ceil: 9
wartosc_zaokraglona: 9 (niepodane zostało miejsce zaokrągleń po przecinku, domyślnie 0 miejsc po przecinku)
UWAGA:
Jakiego typu jest wartość zwracana przez daną funkcję (CEIL / FLOOR / ROUND)?
To zależy od silnika bazodanowego, który wykorzystujesz.
W przypadku PostgreSQL zwracane typy dla funkcji to:
- CEIL - taki sam typ jak typ wejściowy
- FLOOR - taki sam typ jak typ wejściowy
- ROUND - typ numeryczny
Zerknij do dokumentacji silnika, z którego korzystasz na co dzień, żeby mieć pewność.
Operator ANY, służy do sprawdzenia, czy w przeszukiwanym podzbiorze danych (podzapytaniu) znajduje się przynajmniej jedna wartość spełniająca wykorzystany operator. Możesz go użyć w sekcji WHERE oraz / lub w sekcji HAVING.
Składnia:
operator - to jeden z dostępnych operatorów logicznych =, <>, !=, >, >=, <, or <=
Przykład:
Wyświetlenie diet, wśród których dla posiłków jednym ze składników jest owoc pomarańczy.
CREATE TABLE diet_menu ( diet_name text, diet_ingredients text[]
);INSERT INTO diet_menu VALUES ('diet1', '{apple, orange, banana}'); INSERT INTO diet_menu VALUES ('diet2', '{apple, banana}');SELECT * FROM diet_menu WHERE 'orange' = ANY (SELECT UNNEST(diet_ingredients));
Uwaga:
Z operatorem ANY wiąże się również operator SOME, który uwaga jest takim samym operatorem jak ANY.
Co do zasady działania operatory są takie same. Pierwszy standard SQL wszedł w życie w 1986, a sam SQL swoje początki datuje na lata 1970+, stąd 2 operatory, być może jakaś wsteczna kompatybilność?
Operator ALL, służy do sprawdzenia, czy wszystkie wiersze przeszukiwanego podzbioru danych (podzapytania) spełniają wykorzystany operator. Możesz go użyć w sekcji WHERE oraz / lub w sekcji HAVING.
Składnia:
operator - to jeden z dostępnych operatorów logicznych =, <>, !=, >, >=, <, or <=
Przykład:
Wyświetlenie diet, wśród których dla posiłków jednym ze składników nie jest arbuz.
CREATE TABLE diet_menu ( diet_name text, diet_ingredients text[] );INSERT INTO diet_menu VALUES ('diet1', '{apple, orange, banana}'); INSERT INTO diet_menu VALUES ('diet2', '{apple, banana}');SELECT * FROM diet_menu WHERE 'watermelon' <> ALL (SELECT UNNEST(diet_ingredients));
Uwaga:
Z operatorem ANY wiąże się również operator SOME, który uwaga jest takim samym operatorem jak ANY.
Co do zasady działania operatory są takie same. Pierwszy standard SQL wszedł w życie w 1986, a sam SQL swoje początki datuje na lata 1970+, stąd 2 operatory, być może jakaś wsteczna kompatybilność?
Operator EXISTS, służy do wyszukiwania wierszy, które spełniają warunek użyty w podzapytaniu. Enigmatyczny opis, a sprowadza się do tego, że w wyniku dostaniesz rekordy istniejące w podzapytaniu użytym do zbudowania EXIST-a. Operator EXISTS zwraca TRUE, gdy podzapytanie zwróci 1 lub więcej rekordów.
Składnia:
Dlaczego nie JOIN?
JOIN według swojej definicji, służy do łączenia tabel i wyświetlania w wyniku kolumn z różnych obiektów (tabel). EXISTS ma za zadanie "odfiltrować" finalny wynik na podstawie jakiegoś podzapytania.
Przykład:
Wyświetl wszystkie produkty (tabela PRODUCTS), które wzięły udział w transakcji sprzedażowej (tabel SALES)
SELECT p.* FROM products p WHERE EXISTS (SELECT 1 FROM sales s WHERE s.product_id = p.product_id);
Co EXISTS ma wspólnego z ANY / SOME.
Załóżmy, że chcemy wyświetlić tylko produkty, dla których istnieje transakcja sprzedażowa. Możemy to zrobić za pomocą EXIST-a, ale również za pomocą ANY.
-- EXISTS SELECT p.* FROM products p WHERE EXISTS (SELECT 1 FROM sales s WHERE s.product_id = p.product_id);-- ANY SELECT p.* FROM products p WHERE p.product_id = ANY (SELECT s.product_id FROM sales s);
Jaka jest wiec różnica?
Plan zapytania jest identyczny w obu przypadkach. Jeżeli warunek logiczny jest ten sam, ANY / SOME i EXISTS są zamienne.
Hash Join (cost=201.25..226.00 rows=100 width=41) (actual time=4.312..4.597 rows=99 loops=1) Hash Cond: (p.product_id = s.product_id) -> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=41) (actual time=0.013..0.138 rows=1000 loops=1) -> Hash (cost=200.00..200.00 rows=100 width=4) (actual time=4.278..4.278 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 12kB -> HashAggregate (cost=199.00..200.00 rows=100 width=4) (actual time=4.231..4.240 rows=100 loops=1) Group Key: s.product_id -> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=4) (actual time=0.012..2.071 rows=10000 loops=1) Planning time: 0.431 ms Execution time: 4.677 ms
Prawdopodobnie znasz z relacyjnej bazy danych, na której pracujesz na co dzień składnie:
- LIMIT (m.in. MySQL, PostgreSQL, SQLite, H2)
- TOP (m.in. MS SQL Server)
- ROWNUM (Oracle) - pseudo kolumna w bazie Oracle, tworzona w momencie generowania wyników określająca kolejność pobranych wierszy
Przykłady:
SELECT * FROM tab LIMIT 10;
SELECT * FROM tab WHERE ROWNUM <= 10;
SELECT TOP 10 * FROM tab;
Dlaczego, pomiędzy bazami danych są różnice, w takim wydawałoby się prostym elemencie?
Poniekąd byli do tego zmuszeni. Pierwszy raz taka funkcjonalność została wprowadzona do standardu razem z wersją SQL:2008.
FETCH FIRST n ROWS ONLY bo o niej mowa, pozwala na pobranie jedynie n (gdzie n to liczba lub wynik operacji np. (2+2)) wierszy z całego zbioru wynikowego.
Przykład:
SELECT * FROM tab FETCH FIRST 10 ROWS ONLY;
Instrukcja warunkowa (IF) to popularna konstrukcja w językach programowania, pozwalająca na sprawdzenie warunku i wykonanie odpowiedniej akcji w zależności od tego czy warunek jest spełniony, czy też nie.
Składnia (podstawowa - w językach programowania - jako pseudokod)
IF <WARUNEK LOGICZNY>
THEN <AKCJA DO WYKONANIA JAK WARUNEK JEST SPEŁNIONY>
ELSE <AKCJA DO WYKONANIA JAK WARUNEK JEST NIESPEŁNIONY>
END
Warunkiem logicznym może być dowolna funkcja / operator, który w wyniku da wartość TRUE lub FALSE.
Konstrukcja jak powyżej w standardowym SQL-u nie istnieje. Istnieje natomiast w Proceduralnym SQL, tj. w procedurach składowanych i funkcjach wykonywanych w bazie danych.
IF w SQL-u istnieje, kryje się pod operatorem CASE
- warunek - to dowolna operacja, której wynikiem jest wartość logiczna TRUE / FALSE (operator logiczny >, <, =, <> etc.; wynik z podzapytania TRUE / FALSE itp.)
- akcja - czynność która ma zostać wykonana po spełnieniu warunku np. wyświetlenie jakiejś kolumny, dodanie czegoś do wartości etc.
W operatorze CASE warunki są sprawdzane po kolei, po napotkaniu pierwszego warunku, który jest spełniony, wykonywana jest akcja, pozostałe warunki są pomijane. Jeżeli żaden z warunków nie jest spełniony, wykonywana jest akcja z sekcji ELSE.
Jeżeli ELSE nie został zadeklarowany, zwrócona zostanie wartość nieokreślona - NULL.
Przykład:
Operator CASE możesz wykorzystać w samej składni SELECT, w WHERE, w ORDER BY, GROUP BY etc. Jednak taki operator to zawsze dodatkowy narzut w momencie wykonania (na wykonanie instrukcji sprawdzenia + wykonanie akcji), co będzie miało wpływ na wydajność. W niektórych przypadkach warto rozważyć wyliczanie kolumn z operatorem CASE wcześniej (jako element w procesie poprzedzającym wykorzystanie), dodając odpowiednie indeksowanie i następnie wykorzystując wyliczoną kolumnę w SELECT-ie.
Uwaga: W niektórych baza danych IF istnieje np. MySQL, lub jako IIF - Firebird / SQL Server. Jednak nie jest standardem SQL a nakładką na konkretnej bazie danych.
Operacja ORDER BY służy do sortowania danych wynikowych.
Czy wyniki mają jakiekolwiek sortowanie bez ORDER BY.
Nie. W przypadku niepodania klauzuli ORDER BY wyniki zapytania zostaną zwrócone w nieokreślonej formie. Może się zdarzyć, że pojawią się w tej samej kolejności, jak zostały dodane do tabeli źródłowej, ale uważaj i nie bierz tego za pewnik.
Chcesz otrzymać wiersze w żądanej kolejności, używaj ORDER BY.
Składnia
SELECT …
FROM …
ORDER BY < NAZWA_KOLUMNY/FUNKCJI> < DESC | ASC>
< NULLS FIRST | NULLS LAST >
- NAZWA_KOLUMNY/FUNKCJI - nazwa / nazwy kolumn lub funkcji, po których ma zostać wykonane sortowanie
- DESC - sortuj w porządku malejącym
- ASC - sortuj w porządku rosnącym
- NULLS FIRST - pokazuj wartości nieokreślone na początku zbioru wynikowego
- NULLS LAST - pokazuj wartości nieokreślone na końcu zbioru wynikowego
Porządek sortowania DESC / ASC możesz określić dla każdej kolumny / funkcji użytej przy sortowaniu osobno. Domyślnym porządkiem sortowania jest sortowanie rosnące - ASC.
Elementy użyte w składni ORDER BY nie muszą być w części SELECT.
ORDER BY jest jednym z ostatnich logicznych elementów, które zostaną wykonane. Dlatego przy sortowaniu możemy sobie pozwolić na używanie aliasów - zamiast rzeczywistych nazw kolumn lub kolejności pól zamiast ich nazw (np. ORDER BY 1, 2, 3, gdzie 1, 2, 3 to kolejne kolumny po składni SELECT)
Złączenie to powiązanie tabel (lub tabeli), na podstawie pewnego warunku (tych samych kolumn, operatorów logicznych >, < etc.).
W najprostszym przypadku złączenia używamy do odwzorowania relacji pomiędzy tabelami, gdy chcemy dostać określony wynik.
Rodzaje złączeń:
- (INNER) JOIN
- LEFT (OUTER) JOIN
- RIGHT (OUTER) JOIN
- FULL (OUTER) JOIN
(INNER) / (OUTER) w nawiasach oznaczają fakt, że ten fragment jest opcjonalny dla SQL tzn. LEFT OUTER JOIN = LEFT JOIN.
UNION - jest operacją łączenia zbiorów danych. Możesz jej użyć, do połączenia rekordów z różnych tabel w 1 zbiór rekordów.
Po co? Zwykle, gdy potrzebujesz zbudować listę wartości z różnych zbiorów (tabel).
Przykład:
Limity
- ilość kolumn w obu zapytaniach musi być identyczna
- typy danych muszą być kompatybilne w odpowiadających sobie kolumnach - (w przykładzie powyżej car_manufacturer nie może być raz wartością numeryczną, a raz tekstową)
- w celu uzyskania posortowanego rezultatu użyj ORDER BY - możesz go użyć tylko w ostatnim SELECT-cie. ORDER BY zadziała na całym zbiorze wynikowym. Jeżeli chcesz otrzymać posortowane wyniki tylko z pierwszego zapytania, zastosuj podzapytanie dla 1 SELECT-a
UNION vs UNION ALL
- UNION - złączy wyniki usuwając przy tym powielające się wiersze (taki DISTINCT)
- UNION ALL - złączy wyniki nie usuwając powielających się wierszy
Różnica jest znacząca i będzie zauważalna szczególnie na większych zbiorach danych. Operacja usuwania duplikatów jest kosztowna, dlatego warto przed użyciem operacji UNION sprawdzić i zweryfikować czy istnieje możliwość pojawienia się tych samych wartości w złączanych zbiorach.
Uwaga:
W niektórych bazach danych UNION oprócz usuwania duplikatów sortuje również wynikowy zbiór danych (np. SQL Server).
INNER JOIN jest typem złączenia, w którym z dwóch zbiorów (tabel czy podzapytań) bierzemy te wartości (wiersze), dla których zdefiniowany klucz złączenia jest spełniony. Oznacza to, że wartości w kolumnach ze zbiorów użytych w złączeniu mają te same wartości (w przypadku wykorzystania “=” kolumn w JOIN-ie).

NATURAL JOIN jest typem złączenia, w którym z dwóch zbiorów (tabel czy podzapytań) bierzemy tylko ich część wspólną, ale jedynie w sytuacji gdy w obu zbiorach istnieje tak samo nazwana kolumna (lub więcej tak samo nazwanych kolumn).

RIGHT OUTER JOIN jest typem złączenia, w którym z dwóch zbiorów (tabel czy podzapytań) bierzemy wszystkie elementy z 2 zbioru i elementy pasujące (według użytego klucza złączenia) z pierwszego zbioru. Stąd nazwa RIGHT (w odniesieniu do kierunku złączenia) i OUTER mówiące o typie złączenia zewnętrznego (nie tylko część wspólna).

LEFT OUTER JOIN jest typem złączenia, w którym z dwóch zbiorów (tabel czy podzapytań) bierzemy wszystkie elementy z 1 zbioru i elementy pasujące (według użytego klucze złączenia) z drugiego zbioru. Stąd nazwa LEFT (w odniesieniu do kierunku złączenia) i OUTER mówiące o typie złączenia zewnętrznego (nie tylko część wspólna).

Używając CROSS JOIN, w wyniku dostaniemy iloczyn kartezjański zbiorów (tabel). Każdy wiersz z tabeli A zostanie „połączony” z każdym wierszem z tabeli B.
Sprawdzi się w sytuacjach - świadomego połączenia wszystkich wierszy między sobą np. wyświetlenie każdego z produktów w każdym z regionów.

EQUI JOIN - dowolny typ złączenia, w którym w kluczu złączenia używamy znaku =.
NON-EQUI JOIN - dowolny typ złączenia, w którym w kluczu złączenia nie używamy znaku = (ale np. <. >, >=, =<, !=).
SELF JOIN - typ złączenia, w którym łączymy między sobą ten sam zbiór danych / tabele.
Wewnętrzne czy czasami nazywane fizycznymi typami złączeń, lub ogólniej algorytmy złączenia. To złączenia, które są wykorzystywane przez silnik bazy danych do faktycznego łączenia danych (abstrahując od tego, co użytkownik użył w zapytaniu LEFT / INNER JOIN).
Trzy podstawowe typy:
- NESTED LOOP
- MERGE JOIN
- HASH JOIN
Mając dwa zbiory do złączenia N i M, w najprostszym (najgorszym) scenariuszu, każdy rekord ze zbioru N, musi zostać sprawdzony względem rekordów w zbiorze M.
Stąd nazwa "nested loop", przechodzimy przez elementy zbioru M, iterując elementy zbioru N.
W zależności od sytuacji złożoność tego algorytmu może być:
- O(N*M) gdy na kluczu złączenia zbiorów nie mamy indeksu
- O(M*logN) gdy na jednym z kluczy złączenia mamy założony indeks typu B-Tree
- O(M) gdy na jednym z kluczy złączenia mamy założony hash indeks.
Zwykle wykorzystywany gdy jeden ze zbiorów złączenia jest "zdecydowanie" mniejszy od drugiego.
Przykład na podstawie tabel PRODUCTS i SALES:
CREATE INDEX product_id_sales_idx ON sales (product_id); CREATE INDEX product_name_products_idx ON products (product_name);EXPLAIN ANALYZE SELECT * FROM products p JOIN sales s ON s.product_id = p.product_id AND p.product_name = 'Product 1';QUERY PLAN Nested Loop (cost=4.77..68.41 rows=28 width=1148) (actual time=0.369..2.142 rows=1236 loops=1) -> Index Scan using product_name_products_idx on products p (cost=0.27..8.29 rows=1 width=1068) (actual time=0.029..0.098 rows=101 loops=1) Index Cond: ((product_name)::text = 'Product 1'::text) -> Bitmap Heap Scan on sales s (cost=4.50..59.85 rows=28 width=80) (actual time=0.005..0.016 rows=12 loops=101) Recheck Cond: (product_id = p.product_id) Heap Blocks: exact=685 -> Bitmap Index Scan on product_id_sales_idx (cost=0.00..4.49 rows=28 width=0) (actual time=0.003..0.003 rows=12 loops=101) Index Cond: (product_id = p.product_id) Planning time: 0.498 ms Execution time: 2.281 ms
Chcąc złączyć dwa zbiory N i M. Algorytm MERGE JOIN zostanie wykorzystany w momencie gdy zbiory użyte w złączenie są posortowane według klucza złączenia i zostanie wykorzystany operator równości w kluczu złączenia.
Co to oznacza w praktyce? Gdy mamy posortowane dwa zbiory sprawdzanie równości jest bardzo uproszczone.
Patrząc na zbiór N i mając wartość 1, szukamy tej wartości w posortowanym zbiorze M. Bierzemy pierwszy element czy jest on równy 1, nie, czy jest on większy, tak - nie sprawdzamy kolejnych elementów. Dalej bierzemy kolejny rekord ze zbioru N. Taka operacja powtarza się, dopóki wszystkie elementy z jednego ze zbiorów nie zostaną sprawdzone (Dlaczego z jednego? Skoro zbiory są posortowane i przeszukaliśmy wszystko ze zbioru np. N to zbiór M, już nas niczym nowym nie zaskoczy, bo niesprawdzone wartości i tak nie spełnią warunków złączenia).
Złożoność tego algorytmu to O(N+M).
Świetny przy złączeniach "dużych" tabel, gdy oba zbiory są posortowane według klucza złączenia i warunku złączenia użyty jest operator równości "=".
Przykład na podstawie tabel PRODUCTS i SALES:
EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM products ORDER BY product_id) p JOIN (SELECT * FROM sales ORDER BY product_id) s ON s.product_id = p.product_id;QUERY PLAN Merge Join (cost=909.82..1184.70 rows=10000 width=97) (actual time=3.885..8.134 rows=9893 loops=1) Merge Cond: (products.product_id = sales.product_id) -> Sort (cost=69.83..72.33 rows=1000 width=41) (actual time=0.249..0.260 rows=100 loops=1) Sort Key: products.product_id Sort Method: quicksort Memory: 103kB -> Seq Scan on products (cost=0.00..20.00 rows=1000 width=41) (actual time=0.014..0.110 rows=1000 loops=1) -> Materialize (cost=838.39..988.39 rows=10000 width=56) (actual time=3.556..6.009 rows=10000 loops=1) -> Sort (cost=838.39..863.39 rows=10000 width=56) (actual time=3.550..4.883 rows=10000 loops=1) Sort Key: sales.product_id Sort Method: quicksort Memory: 1166kB -> Seq Scan on sales (cost=0.00..174.00 rows=10000 width=56) (actual time=0.009..1.003 rows=10000 loops=1) Planning time: 1.770 ms Execution time: 8.658 ms
Chcąc złączyć dwa zbiory N i M, gdy wykorzystany zostanie algorytmy HASH JOIN.
Baza danych dla mniejszego zbioru, wyznaczy skróty (hash-e) dla kluczy złączenia i zbuduję tabelę skrótów (połączenie wyznaczonego skrótu z rzeczywistym rekordem tabeli N).
Następnie w pętli dla każdego rekordu z tabeli M, dla klucza złączenia zostanie wyznaczony skrót (hash) i porównany ze zbudowaną tabelą skrótów.
Tabela skrótów zwykle jest przechowywana w pamięci, jednak gdy zbiór danych jest bardzo dużo, ilość miejsca w pamięci może się okazać nie wystarczająco, przez co część tabeli skrótów, zostanie zrzucona na dysk i stamtąd będzie odczytywana (co znacznie spowolni odczyt / złączenie).
Złożoność obliczeniowa tego algorytmu przy założeniu, że tabela skrótów zmieści się w pamięci i dodatkowe zasoby nie będą wykorzystane to: O(N+M).
Przykład na podstawie tabel PRODUCTS i SALES:
EXPLAIN ANALYZE SELECT * FROM products p JOIN sales s ON s.product_id = p.product_id AND p.product_name = 'Product 1';QUERY PLAN Hash Join (cost=23.65..244.35 rows=920 width=97) (actual time=0.231..2.355 rows=996 loops=1) Hash Cond: (s.product_id = p.product_id) -> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=56) (actual time=0.011..0.959 rows=10000 loops=1) -> Hash (cost=22.50..22.50 rows=92 width=41) (actual time=0.193..0.193 rows=92 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 15kB -> Seq Scan on products p (cost=0.00..22.50 rows=92 width=41) (actual time=0.016..0.181 rows=92 loops=1) Filter: ((product_name)::text = 'Product 1'::text) Rows Removed by Filter: 908 Planning time: 0.404 ms Execution time: 2.445 ms
Od magii wykorzystywanej przez Ciebie bazy danych.
A tak serio to od wielu, możemy do nich zaliczyć:
- wielkości zbiorów danych użytych w złączeniu
- operatora klucza złączenia (czy jest to równość "=", czy inny operator)
- od tego czy na kluczach złączenia są założonego indeksy
- czy statystyki tabel użytych w złączeniu są aktualne (przeliczone)
Nie ma niestety, jednej doskonałej odpowiedzi na to, który typ złączenia jest najlepszy. Można oczywiście wskazać te z lepszą złożonością obliczeń, jednak w rzeczywistym świecie, każdy przypadek należy rozpatrywać osobno.
Warto analizować plan wykonania zapytania (EXPLAIN / EXPLAIN ANALYZE) i momentami poddawać w wątpliwość przedstawiony przez optymalizator bazy danych sposób łączenia zbiorów.
Duplikatem w danych nazywamy przynajmniej 2 takie wiersze wynikowe, dla których wszystkie kolumny mają takie same wartości.
Przykład:
PRODUCT_NAME | PRODUCT_CATEGORY
Product 1 | Category 1
Product 1 | Category 1
NULL to swego rodzaju znacznik w języku SQL i w relacyjnych bazach danych, wskazujący na fakt, że dana wartość jest nieokreślona (nie ma wartości w danych źródłowych).
Uwaga: NULL nie jest 0 dla liczb czy pustym tekstem dla wartości tekstowych.
Do sposobów radzenia sobie z NULL-em możemy zaliczyć:
- porównania warunkowe IS NULL / IS NOT NULL
- COALESCE() - funkcja, która w przypadku wystąpienia wartości NULL, zamieni ją na podaną wartość np. COALESCE(product_name, 'unknown_product');
- CASE - składnia pozwalająca na zbudowanie warunkowego podejścia do wartości kolumny / funkcji CASE WHEN product_name IS NULL THEN 'unknown_product' ELSE product_name END
Zwróć uwagę, że pomimo faktu, iż COALESCE czy CASE są elementu standardu ISO dla SQL-a, nie muszą być wspierane przez wszystkie bazy danych.
Są też inne alternatywy np. NULLIF(), IFNULL(), ISNULL(), NVL() - których zadaniem jest spełnianie tej samej lub podobnej funkcji tj. uporanie się z wartością nieokreśloną.
Dodanie ograniczenia (CONSTRAINT) typu NOT NULL z dodatkową wartością domyślną DEFAULT ''.
Uwaga: nie zawsze musi to być pożądane rozwiązanie. Czasem wartości NULL są jak naj bardziej OK i takie ograniczenie pozwoli nam sobie z nimi radzić w tabeli, ale przy budowaniu SELECT-a ze złączeniami i tak możemy otrzymać wartości nieokreślone.
CREATE TABLE products_constraint ( product_id SERIAL, product_name varchar(500), product_category varchar(250) NOT NULL DEFAULT '<unknown>', random_desc text );
INSERT INTO products_constraint (product_name,random_desc) VALUES ('Product New Special One', 'Special Constraint Product');
SELECT * FROM products_constraint;
product_id | product_name | product_category | random_desc |
---|---|---|---|
1 | Product New Special One | <unknown> | Special Constraint Product |
Operatora LIKE używamy w sekcji WHERE zapytania, aby znaleźć / odfiltrować wiersze wynikowe według wybranego wzoru w danych tekstowych.
Wynik operatora na danym wierszu to TRUE lub FALSE w zależności od tego czy dany wzór pasuje do wyniku (TRUE) lub nie pasuje (FALSE). W sytuacji odwrotnej, gdy chcemy znaleźć dane niepasujące do wzoru, skorzystamy z operatora NOT LIKE.
Operator LIKE, może korzystać z 2 znaków specjalnych:
- % - znak procenta oznacza wystąpienie 0,1 lub więcej znaków (dowolnych) w szukanym wzorze
- _ - podkreślenie (dolna kreska), oznacza wystąpienie 1 znaku (dowolnego)
Niektóre bazy danych rozszerzają zbiór znaków specjalnych o inne, np.:
- [] - nawiasy kwadratowe - dowolny ze znaków zawartych w nawiasach kwadratowych (SQL Server)
- ^ - “daszek” oznacza zaprzeczenie np. [^cb] wszystkie znaki z wyjątkiem znaku c lub b w wyrazie
- - - myślnik oznacza zakres znaków, np. [a - e]
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15
Lub dodają inne zbliżone operatory np. ILIKE (PostgreSQL) czy SIMILAR (PostgreSQL).
https://www.postgresql.org/docs/12/functions-matching.html#FUNCTIONS-LIKE
SELECT DISTINCT p.product_name as unq_product_name FROM sales s JOIN products p on p.product_id< = s.product_id;
Inne podejście
SELECT count(*) as unq_product_count FROM (SELECT DISTINCT p.product_name FROM sales s JOIN products p on p.product_id = s.product_id ) sq;
SELECT count(distinct p.product_name) as unq_product_count FROM sales s JOIN products p on p.product_id = s.product_id;
Typowy błąd: DISTINCT poza COUNT-em nie uwzględni zduplikowanych wierszy wynikowych, pokazując tylko product_name nie widzimy całości wiersza. COUNT jest robiony na całym zbiorze wynikowym.
SELECT DISTINCT count(p.product_name) as not_so_unq_product_count FROM sales s JOIN products p on p.product_id = s.product_id;
SELECT product_name as unq_product_name FROM (SELECT p.product_name, row_number() over (partition by p.product_name) as rn FROM sales s JOIN products p on p.product_id = s.product_id) sq WHERE rn = 1;
Wykorzystanie funkcji okna (WINDOW FUNCTION - OVER()) + iteratora wiersza row_number().
EXPLAIN ANALYZE SELECT DISTINCT p.product_name as unq_product_name FROM sales s JOIN products p on p.product_id = s.product_id; /* QUERY PLAN HashAggregate (cost=369.00..369.10 rows=10 width=10) (actual time=5.632..5.633 rows=10 loops=1) Group Key: p.product_name -> Hash Join (cost=32.50..344.00 rows=10000 width=10) (actual time=0.386..4.036 rows=9899 loops=1) Hash Cond: (s.product_id = p.product_id) -> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=4) (actual time=0.012..1.236 rows=10000 loops=1) -> Hash (cost=20.00..20.00 rows=1000 width=14) (actual time=0.348..0.348 rows=1000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 55kB -> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=14) (actual time=0.010..0.180 rows=1000 loops=1) Planning time: 0.298 ms Execution time: 5.694 ms */ EXPLAIN ANALYZE SELECT product_name as unq_product_name FROM (SELECT p.product_name, row_number() over (partition by p.product_name) as rn FROM sales s JOIN products p on p.product_id = s.product_id) sq WHERE rn = 1; /* QUERY PLAN Subquery Scan on sq (cost=1008.39..1308.39 rows=50 width=10) (actual time=12.051..18.867 rows=10 loops=1) Filter: (sq.rn = 1) Rows Removed by Filter: 9889 -> WindowAgg (cost=1008.39..1183.39 rows=10000 width=18) (actual time=12.045..17.678 rows=9899 loops=1) -> Sort (cost=1008.39..1033.39 rows=10000 width=10) (actual time=12.039..13.206 rows=9899 loops=1) Sort Key: p.product_name Sort Method: quicksort Memory: 849kB -> Hash Join (cost=32.50..344.00 rows=10000 width=10) (actual time=0.531..4.770 rows=9899 loops=1) Hash Cond: (s.product_id = p.product_id) -> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=4) (actual time=0.009..1.103 rows=10000 loops=1) -> Hash (cost=20.00..20.00 rows=1000 width=14) (actual time=0.493..0.493 rows=1000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 55kB -> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=14) (actual time=0.008..0.225 rows=1000 loops=1) Planning time: 0.342 ms Execution time: 19.018 ms */
SELECT * FROM products p LEFT JOIN sales s on s.product_id = p.product_id WHERE s.id IS NULL;
SELECT * FROM products p WHERE product_id NOT IN (SELECT DISTINCT product_id FROM sales);
Lub z wykorzystaniem składni EXCEPT (MINUS w niektórych bazach danych).
SELECT p.* FROM products p EXCEPT SELECT DISTINCT p.* FROM sales s JOIN products p ON s.product_id = p.product_id;
EXPLAIN ANALYZE SELECT p.* FROM products p LEFT JOIN sales s ON s.product_id = p.product_id WHERE s.id IS NULL;/* Hash Right Join (cost=3.25..35.00 rows=1 width=41) (actual time=0.451..0.453 rows=1 loops=1) Hash Cond: (s.product_id = p.product_id) Filter: (s.id IS NULL) Rows Removed by Filter: 984 -> Seq Scan on sales s (cost=0.00..18.00 rows=1000 width=8) (actual time=0.007..0.117 rows=1000 loops=1) -> Hash (cost=2.00..2.00 rows=100 width=41) (actual time=0.057..0.057 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 16kB -> Seq Scan on products p (cost=0.00..2.00 rows=100 width=41) (actual time=0.014..0.021 rows=100 loops=1) Planning time: 0.425 ms Execution time: 0.515 ms */EXPLAIN ANALYZE SELECT * FROM products p WHERE product_id NOT IN (SELECT DISTINCT product_id FROM sales);/* Seq Scan on products p (cost=21.75..24.00 rows=50 width=41) (actual time=0.555..0.555 rows=1 loops=1) Filter: (NOT (hashed SubPlan 1)) Rows Removed by Filter: 99 SubPlan 1 -> HashAggregate (cost=20.50..21.50 rows=100 width=4) (actual time=0.445..0.464 rows=100 loops=1) Group Key: sales.product_id -> Seq Scan on sales (cost=0.00..18.00 rows=1000 width=4) (actual time=0.008..0.124 rows=1000 loops=1) Planning time: 0.142 ms Execution time: 0.600 ms */EXPLAIN ANALYZE SELECT p.* FROM products p EXCEPT SELECT DISTINCT p.* FROM sales s JOIN products p ON s.product_id = p.product_id;/* HashSetOp Except (cost=0.00..52.00 rows=100 width=1072) (actual time=1.142..1.142 rows=1 loops=1) -> Append (cost=0.00..50.00 rows=200 width=1072) (actual time=0.012..1.034 rows=199 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=0.00..3.00 rows=100 width=45) (actual time=0.011..0.037 rows=100 loops=1) -> Seq Scan on products p (cost=0.00..2.00 rows=100 width=41) (actual time=0.009..0.016 rows=100 loops=1) -> Subquery Scan on "*SELECT* 2" (cost=45.00..47.00 rows=100 width=45) (actual time=0.954..0.977 rows=99 loops=1) -> HashAggregate (cost=45.00..46.00 rows=100 width=41) (actual time=0.953..0.967 rows=99 loops=1) Group Key: p_1.product_id, p_1.product_name, p_1.product_category, p_1.random_desc -> Hash Join (cost=3.25..35.00 rows=1000 width=41) (actual time=0.052..0.515 rows=984 loops=1) Hash Cond: (s.product_id = p_1.product_id) -> Seq Scan on sales s (cost=0.00..18.00 rows=1000 width=4) (actual time=0.006..0.108 rows=1000 loops=1) -> Hash (cost=2.00..2.00 rows=100 width=41) (actual time=0.028..0.028 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 16kB -> Seq Scan on products p_1 (cost=0.00..2.00 rows=100 width=41) (actual time=0.003..0.010 rows=100 loops=1) Planning time: 0.149 ms Execution time: 1.272 ms */
SELECT p.*, COALESCE(s.sales_date, '1900-01-01') as sales_date FROM products p LEFT JOIN sales s on s.product_id = p.product_id ORDER BY s.sales_date NULLS FIRST;
Lub z wykorzystaniem składni CASE WHEN THEN
SELECT p.*, CASE WHEN s.sales_date IS NULL THEN '1900-01-01' ELSE s.sales_date END as sales_date FROM products p LEFT JOIN sales s on s.product_id = p.product_id ORDER BY s.sales_date NULLS FIRST;
SELECT * FROM products WHERE product_name LIKE '%1';
SELECT * FROM products WHERE random_desc LIKE 'a51%';
SELECT * FROM products WHERE product_name LIKE 'Product _';
SELECT COALESCE(p.product_id, s.product_id) as product_id, COALESCE(p.product_name,'<unknown>') as product_name FROM sales s LEFT JOIN products p on p.product_id = s.product_id;
SELECT DISTINCT s.product_id, p.product_name FROM sales s JOIN products p on p.product_id = s.product_id;
WITH selected_product AS ( SELECT random_desc as selected_product_description FROM products WHERE product_name = 'Product 1' LIMIT 1 ) SELECT s.*, sp.selected_product_description, p.random_desc as valid_product_description FROM sales s JOIN selected_product sp on 1=1 LEFT JOIN products p on p.product_id = s.product_id LIMIT 10;
SELECT p.product_name, count(s.id) as amount_sold FROM sales s RIGHT JOIN products p on p.product_id = s.product_id GROUP BY p.product_name ORDER BY p.product_name;
SELECT s.sales_date, s.sales_amount, p.product_name, p.product_category FROM sales s FULL JOIN products p on p.product_id = s.product_id ORDER BY p.product_name;
SELECT s.sales_date, p.* FROM products p LEFT JOIN sales s ON s.product_id = p.product_id ORDER BY s.sales_date DESC NULLS LAST;
SELECT DISTINCT p.product_name FROM sales s JOIN products p ON s.product_id = p.product_id ORDER BY p.product_name;
SELECT s.*, EXTRACT(MONTH FROM s.sales_date) as month_of_sales FROM sales s ORDER BY month_of_sales;
SELECT ROUND(SUM(s.sales_amount),1) as sum_of_sales FROM sales s JOIN products p ON p.product_id = s.product_id AND p.product_name = 'Product 1';
SELECT CEIL(SUM(s.sales_qty)) as total_sales_qty_plus, FLOOR(SUM(s.sales_qty)) as total_sales_qty_minus FROM sales s JOIN products p ON p.product_id = s.product_id AND p.product_name = 'Product 2';
SELECT s.* FROM sales s WHERE s.sales_amount > 5 FETCH FIRST 10 ROWS ONLY;
SELECT s.*, CASE WHEN s.sales_amount > 5 THEN TRUE WHEN s.sales_amount <= 5 THEN FALSE ELSE 'N/A' END as sales_meets_expectations FROM sales s;
SELECT EXTRACT(MONTH FROM s.sales_date), SUM(s.sales_qty), CASE WHEN sum(s.sales_qty) > 1500 THEN 'Y' ELSE 'N' END as exceeded_expected_needs FROM sales s JOIN products p ON p.product_id = s.product_id AND p.product_name = 'Product 1' WHERE EXTRACT(YEAR FROM s.sales_date) = 2020 GROUP BY EXTRACT(MONTH FROM s.sales_date);
SELECT COUNT(DISTINCT p.product_id) FROM products p WHERE NOT EXISTS (SELECT 1 FROM sales s WHERE s.product_id = p.product_id);
SELECT p.* FROM products p WHERE EXISTS (SELECT 1 FROM sales s WHERE s.product_id = p.product_id);
-- opcja 1 WITH products_to_delete AS ( SELECT p.product_id FROM products p LEFT JOIN sales s ON s.product_id = p.product_id WHERE s.id IS NULL ) DELETE FROM products p USING products_to_delete pd WHERE p.product_id = pd.product_id;-- opcja 2 DELETE FROM products p WHERE p.product_id IN (SELECT p.product_id FROM products p LEFT JOIN sales s ON s.product_id = p.product_id WHERE s.id IS NULL);
DELETE FROM products p WHERE p.random_desc LIKE 'c4c%';
SELECT DISTINCT p.product_name FROM sales s JOIN products p ON s.product_id = p.product_id AND EXTRACT(MONTH FROM s.sales_date) = 7 AND EXTRACT(YEAR FROM s.sales_date) = 2020 UNION SELECT DISTINCT p.product_name FROM sales s JOIN products p ON s.product_id = p.product_id AND EXTRACT(MONTH FROM s.sales_date) = 8 AND EXTRACT(YEAR FROM s.sales_date) = 2020
TRUNCATE TABLE sales;-- lub w przypadku istniejących referencji do tabeli SALES TRUNCATE TABLE sales CASCADE;
CREATE TABLE products_old_warehouse AS SELECT * FROM products;TRUNCATE TABLE products_old_warehouse;INSERT INTO products_old_warehouse SELECT * FROM products LIMIT 5;SELECT pod.product_name, pod.product_category FROM products_old_warehouse pod UNION SELECT p.product_name, p.product_category FROM products p LIMIT 10;SELECT pod.product_name, pod.product_category FROM products_old_warehouse pod UNION ALL SELECT p.product_name, p.product_category FROM products p LIMIT 10;
CREATE VIEW sales_with_amount_more_then_10 AS SELECT * FROM sales WHERE sales_amount >10;DROP TABLE sales; //błąd bo istnieje widok zależnyDROP TABLE sales CASCADE; // sukces ale widok sales_with_amount_more_then_10 również zostanie usunięty
CREATE OR REPLACE VIEW products_sold AS SELECT s.sales_date, s.sales_amount, p.product_name, p.product_category FROM sales s JOIN products p on p.product_id = s.product_id;
CREATE MATERIALIZED VIEW products_sold AS SELECT s.sales_date, s.sales_amount, p.product_name, p.product_category FROM sales s JOIN products p on p.product_id = s.product_id;
SELECT product_name, array_agg(DISTINCT product_category) as categories_list FROM products GROUP BY product_name;
SELECT ROUND(SUM(sales_amount)) as total_sales, ROUND(SUM(sales_qty)) as total_qty_sold, p.product_name, ARRAY_AGG(DISTINCT p.product_category) as product_categories_list FROM sales s JOIN products p on p.product_id = s.product_id WHERE EXTRACT(MONTH FROM sales_date) = 8 AND EXTRACT(YEAR FROM sales_date) = 2020 GROUP BY EXTRACT(YEAR FROM sales_date)||''||EXTRACT(MONTH FROM sales_date), p.product_name;
GRANT SELECT ON sales TO user_limited_access;
GRANT ALL PRIVILEGES ON sales TO user_limited_access;
GRANT SELECT (sales_date), UPDATE (discount) ON sales TO user_limited_access;
REVOKE SELECT ON sales FROM user_limited_access;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public_sales FROM user_limited_access;
GRANT limited_access TO user_limited_access;
REVOKE limited_access TO user_limited_access;
DELETE FROM products p WHERE p.product_name = 'Product 1'; ROLLBACK;
INSERT INTO products SELECT * FROM products_history;
SELECT p.*, COALESCE(p.product_name,'')||' - '||COALESCE(p.product_category,'') as product_name_category FROM products p;--- lub z użyciem funkcji CONCATSELECT p.*, CONCAT(COALESCE(p.product_name,''), ' - ', COALESCE(p.product_category,'')) as product_name_category FROM products p;
ORDER BY 2 jak najbardziej zadziała w momencie gdy w zbiorze wynikowym są co najmniej 2 kolumny wtedy zapytanie użyje 2 kolumny do wykonania sortowania.
Natomiast w przykładzie powyżej, baza danych zwróci błąd, gdyż w zbiorze wynikowym nie ma 2 elementu.
SELECT product_id FROM products ORDER BY product_id LIMIT 1;
SELECT product_id FROM products ORDER BY product_id DESC LIMIT 1 OFFSET 1;
SELECT product_name, count(*) FROM products GROUP BY product_name HAVING count(*) > 1;
SELECT p.product_name, SUM(s.sales_amount) FROM sales s JOIN products p ON p.product_id = s.product_id GROUP BY p.product_name HAVING AVG(s.sales_qty) > 5;
SELECT p.product_name, EXTRACT(YEAR FROM s.sales_date) ||''|| EXTRACT(MONTH FROM s.sales_date) as year_month, SUM(s.sales_amount) as total_sales FROM sales s JOIN products p ON p.product_id = s.product_id AND p.product_name = 'Product 1' AND p.product_category = 'Category 1' GROUP BY p.product_name, 2 ORDER BY 2 DESC;
SELECT DISTINCT p.product_name, EXTRACT(YEAR FROM s.sales_date) ||''|| EXTRACT(MONTH FROM s.sales_date) as year_month, SUM(s.sales_amount) OVER (ORDER BY EXTRACT(YEAR FROM s.sales_date) ||''|| EXTRACT(MONTH FROM s.sales_date) desc ) as rolling_sum_of_sales FROM sales s JOIN products p ON p.product_id = s.product_id AND p.product_name = 'Product 1' AND p.product_category = 'Category 1' ORDER BY 2 DESC;
Składnia ORDER BY jest ostatnim elementem "logicznego" planu wykonania zapytania. Stąd gdy w składni SELECT korzystamy z aliasów dla kolumn (bo np. są to kolumny zbudowane na funkcjach) możemy z nich tychże aliasów skorzystać w ORDER BY.
SELECT p.product_name, LEFT(p.random_desc, 5) as desc_small FROM products p ORDER BY desc_small;
Inna ciekawostka, kolejność kolumn wymieniona w części SELECT również może posłużyć w sekcji ORDER BY. Zamiast używać aliasu lub nazwy drugiej kolumny, można użyć miejsca jej wystąpienia w SELECT, czyli cyfry 2.
SELECT p.product_name, LEFT(p.random_desc, 5) as desc_small FROM products p ORDER BY 2;
Tak i Nie 🙂
Domyślnie ORDER BY służy do sortowania całego zbioru wynikowego, więc będzie on ostatnim element składni SELECT po UNION czy UNION ALL.
Można jednak osobno sortować zbiory użyte w UNION / UNION ALL korzystając z podzapytań (czyli oba zbiory traktować jako podzapytania) lub wstawiając sztuczną kolumnę i sortując początkowo po niej.
Przy podejściu ze sztuczną kolumną uważaj na wyniki, UNION, które przy okazji złączenia eliminuje wiersze o tych samych wartościach nie będzie miał zastosowania bo sztuczna kolumna zawsze będzie różna w obrębie zbiorów (czyli na koniec dodatkowo podzapytanie + distinct).
-- ORDER BY ostatnim elementem SELECT p.product_name FROM products p WHERE p.product_category = 'Category 1' UNION SELECT p.product_name FROM products p WHERE p.product_category = 'Category 2' ORDER BY product_name;-- ORDER BY w podzapytaniu SELECT sq.product_name FROM (SELECT p.product_name FROM products p WHERE p.product_category = 'Category 1' ORDER BY p.product_name) sq UNION SELECT p.product_name FROM products p WHERE p.product_category = 'Category 2';-- ORDER BY sztuczna kolumna SELECT p.product_name, 1 as orderCol FROM products p WHERE p.product_category = 'Category 1' UNION SELECT p.product_name, 2 as orderCol FROM products p WHERE p.product_category = 'Category 2' ORDER BY orderCol, product_name;
SELECT *, rank() over (order by sales_total desc) as rank FROM ( SELECT p.product_name, sum(s.sales_amount) as sales_total FROM sales s JOIN products p on p.product_id = s.product_id WHERE extract(month from s.sales_date) = 8 AND extract(year from s.sales_date) = 2020 GROUP BY p.product_name) sq;
SELECT *, COALESCE(lag(sales_total) over (order by sales_total desc) - sales_total ,0) as sales_diff FROM ( SELECT p.product_name, sum(s.sales_amount) as sales_total FROM sales s JOIN products p on p.product_id = s.product_id WHERE extract(month from s.sales_date) = 8 AND extract(year from s.sales_date) = 2020 GROUP BY p.product_name) sq;
SELECT *, first_value(sales_total) over (order by sales_total desc) - sales_total as sales_diff FROM ( SELECT p.product_name, sum(s.sales_amount) as sales_total FROM sales s JOIN products p ON p.product_id = s.product_id WHERE extract(month from s.sales_date) = 8 AND extract(year from s.sales_date) = 2020 GROUP BY p.product_name) sq;
SELECT first_value(sales_total) over (order by sales_total desc) - last_value(sales_total) over () as sales_diff FROM ( SELECT p.product_name, sum(s.sales_amount) as sales_total FROM sales s JOIN products p on p.product_id = s.product_id WHERE extract(month from s.sales_date) = 8 AND extract(year from s.sales_date) = 2020 GROUP BY p.product_name) sq LIMIT 1;
SELECT max(sales_total) - min(sales_total) as sales_diff FROM ( SELECT p.product_name, sum(s.sales_amount) as sales_total FROM sales s JOIN products p on p.product_id = s.product_id WHERE extract(month from s.sales_date) = 8 AND extract(year from s.sales_date) = 2020 GROUP BY p.product_name) sq;
WITH sales_total_august_2020 AS ( SELECT p.product_name, sum(s.sales_amount) as sales_total FROM sales s JOIN products p on p.product_id = s.product_id WHERE extract(month from s.sales_date) = 8 AND extract(year from s.sales_date) = 2020 GROUP BY p.product_name) SELECT mas.max_sales - mis.min_sales as sales_diff FROM (SELECT sales_total as max_sales FROM sales_total_august_2020 ORDER BY sales_total desc LIMIT 1) mas JOIN (SELECT sales_total as min_sales FROM sales_total_august_2020 ORDER BY sales_total asc LIMIT 1) mis ON 1=1;
ALTER TABLE products ADD COLUMN limited_description varchar(250);
ALTER TABLE sales ADD COLUMN sales_over_200k bool DEFAULT FALSE NOT NULL;
ALTER TABLE products ADD COLUMN limited_description varchar(250);UPDATE products SET limited_description = left(random_desc,5);
ALTER TABLE sales DROP COLUMN discount;
CREATE TABLE products_bp AS SELECT * FROM products;ALTER TABLE products ADD COLUMN limited_description varchar(100);ALTER TABLE products DROP COLUMN random_desc;TRUNCATE TABLE products;INSERT INTO products (product_id, product_name, product_category, limited_description) SELECT product_id, product_name, product_category, left(random_desc,5)||right(random_desc,5) as limited_description FROM products_bp;DROP TABLE products_bp;
CREATE INDEX sales_prd_id_idx ON sales USING btree (product_id);
CREATE INDEX CONCURRENTLY sales_prd_id_idx ON sales USING btree (product_id);
CREATE INDEX sales_prd_id_idx ON sales USING btree (product_ID NULLS FIRST);
CREATE INDEX limited_prd_desc_idx ON products (left(random_desc,5));
DROP INDEX IF EXISTS sales_prd_id_idx;
-- BEZ INDEKSU EXPLAIN ANALYZE SELECT * FROM products WHERE product_name = 'Product 1';
QUERY PLAN Seq Scan on products (cost=0.00..10.88 rows=1 width=1068) (actual time=0.014..0.141 rows=104 loops=1) Filter: ((product_name)::text = 'Product 1'::text) Rows Removed by Filter: 896 Planning time: 0.049 ms Execution time: 0.156 ms
-- Z INDEKSEM CREATE INDEX prd_product_name_idx ON products (product_name);EXPLAIN ANALYZE SELECT * FROM products WHERE product_name = 'Product 1';
QUERY PLAN Index Scan using prd_product_name_idx on products (cost=0.27..8.29 rows=1 width=1068) (actual time=0.022..0.057 rows=99 loops=1) Index Cond: ((product_name)::text = 'Product 1'::text) Planning time: 0.248 ms Execution time: 0.083 ms
EXPLAIN ANALYZE SELECT * FROM sales s JOIN products p ON p.product_id = s.product_id;
QUERY PLAN Hash Join (cost=11.57..181.31 rows=1942 width=1148) (actual time=0.274..4.998 rows=9894 loops=1) Hash Cond: (s.product_id = p.product_id) -> Seq Scan on sales s (cost=0.00..129.50 rows=5550 width=80) (actual time=0.021..1.291 rows=10000 loops=1) -> Hash (cost=10.70..10.70 rows=70 width=1068) (actual time=0.224..0.224 rows=1000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 80kB -> Seq Scan on products p (cost=0.00..10.70 rows=70 width=1068) (actual time=0.007..0.091 rows=1000 loops=1) Planning time: 0.213 ms Execution time: 5.598 ms
EXPLAIN ANALYZE SELECT * FROM (SELECT * FROM products ORDER BY product_id) p JOIN (SELECT * FROM sales ORDER BY product_id) s ON s.product_id = p.product_id;
QUERY PLAN Merge Join (cost=909.80..1184.70 rows=10000 width=97) (actual time=3.176..7.206 rows=9894 loops=1) Merge Cond: (products.product_id = sales.product_id) -> Sort (cost=69.83..72.33 rows=1000 width=41) (actual time=0.227..0.239 rows=100 loops=1) Sort Key: products.product_id Sort Method: quicksort Memory: 103kB -> Seq Scan on products (cost=0.00..20.00 rows=1000 width=41) (actual time=0.011..0.104 rows=1000 loops=1) -> Materialize (cost=838.39..988.39 rows=10000 width=56) (actual time=2.896..5.254 rows=10000 loops=1) -> Sort (cost=838.39..863.39 rows=10000 width=56) (actual time=2.892..4.305 rows=10000 loops=1) Sort Key: sales.product_id Sort Method: quicksort Memory: 1166kB -> Seq Scan on sales (cost=0.00..174.00 rows=10000 width=56) (actual time=0.007..0.835 rows=10000 loops=1) Planning time: 0.359 ms Execution time: 7.712 ms
-- BEZ INDEKSU EXPLAIN ANALYZE SELECT * FROM products WHERE product_name <> 'Product 1';
QUERY PLAN Seq Scan on products (cost=0.00..22.50 rows=896 width=41) (actual time=0.022..0.646 rows=896 loops=1) Filter: ((product_name)::text <> 'Product 1'::text) Rows Removed by Filter: 104 Planning time: 0.811 ms Execution time: 0.908 ms
-- Z INDEKSEM CREATE INDEX prd_product_name_idx ON products (product_name);EXPLAIN ANALYZE SELECT * FROM products WHERE product_name <> 'Product 1';
QUERY PLAN Seq Scan on products (cost=0.00..22.50 rows=901 width=41) (actual time=0.016..0.286 rows=901 loops=1) Filter: ((product_name)::text <> 'Product 1'::text) Rows Removed by Filter: 99 Planning time: 0.335 ms Execution time: 0.353 ms
EXPLAIN ANALYZE SELECT * FROM sales s JOIN products p ON s.product_id = p.product_id AND s.sales_amount > 5;
QUERY PLAN Hash Join (cost=11.57..168.37 rows=648 width=1148) (actual time=0.443..7.010 rows=6031 loops=1) Hash Cond: (s.product_id = p.product_id) -> Seq Scan on sales s (cost=0.00..143.38 rows=1850 width=80) (actual time=0.018..3.469 rows=6086 loops=1) Filter: (sales_amount > '5'::numeric) Rows Removed by Filter: 3914 -> Hash (cost=10.70..10.70 rows=70 width=1068) (actual time=0.388..0.388 rows=1000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 80kB -> Seq Scan on products p (cost=0.00..10.70 rows=70 width=1068) (actual time=0.007..0.155 rows=1000 loops=1) Planning time: 0.494 ms Execution time: 7.424 ms
SELECT p.* FROM products p WHERE length(p.random_desc) > 10;
SELECT s.*, p.* FROM sales s JOIN products p ON p.product_id = s.product_id;
SELECT s.*, p.* FROM sales s LEFT JOIN products p ON p.product_id = s.product_id WHERE p.product_ID IS NOT NULL;
EXPLAIN ANALYZE SELECT s.*, p.* FROM sales s JOIN products p ON p.product_id = s.product_id;
QUERY PLAN Hash Join (cost=32.50..344.00 rows=10000 width=97) (actual time=0.260..3.872 rows=9893 loops=1) Hash Cond: (s.product_id = p.product_id) -> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=56) (actual time=0.010..0.789 rows=10000 loops=1) -> Hash (cost=20.00..20.00 rows=1000 width=41) (actual time=0.228..0.228 rows=1000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 80kB -> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=41) (actual time=0.007..0.084 rows=1000 loops=1) Planning time: 0.316 ms Execution time: 4.302 ms
EXPLAIN ANALYZE SELECT s.*, p.* FROM sales s LEFT JOIN products p ON p.product_id = s.product_id WHERE p.product_ID IS NOT NULL;
QUERY PLAN Hash Join (cost=32.50..344.00 rows=10000 width=97) (actual time=0.267..3.655 rows=9893 loops=1) Hash Cond: (s.product_id = p.product_id) -> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=56) (actual time=0.009..0.852 rows=10000 loops=1) -> Hash (cost=20.00..20.00 rows=1000 width=41) (actual time=0.241..0.241 rows=1000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 80kB -> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=41) (actual time=0.006..0.139 rows=1000 loops=1) Filter: (product_id IS NOT NULL) Planning time: 0.084 ms Execution time: 4.072 ms
EXPLAIN ANALYZE SELECT s.*, p.* FROM sales s JOIN products p ON 1=1;
QUERY PLAN Nested Loop (cost=0.00..125196.50 rows=10000000 width=97) (actual time=0.028..1980.528 rows=10000000 loops=1) -> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=56) (actual time=0.013..2.092 rows=10000 loops=1) -> Materialize (cost=0.00..25.00 rows=1000 width=41) (actual time=0.000..0.054 rows=1000 loops=10000) -> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=41) (actual time=0.009..0.097 rows=1000 loops=1) Planning time: 0.322 ms Execution time: 2400.225 ms
Data Craze Weekly
Cotygodniowa porcja wartościowych informacji ze świata danych! Inżynieria danych, analityka, poradniki prosto do Twojej skrzynki.
Zero spamu, 100% wartości.
Administratorem danych osobowych niezbędnych w procesie przetwarzania, w tym podanych powyżej, jest Data Craze - Krzysztof Bury, ul. Piaski 50, 30-199 Rząska, NIP: 7922121365.
Zapisując się na newsletter wyrażasz zgodę na przetwarzanie swoich danych osobowych (imię, e-mail) w ramach działań DataCraze.