Czysty kod SQL

Dzisiaj o tym, jak powinien wyglądać prawidłowo napisany kod SQL. Często osoby początkujące często skupiają się na tym, żeby kod działał, prawie całkiem ignorując kwestię jego czytelności. Może się wydawać, że zwracanie uwagi na czysty kod to fanaberia czy luksus dostępny tylko dla tych co mają za dużo czasu, ale absolutnie tak nie jest – pisanie czystego kodu jest równie ważne jak pisanie działającego kodu. Czysty kod to nie luksus, to wręcz konieczność. Już tłumaczę dlaczego.

Dlaczego?

Autor pythona Guido van Rossum zwrócił uwagę na fakt, że kod jest czytany dużo częściej niż jest pisany. Więcej czasu w pracy z danymi poświęcamy czytaniu i przerabianiu kodu, który już został napisany, a nie pisaniu kodu czy tworzeniu analizy od zera. W artykule „Dlaczego Excel i VBA są słabe?” pisałam dlaczego powinniśmy opierać analizę na kodzie, a nie na programie z graficznym interfejsem – kod gwarantuje, że jesteśmy w stanie odtworzyć analizę krok po kroku. Ale przy źle napisanym kodzie wracamy do punktu wyjścia – taki kod nie pozwala na łatwe odtworzenie, zrozumienie lub zmianę analizy. Same napisanie analizy w kodzie nie wystarczy, aby stworzyć dobrą analizę – kluczowe jest, aby napisać ten kod dobrze. 

Kod powinien być tak napisany, aby jak najbardziej ułatwić przyszłemu czytelnikowi jego zrozumienie. Musimy go napisać w sposób, aby każda kolejna osoba bez konsultacji z nami była w stanie na nim pracować wkładając w to minimalny wysiłek. Jeżeli użyjemy pokrętnej logiki, zapomnimy o dobrych praktykach lub beznadziejnie go sformatujemy, to ciężko będzie zrozumieć lub zmienić kod przyszłemu odbiorcy.

SQL

SQL jest językiem w którym stosunkowo łatwo jest “coś” napisać, ale równie łatwo jest zrobić błąd logiczny i wyciągnąć złe dane. Dlatego pisanie czystego kodu w przypadku SQL jest ultraważne, ponieważ minimalizuje możliwość popełnienia błędów oraz usprawnia ich lokalizowanie i naprawę.

W przypadku SQL nie ma oficjalnego zbioru zasad jak na przykład ma to miejsce w przypadku zasad PEP8 dla pythona. Dlatego poniższe punkty są zlepieniem zasad stosowanych w branży (np. stosowanie wielkich liter dla słów kluczowych), dobrych praktyk (np. stosowanie CTE) lub moich własnych doświadczeń (pisanie komentarzy). Przy każdej zasadzie próbuje wytłumaczyć czemu ona służy i dlaczego warto ją stosować. Nie są to sztywne zasady, które nie podlegają dyskusji, a wręcz część z nich może być trochę kontrowersyjna – w takich przypadkach zapraszam do merytorycznej dyskusji. Niektóre z kwestii zostawiam otwarte, m.in. czy stosować przecinek na końcu czy początku linijki lub czy używać AS przy aliasach, ponieważ te aspekty są (według mnie) mniej istotne.

W jakim stopniu artykuł wyczerpuje temat? Artykuł całkowicie wyczerpuje temat o formatowaniu i poprawnych zasadach pisania SQLa. Artykuł dotyczy tylko języka SQL.

Czego artykuł nie zawiera? Chociaż jeden z poniższych punktów mówi o logice, to artykuł nie jest w stanie wytłumaczyć jak napisać kod prawidłowy logicznie. Jeżeli wasz kod zawiera takie błędy (np. dotyczące wyciągania danych lub tworzenia obliczeń) zastosowanie poniższych zasad samo w sobie nie pomoże w ich naprawieniu, aczkolwiek może pomóc w ich identyfikacji.

Zasady

1. Formatowanie

Wszystkie zasady związane z formatowaniem poprawiają czytelność kodu.

• Każda “sekcja” kodu (SELECT, FROM, WHERE) powinna zacząć się od nowej linijki, podobnie jak każda kolumna w SELECT i WHERE. Umożliwia to szybką możliwość dodania, usunięcia lub zakomentowania danej kolumny.

