Pięć prostych poleceń SQL, aby rozpocząć analizę danych

Zastanawiasz się jak rozpocząć analizę danych w wykorzystaniem SQL? Zapraszam do przewodnika zawierającego 5 prostych poleceń do analizy danych.

1.SQL Polecenie: count(distinct)

Co oznacza komenda SQL count(distinct)

Polecenie SQL count(distinct) służy do zwracania liczby unikatowych wartości w kolumnie lub wyrażeniu.

Jak korzystać z count(distinct)

Użyj funkcji count(distinct), aby zwrócić liczbę całkowicie unikalnych punktów danych, takich jak liczba pracowników, lokalizacje, klienci itp. COUNT( DISTINCT <wyrażenie>)

Na przykład możesz policzyć liczbę różnych miast, w których mieszka klient.

SELECT count(distinct city) as miasto FROM klient

Twój powrót wyglądałby następująco: 1966 (liczba).

Aby być precyzyjnym, otrzymasz kolumnę z jedną wartością:

| CITIES |
|--------|
| 1966   |

Praktyczny przykład: count(distinct)

Analitycy używają count(distinct) przy liczeniu liczby unikalnych odwiedzających, którzy wykazują zachowania na stronie internetowej. Załóżmy na przykład, że mamy tabelę website_intents, która mapuje pliki cookie z pewnym zachowaniem na stronie internetowej:

| COOKIE_ID | IS_VISIT_LANDING_PAGE | IS_VISIT_CHECKOUT | … |
|-----------|-----------------------|-------------------| … |
| abc000    | 1                     | 0                 | … |
| abc001    | 1                     | 1                 | … |
| abc002    | 1                     | 0                 | … |

Oto zapytanie, aby uzyskać liczbę unikalnych plików cookie od użytkowników, którzy dotarli na szczyt ścieżki realizacji zakupu:

SSELECT count(distinct 
                case 
                    when is_visit_checkout = True then cookie_id 
                    else null 
                end) as visited_checkout
FROM website_intents

2.SQL Polecenie: date_trunc()

Do czego służy polecenie SQL date_trunc()

Skraca (obcina) znacznik czasu do określonej ziarnistości, od mikrosekund do tysiąclecia.

Polecenie SQL date_trunc() służy do „obcinania” interwału na podstawie godziny, dnia, tygodnia lub miesiąca i zapewnia bardziej precyzyjny interwał lub znacznik czasu.

Jak korzystać z funkcji date_trunc()

Za pomocą metody date_trunc() można usunąć niepotrzebne informacje ze znacznika czasu lub przedziału czasu.

DATE_TRUNC(ziarnistość; znacznik czasu)

Na przykład możesz skrócić sygnaturę czasową do godziny:

SELECT date_trunc('hour', timestamp '2021-11-4 12:29:05')

Wynik wyglądałby tak: 2021-11-4 12:00:00.

Praktyczny przykład: date_trunc()

Analitycy używają metody date_trunc() do porównywania trendów w wielu miesiącach, tygodniach lub dniach. Dzięki date_trunc() możesz łatwo sprawdzić wskaźniki zachowań w określonym czasie, na przykład zobaczyć, ilu klientów utworzyło konto w zeszłym miesiącu w porównaniu z poprzednimi miesiącami. Załóżmy na przykład, że chcemy uzyskać wszystkie zamówienia utworzone w 2018 r. z tabeli Zamówienia (pochodzącej z przykładowego zestawu danych). Twoje zapytanie może wyglądać następująco:

SELECT count(distinct id) as total_order_2018
FROM ORDERS
WHERE DATE_TRUNC('year', created_at) = timestamp '2018-1-01 00:00:00'

Wynik:

| TOTAL_ORDER_2018 |
|------------------|
| 5834             |

3.SQL Polecenie: coalesce()

Co to jest polecenie SQL coalesce()

Ocenia listy, aby znaleźć wartości inne niż null; to znaczy punkty danych o znanych wartościach.

Polecenie SQL coalesce() jest używane głównie podczas procesów czyszczenia i agregacji danych w celu wypełniania wartości null oraz uczynienia zestawów danych bardziej przyjaznymi dla biznesu i łatwiejszymi do odczytania.

Jak korzystać z metody coalesce()

Użyj metody coalesce(), aby znaleźć lub ustandaryzować informacje inne niż null, ustawiając 2 lub więcej parametrów.

COALESCE(<wyrażenie>; [<wyrażenie>, ...])
Przykład:

SELECT coalesce(null; wartość1; wartość2; wartość3; null)

Wynik: wartość1

Praktyczny przykład coalesce():

Analitycy używają metody coalesce() do czyszczenia i agregowania zestawów danych oraz uczynienia ich bardziej przyjaznymi dla biznesu. Na przykład identyfikowanie pustych pól i zastępowanie ich pustą etykietą, taką jak „brak”. Załóżmy, że masz tabelę klientów z brakującymi numerami telefonów, oznaczonymi „null”.

| CUSTOMER_ID | PHONE_NUMBER | … |
|-------------|--------------| … |
| abc000      | 1111111      | … |
| abc001      | null         | … |
| abc002      | 2222222      | … |

Korzystając z metody coalesce(), oto zapytanie, aby zastąpić wartość null ciągiem „none”:

SELECT customer_id,
      COALESCE(phone_number, 'none') AS phone_number
FROM client

Wynik:

| CUSTOMER_ID | PHONE_NUMBER | … |
|-------------|--------------| … |
| abc000      | 1111111      | … |
| abc001      | none         | … |
| abc002      | 2222222      | … |