źle:
SELECT id, first_name, last_name, age, birthdate FROM users WHERE registration_date > '2022-01-01'

SELECT id, first_name, last_name, age, birthdate 
FROM users 
WHERE registration_date > '2022-01-01' AND country = 'PL'

dobrze:
SELECT id
   , first_name
   , last_name
   , age
   , birthdate 
FROM users 
WHERE registration_date > '2022-01-01' 
AND country = 'PL'

• Indentacja (wyrównanie kodu) powinna być spójna w całym kodzie oraz podążać za logiką obliczeń.

źle:
SELECT id
, first_name
     , last_name
 FROM users 
       WHERE registration_date > '2022-01-01' 
      AND country = 'PL'


dobrze:
SELECT id
     , first_name
     , last_name
FROM users 
WHERE registration_date > '2022-01-01'
  AND country = 'PL'

• Słowa kluczowe (SELECT, FROM, JOIN, WHERE, itd.) oraz funkcje (FIRST_VALUE, MAX, itd.) SQL powinny być pisane wielkimi literami, a nazwy kolumn i tabel małymi.

źle:
select first_name
     , last_name
from USERS
where registration_date > '2022-01-01'

dobrze:
SELECT first_name
     , last_name
FROM users 
WHERE registration_date > '2022-01-01' 

2. Logika

• Obliczenia powinny być logiczne, podążać zasadą od ogółu do szczegółu, nie powinno być zbędnych obliczeń lub przeskakiwania między nimi.
• Funkcje powinny być użyte zgodnie z przeznaczeniem, np. zamiast wielu OR powinno się pisać IN.

źle:
SELECT first_name
     , last_name
FROM users 
WHERE country = 'PL'
   OR country = 'NL'

dobrze:
SELECT first_name
     , last_name
FROM users 
WHERE country IN ('PL', 'NL')

• Kod nie powinien zawierać nieużywanych kolumn (SELECT *), tabel, JOINów, obliczeń lub logiki, która nic nie wnosi, np. ORDER BY w CTE lub subquery jest zbędny, ponieważ sortowanie powinno być tylko na końcu.

3. Nazwy zmiennych

• Każda nazwa powinna mieć znaczenie! Nie nazywamy kolumn czy zmiennych a, a1, a2! Każda nazwa powinna tłumaczyć co dana tabela/kolumna reprezentuje.

źle:
WITH a AS (
  SELECT first_name
       , last_name 
  FROM users 
  WHERE country = 'PL'
) ...

dobrze:
WITH pl_users AS (
  SELECT first_name
       , last_name
  FROM users 
  WHERE country = 'PL'
) ...

• Nazwy zmiennych są pisane z małych liter i oddzielone _

źle:
SELECT id AS UserId

dobrze:
SELECT id AS user_id

• Nazwy powinny być jak najmniej dwuznaczne, np. często w każdej tabeli mamy kolumnę ID, która odnosi się do różnych IDs, np. user ID w tabeli users, transaction ID w tabeli transactions. Jeżeli potrzebujemy jedną z kolumn ID powinniśmy nadać jej nową nazwę wskazującą do jakiego ID się odnosi.

źle:
SELECT u.id
     , t.category
FROM users u
JOIN transactions t ON u.id = t.user_id

dobrze:
SELECT u.id AS user_id
     , t.category
FROM users u 
JOIN transactions t ON u.id = t.user_id

• Jeżeli agregujemy dane używając funkcji SUM, COUNT to dobrze jest nazwać nowo powstałą kolumnę używając nazwy agregacji, np. sum_transactions, cnt_transactions, a nie używać słów typu total lub number, które potrafią być mylące.

źle:
SELECT COUNT(id)   AS transactions_number
     , SUM(amount) AS transactions_total
FROM transactions

dobrze:
SELECT COUNT(id)   AS cnt_transactions
     , SUM(amount) AS sum_amount
FROM transactions

• Nazwy kolumn, tabel i CTE powinny być inne.

źle:
WITH users AS (
  SELECT first_name AS user 
  FROM users
  WHERE country = 'PL'
) ...

dobrze:
WITH pl_users AS (
  SELECT first_name
  FROM users
  WHERE country = 'PL'
) ...

• Warto przyjąć konwencję nazw w zespole/firmie i się jej trzymać, czyli nie używać naprzemiennie podobnych słów typu user/client, mail/email, employee/agent, ticket/issue, tylko zawsze używać jednego z nich.

4. Aliasy

Aliasy służą 2 celom: 1) aby odróżnić, która z kolumn pochodzi z której tabeli; 2) aby nie wypisywać za każdym razem nazwy całej tabeli.

• Aliasy powinny być krótkie, konwencją jest używanie pierwszych liter słów tabeli.

źle:
SELECT us.first_name
FROM users us
JOIN transactions trans ON us.id = trans.user_id 

dobrze:
SELECT u.first_name
FROM users u
JOIN transations t ON u.id = t.user_id

• Jeżeli joinujemy ze sobą przynajmniej dwie tabele to aliasy są obowiązkowe. Jasno to pokazuje z której tabeli jest jaka kolumna, więc ułatwia nam szybką modyfikację kodu. W przypadku użycia tylko 1 tabeli w FROM alias nie jest obowiązkowy, ponieważ nie wnosi on nic do kodu, nie spełnia żadnej funkcji – ja w tym przypadku go nie używam. Aliasy stosujemy w każdej sekcji (SELECT, JOIN, WHERE, GROUP BY, itd.).

źle:
SELECT first_name
FROM users u
JOIN transactions t ON us.id = t.user_id
WHERE country = 'PL'

dobrze:
SELECT u.first_name
FROM users u
JOIN transations t ON u.id = t.user_id
WHERE u.country = 'PL'

jedna tabela -> może być brak aliasów:
SELECT first_name
FROM users
WHERE country = 'PL'

• W przypadku SELF JOIN (łączenia ze sobą dwóch tych samych tabel) odchodzimy od zasady krótkich aliasów z pierwszych liter tabel – w takim przypadku dużo bardziej sprawdzają się aliasy „logiczne”, które mówią o intencji łączonych tabel.

źle:
SELECT u1.first_name AS buyer_first_name
     , u2.first_name AS seller_first_name 
FROM transactions t 
JOIN users u1 ON t.buyer_id = u1.id 
JOIN users u2 ON t.seller_id = u2.id

dobrze:
SELECT buyer.first_name  AS buyer_first_name
     , seller.first_name AS seller_first_name
FROM transations t 
JOIN users buyer  ON t.buyer_id = buyer.id 
JOIN users seller ON t.seller_id = seller.id

lub skrócone aliasy pochodzące od słów "buyer" / "seller"
SELECT b.first_name  AS buyer_first_name
     , s.first_name  AS seller_first_name
FROM transations t 
JOIN users b  ON t.buyer_id = b.id 
JOIN users s ON t.seller_id = s.id

5. LEFT JOIN

LEFT JOIN oraz RIGHT JOIN działają tak samo, różnią się tylko tym, którą tabelę używamy w jakiej kolejności. Ogólnie przyjęło się (przynajmniej w moim środowisku), aby za każdym razem używać LEFT JOINa. Taka praktyka przyzwyczaja do pewnej konwencji dzięki której szybciej jesteśmy w stanie przejrzeć kod. Zresztą uważam, że prościej zrozumieć query w której najpierw bierzemy wszystkie rekordy z jednej tabeli, a później dokładamy do niej dodatkowe informacje w formie rekordów z drugiej tabeli, a nie odwrotnie.

źle:
SELECT u.first_name
FROM transactions t
RIGHT JOIN users u ON t.user_id = u.id

dobrze:
SELECT u.first_name
FROM users u
LEFT JOIN transactions t ON u.id = t.user_id 

6. CTE zamiast subquery

O tym czym jest CTE dowiecie się z osobnego artykułu na ten temat. W skrócie CTE powoduje, że kod jest napisany w kolejności w jakiej są wykonywane obliczenia, zatem prościej go zrozumieć niż przy zastosowaniu subquery. Zagnieżdzone subqueries komplikują czytelność, ponieważ nagle w trakcie czytania kodu musimy oderwać się do całkowicie nowych obliczeń. Więcej info i przykłady znajdziecie w artykule.