4.SQL Polecenie: case

Co to jest polecenie SQL – case

Zwraca wartość, gdy punkty danych w większym zestawie danych spełniają określone warunki.

CASE służy do organizowania danych na podstawie parametrów materialnych, generowania kategorii lub sortowania punktów danych w kategorie lub generowania przydatnych informacji z różnych danych.

Sposób użycia: case

Użyj CASE do wygenerowania wyniku umożliwiającego podjęcie działań na podstawie określonego parametru.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END

Na przykład możesz skategoryzować każdy wynik za pomocą krótkiego komunikatu:

case 
   when score > 9 then 'awesome'
   when score < 5 then 'bad'
   else 'ok' 
end as message

Jeśli nie są spełnione żadne warunki, CASE WHEN da wynik ELSE.

Wynik będzie wyglądał następująco:

| SCORE | MESSAGE |
|-------|---------|
| 10    | awesome |
| 4     | bad     |
| 7     | ok      |

Praktyczny przykład: CASE

CASE może być szczególnie pomocny w analizowaniu lejków, szczególnie w mapowaniu etapów lejka i organizowaniu list klientów na podstawie ich pozycji w lejku.

Na przykład, czy pamiętasz tabelę website_intents, którą zaprezentowaliśmy w przykładzie „Przykład: liczba (odrębna)”? Załóżmy, że mamy tabelę odsłon, która śledzi strony internetowe odwiedzane podczas każdej sesji:

| SESSION_ID | PAGE_URL_PATH | … |
|------------|---------------| … |
| abc000     | /landing-page | … |
| abc001     | /landing-page2| … |
| abc002     | /checkout     | … |

Zapytanie generujące powyższy wynik, może wyglądać następująco:

SELECT id,
    case 
        when(
           page_url_path = '/landing-page.html'
           or page_url_path = '/landing-page2.html'
           or page_url_path = '/landing-page3.html'
        ) then 1 else 0 
    end as is_visit_landing_page,
    case 
        when(
           page_url_path like '/checkout%'
           or page_url_path like '/checkout-new%'
           or page_url_path like '/checkout-enterprise%'
        ) then 1 else 0 
    end as is_visit_checkout,
    ...
FROM pageviews

Co daje:

| SESSION_ID | PAGE_URL_PATH | IS_VISIT_LANDING_PAGE |IS_VISIT_CHECKOUT | … |
|------------|---------------|-----------------------|------------------| … |
| abc000     | /landing-page | 1                     | 0                | … |
| abc001     | /landing-page2| 1                     | 0                | … |
| abc002     | /checkout     | 0                     | 1                | … |

5. SQL: row_number()

Co to jest polecenie SQL row_number()

Porządkuje wiersze w obrębie partycji, przypisując każdemu z nich dokładną pozycję w sekwencji. Zaczyna się od 1 i numeruje wiersze zgodnie z częścią ORDER BY instrukcji window.

Polecenie SQL row_number() służy do szybkiego i precyzyjnego organizowania informacji w zestawie danych na podstawie określonych parametrów.

Należy pamiętać, że row_number() nie jest obsługiwana przez każdą bazę danych.

Sposób użycia: row_number()

Użyj metody row_number(), aby zmienić kolejność listy.

ROW_NUMBER() OVER ( 
	[PARTITION BY partition_column, ... ] 
	ORDER BY sort_column [ASC | DESC], ... 
)

Na przykład możesz zmienić kolejność list kont według czasu ich utworzenia:

SELECT account_created_at,
       row_number() over(
            order by account_created_at
       ) as row
FROM accounts

Wynik:

| ROW | ACCOUNT_CREATED_AT |
|-----|--------------------|
| 1   | 2021-01-14         |
| 2   | 2021-05-09         |
| 3   | 2021-08-22         |

Praktyczny przykład: row_number()

Analitycy używają metody row_number(), aby uporządkować kolejność informacji na listach. Na przykład sortowanie listy informacji o klientach w celu uszeregowania zamówień na podstawie czasu, aby zobaczyć, jak wartość zakupu zmieniała się w czasie. Załóżmy, że masz tabelę informacji o klientach:

| PLAN | ACCOUNT_CREATED_AT | … |
|------|--------------------| … |
| free | 2021-01-14         | … |
| pro  | 2021-02-20         | … |
| free | 2021-05-09         | … |
| pro  | 2021-07-24         | … |
| free | 2021-08-22         | … |

Korzystając z row_number(), możemy uporządkować informacje o kliencie na podstawie czasu utworzenia dla każdej subskrypcji planu:

SELECT plan,
       account_created_at,
       row_number() over(
           partition by plan
           order by account_created_at
       ) as row
FROM accounts

Wynik:

| PLAN | ROW | ACCOUNT_CREATED_AT | … |
|------|-----|--------------------| … |
| free | 1   | 2021-01-14         | … |
| free | 2   | 2021-05-09         | … |
| free | 3   | 2021-08-22         | … |
| pro  | 1   | 2021-02-20         | … |
| pro  | 2   | 2021-07-24         | … |

Pamiętaj, że zastosowanie systemu raportowego daje Ci możliwość przygotowania raportu bez znajomości SQL. Powyższe polecenia mogą być przydatne w bardziej zaawansowanych raportach.

Wpis ten być może pomoże Ci rozpocząć przygodę z SQL.

Zapraszam także do przeczytania innego wpisu dotyczącego najbardziej przydatnych wskazówek do pisania zapytań SQL.

Masz pytania? Zapraszam do kontaktu [email protected].