7. Nazwy kolumn w GROUP BY i ORDER BY

Używanie pełnych nazw kolumn zamiast cyfr w GROUP BY i ORDER BY powoduje, że jedno spojrzenie na te linijki umożliwia nam zrozumienie jak są pogrupowane lub posortowane dane. Przykład poniżej jest dość prosty, więc być może nie jest to zbyt widoczne, ale im dłuższa i bardziej skomplikowana query tym więcej benefitów płynie z tej reguły.

źle:
SELECT t.user_id
     , u.first_name
     , COUNT(t.id) AS cnt_transactions
FROM users u 
JOIN transactions t ON u.id = t.user_id
GROUP BY 1,2 
ORDER BY 2

dobrze:
SELECT t.user_id
     , u.first_name 
     , COUNT(t.id) AS cnt_transactions
FROM users u
JOIN transactions t ON u.id = t.user_id
GROUP BY t.user_id, u.first_name 
ORDER BY u.first_name

8. Komentarze

Ogólnie są dwie szkoły pisania komentarzy. Jedna mówi, że kod sam w sobie powinien być samowytłumaczalny, więc komentarze powinny tylko wskazywać dlaczego coś zostało zrobione, a nie jak. I rzeczywiście pisanie komentarzy, które duplikują to co jest w kodzie może wydawać się zbędne, ale mimo wszystko ja osobiście jestem zwolenniczką większej ilości komentarzy, przede wszystkim takich, które umożliwiają szybką nawigację w długim kodzie. Dlatego komentarze które ja stosuję to:

• Ogólny komentarz na początku: krótkie wytłumaczenie co robi kod i jakie przyjmuje definicje. Dzięki temu nie muszę w ogóle patrzeć na sam SQL aby wiedzieć, czy robi on to czego szukam, jakie dane wyciąga z bazy i jakie obliczenia wykonuje. Dodatkowo, taki komentarz jest bardzo pożyteczny dla ludzi biznesu, którzy nawet nie znając SQL mogą wziąć sobie tę query i je użyć.

/* Code calculates number of transactions per polish user which happened since 1st March 2022 */
 
SELECT t.user_id
     , u.first_name 
     , COUNT(t.id) AS cnt_transactions
FROM users u
JOIN transactions t ON u.id = t.user_id
WHERE u.country = 'PL' 
  AND t.date > '2022-03-01'
GROUP BY t.user_id, u.first_name 
ORDER BY u.first_name

• Metadane: do ogólnego komentarza na początku lubię dodać dodatkowe informacje, typu: autor kodu, numer jiry, link do wizualizacji / dashboardu. Te informacje mogą być przydatne dla kolejnych osób pracujących nad kodem oraz posłużyć do szybkiego wyszukania kodu w lupce githuba (szczególnie w sytuacji gdy repozytorium na githubie nie jest połączone np. z programem do wizualizacji).

/* Code calculates number of transactions per polish user which happened since 1st March 2022.

author: kasiadyl@crappydata.pl
jira: www.jira.com/CD-1
dashboard: www.metabase.com/dashboard/1234
*/
 
SELECT t.user_id
     , u.first_name 
     , COUNT(t.id) AS cnt_transactions
FROM users u
JOIN transactions t ON u.id = t.user_id
WHERE u.country = 'PL' 
  AND t.date > '2022-03-01'
GROUP BY t.user_id, u.first_name 
ORDER BY u.first_name

• Komentarze przy każdym CTE – chyba najbardziej kontrowersyjna część, bo, jak już wspominałam, po co pisać co robi jakie CTE skoro możemy to przeczytać w kodzie. Jednak z doświadczenia wiem, że czasami kod jest długi i robi się dość skomplikowany, dlatego takie komentarze łatwo umożliwiają nawigowanie w całej querce. Przydaje się to przede wszystkim przy niestandardowych rozwiązaniach, np. jak przy szybkim spojrzeniu wydaje się, że jedno z CTE jest zbędne, a wcale nim nie jest.

WITH pl_users AS (     -- select users before counting as there are duplicates in users table
   SELECT DISTINCT id 
   FROM users 
   WHERE country = 'PL'
), 
  pl_users_count AS (  -- count not duplicated users
   SELECT COUNT(id) 
   FROM pl_users
), 
  pl_transactions AS (  -- select transactions before counting as duplicates in the table
   SELECT DISTINCT id 
   FROM transactions t 
   JOIN ...

• Komentarze w kodzie przy sytuacjach dziwnych, nieoczywistych lub zwróceniu uwagi na ważne aspekty, np. jeżeli JOIN jest niestandardowy (zazwyczaj JOINujemy po user_id a w tej sytuacji po innej kolumnie), albo jeden z warunków w WHERE jest niezwykle istotny i nie należy go przez przypadek usunąć

SELECT COUNT(t.id) AS cnt_transactions
FROM transactions t 
JOIN category c ON t.category_name = c.name   /* JOIN on name not ID is intentional 
                                                 as otherwise some records are lost */
WHERE u.country = 'PL' 
  AND t.date > '2022-03-01' 
  AND t.status = 'completed'  -- important condition! 

9. Testowanie

Pracując z danymi jesteśmy odpowiedzialni za jakość analizy, którą dostarczamy, dlatego aby mieć pewność, że wypuszczamy w świat dobre wyniki powinniśmy testować nasz kod. Temat na tyle rozbudowany, że będzie o nim w odrębnym poście 🙂 

10. Git

Finalnie kod powinien być umieszczony na platformie do zarządzania kontrolą wersji (np. githubie) oraz przejść przez proces peer review, czyli powinien zostać sprawdzony przez inne osoby. Składowanie kodu w innej formie jest niepoprawne.

Podsumowanie

Trochę się tego nazbierało, więc w ramach powtórki o czym wspominałam:

1. Formatowanie
2. Logika
3. Nazwy zmiennych
4. Aliasy
5. LEFT JOIN
6. CTE zamiast subquery
7. Nawy kolumn w GROUP BY/ORDER BY
8. Komentarze
9. Testowanie
10. Git

A mój kod SQL wygląda zazwyczaj jak coś w tym stylu:

/* 
This code calculates average (electronic) product cnt with ratio to all products 
from the last 30 days.

author: kasiadyl
link: https://github.com/kdyl/crappydata/blob/main/cte.sql 
*/

WITH all_products AS ( -- count all products per transaction
    SELECT transaction_id
          , month
          , COUNT(product_id) AS count_all_products
    FROM transactions
    WHERE date(created) > date(NOW()) - INTERVAL '30' DAY
    GROUP BY transaction_id, month
)
, electronic_products AS ( -- count electronic products per transaction
    SELECT t.transaction_id
          , COUNT(t.product_id) AS count_electronic_products
    FROM transactions t 
    JOIN category c ON t.category_id = c.category_id
    WHERE c.category_name = 'ELECTRONICS'
      AND date(t.created) > date(NOW()) - INTERVAL '30' DAY
    GROUP BY t.transaction_id
)
, ratio_electronic_to_all AS ( -- calculate ratio of electronic to all products per transaction
    SELECT ap.transaction_id
          , NVL(ep.count_electronic_products, 0) / ap.count_all_products AS ratio
          /* NVL used as if there was no electronic product.
             If ep.count_electronic_products is NULL, it will be replaced with 0 */
    FROM all_products ap
    LEFT JOIN electronic_products ep ON ap.transaction_id = ep.transaction_id
)
SELECT -- at the end calculate averages per month, add number of days
       ap.month
      , AVG(ap.count_all_products)        AS average_product_count
      , AVG(ep.count_electronic_products) AS average_eletronic_product_count
      , AVG(reta.ratio)                   AS average_ratio_electronic_to_al
      , COUNT(c.date)                     AS count_days
FROM all_products ap
LEFT JOIN electronic_products ep       ON ap.transaction_id = ep.transaction_id
LEFT JOIN ratio_electronic_to_all reta ON ap.transaction_id = reta.transaction_id
JOIN calendar c                        ON ap.month = c.month
GROUP BY ap.month

Zapraszam do dyskusji, z jakimi punktami się zgadzacie a z jakimi nie, co byście dodali?

Jeżeli podobają Ci się moje darmowe treści, to będzie mi bardzo miło jak postawisz mi kawę: